Ранжирование в SQL: как зафиксировать порядковый номер при наличии флага

При работе с базами данных часто возникает задача присвоить строкам порядковые номера (ранг), но с условием: если в определённом столбце (например, flag) присутствует значение, то номер не должен увеличиваться. В этой статье мы разберём, почему стандартный запрос с DENSE_RANK не даёт нужного результата, и предложим корректное решение с использованием оконных функций.

Проблема: почему DENSE_RANK не работает с CASE

Исходный запрос пользователя:

SELECT *
,CASE WHEN LENGTH(flag) > 0 THEN NULL ELSE DENSE_RANK() OVER (PARTITION BY user_id ORDER BY product_id) END AS rank
FROM table_1

Проблема в том, что оконная функция DENSE_RANK вычисляется для всех строк раздела, а CASE только подменяет результат. Ранг всё равно присваивается каждой строке, даже если выводится NULL. Порядковый номер не «замораживается» - он просто скрывается.

Правильное решение: предварительная фильтрация и оконная функция

Чтобы порядковый номер не увеличивался для строк с флагом, нужно сначала отделить строки, которые не должны участвовать в нумерации, а затем присвоить ранг только остальным. Вот рабочий вариант:

WITH ranked AS (
  SELECT *
       ,CASE WHEN LENGTH(flag) > 0 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY product_id) END AS rank
  FROM table_1
)
SELECT *
FROM ranked
ORDER BY user_id, product_id;

Однако такой подход всё равно не «замораживает» номер - строки с флагом получат NULL, а остальные будут пронумерованы подряд. Если нужно, чтобы номер оставался таким же, как у предыдущей строки без флага, используйте более сложную логику.

Альтернативный подход: группировка по флагу

Для точного контроля можно применить SUM как оконную функцию, чтобы создать группы, где флаг отсутствует:

WITH data AS (
  SELECT *
       ,SUM(CASE WHEN LENGTH(flag) = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY product_id ROWS UNBOUNDED PRECEDING) AS grp
  FROM table_1
)
SELECT *
       ,CASE WHEN LENGTH(flag) > 0 THEN LAG(grp) OVER (PARTITION BY user_id ORDER BY product_id) ELSE grp END AS rank
FROM data
ORDER BY user_id, product_id;

Этот запрос создаёт временную группу для строк без флага, а затем подтягивает предыдущее значение для строк с флагом, что позволяет сохранить номер неизменным.

Рекомендации по оптимизации

• Используйте ROW_NUMBER вместо DENSE_RANK, если не требуется пропуск номеров при дубликатах.
• Проверяйте индексы на столбцы user_id и product_id для ускорения оконных функций.
• Тестируйте на небольших выборках перед применением на больших таблицах.

Таким образом, ключевая идея - не пытаться обнулить ранг через CASE, а перестроить логику нумерации, изолируя строки с флагом от общего счётчика.