Оконные функции SQL

Нет более обманчивого раздела SQL, чем «оконные функции». Когда слышишь эти слова, думаешь «наверно, просто придумали какие-то дополнительные функции». А вот и нет! «Оконные функции» — это отдельный язык, встроенный в обычный SQL. И он сложнее, чем все, что вы знали о «селектах», вместе взятое.

Если вкратце — оконные функции помогают делать классные аналитические отчеты без участия «экселя» и pandas. Хотите посчитать процент продаж по месяцам от общих продаж за год? Оконные функции. Разделить маркетинговые каналы на эффективные и неэффективные? Оконные функции. Выбрать топ-10 клиентов по каждому сегменту? Тоже они.

Я прочитал несколько десятков статей «для начинающих», которые объясняли, что такое оконные функции. Все они страдали от одной из двух проблем:

  1. Все понятно, но описано 10% возможностей «окошек».
  2. Написано так сложно, что если бы я уже не знал предмет обсуждения — ничего бы не понял.
Как я вижу сложные запросы
Если не разобраться в запросах с «окошками», выглядят они как-то так

Я решил исправить ситуацию и подготовил интерактивный курс об оконных функциях SQL.

О курсе

Это понятное и наглядное введение в оконные функции. Понятное, потому что я умею доходчиво писать о сложных темах. Наглядное — потому что подготовил сотню картинок и пятьдесят упражнений, которые помогут глубоко понять «окошки».

Оконные функции — реально сложная тема. Поэтому курс не пытается объять необъятное, и объясняет их шаг за шагом. А еще на курсе много практики — потому что только с ней абстрактные знания превратятся в навыки.

Обложка курса
О курсе
Песочница
Ранжирование
Фрейм 1
Фрейм 2
Скользящие агрегаты
Медиана

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

Курс можно проходить в онлайн-песочнице, либо использовать любую из следующих СУБД:

  • MySQL 8.0.2+ (MariaDB 10.2+)
  • PostgreSQL 11+
  • SQLite 3.28+
  • MS SQL 2012+
  • Oracle 11g+

Записаться на курс

Чему вы научитесь

Зачем нужны оконные функции

Часть 1. Окна и функции

Часть 2. Фреймы

  • ROWS и GROUPS
  • RANGE
  • EXCLUDE
  • FILTER

Часть 3. Практика

  • Финансы
  • Кластеризация
  • Очистка данных
Постер
Каждая глава подробно раскрывает одну тему
Подробное содержание

Введение

О курсе

Зачем нужны оконные функции

Песочница

Часть 1. Окна и функции

Ранжирование

  • Оконная функция
  • Сортировка окна и сортировка результатов
  • Однозначность сортировки
  • Несколько окон
  • Секции окна
  • Группы
  • Функции ранжирования

Смещение

  • Сравнение с соседями
  • Сравнение с границами
  • Окно, секция, фрейм
  • Сравнение с границами, окончание
  • Функции смещения

Агрегация

  • Агрегат по секции
  • Фильтрация и порядок выполнения
  • Описание окна
  • Функции агрегации

Скользящие агрегаты

  • Скользящее среднее
  • Фрейм
  • Сумма нарастающим итогом
  • Фрейм по умолчанию
  • Функции для скользящих агрегатов

Статистика

  • Кумулятивное распределение
  • Относительный ранг
  • Сводные значения
  • Процентили в SQL
  • Процентиль как оконная функция
  • Статистические функции

Резюме

Часть 2. Фреймы

ROWS и GROUPS

  • ROWS-фреймы
  • GROUPS-фреймы

RANGE

  • RANGE-фреймы
  • Особенности диапазонов
  • Границы фрейма
  • Фрейм по умолчанию
  • Частые вопросы о фреймах

EXCLUDE

  • Исключение строк
  • Виды исключений

FILTER

  • Фильтрация строк
  • CASE как альтернатива FILTER

Резюме

Часть 3. Практика

Финансы

  • Сначала агрегация, затем окна
  • Сначала окна, затем отсев
  • Агрегация и null
  • Выручка по тарифу gold
  • Выручка по тарифам за 1 квартал
  • Скользящее среднее по тарифу platinum
  • Сравнение с декабрем
  • Вклад тарифов
  • Высокая, средняя и низкая выручка
  • 2020 vs 2019
  • Рейтинг месяцев по продажам

Кластеризация

  • Идентифицируем острова
  • Острова с дублями
  • Острова на датах
  • Кластеры значений
  • Кластеры на датах

Очистка данных

  • Дубли
  • Пропуски
  • Предыдущее непустое значение
  • Выбросы

Финал

Курс начинается с основ «окошек» (часть 1), погружается в нюансы фреймов (часть 2) и заканчивает довольно продвинутыми приемами (часть 3).

Первые пять уроков (без задач) доступны по ссылкам в оглавлении выше.

Об авторе

Я Антон Жиянов, Python/Golang разработчик и энтузиаст SQLite. Работаю над опенсорсом, веду курсы и блог.

В 2021 я запустил курс по оконным функциям SQL. Сейчас у него больше 1000 выпускников и 340 отзывов со средним рейтингом 5 звезд.

Записаться на курс

Подписывайтесь на канал, чтобы не пропустить новые заметки 🚀