postgreSum使用SQL对行进行求和，但我们需要在特定条件下停止和开始求和

Question

下面是我在SQL中想要的数据和输出的示例.

<表类> id 日期 标记 tbody><<tr>2022-04-0502022-04-0612022-04-0712022-04-0812022-04-0902022-04-1002022-04-1112022-04-1212022-04-1312022-04-1412022-04-1502022-04-160b2022-04-050b2022-04-061b2022-04-071b2022-04-080

这是一个缺口和孤岛问题。一种方法使用行数差异方法:

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) rn1,
ROW_NUMBER() OVER (PARTITION BY id, flag ORDER BY date) rn2
FROM yourTable
)
SELECT id, date, flag,
SUM(flag) OVER (PARTITION BY id, flag, rn1 - rn2 ORDER BY date) AS count
FROM cte
ORDER BY id, date;