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)

Answer 1

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)

Answer 2

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 |

Answer 3

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

Answer 4

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;