WITH и CTE в SQL: отчет на стъпки

Проблем

Отчет отвътре навън

Собственичката иска списък на клиентите, които са похарчили над 100 € (без отказаните поръчки), с броя на поръчките им. С подзаявки във FROM вече можеш да го направиш:

SQLбаза: магазин

SELECT c.name, t.orders, t.spent
FROM (
  SELECT customer_id, COUNT(*) AS orders, ROUND(SUM(total), 2) AS spent
  FROM (
    SELECT o.id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    WHERE o.status <> 'cancelled'
    GROUP BY o.id, o.customer_id
  ) AS ot
  GROUP BY customer_id
) AS t
JOIN customers c ON c.id = t.customer_id
WHERE t.spent > 100
ORDER BY t.spent DESC;

Резултатът е верен. Но опитай да кажеш какво прави заявката: трябва да започнеш от най-вътрешните скоби и да вървиш навън. След месец и ти няма да я познаеш.

Идеята

WITH

WITH дава име на една стъпка, преди основната заявка:

SQLбаза: магазин

WITH order_totals AS (
  SELECT o.id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.id, o.customer_id
)
SELECT * FROM order_totals;

order_totals е стойността на всяка поръчка, която не е отказана: 53 реда. След WITH името се ползва като таблица. Такава именувана стъпка се казва CTE (common table expression).

Стъпките може да са няколко, разделени със запетаи. Всяка вижда стъпките преди себе си:

SQLбаза: магазин

WITH order_totals AS (
  SELECT o.id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.id, o.customer_id
),
customer_totals AS (
  SELECT customer_id, COUNT(*) AS orders, ROUND(SUM(total), 2) AS spent
  FROM order_totals
  GROUP BY customer_id
)
SELECT c.name, ct.orders, ct.spent
FROM customer_totals ct
JOIN customers c ON c.id = ct.customer_id
WHERE ct.spent > 100
ORDER BY ct.spent DESC;

Същият отчет като в началото, но се чете отгоре надолу: стойност на всяка поръчка, после сума за всеки клиент, после само тези над 100 €. Излизат седем клиенти, начело с Мария (375,70 € в 12 поръчки).

Пробвай

Поръчките над средното

Покажи номера и стойността на поръчките (без отказаните), чиято стойност е над средната стойност на поръчка. Полето вече има стъпката order_totals.

SQLбаза: магазин

WITH order_totals AS (
  SELECT o.id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.id, o.customer_id
)
SELECT id, ROUND(total, 2) AS total
FROM order_totals;

Решение

WITH order_totals AS (
  SELECT o.id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.id, o.customer_id
)
SELECT id, ROUND(total, 2) AS total
FROM order_totals
WHERE total > (SELECT AVG(total) FROM order_totals);

Виж го

Колко живее името

Името от WITH важи само за заявката, на която е част. Тук има две заявки: първата брои поръчките, втората пита пак за order_totals:

SQLбаза: магазин

WITH order_totals AS (
  SELECT o.id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.id, o.customer_id
)
SELECT COUNT(*) FROM order_totals;

SELECT * FROM order_totals;

1. Защо втората заявка не намира order_totals?Защото името от WITH живее само в заявката, на която е частЗащото order_totals е изтрита от първата заявкаЗащото липсва запетая

WITH не създава таблица в базата. Ако една стъпка ти трябва в много отчети, за това има изгледи (VIEW), които ще срещнем в модула за схемата.

Идеята

Кога WITH, кога подзаявка

Резултатът е един и същ. Разликата е за човека, който чете:

кратко условие като price > (SELECT AVG(price) FROM products) е най-ясно на място, като подзаявка;
отчет от няколко стъпки е по-ясен с WITH: всяка стъпка има име, което казва какво е;
стъпка, която трябва два пъти, се пише веднъж.

Удобно е и при търсене на грешка: смени последния SELECT с SELECT * FROM order_totals и виждаш какво дава всяка стъпка.

По-точно, за любопитните

Дали базата изпълнява всяка стъпка отделно или ги слива в една заявка, решава тя. В PostgreSQL преди версия 12 всяко WITH се изпълняваше отделно, а от версия 12 базата може да го слее с основната заявка. Ако някога WITH-заявка е по-бавна от същата с подзаявки, причината често е там.

Упражнение

Три задачи

1. Отделът по продажби иска оборота по градове, за да реши къде да пусне реклама.

Покажи града и оборота от поръчките на клиентите от него (без отказаните), закръглен до два знака. Започни от стъпката order_totals.

SQLбаза: магазин

WITH order_totals AS (
  SELECT o.id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.id, o.customer_id
)
SELECT * FROM order_totals;

Решение

WITH order_totals AS (
  SELECT o.id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.id, o.customer_id
)
SELECT c.city, ROUND(SUM(ot.total), 2) AS revenue
FROM order_totals ot
JOIN customers c ON c.id = ot.customer_id
GROUP BY c.city;

2. Кои категории са донесли над 200 € (без отказаните поръчки)?

Покажи името на категорията и оборота ѝ (сумата на количество по единична цена), само за категориите с оборот над 200 €. Направи една стъпка с оборота по category_id и после я свържи с categories.

SQLбаза: магазин

Решение

WITH category_sales AS (
  SELECT p.category_id, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN orders o ON o.id = oi.order_id
  JOIN products p ON p.id = oi.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY p.category_id
)
SELECT c.name, ROUND(cs.revenue, 2) AS revenue
FROM category_sales cs
JOIN categories c ON c.id = cs.category_id
WHERE cs.revenue > 200;

3. В модула за свързване направи с CROSS JOIN мрежата от всеки град и всяка подкатегория. Сега я попълни: оборотът за всяка двойка, с 0 там, където продажби няма.

Покажи града, името на подкатегорията (колона category) и оборота (колона revenue) от клиентите на този град в тази подкатегория (без отказаните поръчки), закръглен до два знака. За двойките без продажби оборотът е 0. Полето вече има мрежата.

SQLбаза: магазин

WITH grid AS (
  SELECT DISTINCT cu.city, cat.id AS category_id, cat.name AS category
  FROM customers cu
  CROSS JOIN categories cat
  WHERE cu.city IS NOT NULL AND cat.parent_id IS NOT NULL
)
SELECT * FROM grid;

Решение

WITH grid AS (
  SELECT DISTINCT cu.city, cat.id AS category_id, cat.name AS category
  FROM customers cu
  CROSS JOIN categories cat
  WHERE cu.city IS NOT NULL AND cat.parent_id IS NOT NULL
),
sales AS (
  SELECT cu.city, p.category_id, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN customers cu ON cu.id = o.customer_id
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY cu.city, p.category_id
)
SELECT g.city, g.category, COALESCE(ROUND(s.revenue, 2), 0) AS revenue
FROM grid g
LEFT JOIN sales s ON s.city = g.city AND s.category_id = g.category_id;

Тест

Провери се

1. Как се пишат две стъпки в едно WITH?WITH a AS (…) WITH b AS (…) SELECT …WITH a AS (…), b AS (…) SELECT …WITH a AS (…); b AS (…); SELECT …

2. Какво прави WITH order_totals AS (…) с базата?Създава таблица order_totals, която оставаПази резултата до края на деняНищо в базата: дава име на резултат само за тази заявка

3. Какво дава WITH, което подзаявката във FROM не дава?Стъпките се четат отгоре надолу и една стъпка може да се ползва няколко пътиПо-верни резултатиРаботи без GROUP BY

Отчет на стъпки: WITH

Бисквитки