С помощью оконных функций можно изящно и эффективно решать многие задачи 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.

 

Желаемый результат для тестовых данных и емкости входного контейнера, равной 5
Экран 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.

 

Результаты решения с использованием RESET WHEN
Экран 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 показан желаемый результат для тестовых данных.

 

Желаемый результат для решения задачи с островами сложности
Экран 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.

 

Результаты работы кода листинга 7
Экран 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 показывают полученный идентификатор группы для каждого острова.

 

Выходные данные с использованием функции LAG
Экран 5. Выходные данные с использованием функции LAG

Затем потребуется всего один шаг для группирования этих данных по stockid и grp. Возвращаются даты начала и конца каждого периода, а также максимальный курс акции в каждом из них (листинг 10).

Как видите, это решение короче и проще, чем поддерживаемое в настоящее время.

Оконные функции, бесспорно, обладают выдающимися возможностями, но они могут быть еще более эффективными. Оператор RESET WHEN в случае реализации в T-SQL позволит заменить существующие итеративные решения изящными и более мощными решениями на основе набора.

Листинг 1. Создание и заполнение таблицы Transactions 
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);
Листинг 2. Пример простого итеративного решения с использованием курсора
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;
Листинг 3. Решение с использованием RESET WHEN 
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;
Листинг 4. Полный программный код решения 
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);
Листинг 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 COUNT( CASE WHEN runsum > @maxallowedqty THEN 1 END ) AS timesexceeded
FROM C;
Листинг 6. Создание и заполнение таблицы Stocks 
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);
Листинг 7. Программный код для решения задачи с островами сложности
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;
Листинг 8. Формирование идентификатора острова 
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;
Листинг 9. Пример вычисления идентификатора острова с RESET WHEN 
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;
Листинг 10. Шаг для группирования данных 
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;

Поделитесь материалом с коллегами и друзьями

Купить номер с этой статьей в PDF