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

坚持是一种态度

已于 2023-07-06 14:20:25 修改

阅读量3k

点赞数

分类专栏：数据库及存储技术 ClickHouse 文章标签：数据库 postgresql group by 任意时间间隔聚合 date_bin

于 2023-04-30 13:46:56 首次发布

本文链接：https://blog.csdn.net/u010882234/article/details/130447886

版权

数据库及存储技术同时被 2 个专栏收录

50 篇文章 3 订阅

订阅专栏

ClickHouse

24 篇文章 1 订阅

订阅专栏

文章目录

业务场景

我们做的是智慧交通信控平台，需要将实时采集到的交通大数据（信号机灯态、卡口过车、雷达数据等）全部入库，按照时间顺序存储
然后根据原始数据，再计算出一些交通评价指标，存储到数据库，供后续聚合查询和分析统计
前端设备（信号机、雷达、卡口等）上报原始数据，按照各自定义好的数据格式，使用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进行分组聚合统计，完美符合需求