代码编织梦想

业务场景

  • 我们做的是智慧交通信控平台,需要将实时采集到的交通大数据(信号机灯态、卡口过车、雷达数据等)全部入库,按照时间顺序存储
  • 然后根据原始数据,再计算出一些交通评价指标,存储到数据库,供后续聚合查询和分析统计
  • 前端设备(信号机、雷达、卡口等)上报原始数据,按照各自定义好的数据格式,使用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进行分组聚合统计,完美符合需求
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/u010882234/article/details/130447886

sql中的聚合函数(avg、count、max、min、sum)和数据分组-爱代码爱编程

目录 函数的分类聚合函数AVG()COUNT()MAX()MIN()SUM()DISTINC 聚合不同值组合聚合函数 数据分组创建分组GROUP BY汇总分组数据ROLLU过滤分组HAVING分组排序ORDER

postgresql时间处理技巧,每半天,每周,每月和每5分钟统计-爱代码爱编程

一、每半天 如果有张表log_bus_runinfo里有一个created_date是timestamp类型,如何统计12点前的数据? 在 PostgreSQL 中,您可以使用 DATE_TRUNC 函数和 WHERE 子句来统计特定时间范围内的数据。以下是一个查询例子,用于统计 log_bus_runinfo 表中创建日期 created_dat

sql根据日期查询求和-爱代码爱编程

问题:已知一个日期和每天的数据,写一个sql,求出这个已知日期所在周的所有数据之和?原文 比如已知日期是2023-06-07 SELECT SUM(data_column) AS total_sum FROM

mysql 如何按照日期聚合数据_sql按日期聚合-爱代码爱编程

按照日期聚合数据,这里指的是统计一定时间范围内数据条目的个数。 场景: mysql表里面两个字段,一个是id主键,另一个是timestamp,记录插入数据库的时间。现在要统计在每小时新增的数据条目的数量。这就需要用到sql语

sql:求筛选时间段内每天各分组的聚合数据_sql按天分组求和-爱代码爱编程

任务场景: 报表需求,必须由SQL处理完成,页面筛选条件为日期段,需要将所选日期按照每日区分,查出所有组别在每日的进线量、接通量、呼损量、接听率、进线占比、好评率。 进线量为线路全部进线量,接通量为status=‘Ans