Подзапросы, CTE и оконные функции

Собираем сложные запросы из частей: подзапросы, CTE и оконные функции PostgreSQL.

CTE (Common Table Expression) — именованный временный результат, объявленный через WITH, который делает сложный запрос читаемым, разбивая его на шаги.

Подзапросы

Подзапрос — это SELECT внутри другого запроса. Чаще всего он стоит в WHERE, возвращая значение или список для сравнения. Запустите переносимый пример.

CREATE TABLE staff (
    id     INTEGER PRIMARY KEY,
    name   TEXT,
    salary INTEGER
);
INSERT INTO staff (id, name, salary) VALUES
    (1, 'Анна', 120000), (2, 'Борис', 95000),
    (3, 'Вера', 80000), (4, 'Глеб', 130000);

-- Кто зарабатывает больше среднего?
SELECT name, salary
FROM staff
WHERE salary > (SELECT AVG(salary) FROM staff)
ORDER BY salary DESC;

Вывод:

Глеб|130000
Анна|120000

Внутренний запрос (SELECT AVG(salary) FROM staff) вычисляет среднее (106250), и внешний оставляет тех, кто выше этой планки.

CTE: запрос по шагам через WITH

Когда логика сложная, подзапросы вложенные друг в друга становятся нечитаемыми. WITH выносит части наверх и даёт им имена — запрос читается сверху вниз. Это переносимая конструкция, попробуйте.

WITH dept_avg AS (
    SELECT AVG(salary) AS avg_salary FROM staff
)
SELECT name, salary
FROM staff, dept_avg
WHERE salary > avg_salary
ORDER BY salary DESC;

Вывод:

Глеб|130000
Анна|120000

Тот же результат, но теперь «среднее» вынесено в именованный блок dept_avg. В реальных запросах CTE можно соединять цепочкой (WITH a AS (...), b AS (...)), читая логику как последовательность шагов.

Оконные функции (PostgreSQL)

Оконные функции — мощная PostgreSQL-возможность. В отличие от GROUP BY, они считают агрегат «по окну» строк, но не сворачивают строки: каждая строка остаётся, а рядом появляется вычисленное значение. Это уже PG-специфика, поэтому помечаем как pgsql.

-- Пронумеровать сотрудников по убыванию зарплаты
SELECT
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS position,
    RANK()       OVER (ORDER BY salary DESC) AS rank
FROM staff;

Ключевое слово — OVER (...): оно задаёт «окно». Внутри можно указать ORDER BY (порядок) и PARTITION BY (разбить на группы, как мини-GROUP BY без свёртки).

-- Доля каждого заказа в сумме своего отдела
SELECT
    dept,
    name,
    salary,
    SUM(salary) OVER (PARTITION BY dept) AS dept_total,
    salary * 100.0 / SUM(salary) OVER (PARTITION BY dept) AS pct
FROM staff;

Итог

• Подзапрос — SELECT внутри запроса; удобен в WHERE для сравнения со средним, максимумом и т.п.
• WITH (CTE) разбивает сложный запрос на именованные читаемые шаги.
• Оконные функции (OVER, PARTITION BY) считают агрегат, не сворачивая строки — это сильная сторона PostgreSQL.