Всем привет! подскажите, пожалуйста, по оконным функциям. есть таблица клиентов

Question

Всем привет! подскажите, пожалуйста, по оконным функциям. есть таблица клиентов

и их расходов. хочу посчитать скользящую сумму убытков за 12 месяцев (исходные данные по месяцам) в прошлое по состоянию на начало каждого последующего месяца. и это ладно, over/partition by/rows preceding почти решают вопрос (по кол-ву строк в прошлое), но есть сложность: не у всех клиентов были расходы каждый месяц, соответственно в таком случае строка по месяцу без активности для клиента отсутствует (например, идет январь 2020, а потом сразу июль 2020). как с этим можно совладать?
сейчас часть скрипта, где создается окно выглядит так:
sum([LOSS_SUM]) over (partition by ([CLIENT]) order by [DATE] rows between 11 preceding and current row) as [LOSS_SUM_BACK_12M]

#programming #russian #sql

0

11.08.2021

7 ответов

10 просмотров

Александр Ройтман

Примерно так declare @t table ([client] int, [date] date, loss_summ money); insert into @t values (1, '20210101', 1), (1, '20210401', 1), (1, '20210501', 1), (1, '20210601', 1), (1, '20211201', 1); with s as ( select client, date, sum(loss_summ) over (partition by client order by date) as loss_sum__rt, datediff(month, date, lead(date) over (partition by client order by date)) as n from @t ) select a.client, a.date, case when a.date = s.date then s.loss_sum__rt end from s cross apply ( select top (isnull(s.n, 1)) s.client, dateadd(month, row_number() over (order by 1/0) - 1, s.date) from master.dbo.spt_values ) a(client, date); Либо заведите постоянную таблицу-календарь

0

11.08.2021

:D L Автор вопроса

ух ты, спасибо вам большое! 👍

0

11.08.2021

Александр Ройтман

Слегка накосячил. Вот так будет правильнее with s as ( select client, date, loss_summ, sum(loss_summ) over (partition by client order by date) as loss_sum__rt from @t ) select a.client, b.date, s.loss_sum__rt from (select client, min(date) from @t group by client) a (client, date_min) cross apply (select top (12) dateadd(month, row_number() over (order by 1/0) - 1, a.date_min) from master.dbo.spt_values) b(date) left join s on s.client = a.client and s.date = b.date;

0

11.08.2021

:D L Автор вопроса

благодарю! сейчас разберусь, как это работает)

0

11.08.2021

Александр Ройтман

Вариант №3, окончательный 😊 with s as ( select client, date, loss_summ, sum(loss_summ) over (partition by client order by date) as loss_sum__rt from @t ) select a.client, b.date, s.loss_sum__rt from (select client, min(date) from @t group by client) a (client, date_min) cross apply ( select dateadd(month, t.n, a.date_min) from (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)) t(n) ) b(date) left join s on s.client = a.client and s.date = b.date;

0

11.08.2021

:D L Автор вопроса

Стикер

0

11.08.2021

Konstantin Taranov · Accepted Answer

Konstantin Taranov

Сделайте cross join с месяцами чтобы для тех у кого не все месяца добавились строки с нулём

0

11.08.2021

169 похожих чатов

Всем привет! подскажите, пожалуйста, по оконным функциям. есть таблица клиентов

7 ответов

Похожие вопросы