Нарастающий итог - восполнение пропусков
Еще одна типовая задача, которая примыкает к рассмотренной теме, это таблица с разреженными строками, интервалы между которыми требуется заполнить. Пример:
use tempdb
if OBJECT_ID('#t', 'U') is not null drop table #t
create table #t (id int identity primary key, dt date, x int)
insert #t (dt, x) values ('2011-07-02', 5), ('2011-07-05', 8), ('2011-07-07', 15), ('2011-07-08', 17), ('2011-07-11', 21), ('2011-07-14', 24),
('2011-07-15', 27), ('2011-07-20', 28), ('2011-07-23', 31), ('2011-07-24', 36), ('2011-07-25', 39), ('2011-07-28', 42),
('2011-08-02', 45), ('2011-08-03', 50), ('2011-08-10', 56)
select * from #t
Рис.1
Пусть в таблице отражается состояние х некоторой сущности. Таблица содержит записи с теми датами, когда это состояние изменялось. Часто в практических сценариях возникает необходимость иметь колону dt непрерывной. То есть в ней должны содержаться все даты, а не только те, когда происходило изменение колонки х. Если в какую-либо дату значение х не менялось, строка должна содержать значение из предыдущей даты, однако в колонке dt должны содержаться все даты без пропусков. Иными словами, требуется превратить таблицу Рис.1 в
и т.д.
Рис.2
Собственно, этой фигней мы сейчас и займемся по приколу (с) доцент Ищенко. Сергей Яковлевич был человеком во всех отношениях незаурядным, обогатив, в частности, студенческую нетленку многими замечательными фразами. Например, (вдоволь поизмывавшись и согнав семь потов, задумчиво) зачет чтоль тебе поставить ради хохмы?
Совершенно очевидно, как ее решать. Каждую строчку надо продублировать столько раз, сколько дней пропущено до следуюшей строчки, дописав к ней ее порядковый номер = на сколько она отступает от оригинальной. Где-то так:
Рис.3
После чего остается прибавить крайнюю правую колонку к крайней левой, и дело в шляпе. Осталось понять, как нам получить Рис.3. Для начала надо научиться генерить табличку с числами от 0 до n. Это просто:
declare @n int = 10;
with cte(i) as (select 0 union all select i + 1 from cte where i + 1 <= @n)
select * from cte
Рис.4
Кроме того, для получения разницы дат между текущей строчкой и следующей, нужно дату из следующей поставить рядом с текущей. Это еще проще:
with cte as (select id, dt, lead(dt, 1) over (order by dt) next_dt from #t)
select * from cte where next_dt is not null order by dt
Рис.5
Идея заключается в том, чтобы для каждой записи сгенерить рекордсет Рис.4 из стольких строк, сколько величина гэпа до следующей записи, и перемножить каждую запись на этот рекордсет.
К сожалению, CTE не может быть динамический, чтобы его можно было засунуть в cross apply:
with
cte1 as (select id, dt, lead(dt, 1) over (order by dt) next_dt from #t),
cte2(i) as (select 1 union all select i + 1 from cte2 where i + 1 <= (select datediff(d, dt, next_dt) from cte1))
select * from cte1 cross apply cte2
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Скрипт 1
А между тем, рекурсия возможна только в СТЕ:
with
cte1 as (select id, dt, lead(dt, 1) over (order by dt) next_dt from #t)
select * from cte1 cross apply (select 1 as i union all select i + 1 from t
where i + 1 <= (select datediff(d, dt, next_dt) from cte1)) t
Msg 208, Level 16, State 1, Line 1
Invalid object name 't'.
Скрипт 2
Раз так, остается определить максимальный размер дырки:
with cte as (select id, dt, lead(dt, 1) over (order by dt) next_dt from #t)
select max(datediff(d, dt, next_dt)) from cte
Рис.6
и нагенерить прогрессию от 1 до ее длины. Увы, функция MAX(),как и остальные агрегатные функции, запрещена в рекурсивной части CTE:
with
cte1 as (select id, dt, lead(dt, 1) over (order by dt) next_dt from #t),
cte2(i) as (select 1 union all select i + 1 from cte2 where i + 1 <= (select max(datediff(d, dt, next_dt)) from cte1))
select * from cte2
Msg 467, Level 16, State 1, Line 3
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'cte2'.
Скрипт 3
При этом перенести MAX() в первый запрос не удастся:
select max(datediff(d, dt, lead(dt, 1) over (order by dt))) from #t
Msg 4109, Level 15, State 1, Line 1
Windowed functions cannot be used in the context of another windowed function or aggregate.
Скрипт 4
Кто мне объяснит, почему наш мир столь несовершенен?
Как бы то ни было, это не повод опускать руки, т.к. никто не запрещает ввести промежуточный СТЕ:
with
cte1 as (select id, dt, lead(dt, 1) over (order by dt) next_dt from #t),
cte2 as (select max(datediff(d, dt, next_dt)) m from cte1),
cte3(i) as (select 1 union all select i + 1 from cte3 where i + 1 <= (select m from cte2))
select * from cte3
Рис.7
И, таким образом, фишка поперла:
with
cte1 as (select id, dt, lead(dt, 1) over (order by dt) next_dt from #t),
cte2 as (select *, datediff(d, dt, next_dt) gap from cte1),
cte3 as (select max(gap) m from cte2),
cte4(i) as (select 1 union all select i + 1 from cte4 where i + 1 <= (select m from cte3))
select * from cte2 cross apply cte4 order by dt, i
Рис.8
Сейчас cross apply мало чем отличается от кросс-джойна. Исправим это дело, параметризовав его значением из левой таблицы:
with
cte1 as (select id, dt, lead(dt, 1) over (order by dt) next_dt from #t),
cte2 as (select *, datediff(d, dt, next_dt) gap from cte1),
cte3 as (select max(gap) m from cte2),
cte4(i) as (select 1 union all select i + 1 from cte4 where i + 1 <= (select m from cte3))
select * from cte2 cross apply (select * from cte4 where i < cte2.gap) t order by dt, i
Рис.9
Окончательно имеем:
with
cte1 as (select id, dt, lead(dt, 1) over (order by dt) next_dt, x from #t),
cte2 as (select *, datediff(d, dt, next_dt) gap from cte1),
cte3 as (select max(gap) m from cte2),
cte4(i) as (select 1 union all select i + 1 from cte4 where i + 1 <= (select m from cte3))
select dateadd(d, t.i, dt), x from cte2 cross apply (select * from cte4 where i < cte2.gap) t order by 1
Рис.10
Это те записи, которые полагается вставить. Если желательно увидеть результирующую таблицу целиком, то select 1 в рекурсивном cte4 следует заменить на select 0.
Рис.11
Домашнее задание.
Решить аналогичную задачу, когда недостающие строки требуется заполнить не сквозняком вдоль всей таблицы, а внутри промежуточных групп, например, по категориям товаров.
Алексей Шуленин
Comments
Anonymous
January 01, 2003
option(maxrecursion 0)Anonymous
September 26, 2011
А что делать, когда select max(gap) m from cte2 (т.е. интервал без значений между датами) больше 100? The maximum recursion 100 has been exhausted before statement completion. - пишет SQL Server...