SQL Server: вычисляем предыдущее и следующее значение с условием. Решение с оконными функциями. Продолжение
Используйте следующий запрос, чтобы применить функцию к каждому местоположению:
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 7; - снимите символ комментария, чтобы представить упорядоченно
План для запроса с 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 символов справа и преобразуйте их в целые числа. В коде выше приводится полный запрос решения.
Возвращение значения, отличного от элемента упорядочения
Этот запрос формирует выходные данные, показанные в таблице выше, для малого набора тестовых данных.
И еще об оконных возможностях
Оконные функции — лучшее, что придумано со времени появления в продаже заранее нарезанного хлеба. И я говорю не только о функциях Т-SQL, но в целом. Не перестаю удивляться, сколь широкий круг задач удается изящно и эффективно решать с помощью оконных функций. В SQL так много компонентов, связанных с оконными функциями, в том числе вложенные оконные функции, более мощные возможности RANGE и т. д. Надеюсь, кто-нибудь из сотрудников Microsoft прочитает эту статью и продолжит добавлять важные, но пока отсутствующие функции.
