Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Еще одна типовая задача, которая примыкает к рассмотренной теме, это таблица с разреженными строками, интервалы между которыми требуется заполнить. Пример:
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...