SQL Studio / Разборы SQL / Заказ выше среднего: среднего какого клиента?
В списке «заказов выше среднего» неожиданно оказался заказ 31 на 7 000 копеек — единственный заказ своего клиента. Он действительно выше среднего всей базы, но не может быть выше собственного среднего: 7 000 равны 7 000. Если вопрос звучит «дороже среднего заказа того же клиента», подзапрос должен ссылаться на client_id текущей внешней строки.
Исходные данные: отличаем среднее клиента от среднего магазина
Суммы указаны в копейках. У клиента 1 есть 1 000, 3 000 и неизвестная сумма; у клиента 2 — два одинаковых заказа по 2 000; у клиента 3 — один заказ 7 000; у клиента 4 обе суммы неизвестны; у клиента 5 — 0 и 200. Строка с NULL — не нулевая покупка. Для воспроизводимости каждый запрос включает свой набор VALUES.
| client_id | order_id → amount_kopecks |
|---|---|
| 1 | 11 → 1000; 12 → 3000; 13 → NULL |
| 2 | 21 → 2000; 22 → 2000 |
| 3 | 31 → 7000 |
| 4 | 41 → NULL; 42 → NULL |
| 5 | 51 → 0; 52 → 200 |
Правдоподобный, но неверный запрос: среднее по всему магазину
WITH orders(order_id, client_id, amount_kopecks) AS (
VALUES (11, 1, 1000), (12, 1, 3000), (13, 1, NULL),
(21, 2, 2000), (22, 2, 2000),
(31, 3, 7000), (41, 4, NULL), (42, 4, NULL),
(51, 5, 0), (52, 5, 200)
)
SELECT o.order_id, o.client_id, o.amount_kopecks
FROM orders AS o
WHERE o.amount_kopecks > (
SELECT AVG(amount_kopecks) FROM orders
)
ORDER BY o.order_id;
| order_id | client_id | amount_kopecks |
|---|---|---|
| 12 | 1 | 3000 |
| 31 | 3 | 7000 |
Подзапрос не знает ничего о текущем клиенте: он берёт все известные суммы магазина. У клиента 5 заказ на 200 копеек превышает его собственное среднее 100, но ниже общего; поэтому глобальный фильтр его потерял.
Корреляция: подключаем идентификатор внешней строки
Алиас o относится к проверяемому заказу; x — к строкам, из которых вычисляется среднее. Условие x.client_id = o.client_id заставляет внутреннюю агрегацию рассматривать только заказы одного клиента. Это коррелированный скалярный подзапрос.
WITH orders(order_id, client_id, amount_kopecks) AS (
VALUES (11, 1, 1000), (12, 1, 3000), (13, 1, NULL),
(21, 2, 2000), (22, 2, 2000),
(31, 3, 7000), (41, 4, NULL), (42, 4, NULL),
(51, 5, 0), (52, 5, 200)
)
SELECT o.order_id, o.client_id, o.amount_kopecks
FROM orders AS o
WHERE o.amount_kopecks > (
SELECT AVG(x.amount_kopecks)
FROM orders AS x
WHERE x.client_id = o.client_id
)
ORDER BY o.order_id;
| order_id | client_id | amount_kopecks |
|---|---|---|
| 12 | 1 | 3000 |
| 52 | 5 | 200 |
Клиент 2 не попадает: оба заказа равны своему среднему. Клиент 3 с одним известным заказом тоже не попадает: значение равно среднему. Для суммы NULL сравнение > не истинно.
Альтернатива: одна агрегатная строка на клиента в CTE
Для проверки можно предварительно вычислить средние значения по client_id и затем присоединить их к заказам. Этот вариант даёт те же строки, но явнее показывает промежуточный уровень группировки. Выбор по производительности делайте по фактическому плану EXPLAIN QUERY PLAN, а не по правилу «CTE всегда быстрее».
WITH orders(order_id, client_id, amount_kopecks) AS (
VALUES (11, 1, 1000), (12, 1, 3000), (13, 1, NULL),
(21, 2, 2000), (22, 2, 2000),
(31, 3, 7000), (41, 4, NULL), (42, 4, NULL),
(51, 5, 0), (52, 5, 200)
),
avg_by_client AS (
SELECT client_id, AVG(amount_kopecks) AS avg_kopecks
FROM orders
GROUP BY client_id
)
SELECT o.order_id, o.client_id, o.amount_kopecks
FROM orders AS o
JOIN avg_by_client AS a ON a.client_id = o.client_id
WHERE o.amount_kopecks > a.avg_kopecks
ORDER BY o.order_id;
Сверка: снова заказы 12 и 52. Отличие от статьи про общий средний чек в том, что порог здесь свой у каждого клиента.
Граница: NULL, один заказ и одинаковые суммы
Прежде чем доверять сравнению, проверьте средние по группам. AVG в SQLite пропускает NULL, а для группы, где все суммы неизвестны, возвращает NULL. COUNT(*) при этом считает и неизвестные суммы; COUNT(amount_kopecks) — только известные.
WITH orders(order_id, client_id, amount_kopecks) AS (
VALUES (11, 1, 1000), (12, 1, 3000), (13, 1, NULL),
(21, 2, 2000), (22, 2, 2000),
(31, 3, 7000), (41, 4, NULL), (42, 4, NULL),
(51, 5, 0), (52, 5, 200)
)
SELECT client_id, COUNT(*) AS orders_n,
COUNT(amount_kopecks) AS known_n,
AVG(amount_kopecks) AS avg_kopecks
FROM orders
GROUP BY client_id
ORDER BY client_id;
| client_id | orders_n | known_n | avg_kopecks |
|---|---|---|---|
| 1 | 3 | 2 | 2000.0 |
| 2 | 2 | 2 | 2000.0 |
| 3 | 1 | 1 | 7000.0 |
| 4 | 2 | 0 | NULL |
| 5 | 2 | 2 | 100.0 |
Упражнение
Добавьте клиенту 5 ещё один заказ на 100 копеек и проверьте, меняется ли набор строк выше его среднего. Затем мысленно замените x.client_id = o.client_id на сравнение другой колонки: каким станет смысл задачи? Для базовой практики начните с P081: заказы выше общей средней, затем измените запрос на среднюю каждого клиента.
Первоисточники: коррелированные подзапросы SQLite и AVG и обработка NULL.
Все примеры выше рассчитаны на SQLite в бесплатном SQL Studio. Если нужен отдельный структурированный курс на MySQL 8, есть курс «SQL для аналитика» на Stepik; синтаксис и некоторые функции могут отличаться.