Oracle SQL

Фильтр по тегу: join


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

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

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



как размножить строки... cross join и connect by

Чалышев Максим Михайлович
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA

итак , нам необходимо размножить строки в некоторой таблице, на основе заданного запроса
пусть это будет следующий запрос
select object_name from all_objects where object_type = 'CLUSTER'

этот запрос вернет нам 10 строк , по условиям задачи необходимо повторить эти 10 строк по 10 раз

создадим таблицу с размноженными строками , здесь на поможет конструкция cross join и оператор иерархии connect by
вот скрипт создания такой таблицы:
 create table copy1 as select * from 
 (select level lnl from dual connect by level < 11) 
cross join (select object_name from all_objects where object_type = 'CLUSTER') 

где level < 11 количество вставляемых строк

подсчитаем количество строк в данной таблице
 select count(*) from copy1

-- 100 строк, все верно

продемонстрируем работу оператора insert применительно к данной задаче
очистим таблицу
delete from copy1;

произведем вставку
insert into copy1(lnl, object_name) 
select * from (select level lnl from dual connect by level < 11) cross join 
(select object_name from all_objects where object_type = 'CLUSTER')


посмотрим, что получилось
select object_name,count(1) from copy1 group by object_name

успех...
добавлено: 13 мар 17 просмотры: 4336, комментарии: 0