При работе с T-SQL часто бывает необходимо вычислить значение из предшествующей и следующей строк для каждой текущей строки. Для этой цели в T-SQL предусмотрены оконные функции LAG и LEAD. Однако задача усложняется, если требуется учесть условие. Например, если нужно вычислить последнее значение col1, которое было больше x; или, в зависимости от порядка col1, вычислить последнее значение col2, которое было больше x. Далее в статье я объясню, как решать эти задачи.
Для демонстрации вычислений предшествующих и следующих значений с условием мы воспользуемся данными о ежедневных осадках в таблицах, именуемых Locations («Местоположения») и Precipitation («Осадки»). В таблице Locations содержатся места, где собираются данные об атмосферных осадках, а в таблице Precipitation приведены данные об осадках в данном месте в миллиметрах. Используйте программный код листинга 1, чтобы создать таблицы и заполнить их малым набором тестовых данных.
Предположим, нам нужно вычислить для каждой строки в таблице Precipitation два значения:
- Число дней, которое прошло после последнего дня, когда значение осадков превысило 24 миллиметра (не считая сегодняшнего дня). Назовем столбец результатов diffprev.
- Число дней, которое пройдет до следующего дня, когда значение осадков превысит 24 миллиметра (не считая сегодняшнего дня). Назовем столбец результатов diffnext.
В таблице 1 приведен желаемый результат для малого набора тестовых данных.
.jpg) |
Попробуйте найти самое эффективное решение для этой задачи.
Используйте малый набор тестовых данных из листинга 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 (представлены с сортировкой по местоположению и дате).
.jpg) |
Помните, что нам нужно вычислить разницу в днях между prevdt и dt (назовем эту величину diffprev), и между dt и nextdt (назовем эту величину diffnext). Для этого необходимо задействовать функцию DATEDIFF с входными данными на основе текущей даты и результатов вложенных запросов TOP. Если вы не хотите предоставлять вложенные запросы непосредственно в качестве входов для функции DATEDIFF, то можно использовать оператор CROSS APPLY и производную таблицу на основе предложения VALUES, чтобы определить псевдонимы prevdt и nextdt в предложении FROM. Предложение FROM логически оценивается до предложения SELECT, поэтому псевдонимы, определенные в предложении FROM, доступны для вычислений в предложении SELECT. В листинге 5 приводится полное решение, в котором применяется этот подход. Соответствующий план запроса показан на рисунке 1.
.jpg) |
| Рисунок 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.
.jpg) |
| Рисунок 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 показан план для этого запроса.
.jpg) |
| Рисунок 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 прочитает эту статью и продолжит добавлять важные, но пока отсутствующие функции.
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);
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
-- 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;
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 */ ; -- снимите символ комментария, чтобы представить упорядоченно
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 */ ; -- снимите символ комментария, чтобы представить упорядоченно
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 */ ; -- снимите символ комментария, чтобы представить упорядоченно
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
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 */ ; -- снимите символ комментария, чтобы представить упорядоченно