PieCloudDB中按连续日期分组求和Flag=1的Flow值问题排查

数据情况 #

现有PieCloudDB数据库表数据如下：

需求 #

对Flag为1的Flow值按连续日期分组求和，预期输出：

尝试的SQL及错误结果 #

尝试的SQL语句：

WITH CTE AS (
    SELECT
        date,
        flow,
        flag,
        ROW_NUMBER() OVER (ORDER BY date) - ROW_NUMBER() OVER (PARTITION BY flag ORDER BY date) AS grp
    FROM mytable
    WHERE flag = 1
)
SELECT
    MIN(date) AS start,
    MAX(date) AS end,
    SUM(flow)
FROM CTE 
GROUP BY grp
ORDER BY start;

得到的错误结果：

问题原因 #

CTE中提前加入WHERE flag = 1过滤条件，导致第一个ROW_NUMBER() OVER (ORDER BY date)仅对flag=1的行排序，和第二个按flag分区后的行号同步递增，两者差值始终为0，所有flag=1的行被归为同一组，最终求和结果是所有符合条件的Flow总和，无法区分非连续日期段。

修正后的SQL #

方案一：保留全表计算分组标识后过滤 #

先基于全表数据计算分组标识，再筛选flag=1的行进行聚合：

WITH CTE AS (
    SELECT
        date,
        flow,
        flag,
        ROW_NUMBER() OVER (ORDER BY date) - ROW_NUMBER() OVER (PARTITION BY flag ORDER BY date) AS grp
    FROM mytable
)
SELECT
    MIN(date) AS start,
    MAX(date) AS end,
    SUM(flow) AS sum
FROM CTE 
WHERE flag = 1
GROUP BY grp
ORDER BY start;

方案二：基于日期差值分组 #

直接通过日期与行号的差值判断连续日期，逻辑更直观：

WITH CTE AS (
    SELECT
        date,
        flow,
        flag,
        date - INTERVAL '1 day' * ROW_NUMBER() OVER (PARTITION BY flag ORDER BY date) AS grp
    FROM mytable
    WHERE flag = 1
)
SELECT
    MIN(date) AS start,
    MAX(date) AS end,
    SUM(flow) AS sum
FROM CTE
GROUP BY grp
ORDER BY start;

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