我正在查詢一些時間序列資料，并希望將查詢應用于多個指定的時間間隔，以獲取每個間隔的不同行。
這就是我的意思：這些是時間間隔（不要看空的，要使用coalesce()）：
并想像這樣查詢我的資料：
select count(*) from time_series where time between [multiple_intervals_here]
我最接近解決方案的是：
select count(*) from time_series where time (between x and y) or (between a and b)
但顯然，我無法動態指定間隔，也不會為每個間隔獲得一行。
（我將對其應用更多聚合函式，而不僅僅是count()）
最終結果將如下所示：
提前致謝
uj5u.com熱心網友回復：
tCTE 是區間串列的模擬。
with t (id, time_start, time_stop) as
(
values
('interval-A', '2022-05-26T10:00'::timestamp, '2022-05-26T12:00'::timestamp),
('interval-B', '2022-05-26T13:00', '2022-05-26T17:00')
)
select t.id, count(l.x) as "count", avg(l.x) as "avg"
from t
cross join lateral
(
select x from the_table tt
where tt.start_time between t.time_start and t.time_stop
and tt.end_time between t.time_start and t.time_stop
) l
group by t.id;
uj5u.com熱心網友回復：
對每個間隔進行單獨的查詢并合并結果
select 'interval 1' as interval, count(*) from time_series where time between A and B
union
select 'interval 2' as interval, count(*) from time_series where time between C and D
union
select 'interval 3' as interval, count(*) from time_series where time between E and F
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/481680.html
標籤：sql PostgreSQL 时间尺度数据库