• Схема данных - это дополнительный уровень структуризации реляционной базы данных.
• Схема позволяет собирать таблицы в группы, например, по типу товаров.
• Для обращения к схеме данных нужно указать её перед названием таблицы через точку.
• По умолчанию, таблицы в PostgreSQL помещаются в схему public, но можно указать конкретную схему при создании таблицы.
• В рамках курса будут работать с двумя схемами: online_store и tools_shop.

• Исследование данных: теория
• Работа с новой базой данных - с данными интернет-магазина
• Две схемы: online_store и tools_shop
• Таблицы схемы online_store: profiles, sessions, events, orders, costs
• Вы уже работали с ER-диаграммами
• В крупных компаниях даже существуют целые отделы, которые занимаются ETL
• Чтобы убедиться, что таблицы связаны по какому-либо полю, можно сделать несколько проверочных запросов

• Исследование данных: практика
• Работа со схемой tools_shop
• Таблицы схемы tools_shop относятся к разным отделам интернет-магазина
• Таблица users содержит данные о пользователях
• Таблица events содержит данные о событиях
• Таблица event_x_parameter содержит детальную информацию о событиях
• Таблица orders содержит детальные данные о заказах пользователей
• Таблица items содержит данные о товарах
• Таблица order_x_item содержит данные о связи заказа с товарами
• Таблица costs содержит данные о стоимости привлечения пользователей

• Оконные функции выполняют вычисления для набора записей, объединенных по какому-либо признаку.
• Оконные функции не объединяют записи в одну, сохраняя независимость записей.
• Оконные функции возвращают столько же значений, сколько получили на вход.
• Оконные функции могут быть использованы для решения задач, которые не могут быть решены с помощью оператора GROUP BY.
• Выражение OVER определяет, как разделить записи, которые обработает функция.
• Внутри выражения OVER находится оператор PARTITION BY, который разделяет записи на группы в зависимости от значения в поле.
• Выражение OVER может включать другие операторы или быть совсем пустым.

• Окно можно задать с помощью SELECT, WHERE и ORDER BY.
• Выражение OVER с оператором PARTITION BY разделяет записи на разделы и применяет оконную функцию к каждому разделу.
• Пустое выражение OVER равнозначно агрегирующей функции.
• Другие агрегирующие функции, такие как MIN(event_dt) OVER (PARTITION BY user_id), также могут использоваться в оконных функциях.
• Если все значения в поле уникальны, оконная функция будет рассчитана в каждой записи отдельно.

• Оператор PARTITION BY объединяет записи в разделы, аналогично оператору GROUP BY.
• PARTITION BY может использоваться для расчета среднего чека для каждого клиента.
• После PARTITION BY можно указать несколько полей для формирования окна.
• Порядок указания полей после PARTITION BY не важен.
• Для расчета значений по разделам можно использовать другие функции, такие как MIN(), MAX(), COUNT() и SUM().

• Оконные функции позволяют ранжировать записи в SQL с помощью функции ROW_NUMBER().
• ROW_NUMBER() возвращает порядковый номер записи в окне.
• Пример использования функции: SELECT ROW_NUMBER() OVER () FROM online_store.orders WHERE user_id IN (300768196, 840452722, 59432616).
• Результат выполнения функции ROW_NUMBER() можно добавить в отдельное поле.
• ROW_NUMBER() не требует аргумента.
• Функция ROW_NUMBER() не изменяет порядок записей, если добавить оператор ORDER BY.
• Ранжировать записи можно по-другому, добавив оператор ORDER BY в выражение OVER.

• Оператор ORDER BY сортирует записи в таблице по возрастанию или убыванию значения в поле user_id.
• Функция ROW_NUMBER() пронумерует записи в зависимости от их позиции в таблице.
• Порядок ранжирования можно управлять, добавив оператор ORDER BY с полем, по которому сортировать записи.
• Ранжировать записи можно не только по возрастанию, но и по убыванию, используя ключевое слово DESC.
• Выражение с оператором ORDER BY может содержать несколько полей для сортировки по нескольким полям сразу.
• С помощью оконной функции ROW_NUMBER() и оператора ORDER BY можно выбрать запись с определённым рангом.
• Проранжированные записи можно поместить во временную таблицу и из неё уже выбрать запись с нужным рангом.

