sql: 按不同部分计算 um_no 的数量，按小时累计

Question

无特定函数如何按不同段数和按小时累计多少um_no

原创:

+-------+------+------+-----+
| um_no | nums | hour | day |
+-------+------+------+-----+
| mary  |    3 |    8 |   1 |
| john  |    6 |    8 |   1 |
| peter |    2 |    8 |   1 |
| jason |    1 |    8 |   1 |
| mary  |    5 |    9 |   1 |
| john  |    6 |    9 |   1 |
| peter |    6 |    9 |   1 |
| jason |    1 |    9 |   1 |
| mary  |    5 |   10 |   1 |
| john  |    4 |   10 |   1 |
| peter |    2 |   10 |   1 |
| jason |    4 |   10 |   1 |
+-------+------+------+-----+

想要:

+-----+------+---------+----------+-----------+----------+
| day | hour | nums0_5 | nums5_10 | nums10_15 | nums15up |
+-----+------+---------+----------+-----------+----------+
|   1 |    8 |       3 |        1 |         0 |        0 |
|   1 |    9 |       1 |        2 |         1 |        0 |
|   1 |   10 |       0 |        2 |         1 |        1 |
+-----+------+---------+----------+-----------+----------+

Answer 1

你可以尝试.. 与 gordon 的建议一起使用，这真的很棒..

select
  day,
  hour,
  count(*) filter (where sum_nums >= 0 and sum_nums <= 5) as nums0_5,
  count(*) filter (where sum_nums >= 5 and sum_nums <= 10) as nums5_10,
  count(*) filter (where sum_nums >= 10 and sum_nums <= 15) as nums10_15,
  count(*) filter (where sum_nums >= 15) as nums15up
from
  (select 
      *,
      sum(nums) over (partition by um_no, day order by hour) as sum_nums
   from 
      tbl) t
group by
  day,
  hour

你可以在这里看到 DEMO