Информация

Последние записи

Теги


Блоги


Записи из всех блогов на Sql.ru с тегом: dual


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

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

Необходимо вывести все месяцы текущего года, с указанием клиента и суммы его платежей, отдельной колонкой
вывести сумму всех платежей клиента с начала года (нарастающий итог по клиенту с начала года). Если в каком-то
месяце клиент не платил, вывести 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
автор: Myp3_u_K добавлено: 08 дек 19 просмотры: 6409, комментарии: 4