Вычисляем предыдущее и следующее значение с условием

При работе с 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, чтобы получить нужное предыдущее или следующее значение. Например, чтобы получить предшествующую дату, для которой значение осадков больше 24 (назовем ее prevdt), следует применить следующий вложенный запрос (предполагается, что внешнему экземпляру назначен псевдоним Precipitation P1):

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

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

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

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

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

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

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

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

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

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

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

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