Элементарный пример...
Многие из нас используют group by. До знакомства с оконными функциями, стыдно признаться, что моим лучшим решением было выполнить самосоединение для извлечения исходного значения. Я и не подозревал, что этот метод очень неэффективен и неэффектен.
Однако, выполнив group by, мы получим только одно значение в ответ. Во многих случаях мы хотели видеть исходное значение рядом с агрегированным значением, чтобы провести некоторое сравнение. Какова наилучшая практика для достижения этого?
Гораздо лучший способ решить эту проблему - использовать оконную функцию.
Что такое оконная функция?
Как цитируется из Википедии, оконная функция - это функция, которая использует значения из одной или нескольких строк для возврата значения для каждой строки. С помощью оконной функции самосоединение больше не требуется для отображения как исходных, так и агрегированных значений.
Пример
SELECT date, month, day_temperature, AVG(day_temperature)
OVER (PARTITION BY month ORDER BY date) as 'monthly_average'
FROM temperature
Итак, как работает эта оконная функция?
Оконная функция обрабатывает агрегатную функцию в заданном нами ‘окне’. "Окно" - это набор правил, установленных PARTITION BY и ORDER BY. Из-за того, что совокупные правила устанавливаются в операторе select, GROUP BY больше не требуется.
Давайте разберем синтаксис оконной функции.
PARTITION BY это важнейшая часть оконной функции. Здесь устанавливаются правила группировки. В приведенном выше примере запроса значение day_temperature будет усреднено (x) в пределах тех же month значения, поскольку для правила установлено значение PARTITION BY month.
Через ORDER BY здесь мы устанавливаем правило порядка в окне. В агрегатной функции заказы обычно игнорируются (за исключением sum or count, когда ORDER BY используется, он будет вычисляться накопительно. В данном случае в пределах месяца. Когда он не используется, он вычисляет общее количество), однако ORDER BY обычно используется при ранжировании оконных функций.
И вот Результат запроса:
В поле monthly average вычисляется средняя day_temperature каждого месяца.
Типы оконных функций
В общем, оконную функцию можно разделить на 3 категории, которые:
1. Агрегатная оконная функция — в основном выполняет агрегатную функцию, но group by не требуется. Сюда входят все агрегированные функции, такие как SUM, COUNT, AVG, MIN, MAX
2. Функция ранжирования окна — для определения значения на основе ранга в окне. Функция окна ранжирования: CUME_DIST, DENSE_RANK, NTILE, PERCENT_RANK, RANK, ROW_NUMBER
3. Функция окна значений — для извлечения определенных данных в окне. Функция окна значений: ЗАДЕРЖКА, ОПЕРЕЖЕНИЕ, FIRST_VALUE, LAST_VALUE
Примеры использования
Оконные функции могут широко использоваться во многих случаях, поскольку их можно комбинировать с другими функциями. Однако, по моему опыту, наиболее распространенным использованием оконных функций являются:
- Присвоение рангов
- Сравнение значения со средним
- Сравнение значения со следующим значением.
Я приведу пример того, как сравнивать значения с помощью оконной функции.
В таблице температур выше у нас пустое is_hotter_than_yesterday поле, и мы хотим сравнить только в течение того же месяца.
Чтобы заполнить это поле, нам нужно сравнить сегодняшнюю температуру со вчерашней температурой и преобразовать результат в строку ‘ДА’ если вчера было холоднее, или ‘НЕТ’, если вчера было жарче. В пределах одного месяца....
SELECT date, month, day_temperature,
CASE WHEN LEAD(day_temperature,1)
OVER (PARTITION BY month ORDER BY date ASC) < day_temperature
then 'YES' ELSE 'NO' END
FROM temperature
LEAD (return_value, offset) используется для извлечения предыдущего значения в течение месяца раздела. ORDER BY используется для предвидения, если date не отсортированы и беспорядочны.
После сравнения значений нам нужно преобразовать сравнение в ‘ДА’ и ‘НЕТ’. В этом случае я использую case when выражение.
Запрос вернет:
Обратите внимание, что у нас есть 2 пропущенных значения. Для 27 июня 2021 года и 01 июля 2021 года нет предыдущих значений, потому что мы разделены по месяцам. Таким образом, его нельзя сравнивать, возвращая значение null.
Существует бесконечное количество комбинаций функций SQL. Подходите к своим запросам более творчески, и в конечном итоге вы найдете наилучшую практику для решения своей проблемы. Как говорят люди, практика делает совершенным!