文章目录
- 业务场景
- 以固定时间（年/月/日/时/分/秒）聚合
-
- 以任意时间聚合
-
业务场景
- 我们做的是交通信控平台，需要根据实时采集到的交通大数据，计算出一些指标，存储到数据库，供后续聚合查询
- 前端设备上报原始数据，统一使用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
