Как правильно ранжировать наборы данных

Я пытаюсь получить плотный ранг для группировки наборов данных вместе. В моей таблице есть ID, GRP_SET, SUB_SET и INTERVAL, которые просто представляют поле даты. Когда записи вставляются с использованием идентификатора, они вставляются как GRP_SET из 3 строк, показанных как SUB_SET. Как вы можете видеть, когда вставки происходят, интервал может немного измениться, прежде чем он закончит вставку набора.

Вот некоторые примеры данных, и столбец DRANK представляет, какой рейтинг я пытаюсь получить.

with q as (
select 1 id, 'a' GRP_SET, 1 as SUB_SET, 123 as interval, 1 as DRANK from dual union all
select 1, 'a', 2, 123, 1 from dual union all
select 1, 'a', 3, 124, 1 from dual union all
select 1, 'b', 1, 234, 2 from dual union all
select 1, 'b', 2, 235, 2 from dual union all
select 1, 'b', 3, 235, 2 from dual union all
select 1, 'a', 1, 331, 3 from dual union all
select 1, 'a', 2, 331, 3 from dual union all
select 1, 'a', 3, 331, 3 from dual)

select * from q

Пример данных

ID GRP_SET SUBSET INTERVAL DRANK
1  a       1      123      1
1  a       2      123      1
1  a       3      124      1
1  b       1      234      2
1  b       3      235      2
1  b       2      235      2
1  a       1      331      3
1  a       2      331      3
1  a       3      331      3

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

  • Раздел по: ID
  • Сортировка внутри раздела по: ID, Интервал
  • Изменить рейтинг, когда: ID, GRP_SET (изменить)
select
   id, GRP_SET, SUB_SET, interval,
   DENSE_RANK() over (partition by ID order by id, GRP_SET) as DRANK_TEST
from q
Order by
   id, interval
Источник

2 ответа

Решение

С использованием MODEL пункт

Вот, вы выдвигаете свои требования за пределы того, что легко выразить в "обычном" SQL. Но, к счастью, вы используете Oracle, который имеет MODEL пункт, устройство, тайна которого превосходит только его мощность ( отличная техническая документация здесь). Вы должны написать:

SELECT
   id, grp_set, sub_set, interval, drank
FROM (
  SELECT id, grp_set, sub_set, interval, 1 drank
  FROM q
)
MODEL PARTITION BY (id)
      DIMENSION BY (row_number() OVER (ORDER BY interval, sub_set) rn)
      MEASURES (grp_set, sub_set, interval, drank)
      RULES (
        drank[any] = NVL(drank[cv(rn) - 1] + 
                         DECODE(grp_set[cv(rn) - 1], grp_set[cv(rn)], 0, 1), 1)
      )

Доказательство на SQLFiddle

Объяснение:

SELECT
   id, grp_set, sub_set, interval, drank
FROM (
  -- Here, we initialise your "dense rank" to 1
  SELECT id, grp_set, sub_set, interval, 1 drank
  FROM q
)

-- Then we partition the data set by ID (that's your requirement)
MODEL PARTITION BY (id)

-- We generate row numbers for all columns ordered by interval and sub_set,
-- such that we can then access row numbers in that particular order
      DIMENSION BY (row_number() OVER (ORDER BY interval, sub_set) rn)

-- These are the columns that we want to generate from the MODEL clause
      MEASURES (grp_set, sub_set, interval, drank)

-- And the rules are simple: Each "dense rank" value is equal to the
-- previous "dense rank" value + 1, if the grp_set value has changed
      RULES (
        drank[any] = NVL(drank[cv(rn) - 1] + 
                         DECODE(grp_set[cv(rn) - 1], grp_set[cv(rn)], 0, 1), 1)
      )

Конечно, это работает, только если нет чередующихся событий, т.е. нет других grp_set чем a между 123 и 124

Источник

Это может работать для вас. Осложняющим фактором является то, что вы хотите один и тот же "DENSE RANK" для интервалов 123 а также 124 и для интервалов 234 а также 235, Таким образом, мы урежем их до ближайших 10 в целях заказа DENSE_RANK() функция:

SELECT id, grp_set, sub_set, interval, drank
     , DENSE_RANK() OVER ( PARTITION BY id ORDER BY TRUNC(interval, -1), grp_set ) AS drank_test
  FROM q

Пожалуйста, ознакомьтесь с демонстрацией SQL Fiddle здесь.

Если вы хотите, чтобы интервалы были еще ближе друг к другу, чтобы их можно было сгруппировать, вы можете умножить значение перед усечением. Это сгруппировало бы их по 3 с (но, может быть, они вам не нужны)

SELECT id, grp_set, sub_set, interval, drank
     , DENSE_RANK() OVER ( PARTITION BY id ORDER BY TRUNC(interval*10/3, -1), grp_set ) AS drank_test
  FROM q
Источник
Другие вопросы по тегам