业务场景
- 我们做的是智慧交通信控平台,需要将实时采集到的交通大数据(信号机灯态、卡口过车、雷达数据等)全部入库,按照时间顺序存储
- 然后根据原始数据,再计算出一些交通评价指标,存储到数据库,供后续聚合查询和分析统计
- 前端设备(信号机、雷达、卡口等)上报原始数据,按照各自定义好的数据格式,使用
socket
上报给服务端 - 我们使用的编程语言是Java,所以服务端使用
netty
接收 - 实时接收的数据,经过
kafka
批量发送到采集服务,进行数据融合处理,批量写入clickhouse
数据库 - 根据
clickhouse
里的原始数据,按照信控周期(相位或周期)或者固定时间(5分钟),计算出数据指标,再存储PostgreSQL
数据库 - 服务端展示时,一部分查询已有数据指标列表,一部分要按照不同时间粒度再对数据指标进行聚合展示
- 我们业务数据库用的是
PostgreSQL
,目前使用的版本为14.2
以固定时间(年/月/日/时/分/秒)聚合
- 第一种聚合需求,按照固定时间聚合,例如我们展示时,其中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-16
20:38:40+00');
Result: 2001-02-16 00:00:00-05
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16
20: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
以任意时间聚合
- 第二种需求,就是按照一定时间粒度聚合,例如我们的其中两种聚合粒度为:
5分钟
、15分钟
- 对于以任意时间聚合,就比较麻烦了,还需要数学计算,
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
SELECT
intersection_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
FROM
situation_analysis_intersection
WHERE
intersection_id = 1687005
and approach = 'WB'
AND time_stamp >= '2023-04-20 00:00:00'
AND time_stamp < '2023-04-29 00:00:00'
GROUP BY
time_stamp2,
intersection_id,
approach
ORDER BY
time_stamp2
LIMIT 20 OFFSET 0
- 其中
date_bin ( '15 minutes', time_stamp, TIMESTAMP '2023-01-01' ) AS time_stamp2
就是把时间戳time_stamp
处理下,按照15分钟对齐后作为time_stamp2
- 下面的
GROUP BY time_stamp2
,就是再根据对齐后的time_stamp2
进行分组聚合统计,完美符合需求