SQL实现：按用户及3小时时段重置计算累计求和

要实现按用户和工作时段重置的累计求和，核心是为每个用户的每个连续工作时段生成唯一的分组标识，而非仅用0/1区分是否进入新时段。具体实现步骤如下：

1. 生成时段分组标识 #

基于你已有的NEW_DAY字段，通过窗口累加函数为每个用户的不同时段生成唯一的GROUP_ID：

WITH transaction_with_group AS (
    SELECT
        *,
        -- 累加NEW_DAY，同一连续时段会得到相同的GROUP_ID
        SUM(NEW_DAY) OVER (
            PARTITION BY "NAME"
            ORDER BY "TIMESTAMP" ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS GROUP_ID
    FROM (
        SELECT
            *,
            CASE WHEN COALESCE(
                "TIMESTAMP" - LAG("TIMESTAMP", 1) OVER (
                    PARTITION BY "NAME"
                    ORDER BY "TIMESTAMP" ASC
                ), 0
            ) < 3600 * 3 THEN 0 ELSE 1 END AS NEW_DAY
        FROM transaction
    ) t
)

2. 计算时段内累计求和 #

基于NAME和GROUP_ID分组，计算每个时段内的累计数量：

SELECT
    "ID",
    "NAME",
    "TIMESTAMP",
    "QUANTITY",
    SUM("QUANTITY") OVER (
        PARTITION BY "NAME", GROUP_ID
        ORDER BY "TIMESTAMP" ASC
    ) AS CUM_QUANTITY
FROM transaction_with_group
ORDER BY "NAME", "TIMESTAMP";

合并后的完整SQL #

WITH transaction_with_group AS (
    SELECT
        *,
        SUM(NEW_DAY) OVER (
            PARTITION BY "NAME"
            ORDER BY "TIMESTAMP" ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS GROUP_ID
    FROM (
        SELECT
            *,
            CASE WHEN COALESCE(
                "TIMESTAMP" - LAG("TIMESTAMP", 1) OVER (
                    PARTITION BY "NAME"
                    ORDER BY "TIMESTAMP" ASC
                ), 0
            ) < 3600 * 3 THEN 0 ELSE 1 END AS NEW_DAY
        FROM transaction
    ) t
)
SELECT
    "ID",
    "NAME",
    "TIMESTAMP",
    "QUANTITY",
    SUM("QUANTITY") OVER (
        PARTITION BY "NAME", GROUP_ID
        ORDER BY "TIMESTAMP" ASC
    ) AS CUM_QUANTITY
FROM transaction_with_group
ORDER BY "NAME", "TIMESTAMP";

结果说明 #

执行后会得到你期望的结果：

原理是：SUM(NEW_DAY)会为每个用户的每个新时段递增计数，同一时段的所有记录会共享同一个GROUP_ID，再基于这个分组做累计求和就能实现按时段重置的效果。

内容的提问来源于stack exchange，提问作者Dani