sql中的累计和和累计计数

Question

我正在尝试为患者建立治疗方案，下面是我正在制作的示例表。

tbody> <<tr>

PatientID	SessionDate	nextdate	diff	courses	Coursenum	course_Count	Total session
10000	12/13/2012	空	1.1	开始	1	1	10
10000	12/14/2012	12/13/2012	1	现有	1	2	10
10000	12/14/2012	12/14/2012	0	现有	1	3	10
10000	12/17/2012	12/14/2012	3	现有	1	4	10
10000	12/18/2012	12/17/2012	1	现有	1	5	10
10000	12/19/2012	12/18/2012	1	现有	1	6	10
10000	12/21/2012	12/19/2012	2	现有	1	7	10
10000	12/21/2012	12/21/2012	0	现有	1	8	10
10000	12/22/2012	12/21/2012	1	现有	1	9	10
10000	12/24/2012	12/22/2012	2	现有	1	10	10
10000	9/17/2015	1/25/2013	965	开始	2	1	20
10000	9/18/2015	9/17/2015	1	现有	2	2	20
10000	9/21/2015	9/18/2015	3	现有	2	3	20
10000	9/22/2015	9/21/2015	1	现有	2	4	20
10000	9/23/2015	9/22/2015	1	现有	2	5	20
10000	9/25/2015	9/23/2015	2	现有	2	6	20
10000	9/28/2015	9/25/2015	3	现有	2	7	20
10000	9/29/2015	9/28/2015	1	现有	2	8	20
10000	9/30/2015	9/29/2015	1	现有	2	9	20
10000	10/2/2015	9/30/2015	2	现有	2	10	20
10000	10/5/2015	10/2/2015	3	现有	2	11	20
10000	10/6/2015	10/5/2015	1	现有	2	12	20
10000	10/7/2015	10/6/2015	1	现有	2	13	20
10000	10/9/2015	10/7/2015	2	现有	2	14	20
10000	10/12/2015	10/9/2015	3	现有	2	15	20
10000	10/13/2015	10/12/2015	1	现有	2	16	20
10000	10/14/2015	10/13/2015	1	现有	2	17	20
10000	10/16/2015	10/14/2015	2	现有	2	18	20
10000	10/19/2015	10/16/2015	3	现有	2	19	20
10000	10/20/2015	10/19/2015	1	现有	2	20	20

我认为使用count()和sum()的窗口函数在这里可以按正确的顺序工作。

select count(coursenum) over (partition by coursenum order by sessiondate)应该得到你的累计计数。

只要我们想在原始数据本身中显示组级聚合，就可以在SQL中使用窗口函数。

select *, max(course_Count) over (partition by Coursenum order by Session_date) as Total_Session 
from( select *, count(*) over (partition by Coursenum order by Session_date) as course_Count 
from( select *,sum(case when courses = 'start' then 1 else 0 end) over (order by Session_date) as Coursenum
from [table_name]
) A)

有关窗口函数的更多信息，请参阅此链接这是Case语句