PostgreSQL数据库以任意时间间隔聚合查询group by

业务场景

• 我们做的是交通信控平台，需要根据实时采集到的交通大数据，计算出一些指标，存储到数据库，供后续聚合查询
• 前端设备上报原始数据，统一使用socket上报，服务端使用netty接收。经过kafka批量发送到采集服务处理，批量写入clickhouse数据库
• 根据原始数据，按照信控周期（相位或周期）或者固定时间（5分钟），计算出数据指标存储PostgreSQL数据库
• 服务端展示时，一部分查询已有数据列表，一部分要按照不同时间粒度聚合展示
• 我们目前使用的PostgreSQL数据库版本为14.2

以固定时间（年/月/日/时/分/秒）聚合

• 对于日期，我们可以存储时间戳、毫秒数、字符串等，处理时再根据相应类型转换下即可

to_char聚合

• 日期时间一般是 年、月、日、时、分、秒，所以精确到这些粒度都比较简单
• 下面给出一个使用to_char函数的实现，其实主要就是做下字符串截取
• 注意，函数里需要标注参数类型，使用::DATE标注

to_char(date::DATE, 'YYYY') as year
to_char(date::DATE, 'YYYY-MM') as month
to_char(date::DATE, 'YYYY-MM-DD') as day
to_char(date::DATE, 'YYYY-MM-DD  HH24') as hour
to_char(date::DATE, 'YYYY-MM-DD  HH24:MI ') as minute
to_char(date::DATE, 'YYYY-MM-DD  HH24:MI:SS ') as second

date_trunc聚合

• 使用date_trunc也可以做到上面的效果，还可以加上不同时区

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-1620:38:40+00');
Result: 2001-02-16 00:00:00-05
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-1620:38:40+00', 'Australia/Sydney');
Result: 2001-02-16 08:00:00-05
SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Result: 3 days 02:00:00

以任意时间聚合

• 对于以任意时间聚合，就比较麻烦了，还需要数学计算，SQL写起来会特别长
• 这个问题在PostgreSQL14之后得到解决，因为这个版本增加了一个新函数支持date_bin
• 官方文档描述为：The function date_bin “bins” the input timestamp into the specified interval (the stride) aligned with a specified origin.
• 渣翻一下为函数date_bin将输入时间戳“存储”到与指定原点对齐的指定间隔（步长）中。
• 有了这个函数后，我们可以很方便的根据任意时间间隔聚合
• 对齐时间可以根据你的需要写，如果都是今年的新数据，你写2023-01-01都没问题

date_bin聚合

• 下面给出几个示例

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17',TIMESTAMP '2001-01-01');
Result: 2020-02-11 15:30:00
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17',TIMESTAMP '2001-01-01 00:02:30');
Result: 2020-02-11 15:32:30

实际应用

• 根据我们的业务场景，按照15分钟聚合
• 其实时间间隔15 minutes，是作为参数传递进去的，其他时间间隔也都可以实现
• 给一个官方文档地址：PostgreSQL14

SELECTintersection_id,approach,date_bin ( '15 minutes', time_stamp, TIMESTAMP '2023-01-01' ) AS time_stamp2,SUM ( traffic_flow ) AS traffic_flow,round( AVG ( congestion_index ) :: NUMERIC, 2 ) AS congestion_index,round( AVG ( saturation ) :: NUMERIC, 2 ) AS saturation,round( AVG ( queue_length ) :: NUMERIC, 2 ) AS queue_length,round( AVG ( delay ) :: NUMERIC, 2 ) AS delay 
FROMsituation_analysis_intersection 
WHEREintersection_id = 1687005 and approach = 'WB'AND time_stamp >= '2023-04-20 00:00:00' AND time_stamp < '2023-04-29 00:00:00' 
GROUP BYtime_stamp2,intersection_id,approach 
ORDER BYtime_stamp2 LIMIT 20 OFFSET 0