Cumulative sum and cumulative count in sql-CodePudding

I am trying establish treatment courses for patients, below is sample table I am working on.

PatientID	SessionDate	Next_date	diff	courses	Coursenum	course_Count	Total Sesion
10000	12/13/2012	NULL	1.1	start	1	1	10
10000	12/14/2012	12/13/2012	1	existing	1	2	10
10000	12/14/2012	12/14/2012	0	existing	1	3	10
10000	12/17/2012	12/14/2012	3	existing	1	4	10
10000	12/18/2012	12/17/2012	1	existing	1	5	10
10000	12/19/2012	12/18/2012	1	existing	1	6	10
10000	12/21/2012	12/19/2012	2	existing	1	7	10
10000	12/21/2012	12/21/2012	0	existing	1	8	10
10000	12/22/2012	12/21/2012	1	existing	1	9	10
10000	12/24/2012	12/22/2012	2	existing	1	10	10
10000	9/17/2015	1/25/2013	965	start	2	1	20
10000	9/18/2015	9/17/2015	1	existing	2	2	20
10000	9/21/2015	9/18/2015	3	existing	2	3	20
10000	9/22/2015	9/21/2015	1	existing	2	4	20
10000	9/23/2015	9/22/2015	1	existing	2	5	20
10000	9/25/2015	9/23/2015	2	existing	2	6	20
10000	9/28/2015	9/25/2015	3	existing	2	7	20
10000	9/29/2015	9/28/2015	1	existing	2	8	20
10000	9/30/2015	9/29/2015	1	existing	2	9	20
10000	10/2/2015	9/30/2015	2	existing	2	10	20
10000	10/5/2015	10/2/2015	3	existing	2	11	20
10000	10/6/2015	10/5/2015	1	existing	2	12	20
10000	10/7/2015	10/6/2015	1	existing	2	13	20
10000	10/9/2015	10/7/2015	2	existing	2	14	20
10000	10/12/2015	10/9/2015	3	existing	2	15	20
10000	10/13/2015	10/12/2015	1	existing	2	16	20
10000	10/14/2015	10/13/2015	1	existing	2	17	20
10000	10/16/2015	10/14/2015	2	existing	2	18	20
10000	10/19/2015	10/16/2015	3	existing	2	19	20
10000	10/20/2015	10/19/2015	1	existing	2	20	20

I achieved this output using python

Note: 1.1 in diff column is just a placeholeder for NULL

sessions['Coursenum']=(sessions.status.eq('start')).cumsum() sessions['course_count']=sessions.groupby(['Coursenum']).cumcount() 1 sessions['TotalSessions']=sessions.groupby('Coursenum')['course_count'].transform('max')

I wanted to program this using sql. Is there a way to do this in sql?

CodePudding user response：

I think window functions using count() and sum() would work here with the proper order by.

select count(coursenum) over (partition by coursenum order by sessiondate) should get you cumulative count.

CodePudding user response：

We can use window functions in SQL whenever we want to show group-level aggregations in raw data itself.

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

Refer this link for more information on window functions and this for Case statement