Sql 运行总和并在间隙(reset)后重置

发布于03月11日

我已经试着解决一个问题好几天了，但就是解决不了.因此我今天的问题.

PersonID	Visit_date	Medication_intake	Previous_date	Date_diff	Running_sum
1	2012-04-26	1
1	2012-11-16	1	2012-04-26	204	204
1	2013-04-11	0
1	2013-07-19	1
1	2013-12-05	1	2013-07-19	139	343
1	2014-03-18	1	2013-12-05	103	585
1	2014-06-24	0
2	2014-12-01	1
2	2015-03-09	1	2014-12-01	98	98
2	2015-09-28	0

这是我想要的结果.因此，只需计算连续区块的运行总和(药物摄入=1).

PersonID	Visit_date	Medication_intake	Previous_date	Date_diff	Running_sum
1	2012-04-26	1
1	2012-11-16	1	2012-04-26	204	204
1	2013-04-11	0
1	2013-07-19	1
1	2013-12-05	1	2013-07-19	139	139
1	2014-03-18	1	2013-12-05	103	242
1	2014-06-24	0
2	2014-12-01	1
2	2015-03-09	1	2014-12-01	98	98
2	2015-09-28	0

我与Microsoft SQL Server 2019 Express合作.

非常感谢你的建议！

推荐答案

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

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY PersonID
                                 ORDER BY Visit_date) rn1,
              ROW_NUMBER() OVER (PARTITION BY PersonId, Medication_intake
                                 ORDER BY Visit_date) rn2
    FROM yourTable
)

SELECT PersonID, Visit_date, Medication_intake, Previous_date, Date_diff,
       CASE WHEN Date_diff IS NOT NULL AND Medication_intake = 1
            THEN SUM(Date_diff) OVER (PARTITION BY PersonID, rn1 - rn2
                                      ORDER BY Visit_date) END AS Running_sum
FROM cte
ORDER BY PersonID, Visit_date;