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

-- Каждая группа превращается в одну строку
SELECT 
    department_id,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- Результат: 3 строки (по одной на отдел)
-- Отдел 1 | 50000
-- Отдел 2 | 55000
-- Отдел 3 | 48000
-- Пропала информация о каждом сотруднике

-- Каждая строка остается, плюс добавляется вычисление по группе
SELECT 
    name,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary_by_dept
FROM employees;

-- Результат: все строки (каждый сотрудник)
-- Иванов  | Отдел 1 | 50000 | 50000
-- Петров  | Отдел 1 | 60000 | 50000
-- Сидоров | Отдел 2 | 55000 | 55000
-- У каждой строки появилась средняя зарплата по ее отделу

function_name(...) OVER (
    PARTITION BY column1, column2, ...   -- Деление на группы (как GROUP BY)
    ORDER BY column1, column2, ...       -- Порядок строк внутри группы
    ROWS | RANGE BETWEEN ... AND ...     -- Окно (какие строки включить)
)

-- Вычислить среднюю зарплату по всем сотрудникам (одна группа)
SELECT 
    name,
    salary,
    AVG(salary) OVER () AS overall_avg_salary
FROM employees;

-- Результат: у каждой строки одна и та же общая средняя
-- Иванов  | 50000 | 52500
-- Петров  | 60000 | 52500
-- Сидоров | 45000 | 52500
-- Смирнов | 55000 | 52500

SELECT 
    order_id,
    customer_id,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
    AVG(amount) OVER (PARTITION BY customer_id) AS customer_avg,
    COUNT(*) OVER (PARTITION BY customer_id) AS customer_order_count,
    MIN(amount) OVER (PARTITION BY customer_id) AS customer_min,
    MAX(amount) OVER (PARTITION BY customer_id) AS customer_max
FROM orders;

SELECT 
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
    NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC) AS quartile
FROM employees;

