怎么建设网站规划,北京市建设教育协会网站查询,河南安阳网站建设,wordpress视频分享文章目录 业务场景以固定时间#xff08;年/月/日/时/分/秒#xff09;聚合to_char聚合date_trunc聚合 以任意时间聚合date_bin聚合实际应用 业务场景
我们做的是交通信控平台#xff0c;需要根据实时采集到的交通大数据#xff0c;计算出一些指标#xff0c;存储到数据库… 文章目录 业务场景以固定时间年/月/日/时/分/秒聚合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 seconddate_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:4000);
Result: 2001-02-16 00:00:00-05
SELECT date_trunc(day, TIMESTAMP WITH TIME ZONE 2001-02-1620:38:4000, 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 WBAND 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
