Tuesday, September 12, 2017

Calculate Cumulative Sum in Redshift - Get sum of values based on previous row

Here in this post I will be explaining you about the query to get the cumulative sum of the rows based on the previous rows. many times you will need to get this for the reporting purpose.

Sample result set :-

itemweekstartcountryregionquantity
AAAA9/11/2017    INIndia
AAAA9/18/2017    INIndia
AAAA9/25/2017    INIndia
AAAA10/2/2017    INIndia2000
AAAA10/9/2017    INIndia3000
AAAA10/16/2017    INIndia
AAAA10/23/2017    INIndia
AAAA10/30/2017    INIndia

Expected Output Result :-


item weekstart country region quantity csum
AAAA 9/11/2017     IN India
AAAA 9/18/2017     IN India
AAAA 9/25/2017     IN India
AAAA 10/2/2017     IN India 2000 2000
AAAA 10/9/2017     IN India 3000 5000
AAAA 10/16/2017     IN India 5000
AAAA 10/23/2017     IN India 5000
AAAA 10/30/2017     IN India 5000



Below is the query which you can use to get the desired output as mentioned above.

Redshift Query :-

SELECT  ITEM,
                WEEKSTART,
                 COUNTRY,
                 REGION,
                 SUM(QUANTITY) OVER (PARTITION BY item ORDER BY item,weekstart,country ROWS UNBOUNDED PRECEDING) AS csum
FROM table_name
ORDER BY ITEM,WEEKSTART,COUNTRY








at

No comments:

Post a Comment