GROUP BY next months over N years

Question

I need to aggregate amounts grouped by "horizon" 12 next months over 5 year:
assuming we are 2015-08-15

SUM amount from  0 to 12 next months (from 2015-08-16 to 2016-08-15)
SUM amount from 12 to 24 next months (from 2016-08-16 to 2017-08-15)
SUM amount from 24 to 36 next months ...
SUM amount from 36 to 48 next months
SUM amount from 48 to 60 next months

Here is a fiddled dataset example:

+----+------------+--------+
| id | date       | amount |
+----+------------+--------+
|  1 | 2015-09-01 |     10 |
|  2 | 2015-10-01 |     10 |
|  3 | 2016-10-01 |     10 |
|  4 | 2017-06-01 |     10 |
|  5 | 2018-06-01 |     10 |
|  6 | 2019-05-01 |     10 |
|  7 | 2019-04-01 |     10 |
|  8 | 2020-04-01 |     10 |
+----+------------+--------+

Here is the expected result:

+---------+--------+
| horizon | amount |
+---------+--------+
|       1 |     20 |
|       2 |     20 |
|       3 |     10 |
|       4 |     20 |
|       5 |     10 |
+---------+--------+

How can I get these 12 next months grouped "horizons" ?

I tagged PostgreSQL but I'm actually using an ORM so it's just to find the idea. (by the way I don't have access to the date formatting functions)

Subtract 8.5 months and group by year. – Strawberry Jul 30 '15 at 09:21 — Strawberry, Jul 30 '15 at 09:21

score 6 · Accepted Answer · edited May 23 '17 at 10:26

6

I would split by 12 months time frame and group by this:

SELECT
  FLOOR(
      (EXTRACT(EPOCH FROM date) - EXTRACT(EPOCH FROM now()))
        / EXTRACT(EPOCH FROM INTERVAL '12 month')
    ) + 1 AS "horizon",
  SUM(amount) AS "amount"
FROM dataset 
GROUP BY horizon
ORDER BY horizon;

SQL Fiddle

Inspired by: Postgresql SQL GROUP BY time interval with arbitrary accuracy (down to milli seconds)

edited May 23 '17 at 10:26

Community

1
1

answered Jul 30 '15 at 09:15

wid

341
2
5

The most simple and smart answer for me – Pierre de LESPINAY Jul 30 '15 at 09:39
Kudos and +1 for providing link to inspiration. – Patrick Jul 30 '15 at 10:16
1

Note that (a / b) - (c / b) = (a - c) / b. The latter is almost twice as fast to calculate. Also note that `epoch from interval '12 month'` gives you seconds equal to 365.25 days. Depending on which year you are in and which you are comparing to (leap year or not) the result may be a day off in either direction. – Patrick Jul 30 '15 at 10:32
Thx for the factorization, it also makes it simpler, I have updated the answer and the fiddle according – Pierre de LESPINAY Aug 01 '15 at 06:11

score 1 · Answer 2 · answered Jul 30 '15 at 09:26

1

Perhaps CTE?

WITH RECURSIVE grps AS
(
  SELECT 1 AS Horizon, (date '2015-08-15') + interval '1' day AS FromDate, (date '2015-08-15') + interval '1' year AS ToDate
  UNION ALL
  SELECT Horizon + 1, ToDate + interval '1' day AS FromDate, ToDate + interval '1' year
  FROM grps WHERE Horizon < 5
)
SELECT 
  Horizon, 
  (SELECT SUM(amount) FROM dataset WHERE date BETWEEN g.FromDate AND g.ToDate) AS SumOfAmount
FROM 
  grps g

SQL fiddle

answered Jul 30 '15 at 09:26

Eric

5,675
16
24

An amazing solution! Too bad it is so hideously complex while there is a rather straightforward solution. – Patrick Jul 30 '15 at 11:29

Patrick · Answer 3 · 2015-07-31T01:52:41.640

1

Rather simply:

SELECT horizon, sum(amount) AS amount
FROM generate_series(1, 5) AS s(horizon)
JOIN dataset ON "date" >= current_date + (horizon - 1) * interval '1 year'
             AND "date" < current_date + horizon * interval '1 year'
GROUP BY horizon
ORDER BY horizon;

edited Jul 31 '15 at 01:52

answered Jul 30 '15 at 11:21

Patrick

29,357
6
62
90

score 0 · Answer 4 · answered Jul 30 '15 at 08:59

You need a union and an aggregate function:

select 1 as horizon, 
       sum(amount) amount
from the_table
where date >= current_date 
  and date < current_date + interval '12' month
union all
select 2 as horizon, 
       sum(amount) amount
where date >= current_date + interval '12' month
  and date < current_date + interval '24' month
union all 
select 3 as horizon, 
       sum(amount) amount
where date >= current_date + interval '24' month
  and date < current_date + interval '36' month
... and so on ...

But I don't know, how to do that with an obfuscation layer (aka ORM) but I'm sure it supports (or it should) aggregation and unions.

This could easily be wrapped up into a PL/PgSQL function where you pass the "horizon" and the SQL is built dynamically so that all you need to call is something like: select * from sum_horizon(5) where 5 indicates the number of years.

^{Btw: date is a horrible name for a column. For one because it's a reserved word, but more importantly because it doesn't document the meaning of the column. Is it a "release date"? A "due date"? An "order date"?}

`JOIN generate_series(1, 5) AS s(h)` and then `current_date + s.h * interval '1 year'` etc? — Patrick, Jul 30 '15 at 10:36

Walerian Sobczak · Answer 5 · 2015-07-30T09:15:56.353

Assuming you need intervals from current date to this day next year and so on, I would query this like this:

SELECT 1 AS horizon, SUM(amount) FROM dataset
WHERE date > now()
AND date < (now() + '12 months'::INTERVAL)
UNION
SELECT 2 AS horizon, SUM(amount) FROM dataset
WHERE date > (now() + '12 months'::INTERVAL)
AND date < (now() + '24 months'::INTERVAL) 
UNION
SELECT 3 AS horizon, SUM(amount) FROM dataset
WHERE date > (now() + '24 months'::INTERVAL)
AND date < (now() + '36 months'::INTERVAL)
UNION
SELECT 4 AS horizon, SUM(amount) FROM dataset
WHERE date > (now() + '36 months'::INTERVAL)
AND date < (now() + '48 months'::INTERVAL)
UNION
SELECT 5 AS horizon, SUM(amount) FROM dataset
WHERE date > (now() + '48 months'::INTERVAL)
AND date < (now() + '60 months'::INTERVAL)
ORDER BY horizon;

You can generalize it and make something like this using additional variable:

SELECT number AS horizon, SUM(amount) FROM dataset
WHERE date > (now() + ((number - 1) * '12 months'::INTERVAL))
AND date < (now() + (number * '12 months'::INTERVAL));

Where number is an integer from range [1,5]

Here is what I get from the Fiddle:

| horizon | sum |
|---------|-----|
|       1 |  20 |
|       2 |  20 |
|       3 |  10 |
|       4 |  20 |
|       5 |  10 |

Your `number` would come from `JOIN generate_series(1, 5) AS s(number)` — Patrick, Jul 30 '15 at 10:40

score 0 · Answer 6 · answered Jul 30 '15 at 09:14

Try this

select 
id,
sum(case when date>=current_date and date<current_date+interval 1 year then amount else 0 end) as year1,
sum(case when date>=current_date+interval 1 year and date<current_date+interval 2 year then amount else 0 end) as year2,
sum(case when date>=current_date+interval 2 year and date<current_date+interval 3 year then amount else 0 end) as year3,
sum(case when date>=current_date+interval 3 year and date<current_date+interval 4 year then amount else 0 end) as year4,
sum(case when date>=current_date+interval 4 year and date<current_date+interval 5 year then amount else 0 end) as year5
from table
group by id

GROUP BY next months over N years

6 Answers6