当前位置：首页 > news >正文

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

news 2025/8/17 13:33:23

文章目录

- 业务场景
- 以固定时间（年/月/日/时/分/秒）聚合
- - to_char聚合
  - date_trunc聚合
- 以任意时间聚合
- - date_bin聚合
  - 实际应用

业务场景

我们做的是交通信控平台，需要根据实时采集到的交通大数据，计算出一些指标，存储到数据库，供后续聚合查询
前端设备上报原始数据，统一使用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