При работе с T-SQL часто бывает необходимо вычислить значение из предшествующей и следующей строк для каждой текущей строки. Для этой цели в T-SQL предусмотрены оконные функции LAG и LEAD. Однако задача усложняется, если требуется учесть условие. Например, если нужно вычислить последнее значение col1, которое было больше x; или, в зависимости от порядка col1, вычислить последнее значение col2, которое было больше x. Далее в статье я объясню, как решать эти задачи.

Для демонстрации вычислений предшествующих и следующих значений с условием мы воспользуемся данными о ежедневных осадках в таблицах, именуемых Locations («Местоположения») и Precipitation («Осадки»). В таблице Locations содержатся места, где собираются данные об атмосферных осадках, а в таблице Precipitation приведены данные об осадках в данном месте в миллиметрах. Используйте программный код листинга 1, чтобы создать таблицы и заполнить их малым набором тестовых данных.

Предположим, нам нужно вычислить для каждой строки в таблице Precipitation два значения:

  1. Число дней, которое прошло после последнего дня, когда значение осадков превысило 24 миллиметра (не считая сегодняшнего дня). Назовем столбец результатов diffprev.
  2. Число дней, которое пройдет до следующего дня, когда значение осадков превысит 24 миллиметра (не считая сегодняшнего дня). Назовем столбец результатов diffnext.

В таблице 1 приведен желаемый результат для малого набора тестовых данных.

 

Желаемый результат

Попробуйте найти самое эффективное решение для этой задачи.

Используйте малый набор тестовых данных из листинга 1 для проверки корректности вашего решения. Чтобы протестировать производительность решения, требуется гораздо больше данных. Для этой цели используйте программный код в листинге 2, чтобы создать вспомогательную функцию с именем GetNums, которая формирует последовательность целочисленных значений в запрошенном диапазоне.

Воспользуйтесь приведенным в листинге 3 программным кодом для заполнения таблиц данными для 10 тыс. местоположений по результатам ежедневных измерений для каждого (всего около 10 млн измерений).

Решение с фильтром TOP

Вероятно, самое очевидное решение — использовать вложенный запрос с фильтром TOP, чтобы получить нужное предыдущее или следующее значение. Например, чтобы получить предшествующую дату, для которой значение осадков больше 24 (назовем ее prevdt), следует применить следующий вложенный запрос (предполагается, что внешнему экземпляру назначен псевдоним Precipitation P1):

( SELECT TOP (1) dt
    FROM dbo.Precipitation AS P2
    WHERE P2.locid = P1.locid
      AND P2.dt < P1.dt
      AND val > 24
    ORDER BY P2.dt DESC ) AS prevdt

Аналогично, чтобы получить следующую дату, для которой значение осадков больше 24 (назовем ее it nextdt), следует применить такой вложенный запрос:

( SELECT TOP (1) dt
    FROM dbo.Precipitation AS P2
    WHERE P2.locid = P1.locid
      AND P2.dt > P1.dt
      AND val > 24
    ORDER BY P2.dt ) AS nextdt

В листинге 4 приведен полный запрос для вычисления как prevdt, так и nextdt. Выходные данные этого запроса показаны в таблице 2 (представлены с сортировкой по местоположению и дате).

 

Результаты шага Step1

Помните, что нам нужно вычислить разницу в днях между prevdt и dt (назовем эту величину diffprev), и между dt и nextdt (назовем эту величину diffnext). Для этого необходимо задействовать функцию DATEDIFF с входными данными на основе текущей даты и результатов вложенных запросов TOP. Если вы не хотите предоставлять вложенные запросы непосредственно в качестве входов для функции DATEDIFF, то можно использовать оператор CROSS APPLY и производную таблицу на основе предложения VALUES, чтобы определить псевдонимы prevdt и nextdt в предложении FROM. Предложение FROM логически оценивается до предложения SELECT, поэтому псевдонимы, определенные в предложении FROM, доступны для вычислений в предложении SELECT. В листинге 5 приводится полное решение, в котором применяется этот подход. Соответствующий план запроса показан на рисунке 1.

 