• Функции RANK() и DENSE_RANK() отличаются логикой ранжирования повторяющихся значений.
• ROW_NUMBER() присваивает следующий ранг повторяющимся значениям.
• RANK() присваивает одинаковый ранг одинаковым значениям.
• DENSE_RANK() не учитывает количество записей и назначает ранги последовательно.

• NTILE() - функция ранжирования в SQL, которая назначает записям фиксированное количество рангов в зависимости от аргумента.
• Функция NTILE() с аргументом 3 разделит записи на три группы в зависимости от значения revenue.
• Синтаксис NTILE() похож на другие функции ранжирования, но принимает в качестве аргумента количество рангов или групп.
• Если записи не получается разбить на группы поровну, предпочтение отдаётся первым группам.
• В запросе ниже функции NTILE() передали аргумент 5, и в группу с рангом 1 попадут две записи, а в остальные - по одной.

• Операторы PARTITION BY и ORDER BY влияют на работу оконных функций.
• Комбинация операторов позволяет разделить строки на группы и отсортировать значения внутри групп.
• Сочетание с ROW_NUMBER() позволяет проранжировать значения внутри групп.
• Можно выбрать данные о втором или третьем по счету заказе для каждого пользователя.
• Функцию NTILE() можно использовать в сочетании с оператором PARTITION BY для разделения записей на группы внутри каждой группы, сформированной PARTITION BY.

• Оконные функции делятся на агрегирующие и неагрегирующие.
• Агрегирующие функции включают AVG, MIN, MAX, SUM и COUNT.
• Агрегирующие функции требуют указания OVER и передачи аргумента - поля для расчета значения.
• Можно использовать операторы PARTITION BY и ORDER BY в выражении OVER.
• Пример использования агрегирующих функций: нахождение среднего чека по дням с указанием даты заказа.
• Пример использования агрегирующих функций: определение даты последнего заказа для каждого пользователя.
• Пример использования агрегирующих функций: подсчет суммы выручки за каждый день с указанием даты заказа.

• Оператор ORDER BY в выражении OVER позволяет сортировать данные и производить вычисления кумулятивно.
• Сумма с накоплением показывает сумму всех чисел до текущего значения.
• Пример расчета суммы с накоплением для канала Yandex: SELECT costs, SUM(costs) OVER (ORDER BY dt) AS costs_cum FROM online_store.costs WHERE channel = 'Yandex'.
• Для расчета кумулятивных значений по разделам можно использовать функцию COUNT().
• Функции MIN(), MAX(), AVG() также используются для расчета кумулятивных значений.

• Функции смещения LEAD() и LAG() относятся к третьему типу оконных функций - функциям смещения.
• Функции LEAD() и LAG() возвращают данные из других записей в зависимости от их расстояния от текущей записи.
• С помощью функций смещения можно для каждой записи с заказом пользователя вернуть дату прошлого или следующего заказа.
• Функция LAG() позволяет возвращать предыдущие записи, а LEAD() - следующие.
• Можно посчитать значения в сравнении с предыдущим или следующим периодом.

• Оконные функции позволяют упростить запросы с несколькими окнами и упростить код.
• Окно как переменная: определение окна можно вынести в переменную, используя конструкцию WINDOW.
• Ограничения оконных функций: использование DISTINCT с агрегирующими функциями невозможно, оконные функции нельзя сочетать с группировкой, оконные функции нельзя использовать в условиях после WHERE.
• Вместо группировки можно использовать DISTINCT в начале запроса.
• Третье ограничение: оконные функции нельзя использовать в условиях после WHERE, как и агрегирующие функции.

Образовательные результаты

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

• узнать, что агрегирующие функции можно использовать в качестве оконных;
• поработать с основными операторами окна: PARTITION BY и ORDER BY;
• научиться применять функции ранжирования: ROW_NUMBER(), RANK(), DENSE_RANK();
• потренироваться работать с функциями смещения LEAD() и LAG();
• использовать оконные функции для расчёта кумулятивных значений;
• узнать, как сохранять определение окна в конструкцию с WINDOW.

Что ещё почитать

Документация PostgreSQL с полным списком оконных функций.

Что дальше

Вы прошли последнюю тему курса. Впереди — самостоятельный проект.