sql - GROUP BY 下个月 N 年

Question

我需要汇总 5 年内接下来 12 个月按“地平线”分组的金额:
假设我们是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

这是一个fiddled数据集示例:

+----+------------+--------+
| 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 |
+----+------------+--------+

这是预期的结果:

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

我怎样才能得到这 12 个月的分组“视野”？

---

我标记了 PostgreSQL 但我实际上使用的是 ORM，所以它只是为了找到想法。 (顺便说一句，我无权访问日期格式功能)

Answer 1

我会按 12 个月的时间范围拆分并按此分组:

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

灵感来自:Postgresql SQL GROUP BY time interval with arbitrary accuracy (down to milli seconds)