Платежи с нарастающим итогом решение задачи

добавлено: 08 дек 19
понравилось:0
просмотров: 2639
комментов: 3

теги:

Автор: Myp3_u_K

Одна из студенток просила помочь с задачей , задача интересная, поэтому предлагаю вашему вниманию.

Необходимо вывести все месяцы текущего года, с указанием клиента и суммы его платежей, отдельной колонкой
вывести сумму всех платежей клиента с начала года (нарастающий итог по клиенту с начала года). Если в каком-то
месяце клиент не платил, вывести 0


я так решил
create table pay1(userid number, dt date, paysum number);
insert into pay1 values(1, sysdate-10, 22);
insert into pay1 values(2, sysdate-20, 2);
insert into pay1 values(2, sysdate-90, 72);
insert into pay1 values(1, sysdate-50, 32);
insert into pay1 values(1, sysdate-50, 90);
insert into pay1 values(2, sysdate-70, 52);
insert into pay1 values(1, sysdate-50, 92);
insert into pay1 values(1, sysdate-10, 32);
insert into pay1 values(1, sysdate-90, 90);
insert into pay1 values(2, sysdate-90, 52);
insert into pay1 values(1, sysdate-150, 92);
insert into pay1 values(1, sysdate-110, 32);
 


Для нарастающего итога используется аналитическия функция SUM, с под запросом, нулевые значения создаем подзапросом с connect by level интервал дат задаем в конструкции connect by
в данном случае от 2019-05-01 до 2020-01-01

select userid,mn,yy,passum , sum(passum) over (partition by userid order by yy,mn) from ( 
    select userid , extract(month from dt) mn, extract(year from dt) yy, sum(pasum) passum from (
    select decode( userid,null, userid1, userid)  userid  , decode(dt,null,dt1,dt) dt , decode(paysum,null,0,paysum) pasum from pay1
     right join
    (
        select dt1,userid1 from
        (
            (select date'2019-05-01'+level-1 as dt1 from dual connect by level <= date'2020-01-01'-date'2019-05-01') dt
            cross join (select distinct userid userid1 from pay1) pay
        )) payd 
    on trunc(dt) = trunc(dt1) and userid = userid1   
    ) mzt group by userid, extract(month from dt), extract(year from dt) order by yy,mn
    ) tt1

Комментарии


  • Осмелюсь свой вариант через MODEL: http://sqlfiddle.com/#!4/7d81f/22.

  • отличное решение

  • Спасибо :) Но можно сделать еще лучше если RULES заменить на:
    rules upsert all iterate(8) (
    passum[add_months(date'2019-05-01',
    iteration_number)] = nvl(passum[cv()], 0),
    totsum[add_months(date'2019-05-01',
    iteration_number)] = nvl(totsum[cv()], 0)
    )



Необходимо войти на сайт, чтобы оставлять комментарии