業(yè)務場景

• 我們做的是智慧交通信控平臺，需要將實時采集到的交通大數(shù)據（信號機燈態(tài)、卡口過車、雷達數(shù)據等）全部入庫，按照時間順序存儲
• 然后根據原始數(shù)據，再計算出一些交通評價指標，存儲到數(shù)據庫，供后續(xù)聚合查詢和分析統(tǒng)計
• 前端設備（信號機、雷達、卡口等）上報原始數(shù)據，按照各自定義好的數(shù)據格式，使用socket上報給服務端
• 我們使用的編程語言是Java，所以服務端使用netty接收
• 實時接收的數(shù)據，經過kafka批量發(fā)送到采集服務，進行數(shù)據融合處理，批量寫入clickhouse數(shù)據庫
• 根據clickhouse里的原始數(shù)據，按照信控周期（相位或周期）或者固定時間（5分鐘），計算出數(shù)據指標，再存儲PostgreSQL數(shù)據庫
• 服務端展示時，一部分查詢已有數(shù)據指標列表，一部分要按照不同時間粒度再對數(shù)據指標進行聚合展示
• 我們業(yè)務數(shù)據庫用的是PostgreSQL，目前使用的版本為14.2

以固定時間（年/月/日/時/分/秒）聚合

• 第一種聚合需求，按照固定時間聚合，例如我們展示時，其中2個聚合粒度為：小時、天
  
• 對于日期，我們可以存儲時間戳、毫秒數(shù)、字符串等，處理時再根據相應類型轉換下即可
• 日期時間一般是 年、月、日、時、分、秒，這六種都可以直接取到，所以精確到這些粒度都比較簡單

to_char聚合

• 下面給出一個使用to_char函數(shù)的實現(xiàn)，其實主要就是做下字符串截取
• 注意，函數(shù)里需要標注參數(shù)類型，使用::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也可以做到上面的效果，還可以加上不同時區(qū)

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分鐘
• 對于以任意時間聚合，就比較麻煩了，還需要數(shù)學計算，SQL寫起來會特別長
• 這個問題在PostgreSQL14之后得到解決，因為這個版本增加了一個新函數(shù)支持date_bin
• 官方文檔描述為：The function date_bin “bins” the input timestamp into the specified interval (the stride) aligned with a specified origin.
• 渣翻一下為函數(shù)date_bin將輸入時間戳“存儲”到與指定原點對齊的指定間隔（步長）中。
• 有了這個函數(shù)后，我們可以很方便的根據任意時間間隔聚合
• 對齊時間可以根據你的需要寫，如果都是今年的新數(shù)據，你寫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

實際應用示例

• 根據我們的業(yè)務場景，按照15分鐘聚合
• 其實時間間隔15 minutes，是作為參數(shù)傳遞進去的，其他時間間隔也都可以實現(xiàn)
• 給一個官方文檔地址：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進行分組聚合統(tǒng)計，完美符合需求

文章來源地址http://www.zghlxwxcb.cn/news/detail-432529.html

到了這里，關于PostgreSQL數(shù)據庫以任意時間間隔聚合查詢group by的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！