План запроса для решения с оператором TOP
Рисунок 1. План запроса для решения с оператором TOP

Обратите внимание, что для каждой строки в таблице Precipitation вы получаете две операции поиска в индексе: одну для вычисления prvdt и другую для вычисления compute nextdt. Результат — очень большое число логических операций чтения. Для выполнения данного запроса на моем компьютере потребовалась 71 секунда; при этом было выполнено 65 844 026 логических операций чтения (отчет сеанса расширенных событий). Обратите внимание, что это единственный запрос, выполненный мной на моем ноутбуке. Если этот запрос выполняется на компьютере вместе со множеством других запросов, то возникнет конкуренция из-за большого числа операций ввода-вывода. Вероятно, стоит поискать решение, которое выполняет меньше операций чтения.

Часто требуется вычислить только предшествующее или следующее значение, но не оба. На выполнение этой задачи, вычисляя только diffprev и удаляя вычисление diffnext, потребовалось 42 секунды. Тем не менее число операций чтения исчисляется десятками миллионов.

Решение с оконными функциями

Эту задачу можно решить с использованием оконных функций, составив план со значительно меньшим числом операций чтения. Прежде всего, следует вычислить значение gooddt, представляющее текущую дату, для которой значение осадков превышает 24 (NULL в другом случае). Затем вычислите prevdt с использованием оконной функции MAX, применяемой к gooddt, с кадром ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING. Аналогично, вычислите nextdt с использованием оконной функции MIN, применяемой к gooddt, с кадром ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING. Остальное — так же, как в предыдущем решении. В листинге 6 приводится полный запрос решения.

Для данного запроса действует план, показанный на рисунке 2.

 

План для решения с оконными функциями
Рисунок 2. План для решения с оконными функциями

Для выполнения этого запроса потребовалась 51 секунда (и 33 секунды при вычислении только diffprev). Однако важно, что число операций чтения уменьшилось на три порядка величины, до 64 769 (учитывая связанные со сбросом сортированных данных в tempdb). Таким образом, этот запрос породит гораздо меньшую конкуренцию за ресурсы ввода-вывода в среде с многочисленными одновременно выполняемыми запросами.

Для дальнейшей оптимизации можно воспользоваться приемом параллельного CROSS APPLY Адама Маханика. Вы направляете запрос к таблице Locations и с помощью оператора CROSS APPLY применяете логику решения к одному местоположению. Этот прием обычно улучшает параллельную обработку и разбивает такие операции, как сортировка, которые более линейно масштабируются в многочисленные мелкие операции, в целом выполняемые с большей производительностью.

Для реализации данного метода сначала воспользуйтесь программным кодом листинга 7, чтобы инкапсулировать логику решения для одного местоположения во встроенной функции с табличным значением.

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

SELECT L.locid, A.dt, A.val, A.diffprev,
   A.diffnext
FROM dbo.Locations AS L
  CROSS APPLY dbo.GetDiff( L.locid, 24 )
  AS A

/* ORDER BY locid, dt */; -- снимите
   символ комментария, чтобы
   представить упорядоченно

На рисунке 3 показан план для этого запроса.

 

План для запроса с APPLY
Рисунок 3. План для запроса с APPLY

Обратите внимание, что из этого плана исчез сброс сортировки. Для выполнения данного запроса на моем компьютере потребовалась 41 секунда (20 секунд при вычислении только diffprev, так как в данном случае сортировка не требуется). В запросе выполняется 67 375 операций логического чтения.

Возвращение значения, отличного от элемента упорядочения

Последняя задача связана с возвращением значения, применяемого также в качестве элемента упорядочения (в нашем примере — дата). Но что если нужно возвратить другое значение, отличное от элемента упорядочения? Например, задача могла быть сформулирована так: получить значения осадков в предшествующие и последующие дни, в которые значения превышают 24. Чтобы этого достичь, при вычислении goodval вместо записи только даты запишите объединенную строку, составленную из даты и значения, с использованием выражения, которое сохраняет корректное поведение упорядочения:

CASE WHEN val > 24
   THEN CONVERT (CHAR (8), dt, 112)
   + STR (val, 10) END

