Combine multiple selects for statistics generation into on result set

Hey Pros,

I am far away to have good knowledge about SQL, and would ask you to give me some hints.

Currently we aggregate our data with python and I would try to switch this when possible to. (SQL (Postgresql server)

My goal is to have one statment that generate an average for two seperates column’s for specific time intervals (1 Hour, 1 Day, 1 Week, Overall) also all events in each period shoud be counted.

I can create 4 single statments for each interval but strugle how to combine this four selects into on result set.

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;

When possbile the result should look like the data on image

Some hints would be great, thank u

Answer

Consider conditional aggregation by moving WHERE logic to CASE statements in SELECT. Alternatively, in PostgreSQL use FILTER clauses.

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;