Повысьте свои навыки SQL с помощью этого простого объяснения

Таблица содержания

  1. Вступление
  2. Что такое оконные (аналитические) функции?
  3. Анатомия оконной функции
  4. Пример функции агрегирования и функции окна
  5. Преимущества оконных функций

Вступление

Вам удобны простые запросы (SELECT FROM WHERE GROUP BY), и вы хотите поднять свои навыки на новый уровень. Что вам следует узнать дальше?

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

Что такое оконные (аналитические) функции?

Оконная функция похожа на агрегатную функцию в том смысле, что она возвращает агрегированные значения (например, SUM (), COUNT (), MAX ()).

Отличие оконных функций заключается в том, что они не группируют набор результатов. Количество строк на выходе совпадает с количеством строк на входе.

Если вам это пока не понятно, не волнуйтесь. Придерживайтесь примера, и он будет иметь гораздо больше смысла. :)

Анатомия оконной функции

Базовая анатомия оконной функции такова:

SELECT SUM() OVER(PARTITION BY ___ ORDER BY ___) FROM Table

Чтобы упростить понимание, нужно запомнить три основные части:

  1. Агрегатная функция: в приведенном выше примере я использовал SUM (), но вы также можете использовать COUNT (), AVG () и т. д.
  2. PARTITION BY: Просто подумайте об этом как о предложении GROUP BY, но вместо этого оно называется PARTITION BY.
  3. ORDER BY: ORDER BY соответствует ожидаемому. Это важно учитывать, когда важен порядок вывода.

Пример функции агрегирования и функции окна

Для большей ясности предположим, что у нас есть следующая таблица:

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

SELECT Gender, AVG(GPA) as avg_gpa
FROM students
GROUP BY Gender

Следующая часть является ключевой.

Теперь предположим, что мы хотим получить следующий результат:

Мы могли использовать агрегатную функцию (например, приведенную выше), а затем присоединить результаты к исходной таблице, но для этого потребуется два шага.

Вместо этого мы можем использовать оконную функцию для достижения того же результата:

SELECT *,
    AVG(GPA) OVER (PARTITION BY Gender) as avg_gpa
FROM table

С помощью приведенного выше запроса мы разделяем (разбиваем) данные по полу и вычисляем средний средний балл для каждого пола. Затем он создает новый столбец с именем avg_gpa и добавляет связанный средний средний балл для каждой строки.

Преимущества оконных функций

Использование оконных функций дает три основных преимущества:

Простота

Как только вы освоитесь, оконные функции станут намного проще в использовании. Обратите внимание, что в приведенном выше примере требуется только одна дополнительная строка для получения желаемого вывода с использованием оконной функции, в отличие от использования функции агрегирования с последующим объединением результатов.

Скорость

В связи с вышеизложенным, использование оконных функций намного быстрее, чем использование альтернативного метода. Это пригодится, когда вы обрабатываете сотни или тысячи гигабайт данных.

Универсальность

Что наиболее важно, существует масса универсальности оконных функций, которая выходит за рамки этой статьи. Некоторые примеры включают добавление скользящего среднего, добавление номера строки и отставание данных.

Спасибо за прочтение!

Прочитав это, вы должны понять, что такое оконная функция, как написать оконную функцию и преимущества оконной функции.

Надеюсь, эти примеры облегчили понимание того, как работает оконная функция. Как всегда, я желаю вам удачи в ваших учебных начинаниях!

Теренс Шин