Что такое оконная функция в SQL?

А, ну это про SQL-окна, да? Ёпта, тема-то мощная, но пока не врубишься — голова квадратная становится. Слушай сюда, я тебе на пальцах, как для дебила, объясню, только не обижайся.

Представь, что у тебя таблица с данными, как будто это все сотрудники в офисе сидят. Обычная агрегатная функция с GROUP BY — это как начальник, который заходит и орёт: «Так, все по отделам собрались! Света, сколько у вас отдел в целом заработал? Сверни всё в одну строчку отчёта!». И всё, личности каждого сотрудника — нахуй, есть только общая сумма по отделу.

А оконная функция — это хитрая жопа. Это как будто ты каждому сотруднику выдаёшь по умному монитору. Он сидит на своём месте, смотрит на свой оклад, но на мониторе у него ещё куча инфы: «Ты, Вася, в своём отделе по зарплате на таком-то месте», или «Средняя температура по больнице в твоём департаменте вот такая». При этом все строки-сотрудники остаются на своих местах, их не сворачивают в одну. Удобно, блядь? Каждый при своих данных, но с аналитикой сверху.

Вот из чего эта магия собирается, в рот мне чих-пых:

• PARTITION BY — Это разбивка на куски. Как GROUP BY, только без агрегации. Сказал PARTITION BY department — и всё, теперь вычисления идут в рамках каждого отдела отдельно. Для отдела бухгалтерии — свои цифры, для IT — свои. Без этого — всё мешается в одну кучу.
• ORDER BY — Ну тут всё ясно, чувак. Порядок внутри этого куска. Кого считать первым, кого последним. Без него иногда можно, но часто — нихуя не выйдет.
• ROWS/RANGE BETWEEN — Вот это уже поинтереснее. Это границы «окошка», которое ездит вдоль строк. Типа «считай не по всем строкам партиции, а только по двум предыдущим и текущей». Для скользящих средних — самое то.

Какие бывают эти функции, ёпта:

1. Агрегатные (SUM, AVG): Те же самые, но не убивают группу. Можешь для каждой строки вывести и её зарплату, и сумму по отделу. Красота.
2. Ранжирующие (ROW_NUMBER, RANK): Это чтобы места распределять. Кто первый, кто второй. RANK может давать одинаковые места и делать дырки, а DENSE_RANK — без дырок. Запутаться — раз плюнуть.
3. Функции смещения (LAG, LEAD): Вообще огонь. LAG — глянь на строчку выше, что там было. LEAD — глянь на строчку ниже. Для анализа «а что было вчера?» — идеально. Раньше для этого самообъединения городили, терпения ноль ебать.

Смотри, как это в жизни выглядит, на примерах:

-- Пример 1: Рейтинг богатеньких буратин в каждом отделе
SELECT
    department,
    name,
    salary,
    -- Внутри каждого отдела (PARTITION BY department) сортируем по ЗП 
    -- и каждому приклеиваем номерок по порядку
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank_in_dept
FROM employees;

Тут каждый работяга увидит, на каком он месте в своей песочнице. Сам от себя охуеет, если окажется последним.

-- Пример 2: Скользящее среднее за три дня. Чтобы понять, не пизда ли тренду.
SELECT
    date,
    revenue,
    -- Сортируем по дате и для каждой строки берём среднее 
    -- по ней самой и двум предыдущим (PRECEDING)
    AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg_3day
FROM daily_sales;

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

-- Пример 3: Унижение менеджеров путём сравнения с лучшим в городе
SELECT
    city,
    manager,
    sales,
    -- Разбиваем по городам, в каждом городе сортируем по продажам от лучшего к худшему,
    -- и берём значение из самой первой строки (FIRST_VALUE) — это и есть максимум.
    FIRST_VALUE(sales) OVER (
        PARTITION BY city
        ORDER BY sales DESC
        -- Вот эта штука ROWS BETWEEN... говорит "смотри на весь фрейм целиком", 
        -- а не только на строки до текущей. Без неё LAST_VALUE будет работать иначе.
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as best_sales_in_city
FROM manager_performance;

Каждый менеджер будет видеть свои жалкие продажи и рядом — цифру короля города. Мотивация, блядь.

Короче, суть в чём: эти функции — мощнейший инструмент. Раньше, чтобы такое сделать, приходилось таблицы сами на себя джойнить или подзапросы корявые писать, а теперь — одна строка с OVER(). Ёперный театр, как же раньше-то жили.