我有一個這樣的表：
create table simulation_book_data
(
dt date,
t time without time zone,
price numeric
)
線條看起來像這樣：
"2021-11-17" "06:39:37.7663" 2
"2021-11-17" "06:39:38.7663" 5
"2021-11-17" "06:39:39.7663" 0
"2021-11-17" "06:39:40.7663" 9
"2021-11-17" "06:39:41.7663" 3
"2021-11-17" "06:39:42.7663" 7
"2021-11-17" "06:39:43.7663" 5
"2021-11-17" "06:39:44.7663" 6
"2021-11-17" "06:39:45.7663" 3
"2021-11-17" "06:39:46.7663" 1
"2021-11-17" "06:39:47.7663" 2
"2021-11-17" "06:39:48.7663" 5
"2021-11-17" "06:39:49.7663" 8
"2021-11-17" "06:39:50.7663" 9
"2021-11-17" "06:39:51.7663" 4
"2021-11-17" "06:39:52.7663" 5
"2021-11-17" "06:39:53.7663" 6
"2021-11-17" "06:39:54.7663" 6
"2021-11-17" "06:39:55.7663" 7
"2021-11-17" "06:39:56.7663" 8
"2021-11-17" "06:39:57.7663" 9
"2021-11-17" "06:39:58.7663" 7
"2021-11-17" "06:39:59.7663" 8
"2021-11-17" "06:40:00.7663" 9
"2021-11-17" "06:40:01.7663" 1
我想對每分鐘的價格列求和，但找不到如何處理時間范圍（不是時間戳）
uj5u.com熱心網友回復：
sum()使用組呼叫函式date_trunc：
SELECT date_trunc('minute',dt t),sum(price)
FROM simulation_book_data
GROUP BY 1;
date_trunc | sum
--------------------- -----
2021-11-17 06:39:00 | 125
2021-11-17 06:40:00 | 10
(2 rows)
演示： db<>fiddle
uj5u.com熱心網友回復：
您可以使用 sum() 作為視窗函式，to_char 將時間戳轉換為格式 'HH24:MI' 省略秒
select distinct on (dt, to_char(sbd.t, 'HH24:MI'::text))
sbd.dt,
sbd.t,
to_char(sbd.t, 'HH24:MI'::text),
sum(price) over (partition by dt, to_char(sbd.t, 'HH24:MI'::text))
from simulation_book_data sbd
結果：
2021-11-17 06:39:37 06:39 125
2021-11-17 06:40:00 06:40 10
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/363136.html
標籤：sql PostgreSQL的