Табличные выражения SQL

Прием № 1, чтобы писать хорошие читаемые SQL-запросы — это табличные выражения (CTE). Люди их боятся, а зря. Давайте разберемся за три минуты, читать увесистую книгу по SQL или проходить курсы не придется.

Проблема

Допустим, у нас есть таблица продаж по месяцам за два года:

┌──────┬───────┬───────┬──────────┬─────────┐
│ year │ month │ price │ quantity │ revenue │
├──────┼───────┼───────┼──────────┼─────────┤
│ 2019 │ 1     │ 60    │ 200      │ 12000   │
│ 2019 │ 2     │ 60    │ 660      │ 39600   │
│ 2019 │ 3     │ 60    │ 400      │ 24000   │
│ 2019 │ 4     │ 60    │ 300      │ 18000   │
│ 2019 │ 5     │ 60    │ 440      │ 26400   │
│ 2019 │ 6     │ 60    │ 540      │ 32400   │
│ 2019 │ 7     │ 60    │ 440      │ 26400   │
│ 2019 │ 8     │ 60    │ 440      │ 26400   │
│ 2019 │ 9     │ 60    │ 250      │ 15000   │
│ 2019 │ 10    │ 60    │ 420      │ 25200   │
│ ...  │ ...   │ ...   │ ...      │ ...     │
└──────┴───────┴───────┴──────────┴─────────┘

песочница

Мы хотим выбрать только те месяцы, выручка за которые превысила среднемесячную за год.

Для начала посчитаем среднемесячную выручку по годам:

select
  year,
  avg(revenue) as avg_rev
from sales
group by year;

┌──────┬─────────┐
│ year │ avg_rev │
├──────┼─────────┤
│ 2019 │ 25125.0 │
│ 2020 │ 48625.0 │
└──────┴─────────┘

Теперь можно выбрать только те записи, revenue в которых не уступает avg_rev:

select
  sales.year,
  sales.month,
  sales.revenue,
  round(totals.avg_rev) as avg_rev
from sales
  join (
    select
      year,
      avg(revenue) as avg_rev
    from sales
    group by year
  ) as totals
  on sales.year = totals.year
where sales.revenue >= totals.avg_rev;

┌──────┬───────┬─────────┬─────────┐
│ year │ month │ revenue │ avg_rev │
├──────┼───────┼─────────┼─────────┤
│ 2019 │ 2     │ 39600   │ 25125.0 │
│ 2019 │ 5     │ 26400   │ 25125.0 │
│ 2019 │ 6     │ 32400   │ 25125.0 │
│ 2019 │ 7     │ 26400   │ 25125.0 │
│ ...  │ ...   │ ...     │ ...     │
└──────┴───────┴─────────┴─────────┘

Решили с помощью подзапроса:

• внутренний запрос считает среднемесячную выручку;
• внешний соединяется с ним и фильтрует результаты.

Запрос в целом получился сложноват. Если вернетесь к нему спустя месяц — наверняка потратите какое-то время на «распутывание». Проблема в том, что такие вложенные запросы приходится читать наоборот:

• найти самый внутренний запрос, осознать;
• мысленно присоединить к более внешнему;
• присоединить к следующему внешнему, и так далее.

Хорошо, когда вложенных уровня два, как в нашем примере. На практике же я часто встречаю трех- и четырехуровневые подзапросы. Форменное издевательство над читателем.

Решение

Вместо подзапроса можно использовать табличное выражение (common table expression, CTE). Любой подзапрос X:

select a, b, c
from (X)
where e = f

Механически превращается в CTE:

with cte as (X)
select a, b, c
from cte
where e = f

В нашем примере:

with totals as (
  select
    year,
    avg(revenue) as avg_rev
  from sales
  group by year
)

select
  sales.year,
  sales.month,
  sales.revenue,
  round(totals.avg_rev) as avg_rev
from sales 
  join totals on totals.year = sales.year
where sales.revenue >= totals.avg_rev;

С табличным выражением запрос становится одноуровневым — так воспринимать его намного проще. Кроме того, табличное выражение можно переиспользовать в пределах запроса, как будто это обычная таблица:

with totals as (...)
select ... from sales_ru join totals ...
union all
select ... from sales_us join totals ...

Табличные выражения SQL чем-то похожи на функции в обычном языке программирования — они уменьшают общую сложность:

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

CTE против подзапроса

Существует миф, что «CTE медленные». Он пришел из старых версий PostgreSQL (11 и раньше), которые всегда материализовали CTE — вычисляли полный результат табличного выражения и запоминали до конца запроса.

Обычно это хорошо: один раз вычислил результат, и дальше используешь его несколько раз по ходу основного запроса. Но иногда материализация мешала движку оптимизировать запрос:

with cte as (select * from foo)
select * from cte where id = 500000;

Здесь выбирается ровно одна запись по идентификатору, но материализация создает в памяти копию всей таблицы — из-за этого запрос отработает очень медленно.

PostgreSQL 12+ и другие современные СУБД поумнели и больше так не делают. Материализация применяется, когда от нее больше пользы, чем вреда. Плюс, многие СУБД позволяют явно управлять этим поведением через инструкции MATERIALIZED / NOT MATERIALIZED.

Так что CTE не медленнее подзапросов. А если сомневаетесь, всегда можно сделать два варианта — подзапрос и табличное выражение — и сравнить план и время выполнения.

Как понять, когда использовать подзапрос, а когда CTE? Я вывел для себя простое правило, которое пока ни разу не подвело:

Всегда использовать CTE

Чего и вам желаю.

──

P.S. Хотите освоить современный SQL? Обратите внимание на Оконные функции