Как проранжировать данные таким образом, чтобы не менялся номер по определенному условию?

Question

[Diana]

У меня есть таблица:


Мне нужно проставить ранг так, чтобы порядковый номер не увеличивался если в столбце flag присутствуют значения


Я пишу такой запрос, думая, что он мне оставит пустоту если в поле flag есть значения, а остальное проранжирует по порядку, но он мне выдает не тот результат, что нужно:

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

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

Answer 1

[Akina]

WITH cte AS (
  SELECT *,
         SUM((flag IS NULL) :: INT) OVER (PARTITION BY user_id ORDER BY product_id ASC) grp
  FROM test
  )
SELECT product_id, user_id, flag,
       DENSE_RANK() OVER (PARTITION BY user_id ORDER BY grp ASC) "rank"
FROM cte
ORDER BY 1,2;

fiddle

Comments

[Diana]

А в рамках оконной функции есть решение? или только с CTE?

[Akina]

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

Но и с изменением алгоритма не всё ладно. По факту алгоритм получается итеративный, т.е. нельзя посчитать следующее одновременно с предыдущим, только после. Причём итерировать надо от результата агрегирования - то есть опять же по факту имеем вложенные итерации. В рамках синтаксиса SQL всё это на один уровень не разворачивается в принципе - то есть мимо подзапроса или CTE ну никак не проскочить.

Вот если бы (user_id, product_id) было уникальным - тогда да, агрегация долой, и всё сворачивается в плоский одноуровневый запрос.