基于条件将值聚合至前序行的SQL查询实现需求

要实现“累加Time，遇Time和Qty均非0的记录时停止累加并重启；Spent_Qty为当前累加段内所有Qty的总和”的需求，核心是通过条件分组将记录划分为不同的累加段，再在每个段内做滚动累加。以下是具体实现：

前提说明 #

SQL表本身是无序的，因此必须有一个能确定记录顺序的字段（比如自增ID、时间戳等，下文以row_num为例）。如果没有现成的顺序字段，可以通过ROW_NUMBER() OVER (ORDER BY Time)生成（需确保Time字段能唯一确定记录顺序）。

SQL查询语句 #

WITH grouped_data AS (
    SELECT
        row_num,
        Time,
        Qty,
        -- 生成分组ID：统计当前行之前所有满足「Time和Qty均非0」的记录数，实现累加段的重置
        SUM(CASE WHEN Time != 0 AND Qty != 0 THEN 1 ELSE 0 END) 
            OVER (ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 
            AS group_id
    FROM your_table
)
SELECT
    row_num,
    Time,
    Qty,
    -- 每个分组内滚动累加Time
    SUM(Time) OVER (PARTITION BY group_id ORDER BY row_num) AS Accumulated_Time,
    -- 每个分组内累计Qty总和（即Spent_Qty）
    SUM(Qty) OVER (PARTITION BY group_id ORDER BY row_num) AS Spent_Qty
FROM grouped_data;

逻辑解释 #

1. 分组ID生成：

   • 用SUM() OVER (...)窗口函数统计当前行之前所有触发重置条件（Time != 0 AND Qty != 0）的记录数量，每遇到一条触发记录，后续行的group_id就会加1，从而将连续的累加段划分开。
   • ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING确保当前行的触发记录不会计入自身的分组ID，使得触发记录和之前的行属于同一累加段，累加至触发记录后停止，下一行进入新段。

2. 滚动累加计算：

   • 用SUM() OVER (PARTITION BY group_id ORDER BY row_num)在每个分组内按顺序累加Time，得到Accumulated_Time。
   • 同样的方式累加Qty，得到当前累加段的累计总和Spent_Qty。

调整触发行归属（可选） #

如果希望触发记录作为新累加段的起始行，只需修改分组ID的计算逻辑：

SUM(CASE WHEN Time != 0 AND Qty != 0 THEN 1 ELSE 0 END) 
    OVER (ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
    AS group_id

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