postgresql - 如何使用postgresql按日期查询每小时聚合数据？

Question

有一张表:

ID DATE 
1  2017-09-16 20:12:48
2  2017-09-16 20:38:54
3  2017-09-16 23:58:01
4  2017-09-17 00:24:48
5  2017-09-17 00:26:42
..

我需要的结果是最近 7 天的数据以及每小时汇总的行数:

COUNT DATE
2     2017-09-16 21:00:00
0     2017-09-16 22:00:00
0     2017-09-16 23:00:00
1     2017-09-17 00:00:00
2     2017-09-17 01:00:00
..

我用 EXTRACT、DISTINCT 尝试了不同的东西，还使用了 generate_series 函数(大多数东西来自类似的 stackoverflow 问题)

这次尝试是目前最好的一次:

SELECT 
  date_trunc('hour', demotime) as date,
  COUNT(demotime) as count
FROM demo
GROUP BY date

如何生成7天的每小时系列并填写行数？

Answer 1

SQL DEMO

SELECT dd, count("demotime")     
FROM generate_series
        ( current_date - interval '7 days'
        , current_date 
        , '1 hour'::interval) dd
LEFT JOIN Table1        
  ON dd = date_trunc('hour', demotime)        
GROUP BY dd;

从现在开始工作 - 7 天:

SELECT dd, count("demotime")     
FROM generate_series
        ( date_trunc('hour', NOW()) - interval '7 days'
        , date_trunc('hour', NOW())
        , '1 hour'::interval) dd
LEFT JOIN Table1        
  ON dd = date_trunc('hour', demotime)        
GROUP BY dd;