Используйте оконные функции MIN и MAX, как раньше (назовите столбцы результатов prevgoodval и nextgoodval). Затем во внешнем запросе извлеките из каждого столбца результатов 10 символов справа и преобразуйте их в целые числа. В листинге 8 приводится полный запрос решения.

Этот запрос формирует выходные данные, показанные в таблице 3, для малого набора тестовых данных.

И еще об оконных возможностях

Оконные функции — лучшее, что придумано со времени появления в продаже заранее нарезанного хлеба. И я говорю не только о функциях T-SQL, но в целом. Не перестаю удивляться, сколь широкий круг задач удается изящно и эффективно решать с помощью оконных функций. В SQL так много компонентов, связанных с оконными функциями, в том числе вложенные оконные функции, более мощные возможности RANGE и т. д. Надеюсь, кто-нибудь из сотрудников Microsoft прочитает эту статью и продолжит добавлять важные, но пока отсутствующие функции.

Листинг 1. DDL и малый набор тестовых данных
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID(N'dbo.Precipitation', N'U') IS NOT NULL
  DROP TABLE dbo.Precipitation;
IF OBJECT_ID(N'dbo.Locations', N'U') IS NOT NULL
  DROP TABLE dbo.Locations;
GO
CREATE TABLE dbo.Locations
(
  locid INT  NOT NULL
    CONSTRAINT PK_Locations PRIMARY KEY
);
CREATE TABLE dbo.Precipitation
(
  locid INT  NOT NULL,
  dt    DATE NOT NULL,
  val   INT  NOT NULL,
  CONSTRAINT PK_Precipitation PRIMARY KEY(locid, dt, val),
  CONSTRAINT FK_Precipitation_Locations
    FOREIGN KEY(locid) REFERENCES dbo.Locations(locid)
);
INSERT INTO dbo.Locations(locid) VALUES(1),(2);
INSERT INTO dbo.Precipitation(locid, dt, val) VALUES
  (1, '20151214', 10),
  (1, '20151215', 0),
  (1, '20151216', 0),
  (1, '20151217', 100),
  (1, '20151218', 50),
  (1, '20151219', 20),
  (1, '20151220', 210),
  (1, '20151221', 20),
  (1, '20151222', 0),
  (1, '20151223', 0),
  (1, '20151224', 0),
  (1, '20151225', 40),
  (2, '20151214', 0),
  (2, '20151215', 140),
  (2, '20151216', 60),
  (2, '20151217', 40),
  (2, '20151218', 0),
  (2, '20151219', 20),
  (2, '20151220', 0),
  (2, '20151221', 0),
  (2, '20151222', 0),
  (2, '20151223', 45),
  (2, '20151224', 120),
  (2, '20151225', 130);
Листинг 2. Вспомогательная функция GetNums
IF OBJECT_ID(N’dbo.GetNums’, N’IF’) IS NOT NULL DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
    L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
             FROM L5)
  SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
  FROM Nums
  ORDER BY rownum;
GO
Листинг 3. Большой набор тестовых данных
-- 10 000 местоположений x 3 года ~= 10 000 000 измерений
TRUNCATE TABLE dbo.Precipitation;
DELETE FROM dbo.Locations;

INSERT INTO dbo.Locations(locid)
  SELECT n FROM dbo.GetNums(1, 10000);

INSERT INTO dbo.Precipitation(locid, dt, val)
  SELECT L.n AS locid, DATEADD(day, D.n, '20130101') AS dt,
    ABS(CHECKSUM(NEWID())) % 2 * ABS(CHECKSUM(NEWID())) % 100 AS val
  FROM dbo.GetNums(0, DATEDIFF(day, '20130101', '20151231')) AS D
    CROSS JOIN dbo.GetNums(1, 10000) AS L;
