Оконные функции SQL: OVER, RANK, ROW_NUMBER

GROUP BY отвечает на вопрос «сколько». Но иногда нужно не число, а сама шеренга — каждый человек на месте, и рядом с ним подписано, каким он идёт по счёту. GROUP BY для этого не годится: он схлопывает строки, а вам нужны все.

Оконная функция считает поверх строк, не сворачивая их. OVER(...) — «вот ваша рамка, смотрите вокруг, но сами никуда не девайтесь».

SELECT person_id, amount,
  RANK() OVER (ORDER BY amount DESC) AS место
FROM receipts;

Кто встанет вторым, если суммы равны?

RANK() двум равным даёт одно место, а следующему — с пропуском: 1, 1, 3. DENSE_RANK() пропуск не оставляет: 1, 1, 2. ROW_NUMBER() вообще не признаёт равенства — раздаёт номера подряд, даже если суммы совпали.

«ROW_NUMBER не про справедливость. Он про то, что кому-то придётся стоять вторым, даже если суммы у обоих одинаковые.»

Капкан — в этом самом «кому-то»: если в ORDER BY окна есть равные значения, а порядок между ними ничем не закреплён, ROW_NUMBER() решит, кто первый, а кто второй, на своё усмотрение — и при повторном запуске может решить иначе.

ROW_NUMBER() OVER (ORDER BY amount DESC)
-- при равных amount — непредсказуемо кто 1, кто 2
ROW_NUMBER() OVER (ORDER BY amount DESC, id)
-- id как судья на ничью — теперь стабильно

Второе применение окна — нарастающий итог: не рейтинг, а бегущая сумма, которая растёт от строки к строке.

SELECT time, amount,
  SUM(amount) OVER (ORDER BY time) AS итого_к_моменту
FROM receipts;

Почему нарастающий итог скачет через ступеньку?

Второй капкан прячется глубже: без явно указанной рамки окно по умолчанию суммирует все строки с ОДИНАКОВЫМ значением сортировки как одну — итог на границе связки скачет, а не растёт по одной строке за раз.

SUM(amount) OVER (ORDER BY time)
-- по умолчанию: связки по time считаются вместе
SUM(amount) OVER (ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- явная рамка — строго по одной строке

«Окно не толкает соседей в общую кучу, как GROUP BY. Оно ставит каждого на своё место — и одновременно показывает всю очередь целиком.»

На работе так считают долю от группы и место в рейтинге, не теряя ни одной строки из вида. Разница коротко: GROUP BY — для сводки, окно — когда нужны и строки, и метрика группы одновременно.

Рейтинг, который не теряет ни одной строки, понадобится вам в архиве уже на втором деле. Там и проверьте.