如何用SQL窗口函数计算连续金额大于0的天数？

要实现按key分组，计算amount连续大于0的天数（遇到0则重置计数），核心是先将连续的非0记录划分为独立分组，再在分组内统计天数。你的现有代码未实现分组重置，导致累加不会中断。

解决方案步骤 #

1. 标记非0记录：用CASE语句标记当前行amount是否大于0。
2. 生成分组ID：通过窗口累加的方式，每次遇到amount<=0的行时，分组ID递增，这样连续的非0记录会被归为同一分组。
3. 分组内统计天数：在每个key和分组ID的范围内，按日期升序生成行号，行号即为连续非0的天数；非0记录则天数设为0。

完整SQL代码（PostgreSQL） #

WITH grouped_records AS (
    SELECT
        key,
        date,
        amount,
        -- 标记当前记录是否为正金额
        CASE WHEN amount > 0 THEN 1 ELSE 0 END AS is_positive,
        -- 生成连续非0段的分组ID：遇到非正金额时分组ID+1
        SUM(CASE WHEN amount <= 0 THEN 1 ELSE 0 END) OVER (
            PARTITION BY key
            ORDER BY date ASC
        ) AS positive_group_id
    FROM your_table -- 替换为你的实际表名
)
SELECT
    key,
    date,
    amount,
    -- 非正金额天数为0，正金额则取分组内的行号作为连续天数
    CASE WHEN is_positive = 1 THEN
        ROW_NUMBER() OVER (
            PARTITION BY key, positive_group_id
            ORDER BY date ASC
        )
    ELSE 0 END AS days
FROM grouped_records
ORDER BY key, date DESC; -- 按日期降序输出，与示例格式一致

代码说明 #

• SUM(CASE WHEN amount <=0 THEN 1 ELSE 0 END) OVER (...)：这个窗口函数会在每个key内，按日期升序遍历，每遇到一个非正金额的行，就给分组ID加1，这样连续的正金额行会共享同一个分组ID。
• ROW_NUMBER() OVER (...)：在每个分组内，按日期升序生成行号，正好对应连续非0的天数（最早的正金额行是1，后续依次递增）。
• 最终按date DESC排序，输出结果与你提供的示例格式完全匹配。

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