Коррелированный подзапрос SQL: заказ выше среднего своего клиента

Глобальное среднее ошибочно включает одинокий дорогой заказ. Сравниваем каждую строку со своей группой.

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_idorder_id → amount_kopecks
111 → 1000; 12 → 3000; 13 → NULL
221 → 2000; 22 → 2000
331 → 7000
441 → NULL; 42 → NULL
551 → 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_idclient_idamount_kopecks
1213000
3137000

Подзапрос не знает ничего о текущем клиенте: он берёт все известные суммы магазина. У клиента 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_idclient_idamount_kopecks
1213000
525200

Клиент 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_idorders_nknown_navg_kopecks
1322000.0
2222000.0
3117000.0
420NULL
522100.0

Упражнение

Добавьте клиенту 5 ещё один заказ на 100 копеек и проверьте, меняется ли набор строк выше его среднего. Затем мысленно замените x.client_id = o.client_id на сравнение другой колонки: каким станет смысл задачи? Для базовой практики начните с P081: заказы выше общей средней, затем измените запрос на среднюю каждого клиента.

Первоисточники: коррелированные подзапросы SQLite и AVG и обработка NULL.

По соседству: средний чек · GROUP BY и HAVING · сравнение внутри групп.


Все примеры выше рассчитаны на SQLite в бесплатном SQL Studio. Если нужен отдельный структурированный курс на MySQL 8, есть курс «SQL для аналитика» на Stepik; синтаксис и некоторые функции могут отличаться.