SELECT 
    date,
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY date) AS previous_day_amount,
    LEAD(amount, 1, 0) OVER (ORDER BY date) AS next_day_amount,
    amount - LAG(amount, 1, 0) OVER (ORDER BY date) AS day_over_day_change,
    FIRST_VALUE(amount) OVER (ORDER BY date) AS first_amount,
    LAST_VALUE(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount
FROM daily_sales;

SELECT 
    date,
    amount,
    -- Накопительный итог (от начала до текущей строки)
    SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS running_total,
    -- Скользящее среднее за 7 дней (текущий + 3 дня назад + 3 дня вперед)
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS moving_avg_7d,
    -- Только предыдущие 3 дня
    SUM(amount) OVER (ORDER BY date ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS prev_3_days_sum
FROM daily_sales;

-- Без PARTITION BY (одна группа — вся таблица)
SELECT 
    name,
    department,
    salary,
    AVG(salary) OVER () AS company_avg  -- 52500 для всех
FROM employees;

-- С PARTITION BY (группировка по отделам)
SELECT 
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg  -- своя средняя для каждого отдела
FROM employees;

SELECT 
    order_id,
    year,
    month,
    amount,
    AVG(amount) OVER (PARTITION BY year, month) AS monthly_avg
FROM orders;

SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

-- Самый высокооплачиваемый получает ранг 1

-- Без ORDER BY: агрегат по всей группе (одинаков для всех строк)
SELECT 
    date,
    amount,
    SUM(amount) OVER () AS total  -- 1000 для всех строк
FROM sales;

-- С ORDER BY: накопительный итог
SELECT 
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) AS running_total  -- растет с каждой датой
FROM sales;

-- Эти два выражения эквивалентны
SUM(amount) OVER (ORDER BY date)
SUM(amount) OVER (ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

ROWS | RANGE BETWEEN start AND end

-- Данные
-- date       | amount
-- 2024-01-01 | 100
-- 2024-01-01 | 200
-- 2024-01-02 | 300

-- ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
-- Для второй строки (2024-01-01, 200): включает предыдущую строку (100)
-- Для третьей строки (2024-01-02, 300): включает предыдущую строку (200)

-- RANGE BETWEEN 1 PRECEDING AND CURRENT ROW (при ORDER BY date)
-- Для строк 2024-01-01: включает все строки с date >= 2023-12-31 (только эти же строки)
-- Для строки 2024-01-02: включает все строки с date >= 2024-01-01 (все три строки)

SELECT 
    date,
    amount,
    
    -- Накопительный итог от начала до текущей строки
    SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS running_total,
    
    -- Скользящее среднее за 3 дня (текущий + предыдущий + следующий)
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg_3d,
    
    -- Сумма за 7 дней до текущей (исключая текущий)
    SUM(amount) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS prev_7_days,
    
    -- Сумма от начала группы до конца (окно "все строки")
    SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total,
    
    -- Сумма только текущей строки и двух следующих
    SUM(amount) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS current_and_next_2
    
FROM daily_sales
ORDER BY date;

SELECT 
    date,
    amount,
    LAG(amount) OVER (ORDER BY date) AS prev_amount,        -- предыдущее значение
    LEAD(amount) OVER (ORDER BY date) AS next_amount,      -- следующее значение
    amount - LAG(amount) OVER (ORDER BY date) AS change,   -- изменение
    CASE 
        WHEN amount > LAG(amount) OVER (ORDER BY date) THEN 'up'
        WHEN amount < LAG(amount) OVER (ORDER BY date) THEN 'down'
        ELSE 'same'
    END AS trend
FROM daily_sales;

SELECT 
    date,
    amount,
    LAG(amount, 3) OVER (ORDER BY date) AS amount_3_days_ago,    -- 3 дня назад
    LEAD(amount, 7) OVER (ORDER BY date) AS amount_7_days_ahead  -- через 7 дней
FROM daily_sales;

SELECT 
    date,
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY date) AS prev_amount,  -- 0 для первой строки
    LEAD(amount, 1, 0) OVER (ORDER BY date) AS next_amount  -- 0 для последней строки
FROM daily_sales;

-- Сравнение с предыдущим заказом того же клиента
SELECT 
    order_id,
    customer_id,
    order_date,
    amount,
    LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_customer_amount,
    amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS change_from_prev
FROM orders;

SELECT 
    department,
    name,
    salary,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_paid,
    LAST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_paid
FROM employees;

-- Неправильно (вернет текущую строку)
LAST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS wrong

-- Правильно
LAST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS correct

-- Вторая по величине зарплата в каждом отделе
SELECT 
    department,
    name,
    salary,
    NTH_VALUE(salary, 2) OVER (PARTITION BY department ORDER BY salary DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_highest_salary
FROM employees;

-- Найти дублирующиеся email
SELECT 
    id,
    name,
    email,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users
WHERE rn > 1;  -- Для фильтрации нужен подзапрос, так как оконные функции нельзя в WHERE

-- С подзапросом
SELECT * FROM (
    SELECT 
        id,
        name,
        email,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
) t
WHERE rn > 1;

-- В PostgreSQL (через CTE)
WITH duplicates AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);

SELECT 
    product_id,
    sales_amount,
    sales_amount / SUM(sales_amount) OVER () * 100 AS percentage_of_total
FROM monthly_sales
WHERE year = 2024;

SELECT 
    product_id,
    category,
    sales_amount,
    sales_amount / SUM(sales_amount) OVER (PARTITION BY category) * 100 AS percentage_in_category
FROM monthly_sales
WHERE year = 2024;

SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS monthly_amount,
    SUM(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS cumulative_amount
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

SELECT 
    month,
    amount,
    LAG(amount) OVER (ORDER BY month) AS prev_month_amount,
    ((amount - LAG(amount) OVER (ORDER BY month)) / LAG(amount) OVER (ORDER BY month)) * 100 AS growth_percent
FROM monthly_sales;

SELECT * FROM (
    SELECT 
        department,
        name,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn <= 3;

-- В PostgreSQL (с percentile_cont)
SELECT 
    date,
    amount,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) 
        OVER (ORDER BY date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS median_7d
FROM daily_sales;

-- Если в daily_sales есть пропущенные даты, заполняем последним известным значением
SELECT 
    date,
    COALESCE(amount, LAST_VALUE(amount IGNORE NULLS) OVER (ORDER BY date)) AS filled_amount
FROM daily_sales;

SELECT 
    column1,
    column2,
    ROW_NUMBER() OVER (ORDER BY column3) AS rn
FROM table1
WHERE column1 > 100
GROUP BY column1, column2
HAVING COUNT(*) > 1
ORDER BY column3;

-- Это НЕ работает
SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 10;  -- Ошибка! Оконная функция недоступна в WHERE

-- Это работает
SELECT * FROM (
    SELECT 
        name,
        salary,
        ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn <= 10;

-- FILTER позволяет включать только определенные строки
SELECT 
    department,
    SUM(salary) FILTER (WHERE position = 'manager') OVER (PARTITION BY department) AS managers_total
FROM employees;

-- WINDOW clause для повторного использования
SELECT 
    date,
    amount,
    SUM(amount) OVER w AS running_total,
    AVG(amount) OVER w AS running_avg
FROM daily_sales
WINDOW w AS (ORDER BY date ROWS UNBOUNDED PRECEDING);

-- Плохо: разные PARTITION BY требуют разных сортировок
SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary) AS rn1,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY salary) AS rn2
FROM employees;
-- Возможно, две сортировки

-- Лучше: одинаковый PARTITION BY, одна сортировка
SELECT 
    *,
    ROW_NUMBER() OVER w AS rn1,
    RANK() OVER w AS rn2
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary);

-- Ошибка
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 10;

-- Исправление
SELECT * FROM (
    SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn <= 10;

-- Ошибка: LAST_VALUE вернет текущую строку
SELECT 
    name,
    salary,
    LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) AS lowest_salary
FROM employees;

-- Исправление
SELECT 
    name,
    salary,
    LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_salary
FROM employees;

-- Может дать неожиданные результаты при одинаковых значениях ORDER BY
SELECT 
    date,
    amount,
    SUM(amount) OVER (ORDER BY date RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS range_sum
FROM sales;
-- При одинаковых датах включит все строки с этой датой

-- DISTINCT применяется ПОСЛЕ оконных функций
SELECT DISTINCT
    department,
    ROW_NUMBER() OVER (ORDER BY department) AS rn
FROM employees;
-- rn будет случайным, а не 1,2,3

-- ROW_NUMBER дает уникальные номера (1,2,3,4,5)
-- RANK дает ранги с пропусками (1,2,2,4,5)
-- Выбирайте нужный в зависимости от бизнес-требований