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

У меня есть таблица транзакций в базе данных Oracle. Я пытаюсь собрать отчет для системы доставки, включающей несколько типов транзакций. Тип "запроса" может фактически быть одним из четырех подтипов ("A", "B", "C" и "D" для этого примера), а тип "доставки" может быть одним из четырех различных подтипов. типы ('PULL', 'PICKUP', 'MAIL'). Может быть от 1 до 5 транзакций для получения элемента от "запроса" до "доставки", и ряд типов "доставки" также являются промежуточными транзакциями. Пример:

Item | Transaction | Timestamp
001  | REQ-A       | 2014-07-31T09:51:32Z
002  | REQ-B       | 2014-07-31T09:55:53Z
003  | REQ-C       | 2014-07-31T10:01:15Z
004  | REQ-D       | 2014-07-31T10:02:29Z
005  | REQ-A       | 2014-07-31T10:05:47Z
002  | PULL        | 2014-07-31T10:20:04Z
002  | MAIL        | 2014-07-31T10:20:06Z
001  | PULL        | 2014-07-31T10:22:21Z
001  | TRANSFER    | 2014-07-31T10:22:23Z
003  | PULL        | 2014-07-31T10:24:10Z
003  | TRANSFER    | 2014-07-31T10:24:12Z
004  | PULL        | 2014-07-31T10:26:28Z
005  | PULL        | 2014-07-31T10:28:42Z
005  | TRANSFER    | 2014-07-31T10:28:44Z
001  | ARRIVE      | 2014-07-31T11:45:01Z
001  | PICKUP      | 2014-07-31T11:45:02Z
003  | ARRIVE      | 2014-07-31T11:47:44Z
003  | PICKUP      | 2014-07-31T11:47:45Z
005  | ARRIVE      | 2014-07-31T11:49:45Z
005  | PICKUP      | 2014-07-31T11:49:46Z

Мне нужен отчет вроде:

Item | Start Tx | End Tx | Time
001  | REQ-A    | PICKUP | 1:53:30
002  | REQ-B    | MAIL   | 0:24:13
003  | REQ-C    | PICKUP | 1:46:30
004  | REQ-D    | PULL   | 0:23:59
005  | REQ-A    | PICKUP | 1:43:59

Что я имею:

Item | Start Tx | End Tx   | Time
001  | REQ-A    | PULL     | 0:30:49
001  | REQ-A    | TRANSFER | 0:30:51
001  | REQ-A    | ARRIVE   | 1:53:29
001  | REQ-A    | PICKUP   | 1:53:30
002  | REQ-B    | PULL     | 0:24:11
002  | REQ-B    | MAIL     | 0:24:13
003  | REQ-C    | PULL     | 0:22:55
003  | REQ-C    | TRANSFER | 0:22:57
003  | REQ-C    | ARRIVE   | 1:46:29
003  | REQ-C    | PICKUP   | 1:46:30
004  | REQ-D    | PULL     | 0:23:59
005  | REQ-A    | PULL     | 0:22:55
005  | REQ-A    | TRANSFER | 0:22:57
005  | REQ-A    | ARRIVE   | 1:43:58
005  | REQ-A    | PICKUP   | 1:43:59

Что я делаю, чтобы получить эти данные:

SELECT Item, Transaction, nextTransaction, nextTimestamp - Timestamp
FROM (
    SELECT Item, Transaction, Timestamp,
      LEAD(Transaction, 5) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTransaction"
      LEAD(Timestamp, 5) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTimestamp"
    FROM Transactions
    UNION ALL 
    SELECT Item, Transaction, Timestamp,
      LEAD(Transaction, 4) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTransaction"
      LEAD(Timestamp, 4) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTimestamp"
    FROM Transactions
    UNION ALL 
    SELECT Item, Transaction, Timestamp,
      LEAD(Transaction, 3) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTransaction"
      LEAD(Timestamp, 3) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTimestamp"
    FROM Transactions
    UNION ALL 
    SELECT Item, Transaction, Timestamp,
      LEAD(Transaction, 2) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTransaction"
      LEAD(Timestamp, 2) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTimestamp"
    FROM Transactions
    UNION ALL 
    SELECT Item, Transaction, Timestamp,
      LEAD(Transaction, 1) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTransaction"
      LEAD(Timestamp, 1) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTimestamp"
    FROM Transactions
)
WHERE nextTransaction IS NOT NULL
AND Transaction IN ('REQ-A', 'REQ-B', 'REQ-C', 'REQ-D')

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

Источник

2 ответа

Решение

Похоже, вы хотите, чтобы первые и последние транзакции основывались на времени. Я думаю, что следующее делает то, что вы хотите:

select item,
       min(transaction) keep (dense_rank first order by timestamp) as StartTx, 
       min(transaction) keep (dense_rank last order by timestamp) as EndTx,
       max(timestamp) - min(timestamp)
from transactions t
group by item;
Источник

Вы могли бы использовать first_value аналитическая функция вместо lead:

select item, start_tran, end_tran, end_time - start_time
from (
  select item,
    first_value(transaction) over (partition by item
      order by timestamp) as start_tran,
    first_value(timestamp) over (partition by item
      order by timestamp) as start_time,
    first_value(transaction) over (partition by item
       order by timestamp desc) as end_tran,
    first_value(timestamp) over (partition by item
       order by timestamp desc) as end_time,
    row_number() over (partition by item
       order by timestamp) as rn
  from transactions
)
where rn = 1
order by item;

      ITEM START_TRAN END_TRAN   END_TIME-START_TIME
---------- ---------- ---------- -------------------
         1 REQ-A      PICKUP     0 1:53:30.0         
         2 REQ-B      MAIL       0 0:24:13.0         
         3 REQ-C      PICKUP     0 1:46:30.0         
         4 REQ-D      PULL       0 0:23:59.0         
         5 REQ-A      PICKUP     0 1:43:59.0

row_number вместо distinct, Внутренний запрос генерирует одну строку для каждой из строк в исходной таблице, причем только item и результаты аналитической функции, так что они все одинаковы для каждого item; пункт 1 имеет пять одинаковых строк, каждая из которых показывает первый и последний transaction и соответствующий timestamp, Внешний запрос в основном сворачивает их, но также вычитает временную метку, чтобы получить истекший интервал.

SQL Fiddle не очень хорошо показывает интервалы, но вы можете извлечь значения часов / минут / секунд из них, если хотите представить их по-другому. Или, если столбец на самом деле дата, то вы можете просто использовать to_char() конечно

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