CODEWITHSUNDEEP
sqlpostgresql-9.3window-functionscumulative-sum
jsxgd
jsxgd

Reputation: 413

Rolling 12 month Sum in PostgreSQL

I need to be able to create a Trailing Twelve Month report using SQL (PostgreSQL) - essentially a window/rolling 12 month sum that sums up the current month's totals + the previous 11 months for each month.

I have this table:

CREATE TABLE order_test(
order_id text,
sale_date date,
delivery_date date,
customer_id text,
vendor_id text,
order_total float);

with these values:

insert into order_test
values ('1', '2016-06-01', '2016-06-10', '2', '3', 200.10),
   ('2', '2016-06-02', '2016-06-11', '2', '4', 150.50),
   ('3', '2016-07-02', '2016-07-11', '5', '4', 100.50),
   ('4', '2016-07-02', '2016-07-11', '1', '4', 150.50),
   ('5', '2016-07-02', '2016-07-11', '1', '4', 150.50),
   ('6', '2016-08-02', '2016-08-11', '6', '4', 300.50),
   ('7', '2016-08-02', '2016-08-11', '6', '4', 150.50),
   ('8', '2016-09-02', '2016-09-11', '1', '4', 150.50),
   ('9', '2016-10-02', '2016-10-11', '1', '4', 150.50),
   ('10', '2016-11-02', '2016-11-11', '1', '4', 150.50),
   ('11', '2016-12-02', '2016-12-11', '6', '4', 150.50),
   ('12', '2017-01-02', '2017-01-11', '7', '4', 150.50),
   ('13', '2017-01-02', '2017-01-11', '1', '4', 150.50),
   ('14', '2017-01-02', '2017-01-11', '1', '4', 100.50),
   ('15', '2017-02-02', '2017-02-11', '1', '4', 150.50),
   ('16', '2017-02-02', '2017-02-11', '1', '4', 150.50),
   ('17', '2017-03-02', '2017-03-11', '2', '4', 150.50),
   ('18', '2017-03-02', '2017-03-11', '2', '4', 150.50),
   ('19', '2017-04-02', '2017-04-11', '6', '4', 120.50),
   ('20', '2017-05-02', '2017-05-11', '1', '4', 150.50),
   ('21', '2017-06-02', '2017-06-11', '2', '4', 150.50),
   ('22', '2017-06-02', '2017-06-11', '1', '4', 130.50),
   ('23', '2017-07-02', '2017-07-11', '1', '4', 150.50),
   ('24', '2017-07-02', '2017-07-11', '5', '4', 200.50),
   ('25', '2017-08-02', '2017-08-11', '1', '4', 150.50),
   ('26', '2017-09-02', '2017-09-11', '2', '4', 100.50),
   ('27', '2017-09-02', '2017-10-11', '1', '4', 150.50);

These are individual sales. For each month, I need the previous 11 months + that month's total (sale month).

I've tried a window calculation like this:

select date_trunc('month', sale_date) as sale_month,
       sum(order_total) over w as total_sales
from order_test
where (delivery_date < current_date) and
      (sale_date >= (date_trunc('month', current_date) - interval '1 year'))
window w as (Partition by date_trunc('month', sale_date)
             order by sale_date
             rows between current row and 11 following)

but it's giving me this:

      sale_month    total_sales
1   01.09.2016 00:00:00 150,5
2   01.10.2016 00:00:00 150,5
3   01.11.2016 00:00:00 150,5
4   01.12.2016 00:00:00 150,5
5   01.01.2017 00:00:00 401,5
6   01.01.2017 00:00:00 251
7   01.01.2017 00:00:00 100,5
8   01.02.2017 00:00:00 301
9   01.02.2017 00:00:00 150,5
10  01.03.2017 00:00:00 301
11  01.03.2017 00:00:00 150,5
12  01.04.2017 00:00:00 120,5
13  01.05.2017 00:00:00 150,5
14  01.06.2017 00:00:00 281
15  01.06.2017 00:00:00 130,5
16  01.07.2017 00:00:00 351
17  01.07.2017 00:00:00 200,5
18  01.08.2017 00:00:00 150,5
19  01.09.2017 00:00:00 100,5

where there should only be one row per month.

Upvotes: 4

Views: 7696

Answers (2)

zarruq
zarruq

Reputation: 2465

In inner query derived table, you need to truncate Sale_Date column to month precision using date_trunc and group by the resulting column to get the Month_total sales and then in outer query, use cumulative window sum function on month_total sales data ordering by Sale_Month to get your desired result as below.

SELECT sale_Month
    ,month_total
    ,sum(month_total) OVER (
        ORDER BY sale_Month ASC rows BETWEEN 11 preceding
                AND CURRENT row
        ) AS Sum_Series
FROM (
    SELECT date_trunc('month', Sale_Date) AS Sale_Month
        ,sum(Order_Total) AS Month_Total
    FROM order_test
    GROUP BY 1
    ORDER BY 1
    ) t

Kindly note that AND CURRENT row is optional as cumulative window function includes the current row by default, so the query can be rewritten as below.

SELECT sale_Month
    ,month_total
    ,sum(month_total) OVER (
        ORDER BY sale_Month ASC rows 11 preceding
        ) AS Sum_Series
FROM (
    SELECT date_trunc('month', Sale_Date) AS Sale_Month
        ,sum(Order_Total) AS Month_Total
    FROM order_test
    GROUP BY 1
    ORDER BY 1
    ) t

Result:

sale_month            month_total   sum_series
----------------------------------------------
2016-06-01T00:00:00Z    350.6         350.6
2016-07-01T00:00:00Z    401.5         752.1
2016-08-01T00:00:00Z    451           1203.1
2016-09-01T00:00:00Z    150.5         1353.6
2016-10-01T00:00:00Z    150.5         1504.1
2016-11-01T00:00:00Z    150.5         1654.6
2016-12-01T00:00:00Z    150.5         1805.1
2017-01-01T00:00:00Z    401.5         2206.6
2017-02-01T00:00:00Z    301           2507.6
2017-03-01T00:00:00Z    301           2808.6
2017-04-01T00:00:00Z    120.5         2929.1
2017-05-01T00:00:00Z    150.5         3079.6
2017-06-01T00:00:00Z    281           3010
2017-07-01T00:00:00Z    351           2959.5
2017-08-01T00:00:00Z    150.5         2659
2017-09-01T00:00:00Z    251           2759.5

You can check the demo here

Upvotes: 4

sidm
sidm

Reputation: 96

If I understand it correctly, you want all months to have cumulative data for the last 11 months. But the first 11 rows won't have preceding 11 entries to calculate the rolling sum. But you have mentioned that all months should have a cumulative total. So I believe you are looking for something like this. 

with x as (
select date_trunc('month', sale_date) as sale_month,sum(order_total) as monthly_order_total from order_test
group by 1 order by 1  asc)

select sale_month, monthly_order_total,
sum(monthly_order_total ) over (order by sale_month asc rows between 11 preceding and current row)

from x

Upvotes: 1

Related Questions