Условие простое: есть таблица с одним столбцом Element (значения A, B, C, D, E, F), нужно добавить колонку ID с числами от 1 до 6. Выглядит просто - пишем
ROW_NUMBER() OVER (ORDER BY element) или RANK() .. и идём пить кофе. Но есть нюанс, использование окошек запрещено, у нас на руках только базовый SQL.Такие задачки любят давать на собеседованиях, потому что они проверяют не знание синтаксиса, а понимание того, как вообще работает SQL под капотом.
А решение на самом деле простое: идея в том, чтобы соединить таблицу саму с собой и для каждого элемента посчитать, сколько элементов идут до него (включая его самого):
SELECT
t1.element,
COUNT(t2.element) AS id
FROM elements t1
JOIN elements t2
ON t1.element >= t2.element
GROUP BY t1.element
ORDER BY id;
Логика такая: для A условию
>= A удовлетворяет только сам A, поэтому COUNT = 1. Для B подходят A и B, поэтому COUNT = 2. И так далее.Ещё один вариант оформления через коррелированный подзапрос (не надо так делать в проде!):
SELECT
element,
(SELECT COUNT(*) FROM elements t2 WHERE t2.element <= t1.element) AS id
FROM elements t1
ORDER BY element;
Подводные камни о которых часто забывают:
Во-первых, без
ORDER BY результат недетерминирован — в SQL нет «естественного порядка» строк, это фундамент реляционной теории. Во-вторых, если в реальных данных будут дубликаты, вы получите одинаковые номера, причем это будет похоже не на чистый RANK, а со сдвигом. А чтобы получить логику
DENSE_RANK, придётся использовать COUNT(DISTINCT t2.element).В-третьих, производительность O(n²) — на больших таблицах это будет очень больно 👾.
Проверьте, self-join работает в 10–25 раз медленнее, чем
ROW_NUMBER(). Поэтому в проде для таких задач используем оконные функции. А вот для понимания механики и для собеседований такое решение самое то (но не забываем упомянуть о моментах выше).А вы встречали подобные задачи на интервью? Какое решение предложили бы?
#sql