С помощью оконных функций можно изящно и эффективно решать многие задачи T-SQL, связанные с обработкой запросов. И все же в версии SQL Server 2017 остаются задачи, для которых трудно найти решение на основе набора, но зато их можно обработать, если дополнить T-SQL поддержкой для оконного оператора с именем RESET WHEN. Этот оператор осуществляет пересоздание оконного раздела, когда выполняется определенное условие — возможно, на основе оконной функции. Это нестандартная функция, но сейчас она поддерживается компанией Teradata (документацию по функции можно найти по адресу: http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1145_111A/Ordered_Analytical_Functions.083.010.html#ww1285495). Хочу выразить благодарность Алехандро Месе (обладателю статуса Microsoft Data Platform MVP), познакомившему меня с этой функцией.
В данной статье я представлю две классические задачи обработки запросов T-SQL, опишу решения, используемые в настоящее время, и объясню, как применить усовершенствованные решения с использованием оператора RESET WHEN. Надеюсь, дочитав статью до конца, вы поймете, насколько важна эта функция, и проголосуете за нее на сайте Microsoft Connect (https://connect.microsoft.com/SQLServer/feedback/details/2748755).
Убавление величины
Начнем с задачи, связанной с вычислением нарастающих итогов, которые нужно убавить, когда выполняется определенное условие. Джери Решеф, обладатель статуса Microsoft Data Platform MVP, представил исходную задачу, а затем Шарон Ример из компании Naya Technologies предложил вариант этой задачи.
Среди тестовых данных для рассматриваемой задачи — таблица с именем Transactions, которую вы создаете и заполняете с помощью программного кода листинга 1.
Транзакции добавляют величины некоторого элемента в контейнер на основе упорядочения по столбцу txid. Если кумулятивная величина превышает емкость контейнера (представленного как вход), то контейнер должен быть убавлен. Ваше решение должно показать состояние контейнера (общую величину) после каждой транзакции: 0 это общая величина после убавления, а также величина убавления, когда это применимо. На экране 1 приведен желаемый результат для тестовых данных и емкости входного контейнера, равной 5.
.jpg) |
| Экран 1. Желаемый результат для тестовых данных и емкости входного контейнера, равной 5 |
Решение на основе курсора
Такие задачи часто пытаются решать с использованием рекурсивных запросов. Эти решения могут быть изящными, но они не очень эффективны. Также применяется метод, известный как «подстановочное обновление» (quirky update), который очень эффективен, но не гарантирует результата, поскольку зависит от физического порядка обработки. Пока я не нашел эффективного, гарантирующего результат решения на основе наборов для этой задачи и вынужден использовать итеративные решения (на основе T-SQL или CLR).
В листинге 2 приведен пример простого итеративного решения T-SQL с использованием курсора.
Программный код определяет переменную курсора и использует ее, чтобы извлекать транзакции по одной в хронологическом порядке. Величины накапливаются в переменной с именем @totalqty. После извлечения каждой строки программный код проверяет, не превышает ли накопленная величина емкость контейнера. Если происходит превышение, переменной с именем @depletionqty присваивается значение @totalqty, а затем текущее значение @totalqty обнуляется. После этого программный код записывает сведения о текущей транзакции (txid и qty) наряду с текущими значениями @totalqty и @depletionqty в табличную переменную. После прохода по всем транзакциям код запрашивает табличную переменную, чтобы получить желаемый результат.
Решение с использованием RESET WHEN (не поддерживается в SQL Server 2017)
Недостатки применения итеративных решений хорошо известны. Вопрос в том, существует ли удачная альтернатива на основе набора. До настоящего времени я не нашел таковой для поставленной задачи с использованием существующих инструментов T-SQL, но хотел бы, чтобы она когда-нибудь появилась. Как отмечалось выше, компания Teradata поддерживает оконный оператор с именем RESET WHEN, который пересоздает оконный раздел при выполнении определенного условия. Ценность этого предложения в том, что в условии можно использовать оконную функцию и вы можете узнать, что аккумулировано до предыдущей строки. В нашей задаче оконный раздел пересоздается, когда сумма величин от начала секции до предыдущей строки превышает лимит входного контейнера (листинг 3). Помните, что на сегодня этот программный код не поддерживается в SQL Server.
Если бы он поддерживался, то были бы получены такие выходные данные, как показано на экране 2.
.jpg) |
| Экран 2. Результаты решения с использованием RESET WHEN |
Как мы видим, оконный раздел пересоздан после транзакций с идентификаторами 2, 5 и 8.
Чтобы получить окончательный желаемый результат, присваиваем общей величине контейнера (назовем ее totalqty) значение 0, когда сумма с накоплением превышает лимит контейнера, и значение суммы с накоплением в противном случае. Затем можно вычислить величину убывания (назовем ее depletionqty) как сумму с накоплением за вычетом общей величины. В листинге 4 приведен полный программный код решения.
Как видите, решение простое, компактное и изящное.
Недавно Шарон Ример из компании Naya Technologies представил вариант этой задачи на основе заказа от одного из клиентов компании. Требовалось вычислить, сколько раз контейнер превышает входной лимит. Для этой цели следует иметь такое же определение для CTE C и использовать внешний запрос для подсчета.
SELECT COUNT (CASE WHEN runsum > @maxallowedqty THEN 1 END) AS timesexceeded FROM C;
Полное решение выглядит таким образом, как показано в листинге 5. Опять-таки компактно и изящно.
Островки сложности
Есть много других задач, для которых на сегодня существует приемлемое решение T-SQL на основе набора, но их проще решить с помощью оператора RESET WHEN. Хороший пример — задачи об островах сложности, в которых нужно определить новый остров всякий раз, когда выполняется условие, сравнивающее какой-то элемент из текущей строки с элементом из предыдущей. Чтобы продемонстрировать такую задачу, я использую таблицу с именем Stocks, созданную и заполненную с помощью программного кода листинга 6.
В этой таблице отслеживаются курсы акций на момент закрытия дневных торгов. Предположим, вам требуется идентифицировать периоды (острова), в которых значение курса акций больше или равно 50, и для каждого периода необходимо показать время начала и конца, а также максимальное значение курса в течение периода. При этом необходимо игнорировать периоды продолжительностью до 6 дней. На экране 3 показан желаемый результат для тестовых данных.
.jpg) |
| Экран 3. Желаемый результат для решения задачи с островами сложности |
Например, обратите внимание, что для первого острова для акции с идентификатором 1 игнорируется период между Aug. 9, 2017 и Aug. 15, 2017, поскольку его длительность не превышает 6 дней, но не игнорируется период между Aug. 18, 2017 и Aug. 25, 2017, так как его продолжительность 7 дней.
Поддерживаемое решение T-SQL
Как отмечалось выше, на сегодня существуют поддерживаемые решения на основе набора для задач с островами, подобных рассматриваемой, но они длиннее и сложнее, чем предусматривающие использование оператора RESET WHEN. При применении одного из решений, поддерживаемых в настоящее время, на первом шаге вычисляется флаг (назовем его isstart), которому присваивается значение 0, если это не начало острова, путем сравнения какого-нибудь элемента текущей строки с неким элементом предшествующей (полученным с помощью функции LAG). В противном случае флагу присваивается значение 1. В нашем случае после фильтрации только строк, в которых значение акции превышает или равно 50, флаг получает значение 0, когда разница между предыдущей датой и текущей менее 7 дней; в противном случае флаг имеет значение 1. В листинге 7 показан программный код, реализующий этот шаг. Выходные данные, которые он формирует, приведены на экране 4.
.jpg) |
| Экран 4. Результаты работы кода листинга 7 |
На втором шаге мы получаем идентификатор острова путем вычисления суммы с накоплением флага isstart. Наконец, данные группируются по stockid и isstart, и возвращаются даты начала и конца острова, а также максимальный курс акций в течение периода. В листинге 8 приводится полный текст решения.
Решение с использованием RESET WHEN (не поддерживается в версии SQL Server 2017)
С помощью оператора RESET WHEN решить задачу было бы проще, так как можно всего лишь пересоздать оконный раздел, когда выполняется условие для начала нового острова. Затем можно воспользоваться минимальной датой в секции как идентификатором острова.
В листинге 9 показан пример вычисления идентификатора острова (назовем его grp).
В приведенном программном коде используется функция окна MIN, чтобы получить дату из предыдущей строки с помощью экстента фрейма окна ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING. Иначе можно получить дату из предшествующей строки с помощью функции LAG, например так (показан только альтернативный оператор RESET WHEN):
RESET WHEN DATEDIFF (day, LAG (dt) OVER (PARTITION BY stockid ORDER BY dt), dt) >= 7
Выходные данные этого шага на экране 5 показывают полученный идентификатор группы для каждого острова.
.jpg) |
| Экран 5. Выходные данные с использованием функции LAG |
Затем потребуется всего один шаг для группирования этих данных по stockid и grp. Возвращаются даты начала и конца каждого периода, а также максимальный курс акции в каждом из них (листинг 10).
Как видите, это решение короче и проще, чем поддерживаемое в настоящее время.
Оконные функции, бесспорно, обладают выдающимися возможностями, но они могут быть еще более эффективными. Оператор RESET WHEN в случае реализации в T-SQL позволит заменить существующие итеративные решения изящными и более мощными решениями на основе набора.
SET NOCOUNT ON; USE tempdb; DROP TABLE IF EXISTS dbo.Transactions; GO CREATE TABLE dbo.Transactions ( txid INT NOT NULL CONSTRAINT PK_Transactions PRIMARY KEY, qty INT NOT NULL ); GO TRUNCATE TABLE dbo.Transactions; INSERT INTO dbo.Transactions(txid, qty) VALUES(1,2),(2,5),(3,4),(4,1),(5,10),(6,3), (7,1),(8,2),(9,1),(10,2),(11,1),(12,9);
SET NOCOUNT ON; DECLARE @maxallowedqty AS INT = 5; DECLARE @C AS , @txid AS INT, @qty AS INT, @totalqty AS INT = 0, @depletionqty AS INT = 0; DECLARE @Result AS TABLE ( txid INT NOT NULL PRIMARY KEY, qty INT NOT NULL, totalqty INT NOT NULL, depletionqty INT NOT NULL ); SET @C = FORWARD_ONLY STATIC READ_ONLY FOR SELECT txid, qty FROM dbo.Transactions ORDER BY txid; OPEN @C; FETCH NEXT FROM @C INTO @txid, @qty; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @totalqty += @qty, @depletionqty = 0; IF @totalqty > @maxallowedqty BEGIN SET @depletionqty = @totalqty; SET @totalqty = 0; END; INSERT INTO @Result(txid, qty, totalqty, depletionqty) VALUES(@txid, @qty, @totalqty, @depletionqty); FETCH NEXT FROM @C INTO @txid, @qty; END; SELECT txid, qty, totalqty, depletionqty FROM @Result ORDER BY txid;
DECLARE @maxallowedqty AS INT = 5; SELECT txid, qty, SUM(qty) OVER(ORDER BY txid RESET WHEN -- сбросить секцию окна, когда -- сумма с накоплением до предшествующей строки > @maxallowedqty SUM(qty) OVER(ORDER BY txid ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) > @maxallowedqty ROWS UNBOUNDED PRECEDING) AS runsum FROM dbo.Transactions;
DECLARE @maxallowedqty AS INT = 5; WITH C AS ( SELECT *, SUM(qty) OVER(ORDER BY txid RESET WHEN SUM(qty) OVER(ORDER BY txid ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) > @maxallowedqty ROWS UNBOUNDED PRECEDING) AS runsum FROM dbo.Transactions ) SELECT txid, qty, totalqty, runsum - totalqty AS depletionqty FROM C CROSS APPLY ( VALUES( CASE WHEN runsum > @maxallowedqty THEN 0 ELSE runsum END ) ) AS A(totalqty);
WITH C AS ( SELECT *, SUM(qty) OVER(ORDER BY txid RESET WHEN SUM(qty) OVER(ORDER BY txid ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) > @maxallowedqty ROWS UNBOUNDED PRECEDING) AS runsum FROM dbo.Transactions ) SELECT COUNT( CASE WHEN runsum > @maxallowedqty THEN 1 END ) AS timesexceeded FROM C;
SET NOCOUNT ON; USE tempdb; DROP TABLE IF EXISTS dbo.StockRates; GO CREATE TABLE dbo.StockRates ( stockid INT NOT NULL, dt DATE NOT NULL, val INT NOT NULL, CONSTRAINT PK_StockRates PRIMARY KEY(stockid, dt) ); GO INSERT INTO dbo.StockRates VALUES (1, '2017-08-01', 13), (1, '2017-08-02', 14), (1, '2017-08-03', 17), (1, '2017-08-04', 40), (1, '2017-08-05', 45), (1, '2017-08-06', 52), (1, '2017-08-07', 56), (1, '2017-08-08', 60), (1, '2017-08-09', 70), (1, '2017-08-10', 30), (1, '2017-08-11', 29), (1, '2017-08-12', 35), (1, '2017-08-13', 40), (1, '2017-08-14', 45), (1, '2017-08-15', 60), (1, '2017-08-16', 60), (1, '2017-08-17', 55), (1, '2017-08-18', 60), (1, '2017-08-19', 20), (1, '2017-08-20', 15), (1, '2017-08-21', 20), (1, '2017-08-22', 30), (1, '2017-08-23', 40), (1, '2017-08-24', 20), (1, '2017-08-25', 60), (1, '2017-08-26', 80), (1, '2017-08-27', 70), (1, '2017-08-28', 70), (1, '2017-08-29', 40), (1, '2017-08-30', 30), (1, '2017-08-31', 10), (2, '2017-08-01', 3), (2, '2017-08-02', 4), (2, '2017-08-03', 7), (2, '2017-08-04', 30), (2, '2017-08-05', 35), (2, '2017-08-06', 42), (2, '2017-08-07', 46), (2, '2017-08-08', 50), (2, '2017-08-09', 60), (2, '2017-08-10', 20), (2, '2017-08-11', 19), (2, '2017-08-12', 25), (2, '2017-08-13', 30), (2, '2017-08-14', 35), (2, '2017-08-15', 50), (2, '2017-08-16', 50), (2, '2017-08-17', 45), (2, '2017-08-18', 50), (2, '2017-08-19', 10), (2, '2017-08-20', 5), (2, '2017-08-21', 10), (2, '2017-08-22', 20), (2, '2017-08-23', 30), (2, '2017-08-24', 10), (2, '2017-08-25', 50), (2, '2017-08-26', 70), (2, '2017-08-27', 60), (2, '2017-08-28', 60), (2, '2017-08-29', 30), (2, '2017-08-30', 20), (2, '2017-08-31', 1);
SELECT stockid, dt, val, CASE WHEN DATEDIFF(day, LAG(dt) OVER(PARTITION BY stockid ORDER BY dt), dt) < 7 THEN 0 ELSE 1 END AS isstart FROM dbo.StockRates WHERE val >= 50;
WITH C1 AS ( SELECT *, CASE WHEN DATEDIFF(day, LAG(dt) OVER(PARTITION BY stockid ORDER BY dt), dt) < 7 THEN 0 ELSE 1 END AS isstart FROM dbo.StockRates WHERE val >= 50 ), C2 AS ( SELECT *, SUM(isstart) OVER(PARTITION BY stockid ORDER BY dt ROWS UNBOUNDED PRECEDING) AS grp FROM C1 ) SELECT stockid, MIN(dt) AS startdate, MAX(dt) AS enddate, MAX(val) as maxvalue FROM C2 GROUP BY stockid, grp ORDER BY stockid, startdate;
SELECT stockid, dt, val, MIN(dt) OVER(PARTITION BY stockid ORDER BY dt RESET WHEN DATEDIFF(day, MIN(dt) OVER( PARTITION BY stockid ORDER BY dt ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), dt) >= 7 ROWS UNBOUNDED PRECEDING) AS grp FROM dbo.Stocks WHERE val >= 50;
WITH C AS ( SELECT *, MIN(dt) OVER(PARTITION BY stockid ORDER BY dt RESET WHEN DATEDIFF(day, LAG(dt) OVER(PARTITION BY stockid ORDER BY dt), dt) >= 7 ROWS UNBOUNDED PRECEDING) AS grp FROM dbo.Stocks WHERE val >= 50 ) SELECT stockid, grp AS startdate, MAX(dt) AS enddate, MAX(val) as maxvalue FROM C GROUP BY stockid, grp ORDER BY stockid, grp;