Присоединить к таблице сумму значений последних ненулевых строк за прошлый периоды

Question

Есть Таблица 1 формата:

Provider	Date
001	01.03.2000
001	01.04.2000

(поставщиков > 1) В один месяц на 1,у поставщика может быть только одна дата, месяца могут пропускаться

и Таблица 2 формата:

Provider	Contract	Date	Sum
001	111111	01.01.2000	100
001	111111	01.03.2000	150
001	222222	01.01.2000	200
001	222222	01.04.2000	250

(> 1 поставщиков, > 1 договоров у каждого поставщика)

Как можно соединить вторую с первой, чтобы был появился у первой результирующие столбец суммы по поставщику и договору. Если дата пропускается, мы берём последнюю известную дату Например:

Provider	Date	Result
001	01.03.2000	150 + 200
001	01.04.2000	150 + 250

По контракту 111111 на 03 мес 150, по 222222 последняя известная сумма 200 По контракту 111111 на 04 мес последняя известная сумма 150, по 222222 250

Answer 1

Пример

provider	date
001	2000-03-01
001	2000-04-01

provider	contract	date	summa
001	111111	2000-01-01	100
001	111111	2000-03-01	150
001	222222	2000-01-01	200
001	222222	2000-04-01	250

Присоединяем к каждой строке таблицы1 все строки данного поставщика из таблицы2 и нумеруем по убыванию даты в пределах каждого договора.

select *
  ,row_number()over(partition by t1.provider,t1.date,t2.contract
                order by t2.date desc)rn
from table1 t1
left join table2 t2 on t2.provider=t1.provider
  and t2.date<=t1.date
order by t1.provider,t1.date,t2.date

provider	date	provider	contract	date	summa	rn
001	2000-03-01	001	111111	2000-01-01	100	2
001	2000-03-01	001	222222	2000-01-01	200	1
001	2000-03-01	001	111111	2000-03-01	150	1
001	2000-04-01	001	222222	2000-01-01	200	2
001	2000-04-01	001	111111	2000-01-01	100	2
001	2000-04-01	001	111111	2000-03-01	150	1
001	2000-04-01	001	222222	2000-04-01	250	1

итоговое суммирование

select provider,date,sum(summa)summa
  ,string_agg(cast(summa as varchar),'+')s
from(
  select t1.provider,t1.date,t2.summa,t2.date rdate
    ,row_number()over(partition by t1.provider,t1.date,t2.contract
                      order by t2.date desc)rn
  from table1 t1
  left join table2 t2 on t2.provider=t1.provider
         and t2.date<=t1.date
)a
where rn=1
group by provider,date
order by provider,date

provider	date	summa	s
001	2000-03-01	350	150+200
001	2000-04-01	400	150+250

fiddle

БЛОГ НА HUSL

Присоединить к таблице сумму значений последних ненулевых строк за прошлый периоды

Ответы (1 шт):