Пропуски в последовательности и пользователи без заказов

В таблице легко найти то, что в ней есть. Труднее найти то, чего нет: пропущенный номер счёта, месяц без единой продажи, клиента, который зарегистрировался и ничего не купил. SQL возвращает только существующие строки, поэтому отсутствие приходится сначала построить, а потом сравнить с данными. Этот урок про три таких вопроса, все они встречаются на собеседованиях.

Как спрашивают

Номера счетов должны идти подряд. Найди, каких номеров не хватает.

Выведи выручку по месяцам так, чтобы месяцы без продаж тоже были в отчёте, с нулём.

Найди пользователей, у которых нет ни одного заказа.

Короткий ответ

Пропуски в числах ищу двумя способами: через LEAD, сравнивая каждое значение со следующим, или через generate_series, строя полный ряд и вычитая из него существующие значения. Для месяцев без продаж строю ряд месяцев через generate_series и присоединяю к нему данные LEFT JOIN-ом, а пустые суммы заменяю нулём через COALESCE. Пользователей без заказов нахожу анти-джойном: NOT EXISTS или LEFT JOIN ... WHERE o.id IS NULL. NOT IN здесь опасен из-за NULL.

Разбор: перекличка

Учитель проводит перекличку. Он не спрашивает «кого нет?», ведь отсутствующие не ответят. Он читает список класса целиком и отмечает, кто откликнулся. Кого не отметили, того нет.

В SQL то же самое: сначала строим полный «список класса», потом сравниваем его с тем, что лежит в таблице. Полный список часто даёт функция generate_series, которая выдаёт последовательность чисел или дат.

Пропуски в номерах

Пусть номера счетов такие, данные зададим прямо в запросе:

WITH ids(id) AS (VALUES (1), (2), (3), (5), (6), (9), (10))
SELECT id + 1 AS gap_start, next_id - 1 AS gap_end
FROM (
  SELECT id, lead(id) OVER (ORDER BY id) AS next_id
  FROM ids
) t
WHERE next_id - id > 1;

gap_start | gap_end
----------+--------
        4 |       4
        7 |       8

LEAD из урока про соседние строки ставит рядом с каждым номером следующий. Если разница больше единицы, между ними дыра: после 3 идёт 5, значит, пропущен 4; после 6 идёт 9, пропущены 7 и 8. Этот способ отдаёт пропуски диапазонами и не зависит от длины дыры.

Способ с перекличкой отдаёт каждый пропущенный номер отдельной строкой:

WITH ids(id) AS (VALUES (1), (2), (3), (5), (6), (9), (10))
SELECT s.id
FROM generate_series((SELECT min(id) FROM ids), (SELECT max(id) FROM ids)) AS s(id)
WHERE NOT EXISTS (SELECT 1 FROM ids WHERE ids.id = s.id);

id
--
 4
 7
 8

Уточни у интервьюера, что нужно: диапазоны или список. На миллионе номеров с редкими дырами LEAD выгоднее, он не строит ряд из миллиона чисел.

Острова: обратная задача

Иногда спрашивают наоборот: выведи непрерывные участки, «острова». Это классическая задача gaps and islands. Приём такой: у подряд идущих номеров разность «номер минус порядковый номер строки» одинакова.

WITH ids(id) AS (VALUES (1), (2), (3), (5), (6), (9), (10))
SELECT min(id) AS from_id, max(id) AS to_id
FROM (
  SELECT id, id - row_number() OVER (ORDER BY id) AS grp
  FROM ids
) t
GROUP BY grp
ORDER BY from_id;

from_id | to_id
--------+------
      1 |     3
      5 |     6
      9 |    10

Для 1, 2, 3 разность равна 0, для 5 и 6 равна 1, для 9 и 10 равна 3. Группировка по разности склеивает каждый остров в одну строку.