DB2 SQL - плотное ранговое группирование двух полей, упорядоченное по Datetime

Я знаю, что это проблема, которую другие должны были решить в прошлом, но, насколько я знаю, мне еще предстоит преодолеть горб. У меня есть данные, которые упорядочены по дате и времени, которые должны быть сгруппированы по комбинации двух полей (статус и очередь). В тех случаях, когда статус и очередь одинаковы в течение определенного периода времени, их следует рассматривать как часть одной и той же группы и, следовательно, иметь одинаковый идентификатор.

Чтобы достичь этого, я попытался реализовать DENSE_RANK(), и для всех намерений и целей он был успешным - за исключением упорядочения групп. Ниже приведен пример:

WITH TEMP1 (EVENT_DATE, PRV_EVENT_DATE, STATUS, PRV_STATUS, QUEUE, PRV_QUEUE) AS
   (VALUES ('2012-09-04 11:40:19.936141', '', 'CREATED', '', 'SYSTEM', '')
          ,('2012-09-04 11:40:21.207140', '2012-09-04 11:40:19.936141', 'CREATED', 'CREATED', 'SYSTEM', 'SYSTEM')
          ,('2012-09-04 11:40:27.771140', '2012-09-04 11:40:21.207140', 'PROCESS', 'CREATED', 'PROCESS', 'SYSTEM')
          ,('2012-09-05 00:01:20.384180', '2012-09-04 11:40:27.771140', 'SUSPEND', 'PROCESS', 'SYSTEM', 'SYSTEM')
          ,('2012-09-05 00:02:14.042180', '2012-09-05 00:01:20.384180', 'SUSPEND', 'SUSPEND', 'PEND', 'SYSTEM')
          ,('2012-09-06 00:02:14.642180', '2012-09-05 00:02:14.042180', 'SUSPEND', 'SUSPEND', 'SYSTEM', 'SYSTEM')
          ,('2012-09-06 00:02:33.433180', '2012-09-06 00:02:14.642180', 'SUSPEND', 'SUSPEND', 'SYSTEM', 'SYSTEM')
   )  
SELECT 
ROW_NUMBER() OVER (ORDER BY EVENT_DATE) AS "RN",
DENSE_RANK() OVER ( ORDER BY status, queue, date(event_date)) AS "GRP",
EVENT_DATE, PRV_EVENT_DATE, STATUS, PRV_STATUS, QUEUE, PRV_QUEUE
FROM TEMP1
ORDER BY EVENT_DATE

Результаты такие:

RN GRP EVENT_DATE                  PRV_EVENT_DATE             STATUS  PRV_STATUS QUEUE
1   1  2012-09-04 11:40:19.936141                             CREATED            SYSTEM
2   1  2012-09-04 11:40:21.207140  2012-09-04 11:40:19.936141 CREATED CREATED    SYSTEM
3   2  2012-09-04 11:40:27.771140  2012-09-04 11:40:21.207140 PROCESS CREATED    PROCESS 
4   4  2012-09-05 00:01:20.384180  2012-09-04 11:40:27.771140 SUSPEND PROCESS    SYSTEM
5   3  2012-09-05 00:02:14.042180  2012-09-05 00:01:20.384180 SUSPEND SUSPEND    PEND
6   5  2012-09-06 00:02:14.642180  2012-09-05 00:02:14.042180 SUSPEND SUSPEND    SYSTEM

Как вы можете сказать, "GRP" вышел из строя (и я также знаю, что использование даты (EVENT_DATE) не является решением).

Источник

1 ответ

Решение

Непонятно (по крайней мере, мне), чего вы на самом деле хотите. Новая группа, когда есть изменение "STATUS" или "QUEUE" по сравнению с предыдущей? Или есть более сложные правила?

Похоже, ваши данные уже являются результатом запроса, в котором вы вычисляете предыдущее значение, используя значение MIN(состояние / очередь) OVER (строка между 1 PRECEDING и 1 PRECEDING)

Вы никогда не получите правильный порядок при преобразовании в DATE, попробуйте следующий расчет:

SUM(CASE WHEN status = prv_status AND queue = prv_queue THEN 0 ELSE 1 END)
OVER (ORDER BY event_date 
      ROWS UNBOUNDED PRECEDING)

Изменить: Без SUM OVER вы должны использовать скалярный подзапрос в качестве входных данных для DENSE_RANK, это должно работать:

SELECT 
   (SELECT MAX(event_date) 
    FROM TEMP1 AS t2 
    WHERE t2.event_date < t1.event_date
    AND t1.status <> t2.status 
    AND t1.QUEUE <> t2.queue) AS x,

    DENSE_RANK() OVER ( ORDER BY x) AS "GRP",

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

Возможно, вам лучше придерживаться "неправильного" порядка, по крайней мере, это одно и то же неправильное значение для всех строк группы:-)

Источник
Другие вопросы по тегам