Листинг 4. Полный запрос для вычисления prevdt и nextdt
SELECT locid, dt, val,
  ( SELECT TOP (1) dt
    FROM dbo.Precipitation AS P2
    WHERE P2.locid = P1.locid
      AND P2.dt < P1.dt
      AND val > 24
    ORDER BY P2.dt DESC ) AS prevdt,
  ( SELECT TOP (1) dt
    FROM dbo.Precipitation AS P2
    WHERE P2.locid = P1.locid
      AND P2.dt > P1.dt
      AND val > 24
    ORDER BY P2.dt ) AS nextdt
FROM dbo.Precipitation AS P1
/* ORDER BY locid, dt */ ; -- снимите символ комментария, чтобы представить упорядоченно
Листинг 5. Решение с оператором CROSS APPLY
SELECT locid, dt, val,
  DATEDIFF(day, prevdt, dt) AS diffprev,
  DATEDIFF(day, dt, nextdt) AS diffnext
FROM dbo.Precipitation AS P1
  CROSS APPLY
    ( VALUES(
        ( SELECT TOP (1) dt
          FROM dbo.Precipitation AS P2
          WHERE P2.locid = P1.locid
            AND P2.dt < P1.dt
            AND val > 24
          ORDER BY P2.dt DESC ),
        ( SELECT TOP (1) dt
          FROM dbo.Precipitation AS P2
          WHERE P2.locid = P1.locid
            AND P2.dt > P1.dt
            AND val > 24
          ORDER BY P2.dt )
      ) ) AS A(prevdt, nextdt)
/* ORDER BY locid, dt */ ; -- снимите символ комментария, чтобы представить упорядоченно
Листинг 6. Решение с оконными функциями
WITH C AS
(
  SELECT *,
    MAX(gooddt) OVER(PARTITION BY locid
                     ORDER BY dt
                     ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prevdt,
    MIN(gooddt) OVER(PARTITION BY locid
                     ORDER BY dt
                     ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS nextdt
  FROM dbo.Precipitation
    CROSS APPLY ( VALUES( CASE WHEN val > 24 THEN dt END ) ) AS A(gooddt)
)
SELECT locid, dt, val,
  DATEDIFF(day, prevdt, dt) AS diffprev,
  DATEDIFF(day, dt, nextdt) AS diffnext
FROM C
/* ORDER BY locid, dt */ ; -- снимите символ комментария, чтобы представить упорядоченно
Листинг 7. Логика инкапсуляции для одного местоположения во встроенной функции с табличным значением
IF OBJECT_ID(N'dbo.GetDiff', N'IF') IS NOT NULL DROP FUNCTION dbo.GetDiff;
GO
CREATE FUNCTION dbo.GetDiff(@locid AS INT, @minprecip AS INT) RETURNS TABLE
AS
RETURN
  WITH C AS
  (
    SELECT dt, val,
      MAX(gooddt) OVER(ORDER BY dt
                       ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prevdt,
      MIN(gooddt) OVER(ORDER BY dt
                       ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS nextdt
    FROM dbo.Precipitation
      CROSS APPLY ( VALUES( CASE WHEN val > @minprecip THEN dt END ) ) AS A(gooddt)
    WHERE locid = @locid
  )
  SELECT dt, val,
    DATEDIFF(day, prevdt, dt) AS diffprev,
    DATEDIFF(day, dt, nextdt) AS diffnext
  FROM C;
GO
Листинг 8. Возвращение значения, отличного от элемента упорядочения
WITH C AS
(
  SELECT *,
    MAX(goodval)
      OVER(PARTITION BY locid
           ORDER BY dt
           ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prevgoodval,
    MIN(goodval)
      OVER(PARTITION BY locid
           ORDER BY dt
           ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS nextgoodval
  FROM dbo.Precipitation
    CROSS APPLY ( VALUES( CASE WHEN val > 24 THEN
                    CONVERT(CHAR(8), dt, 112) + STR(val, 10) END ) )
    AS A(goodval)
)
SELECT locid, dt, val, prevval, nextval
FROM C
  CROSS APPLY ( VALUES( CAST(RIGHT(prevgoodval, 10) AS INT),
                        CAST(RIGHT(nextgoodval, 10) AS INT) ) )
    AS A(prevval, nextval)
/* ORDER BY locid, dt */ ; -- снимите символ комментария, чтобы представить упорядоченно