CODEWITHSUNDEEP
sqlamazon-redshift
thinksql
thinksql

Reputation: 29

How do I calculate Moving Annual Total (Rolling 12 months) in Redshift SQL?

I am facing trouble trying to create a new column that states the rolling sum partitioned by Market and Product. For simplicity, I only included one unique Market and Product, here is a simple example of what I aim to achieve:

enter image description here

There are two hurdles I face:

  1. There will be repeated rows of a date, see highlighted in yellow.
  2. The code logic I tried is not working:
            SELECT   ,
             TO_DATE(A.DATE
                      || '01','YYYYMMDD') AS DATE_PERIOD ,
             EXTRACT(YEAR FROM (TO_DATE(A.DATE
                      || '01','YYYYMMDD'))) AS YEARS ,
             EXTRACT(MONTH FROM (TO_DATE(A.DATE
                      || '01','YYYYMMDD'))) AS MONTHS ,
             A.PRODUCT                      AS PRODUCT ,
             A.MARKET                       AS MKT ,
             SUM(A.LOCAL_CURRENCY_VAL) OVER (PARTITION BY EXTRACT(YEAR FROM (TO_DATE(A.DATE
                      || '01','YYYYMMDD'))),A.MARKET, A.PRODUCT ORDER BY EXTRACT(MONTH FROM (TO_DATE(A.DATE
                      || '01','YYYYMMDD'))) ROWS BETWEEN 11 PRECEDING AND    CURRENT ROW ) AS ROLLING_12     
FROM MYTABLE AS A
    GROUP BY ,
         A.PRODUCT ,
         A.LOCAL_CURRENCY_VAL ,
         EXTRACT(YEAR FROM (TO_DATE(A.DATA_PERIOD
                  || '01','YYYYMMDD'))) ,
         EXTRACT(MONTH FROM (TO_DATE(A.DATA_PERIOD
                  || '01','YYYYMMDD'))) ,
         A.DATA_PERIOD

Upvotes: 0

Views: 3432

Answers (1)

Hyruma92
Hyruma92

Reputation: 876

You are nearly there, you have to use the window function but you use it in a wrong way. Let's go step by step without overcomplicate the query:

First of all you said that you have repeated rows of a date. Let's aggregate these values and remove the duplicates:

SELECT "date", market, product, sum(sales_value) as overall_sales_value
FROM my_table 
GROUP BY date, market, product

As you can see this is very easy and we get rid of the duplicates rows.

Now let's proceed with the calculus of the window function. We will use the output of the preceding query as a new table called my_sub_table.

SELECT "date", market, product, overall_sales_value,
       sum(overall_sales_value) over (partition by market, product 
                                      order by "date" 
                                      rows between 11 preceding and current row)
FROM my_sub_table

This achieve exactly what you want. In your case another problem was that you were putting the date column in the partition by, but it doesn't make sense because you want this stat for each market and product. More details on the usage of the window function here.

So, final query:

SELECT "date", market, product, overall_sales_value,
       sum(overall_sales_value) over (partition by market, product 
                                      order by "date" 
                                      rows between 11 preceding and current row)
FROM (SELECT "date", market, product, sum(sales_value) as overall_sales_value
      FROM my_table 
      GROUP BY date, market, product) as my_sub_table

Upvotes: 1

Related Questions