Раньше SQL-разработчики жили без оконных функций. Как они выживали 👀? Натолкнулась тут на задачку и хочу с вами её разобрать.
Условие простое: есть таблица с одним столбцом 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(). Поэтому в проде для таких задач используем оконные функции. А вот для понимания механики и для собеседований такое решение самое то (но не забываем упомянуть о моментах выше).
А вы встречали подобные задачи на интервью? Какое решение предложили бы?
