Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 06 December 2019 08:17:18


www.sql-ex.ru
Skip Navigation Links  

 

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

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

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

Моисеенко С.И.

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

Например, в списке
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 итераций, то появится следующее сообщение:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
(Выполнение оператора прервано. Максимальное число итераций 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.

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

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


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.