sql - Oracle GROUP BY 类似的时间戳？

Question

我有一个结构如下的事件表:

id  prd_id  act_dt               grp
------------------------------------
1   1       2000-01-01 00:00:00
2   1       2000-01-01 00:00:01
3   1       2000-01-01 00:00:02
4   2       2000-01-01 00:00:00
5   2       2000-01-01 00:00:01
6   2       2000-01-01 01:00:00
7   2       2000-01-01 01:00:01
8   3       2000-01-01 00:00:00
9   3       2000-01-01 00:00:01
10  3       2000-01-01 02:00:00

我想按产品 ( prd_id ) 和事件日期 ( act_dt ) 拆分此事件表中的数据，并使用每个组的序列值更新组 ( grp ) 列。

关键是，我需要按相似的时间戳进行分组，其中相似的意思是“所有记录的差异正好是 1 秒”。换句话说，在一个组内，按日期排序的任何 2 条记录之间的差异将恰好是 1 秒，而第一条和最后一条记录之间的差异可以是任意时间量，只要所有中间记录都是 1 秒分开。

对于示例数据，组将是:

id  prd_id  act_dt               grp
------------------------------------
1   1       2000-01-01 00:00:00  1
2   1       2000-01-01 00:00:01  1
3   1       2000-01-01 00:00:02  1
4   2       2000-01-01 00:00:00  2
5   2       2000-01-01 00:00:01  2
6   2       2000-01-01 01:00:00  3
7   2       2000-01-01 01:00:01  3
8   3       2000-01-01 00:00:00  4
9   3       2000-01-01 00:00:01  4
10  3       2000-01-01 02:00:00  5

我会用什么方法来完成这个？

表的大小约为 2000 万行，如果这会影响用于解决问题的方法。

Answer 1

我不是 Oracle 专家，所以我在猜测一行的最佳选择:

    (CAST('2010-01-01' AS DATETIME) - act_dt) * 24 * 60 * 60      AS time_id,

这只需是“从 [aDateConstant] 到 act_dt 的秒数”。结果可能是否定的。它只需要一个秒数，即可转动您的 act_dt进入一个INT。其余的应该可以正常工作。

WITH
  sequenced_data
AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY prd_id  ORDER BY act_dt)      AS sequence_id,
    (CAST('2010-01-01' AS DATETIME) - act_dt) * 24 * 60 * 60      AS time_id,
    *
  FROM
    yourTable
)
SELECT
  DENSE_RANK() OVER (PARTITION BY prd_id ORDER BY time_id - sequence_id) AS group_id,
  *
FROM
  sequenced_data 

示例数据:

 sequence_id | time_id | t-s | group_id
-------------+---------+-----+----------
      1      |   1     |  0  |    1
      2      |   2     |  0  |    1
      3      |   3     |  0  |    1
      4      |   8     |  4  |    2
      5      |   9     |  4  |    2
      6      |   12    |  6  |    3
      7      |   14    |  7  |    4
      8      |   15    |  7  |    4

注意:这确实假设有 不是 同一时间的多条记录。如果有，则需要先过滤掉它们。可能只是在前面的 CTE 中使用 GROUP BY。