SQL查询按附近时间戳分组

Question

我有一个带有时间戳列的表。我希望能够按标识符列(例如 cusip)进行分组，对另一列(例如数量)进行求和，但仅限于彼此相距在 30 秒内的行，即不固定30 秒桶间隔。给定数据:

       cusip| quantity|    timestamp============|=========|=============BE0000310194|      100| 16:20:49.000BE0000314238|       50| 16:38:38.110BE0000314238|       50| 16:46:21.323BE0000314238|       50| 16:46:35.323

我想编写一个返回的查询:

       cusip| quantity============|=========BE0000310194|      100BE0000314238|       50BE0000314238|      100

编辑:此外，如果我还可以从查询中获取 MIN(timestamp)，这将大大简化事情。

Answer 1

从 Sean G 解决方案中，我已删除完整表上的 Group By。事实上，Oracle SQL 重新调整了几个部分。

首先找到上次的时间后，分配自己的父ID。如果 Previous Time 中有 null，那么我们排除给它一个 ID。

现在基于通过避免空值来获取最近的自身父 ID，以便所有最近的 30 秒 cusip 落入一个组。

由于有 CUSIP 列，我假设数据集将是大型市场交易数据。而不是在完整表上使用分组，而是使用 CUSIP 和最终组父 ID 进行分区以获得更好的性能。

SELECT
id,
sub.parent_id,
sub.cusip,
timestamp,
quantity,
sum(sub.quantity) OVER(
    PARTITION BY cusip, parent_id
) sum_quantity,
MIN(sub.timestamp) OVER(
    PARTITION BY cusip, parent_id
) min_timestamp
FROM
(
    SELECT
        base_sub.*,
        CASE
            WHEN base_sub.self_parent_id IS NOT NULL THEN
                base_sub.self_parent_id
            ELSE
                LAG(base_sub.self_parent_id) IGNORE NULLS OVER(
                    PARTITION BY cusip
                    ORDER BY
                        timestamp, id
                )
        END parent_id
    FROM
        (
            SELECT
                c.*,
                CASE
                    WHEN nvl(abs(EXTRACT(SECOND FROM to_timestamp(previous_timestamp, 'yyyy/mm/dd hh24:mi:ss') - to_timestamp
                    (timestamp, 'yyyy/mm/dd hh24:mi:ss'))), 31) > 30 THEN
                        id
                    ELSE
                        NULL
                END self_parent_id
            FROM
                (
                    SELECT
                        my_table.id,
                        my_table.cusip,
                        my_table.timestamp,
                        my_table.quantity,
                        LAG(my_table.timestamp) OVER(
                            PARTITION BY my_table.cusip
                            ORDER BY
                                my_table.timestamp, my_table.id
                        ) previous_timestamp
                    FROM
                        my_table
                ) c
        ) base_sub
) sub

下面是表格行

输入数据:

下面是输出

结果