mysql - 按连续时间分组

Question

你好，我有一个专栏如下

+--------+--------+
| day    |  amount| 
+--------+---------
| 2      |   2    |
| 1      |   3    | 
| 1      |   4    | 
| 2      |   2    |
| 3      |   3    | 
| 4      |   3    |
| 5      |   6    |
| 6      |   6    |
+--------+--------+

现在我想要这样的东西:将第 1 天到第 3 天求和作为第一行，将第 2 天到第 4 天求和作为第 2 行，依此类推。

+--------+--------+
| day    |  amount| 
+--------+---------
| 1-3    |   14   |
| 2-4    |   10   | 
| 3-5    |   12   |
| 4-6    |   15   |
+--------+--------+

能否提供任何帮助，谢谢!

Answer 1

我只会使用相关子查询:

select day, day + 2 as end_day,
       (select sum(amount)
        from t t2
        where t2.day in (t.day, t.day + 1, t.day + 2)
       ) as amount
from (select distinct day from t) t;

这将返回所有日期的行，不限于最后 4 天。如果您真的想要该限制，则可以使用:

select day, day + 2 as end_day,
       (select sum(amount)
        from t t2
        where t2.day in (t.day, t.day + 1, t.day + 2)
       ) as amount
from (select distinct day
      from t
      order by day
      offset 1 limit 99999999
     ) t
order by day;