SQL Server: вычисляем предыдущее и следующее значение с условием. Решение с оконными функциями
Содержание:
1. Решение с фильтром TOP;
2. Решение с оконными функциями (Вы читаете данный раздел).
Эту задачу можно решить с использованием оконных функций, составив план со значительно меньшим числом операций чтения. Прежде всего, следует вычислить значение gooddt, представляющее текущую дату, для которой значение осадков превышает 24 (NULL в другом случае). Затем вычислите prevdt с использованием оконной функции МАХ, применяемой к gooddt, с кадром ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING. Аналогично, вычислите nextdt с использованием оконной функции MIN, применяемой к gooddt, с кадром ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING. Остальное — так же, как в предыдущем решении. В коде, представленном ниже, приводится полный запрос решения.
Решение с оконными функциями
План для решения с оконными функциями
Для данного запроса действует план, показанный на скриншоте, представленном выше. Для выполнения этого запроса потребовалась 51 секунда (и 33 секунды при вычислении только diffprev). Однако важно, что число операций чтения уменьшилось на три порядка величины, до 64769 (учитывая связанные со сбросом сортированных данных в tempdb). Таким образом, этот запрос породит гораздо меньшую конкуренцию за ресурсы ввода-вывода в среде с многочисленными одновременно выполняемыми запросами.
Для дальнейшей оптимизации можно воспользоваться приемом параллельного CROSS APPLY Адама Маханика. Вы направляете запрос к таблице Locations и с помощью оператора CROSS APPLY применяете логику решения к одному местоположению. Этот прием обычно улучшает параллельную обработку и разбивает такие операции, как сортировка, которые более линейно масштабируются в многочисленные мелкие операции, в целом выполняемые с большей производительностью.
Логика инкапсуляции для одного местоположения во встроенной функции с табличным значением
Для реализации данного метода сначала воспользуйтесь программным кодом, представленным выше, чтобы инкапсулировать логику решения для одного местоположения во встроенной функции с табличным значением.
1. Решение с фильтром TOP;
2.
Эту задачу можно решить с использованием оконных функций, составив план со значительно меньшим числом операций чтения. Прежде всего, следует вычислить значение gooddt, представляющее текущую дату, для которой значение осадков превышает 24 (NULL в другом случае). Затем вычислите prevdt с использованием оконной функции МАХ, применяемой к gooddt, с кадром ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING. Аналогично, вычислите nextdt с использованием оконной функции MIN, применяемой к gooddt, с кадром ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING. Остальное — так же, как в предыдущем решении. В коде, представленном ниже, приводится полный запрос решения.
Решение с оконными функциями
План для решения с оконными функциями
Для данного запроса действует план, показанный на скриншоте, представленном выше. Для выполнения этого запроса потребовалась 51 секунда (и 33 секунды при вычислении только diffprev). Однако важно, что число операций чтения уменьшилось на три порядка величины, до 64769 (учитывая связанные со сбросом сортированных данных в tempdb). Таким образом, этот запрос породит гораздо меньшую конкуренцию за ресурсы ввода-вывода в среде с многочисленными одновременно выполняемыми запросами.
Для дальнейшей оптимизации можно воспользоваться приемом параллельного CROSS APPLY Адама Маханика. Вы направляете запрос к таблице Locations и с помощью оператора CROSS APPLY применяете логику решения к одному местоположению. Этот прием обычно улучшает параллельную обработку и разбивает такие операции, как сортировка, которые более линейно масштабируются в многочисленные мелкие операции, в целом выполняемые с большей производительностью.
Логика инкапсуляции для одного местоположения во встроенной функции с табличным значением
Для реализации данного метода сначала воспользуйтесь программным кодом, представленным выше, чтобы инкапсулировать логику решения для одного местоположения во встроенной функции с табличным значением.
