Блог

У меня есть таблица TRANS, которая содержит следующие записи:

 TRANS_ID TRANS_DT     QTY    
1        01-Aug-2020  5
1        01-Aug-2020  1
1        03-Aug-2020  2
2        02-Aug-2020  1
  

Ожидаемый результат:

 TRANS_ID TRANS_DT     BEGBAL TOTAL END_BAL 
1        01-Aug-2020  0      6     6
1        02-Aug-2020  6      0     6      
1        03-Aug-2020  6      2     8
2        01-Aug-2020  0      0     0
2        02-Aug-2020  0      1     1      
2        03-Aug-2020  1      0     1
  

Каждый trans_id начинается с начального баланса, равного 0 (01 августа 2020 года). Для последующих дней начальный баланс является конечным балансом предыдущего дня и так далее.
Я могу создать блок PL / SQL для создания выходных данных. Возможно ли получить выходные данные в 1 инструкции SQL?

Спасибо.

Попробуйте следующий скрипт с использованием CTE-

Демонстрация здесь

 WITH CTE 
AS
(
    SELECT DISTINCT A.TRANS_ID,B.TRANS_DT
    FROM your_table A
    CROSS JOIN (SELECT DISTINCT TRANS_DT FROM your_table) B

),
CTE2
AS
(
    SELECT C.TRANS_ID,C.TRANS_DT,SUM(D.QTY) QTY
    FROM CTE C
    LEFT JOIN your_table D 
        ON C.TRANS_ID = D.TRANS_ID 
        AND C.TRANS_DT = D.TRANS_DT
    GROUP BY C.TRANS_ID,C.TRANS_DT
    ORDER BY C.TRANS_ID,C.TRANS_DT
)

SELECT F.TRANS_ID,F.TRANS_DT,
(    
    SELECT COALESCE (SUM(QTY), 0) FROM CTE2 E 
    WHERE E.TRANS_ID = F.TRANS_ID AND E.TRANS_DT < F.TRANS_DT
) BEGBAL,
(    
    SELECT COALESCE (SUM(QTY), 0) FROM CTE2 E 
    WHERE E.TRANS_ID = F.TRANS_ID AND E.TRANS_DT = F.TRANS_DT
) TOTAL ,
(    
    SELECT COALESCE (SUM(QTY), 0) FROM CTE2 E 
    WHERE E.TRANS_ID = F.TRANS_ID AND E.TRANS_DT <= F.TRANS_DT
) END_BAL 
FROM CTE2 F
  

Вы также можете сделать вот так (я бы предположил, что это немного быстрее): Демо

 with 
dt_between as (
  select mindt   level - 1 as trans_dt
  from (select min(trans_dt) as mindt, max(trans_dt) as maxdt from t)
  connect by level <= maxdt - mindt   1
),
dt_for_trans_id as (
  select *
  from dt_between, (select distinct trans_id from t)
),
qty_change as (
  select distinct trans_id, trans_dt,
    sum(qty) over (partition by trans_id, trans_dt) as total,
    sum(qty) over (partition by trans_id order by trans_dt) as end_bal
  from t
  right outer join dt_for_trans_id using (trans_id, trans_dt)
)
select 
  trans_id,
  to_char(trans_dt, 'DD-Mon-YYYY') as trans_dt,
  nvl(lag(end_bal) over (partition by trans_id order by trans_dt), 0) as beg_bal, 
  nvl(total, 0) as total,
  nvl(end_bal, 0) as end_bal
from qty_change q
order by trans_id, trans_dt
  

dt_between возвращает все дни между min(trans_dt) и max(trans_dt) в ваших данных.

dt_for_trans_id возвращает все эти дни для каждого trans_id в ваших данных.

qty_change находит разницу для каждого дня (которая есть TOTAL в вашем примере) и совокупную сумму за все дни (которая есть END_BAL в вашем примере).

Основной выбор берет END_BAL из предыдущего дня и вызывает его BEG_BAL , он также выполняет некоторое форматирование конечного вывода.

Прежде всего, вам нужно сгенерировать даты, затем вам нужно объединить ваши значения с помощью TRANS_DT, а затем слева объединить ваши агрегированные данные с датами. Самый простой способ получить требуемые суммы — использовать аналитические оконные функции:

 with dates(dt) as ( -- generating dates between min(TRANS_DT) and max(TRANS_DT) from TRANS
   select min(trans_dt) from trans
   union all
   select dt 1 from dates
   where dt 1<=(select max(trans_dt) from trans)
)
,trans_agg as ( -- aggregating QTY in TRANS
   select TRANS_ID,TRANS_DT,sum(QTY) as QTY
   from trans
   group by TRANS_ID,TRANS_DT
)
select -- using left join partition by to get data on daily basis for each trans_id:
   dt,
   trans_id,
   nvl(sum(qty) over(partition by trans_id order by dates.dt range between unbounded preceding and 1 preceding),0) as BEGBAL,
   nvl(qty,0) as TOTAL,
   nvl(sum(qty) over(partition by trans_id order by dates.dt),0) as END_BAL 
from dates
     left join trans_agg tr
          partition by (trans_id)
          on tr.trans_dt=dates.dt;
  

Полный пример с образцами данных:

 alter session set nls_date_format='dd-mon-yyyy';
with trans(TRANS_ID,TRANS_DT,QTY) as (
select 1,to_date('01-Aug-2020'),  5 from dual union all
select 1,to_date('01-Aug-2020'),  1 from dual union all
select 1,to_date('03-Aug-2020'),  2 from dual union all
select 2,to_date('02-Aug-2020'),  1 from dual
)
,dates(dt) as ( -- generating dates between min(TRANS_DT) and max(TRANS_DT) from TRANS
   select min(trans_dt) from trans
   union all
   select dt 1 from dates
   where dt 1<=(select max(trans_dt) from trans)
)
,trans_agg as ( -- aggregating QTY in TRANS
   select TRANS_ID,TRANS_DT,sum(QTY) as QTY
   from trans
   group by TRANS_ID,TRANS_DT
)
select 
   dt,
   trans_id,
   nvl(sum(qty) over(partition by trans_id order by dates.dt range between unbounded preceding and 1 preceding),0) as BEGBAL,
   nvl(qty,0) as TOTAL,
   nvl(sum(qty) over(partition by trans_id order by dates.dt),0) as END_BAL 
from dates
     left join trans_agg tr
          partition by (trans_id)
          on tr.trans_dt=dates.dt;
  

Вы можете использовать рекурсивный запрос для генерации общего диапазона дат, cross join его со списком различных tran_id , затем привести таблицу с left join . Последний шаг — агрегирование и оконные функции:

 with all_dates (trans_dt, max_dt) as (
    select min(trans_dt), max(trans_dt) from trans group by trans_id
    union all
    select trans_dt   interval '1' day, max_dt from all_dates where trans_dt < max_dt
)
select
    i.trans_id,
    d.trans_dt,
    coalesce(sum(sum(t.qty)) over(partition by i.trans_id order by d.trans_dt), 0) - coalesce(sum(t.qty), 0) begbal,
    coalesce(sum(t.qty), 0) total,
    coalesce(sum(sum(t.qty)) over(partition by i.trans_id order by d.trans_dt), 0) endbal
from all_dates d
cross join (select distinct trans_id from trans) i
left join trans t on t.trans_id = i.trans_id and t.trans_dt = d.trans_dt
group by i.trans_id, d.trans_dt
order by i.trans_id, d.trans_dt