CTE: WITH как черновик аналитика

Мира · маркетолог магазина настолок «Кубик»

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

Посчитать это одним запросом можно: подзапрос во FROM, в нём ещё один, сверху деление на сумму из третьего. Работать будет. Но «чтобы бухгалтер мог проверить» такой запрос не годится: его и автор через неделю не прочтёт. Сегодня учимся писать запросы, которые читаются сверху вниз, как рецепт.

Черновик на полях

Сложную задачу по математике ты решаешь не одной строкой. Сначала на полях: «пусть A это выручка, B это себестоимость, тогда маржа A − B». Каждый шаг получает имя, следующий шаг пользуется предыдущим.

В SQL такие поля дают CTE, именованные подзапросы. Они объявляются словом WITH перед основным запросом:

WITH имя AS (
  SELECT ...
)
SELECT ... FROM имя;

Внутри WITH можно объявить несколько шагов через запятую, и каждый следующий может читать предыдущие.

Выручка, маржа, доля в три шага

Маржа позиции это цена сделки минус себестоимость, умноженные на количество: qty * (order_items.price - products.cost). Разложим задачу Миры на шаги:

WITH sales AS (
  -- шаг 1: каждая позиция превращается в выручку и маржу
  SELECT p.genre,
         i.qty * i.price            AS revenue,
         i.qty * (i.price - p.cost) AS margin
  FROM orders o
  JOIN order_items i ON i.order_id = o.id
  JOIN products p ON p.id = i.product_id
  WHERE o.status <> 'cancelled'
),
by_genre AS (
  -- шаг 2: складываем по жанрам
  SELECT genre, sum(revenue) AS revenue, sum(margin) AS margin
  FROM sales
  GROUP BY genre
)
-- шаг 3: доля жанра в марже магазина
SELECT genre, revenue, margin,
       round(margin * 100 / (SELECT sum(margin) FROM by_genre), 1) AS margin_share
FROM by_genre
ORDER BY margin DESC;

 genre       | revenue    | margin     | margin_share
-------------+------------+------------+--------------
 wargame     | 7016543.00 | 2817793.00 |         30.7
 family      | 3745284.00 | 1591747.00 |         17.4
 strategy    | 3785283.00 | 1579870.00 |         17.2
 cooperative | 2809590.00 | 1183390.00 |         12.9
 party       | 2095020.00 |  946427.00 |         10.3
 card        | 1022786.00 |  446132.00 |          4.9
 puzzle      |  689464.00 |  330136.00 |          3.6
 kids        |  710813.00 |  273383.00 |          3.0

Каждый шаг читается отдельно и проверяется отдельно. Хочешь убедиться, что шаг 1 считает правильно? Замени основной запрос на SELECT * FROM sales LIMIT 10 и посмотри глазами. Так и отлаживают длинные расчёты: шаг за шагом сверху вниз.

Обрати внимание на шаг 3: CTE by_genre используется дважды, в FROM и в подзапросе с общей суммой. Подзапросом пришлось бы написать весь расчёт два раза.