Обзор решений с использованием оконных функций T-SQL
157Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Обзор решений с использованием оконных функций
Исходник базы данныхСтатьи в этом разделе рассказывают об оконных функциях и их оптимизации. Материал очень технический, и хотя я считаю его очень интересным, я понимаю, почему некоторые находят его скучным. Многим намного интереснее читать о применении этих функций к решению практических задач - этому посвящен последний раздел. Увидев оконные функции в действии, вы по-настоящему поймете их ценность. Но как убедить вас потратить время на изучение технических деталей и потерпеть до конца, оставив «на сладкое» самую интересную часть. Что, если я прямо сейчас вкратце покажу решения задач с применением оконных функций?
Я буду решать задачу запроса данных в таблице, содержащей в одном из столбцов последовательность значений, и определения диапазонов последовательных значений. Эта задача называется задачей обнаружения диапазонов. Последовательность может быть числовой, временной (что случается чаще) или относиться к другому типу данных, который поддерживает полное упорядочение. Последовательность может содержать уникальные значения или разрешать дубликаты. Под интервалом подразумевается любой фиксированный интервал, который соответствует типу столбца (например целое «1», целое «7», временной интервал в один день, в две недели и т.д.) Позже я рассмотрю разные варианты этой задачи. Здесь я покажу простой случай, который позволит понять, как это работает, - числовая последовательность с целым «1» в качестве интервала.
Следующий код генерирует пример данных для этой задачи:
SET NOCOUNT ON;
use TSQL2012;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
col1 INT NOT NULL
CONSTRAINT PK_T1 PRIMARY KEY
);
INSERT INTO dbo.T1(col1)
VALUES(2),(3),(11),(12),(13),(27),(33),(34),(35),(42);
GOКак видите, в последовательности значений в столбце col1 таблицы T1 есть пробелы. Задача состоит в определении диапазонов с последовательными значениями (их еще называют островками) и возвращении начала и конца каждого островка. Вот как должен выглядеть результат:
start_range end_range --------------------------- 2 3 11 13 27 27 33 35 42 42
Если вам непонятна практическая ценность этой задачи, скажу, что есть много примеров применения в производственной среде. Вот несколько примеров: создание отчетов о доступности, определение периодов активности (например, продаж), определение последовательных периодов в которых выполняется определенный критерий (например, периоды, когда стоимость акции была выше или ниже определенного уровня), определение диапазонов используемых автомобильных номерных знаков и т.п. Я специально сильно упростил пример, чтобы можно было сосредоточиться на используемых приемах.
Для решения более сложных задач нужно лишь немного изменить решение в этой простой ситуации. Поэтому считайте это задачкой на сообразительность - создайте эффективное, основанное на наборах решение этой задачи. Для начала попытайтесь создать решение, которое просто решает задачу. После этого заполните таблицу серьезным количеством столбцов, например 10 млн, и попытайтесь снова применить свою методику. Оцените производительность. Только после этого посмотрите на мои решения.
Прежде чем показать решение на основе оконных функций, я приведу одно из многих решений, в котором применяются традиционные конструкции языка. А именно, я воспользуюсь вложенными запросами. Чтобы понять стратегию первого решения, посмотрите на значения в последовательности T1.col1, в которую я добавил концептуальный атрибут, которого не было раньше и который я считаю идентификатором группы:
col1 grp ----------------------- 2 a 3 a 11 b 12 b 13 b 27 c 33 d 34 d 35 d 42 e
Атрибута grp пока еще нет. С точки зрения концепции это значение, которое уникально идентифицирует островок. Это означает, что он един у всех членов одного островка и разный у разных островков. Если удастся вычислить такой идентификатор группы, после этого можно выполнить группировку по атрибуту grp и вернуть минимальное и максимальное значения col1 в каждой группе (островке). Один из способов получения такого идентификатора с использованием традиционных конструкций языка - найти для каждого значения col1 минимальное значение col1, которое больше или равно текущему значению и у которого нет последующего значения.
В качестве примера в соответствии с этой логикой попытайтесь определить по отношению к значению 2, какое значение в col1 больше или равно 2 и размещается перед отсутствующим значением? Это 3. А теперь попробуйте сделать то же самое со значением 3. Вы также получите 3. Поэтому 3 является идентификатором островка, который начинается с 2 и заканчивается 3. В островке, который начинается с 11 и заканчивается 13, идентификатором группы всех членов является 13. Как видите, идентификатором группы для островка фактически является последний член островка. Вот код T-SQL, в котором реализован такой подход:
SELECT col1,
(SELECT MIN(B.col1)
FROM dbo.T1 AS B
WHERE B.col1 >= A.col1
-- является ли текущая строка последней в этой группе?
AND NOT EXISTS
(SELECT *
FROM dbo.T1 AS C
WHERE C.col1 = B.col1 + 1)) AS grp
FROM dbo.T1 AS A;Этот запрос дает следующий результат:
Следующий этап довольно прямолинеен: определите табличное выражение на основе последнего запроса, а во внешнем запросе выполните группировку по идентификатору группы и верните минимальное и максимальное значения col1 в каждой группе:
SELECT MIN(col1) AS start_range, MAX(col1) AS end_range
FROM (SELECT col1,
(SELECT MIN(B.col1)
FROM dbo.T1 AS B
WHERE B.col1 >= A.col1
AND NOT EXISTS
(SELECT *
FROM dbo.T1 AS C
WHERE C.col1 = B.col1 + 1)) AS grp
FROM dbo.T1 AS A) AS D
GROUP BY grp;В таком решении есть две проблемы. Во-первых, сложно понять логику решения. Во-вторых, оно работает катастрофически медленно. Я пока не хочу анализировать все планы выполнения - мы еще много будем обсуждать их в этом руководстве, но я могу сказать, что для каждой строки в таблице SQL Server выполняет почти две полных операции чтения данных. А теперь подумайте, сколько работы придется проделать для таблицы с 10 млн строк. Общее число строк, которые нужно обсчитать, невероятно велико.
Следующее решение также вычисляет идентификаторы групп, но при этом используются оконные функции. Первый шаг в решении - использование функции ROW_NUMBER для вычисления номеров строк на основе упорядочения по col1. Подробные сведения о функции ROW_NUMBER я предоставлю попозже, а сейчас будет достаточно сказать, что она вычисляет уникальные идентификаторы в секции, начиная с 1 и с шагом 1 в соответствии с заданным упорядочением.
Таким образом, следующий запрос возвратит значения col1 и числа строк на основе упорядочения по col1:
SELECT col1, ROW_NUMBER() OVER(ORDER BY col1) AS rownum
FROM dbo.T1;
А теперь посмотрим внимательно на эти две последовательности. Первая (col1) содержит пробелы, а вторая (rownum) - нет. Зная это, попытайтесь прикинуть, каково уникальное отношение между двумя последовательностями в контексте островка. В рамках островка члены обеих последовательностей увеличиваются на фиксированную величину. Поэтому разница между членами последовательности фиксирована. В следующем островке col1 увеличивается более чем на 1, а шаг увеличения rownum остается равным 1, поэтому разница между членами увеличивается. Иначе говоря, разница между членами константа и уникальна в каждом островке. Выполните следующий запрос, который определяет эту разницу:
SELECT col1, col1 - ROW_NUMBER() OVER(ORDER BY col1) AS diff
FROM dbo.T1;
Видно, что эта разница удовлетворяет двум требованиям к нашему идентификатору групп, поэтому ее можно использовать в этом качестве. Остальное выглядит так же, как и в предыдущем решении, а именно: нужно сгруппировать строки по идентификатору группы и вернуть максимальное и минимальное значения col1 в каждой группе:
SELECT MIN(col1) AS start_range, MAX(col1) AS end_range
FROM (SELECT col1,
-- разница является константой и уникальна в рамках диапазона (островка)
col1 - ROW_NUMBER() OVER(ORDER BY col1) AS grp
FROM dbo.T1) AS D
GROUP BY grp;Полюбуйтесь, насколько лаконично и просто выглядит это решение. Конечно, всегда лучше добавить пару-тройку комментариев, чтобы те, кто видит решение в первый раз, могли лучше понять его.
Это решение очень эффективно. Усилия по назначению номеров строкам пренебрежительно мало по сравнению с объемом работы, выполняемым в предыдущем решении. Здесь используется только одна операция упорядоченного чтения индекса столбца col1 и итератор, который циклически увеличивает значение счетчика. В тесте производительности, который я выполнил с 10 млн строк, выполнение этого запроса заняло 10 секунд. Другие решения выполнялись намного дольше.
Я надеюсь, что этого короткого примера использования оконных функций достаточно, чтобы заинтриговать вас и показать их мощь. А теперь вернемся к изучению технических деталей оконных функций. Далее у вас будет возможность увидеть больше примеров.
