嘿，專業人士，
我對SQL的了解很遠，希望你能給我一些提示。
目前，我們使用 python 聚合我們的資料，我會嘗試在可能的情況下切換它。（SQL（Postgresql 服務器）
我的目標是有一個陳述句為特定時間間隔的兩個單獨列生成平均值（1 Hour, 1 Day, 1 Week, Overall），并且每個時期的所有事件都應該計算在內。
我可以為每個間隔創建 4 個單個陳述句，但很難將這四個選擇組合到結果集中。
select
count(id) as hour_count,
camera_name,
round(avg("pconf")) as hour_p_conf,
round(avg("dconf")) as hour_d_conf
from camera_events where timestamp between NOW() - interval '1 HOUR' and NOW() group by camera_name;
select
count(id) as day_count,
camera_name,
round(avg("pconf")) as day_p_conf,
round(avg("dconf")) as day_d_conf
from camera_events where timestamp between NOW() - interval '1 DAY' and NOW() group by camera_name;
select
count(id) as week_count,
camera_name,
round(avg("pconf")) as week_p_conf,
round(avg("dconf")) as week_d_conf
from camera_events where timestamp between NOW() - interval '1 WEEK' and NOW() group by camera_name;
select
count(id) as overall_count,
camera_name,
round(avg("pconf")) as overall_p_conf,
round(avg("dconf")) as overall_d_conf
from camera_events group by camera_name;
如果可能，結果應該看起來像影像上的資料
一些提示會很棒，謝謝你
uj5u.com熱心網友回復：
通過將WHERE邏輯移動CASE到SELECT. 或者，在 PostgreSQL 中使用FILTER子句。
select
camera_name,
count(id) filter(timestamp between NOW() - interval '1 HOUR' and NOW()) as hour_count,
round(avg("pconf") filter(timestamp between NOW() - interval '1 HOUR' and NOW())) as hour_p_conf,
round(avg("dconf") filter(timestamp between NOW() - interval '1 HOUR' and NOW())) as hour_d_conf,
count(id) filter(timestamp between NOW() - interval '1 DAY' and NOW()) as day_count,
round(avg("pconf") filter(timestamp between NOW() - interval '1 DAY' and NOW())) as day_p_conf,
round(avg("dconf") filter(timestamp between NOW() - interval '1 DAY' and NOW())) as day_d_conf,
count(id) filter(timestamp between NOW() - interval '1 WEEK' and NOW()) as week_count,
round(avg("pconf") filter(timestamp between NOW() - interval '1 WEEK' and NOW())) as week_p_conf,
round(avg("dconf") filter(timestamp between NOW() - interval '1 WEEK' and NOW())) as week_d_conf,
count(id) as overall_count,
round(avg("pconf")) as overall_p_conf,
round(avg("dconf")) as overall_d_conf
from camera_events
group by camera_name;
uj5u.com熱心網友回復：
最簡單的方法是加入他們。例如：
select
coalesce(h.camera_name, d.camera_name, w.camera_name) as camera_name
h.hour_count, h.hour_p_conf, h.hour_d_conf
d.day_count, d.day_p_conf, d.day_d_conf
w.week_count, w.week_p_conf, w.week_d_conf
from (
-- hourly query here
) h
full join (
-- daily query here
) d on d.camera_name = h.camera_name
full join (
-- weekly query here
) w on w.camera_name = coalesce(h.camera_name, d.camera_name)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/486196.html
標籤：sql PostgreSQL
下一篇：SQL一對多，最佳實踐？