Версия для печати

На главную страницу

Пропущенные даты

На одном форуме был задан вопрос о том, как вывести все даты, отсутствующие в некоторой последовательности дат.

Например, в списке
2006-11-16
2006-11-18
2006-11-19
2006-11-23

отсутствуют даты: 2006-11-17
2006-11-20
2006-11-21
2006-11-22

Причем задачу нужно было решить непроцедурно, т.е. одним запросом, что естественным образом вписывается в идеологию нашего сайта. В рамках SQL Server 2000 решить задачу одним запросом в общей постановке не представляется возможным, т.к. для этого потребуется генерация последовательности дат, которая всегда будет иметь фиксированную границу. А для решения задачи в общей постановке граница последовательности должна определяться некоторым (в данном случае максимальным) значением даты, которое имеет характер переменной, т.к. меняется со временем.

Однако в SQL Server 2005 появилась возможность писать рекурсивные запросы с помощью CTE (общие табличные выражения). Кстати говоря, рекурсивные запросы уже стандартизованы, так что их использование не является лишь особенностью конкретной реализации, хотя имеются различия в синтаксисе.

Итак, я решил написать CTE-запрос, чтобы ответить на вопрос и приобрести некоторый практический опыт.

Создадим временную табличку с тестовыми данными:

CREATE TABLE #Tdates(DT DATETIME)
INSERT INTO #Tdates
SELECT '20061116' AS DT
UNION ALL
SELECT '20061118'
UNION ALL
SELECT '20061119'
UNION ALL
SELECT '20061123'

Идея решения очень проста:

- С помощью рекурсивного CTE-запроса строим последовательность дат, т.е. ряд значений с шагом 1 от минимальной до максимальной даты из таблицы #Tdates. Это может выглядеть следующим образом:

WITH t(d,lim) AS
(
SELECT MIN(dt) d, MAX(dt) lim FROM #Tdates
UNION ALL
SELECT t.d+1,lim FROM t
WHERE d < lim
)

Ограничение d < lim необходимо, чтобы рекурсия не была бесконечной. Кстати говоря, по умолчанию используется 100 итераций, т.е. если предел не будет указан или результат не будет получен за 100 итераций, то появится следующее сообщение:

Чтобы гарантировать достижение указанного предела, можно задать неограниченное число итераций с помощью хинта:

option (maxrecursion 0)

- Теперь нам осталось выполнить реляционную операцию разности множества дат в сгенерированной последовательности и в исходной таблице. Задача не представляющая никакой сложности, однако, ввиду обилия методов реализации этой операции, захотелось оценить их производительность.

Итак,
1. Использование предиката NOT IN

WITH t(d,lim) AS
(
SELECT MIN(dt) d, MAX(dt) lim FROM #Tdates
UNION ALL
SELECT t.d+1,lim FROM t
WHERE d < lim
)
SELECT t.d FROM t
WHERE t.d NOT IN (SELECT dt FROM #Tdates)
OPTION (MAXRECURSION 0)

2. Использование предиката NOT EXISTS

WITH t(d,lim) AS
(
SELECT MIN(dt) d, MAX(dt) lim FROM #Tdates
UNION ALL
SELECT t.d+1,lim FROM t
WHERE d < lim
)
SELECT t.d FROM t
WHERE NOT EXISTS (SELECT * FROM #Tdates WHERE dt=t.d)
OPTION (MAXRECURSION 0)

3. Использование внешнего соединения

WITH t(d,lim) AS
(
SELECT MIN(dt) d, MAX(dt) lim FROM #Tdates
UNION ALL
SELECT t.d+1,lim FROM t
WHERE d < lim
)
SELECT t.d FROM t LEFT JOIN #Tdates t1
ON t.d=t1.dt
WHERE t1.dt IS NULL
OPTION (MAXRECURSION 0)

4. Использование явной операции разности (EXCEPT) из стандарта SQL-92, появившейся в SQL Server только в версии 2005.

WITH t(d,lim) AS
(
SELECT MIN(dt) d, MAX(dt) lim FROM #Tdates
UNION ALL
SELECT t.d+1,lim FROM t
WHERE d < lim
)
SELECT t.d FROM t
EXCEPT
SELECT dt FROM #Tdates T1
OPTION (MAXRECURSION 0)

Я уже приготовился к тому, что на таком незначительном объеме данных все алгоритмы дадут примерно одинаковый результат (по фактическому плану выполнения). Так оно и оказалось, но только для первых трех "классических" методов. Если выполнить сразу пакет из вышеприведенных 4 запросов, то почти половина времени уйдет на выполнения последнего запроса.

Я внимательно не анализировал его план, но обратил внимание на то, что большая часть времени здесь ушла на выполнение сортировки. Оно и понятно, если вспомнить о том, что эта операция должна оставить только уникальные строки, т.е. удалить дубликаты, что выполняется с помощью сортировки. Первое, что пришло в голову, написать EXCEPT ALL. Однако оказалось, что такая конструкция пока не поддерживается.

Вывод, который я для себя сделал, заключается в том, что не следует использовать EXCEPT, если вам попутно не нужно устранить дубликаты.

Напомню, что если вы не являетесь участником третьего этапа, то можете писать CTE-запросы на сайте, поставив флажок "Без проверки" на странице с упражнениями на SELECT.

На главную страницу

  Версия для печати