view.sql

-- temperature and humidity
CREATE VIEW temperature_humidity_summary_minute WITH (timescaledb.continuous) 
AS
    SELECT device_id,
        time_bucket(INTERVAL '1 minute', time) AS bucket,
        AVG(temperature) AS avg_temp,
        AVG(humidity) AS avg_humidity
    FROM sensor_data
    GROUP BY device_id,
        bucket;

-- air quality (lpg, co, smoke)
CREATE VIEW air_quality_summary_minute WITH (timescaledb.continuous)
AS
    SELECT device_id,
        time_bucket(INTERVAL '1 minute', time) AS bucket,
        AVG(lpg) AS avg_lpg,
        AVG(co) AS avg_co,
        AVG(smoke) AS avg_smoke
    FROM sensor_data
    GROUP BY device_id,
        bucket;

-- light
CREATE VIEW light_summary_minute WITH (timescaledb.continuous)
AS
    SELECT device_id,
        time_bucket(INTERVAL '1 minute', time) AS bucket,
        AVG(
            case
                when light = 't' then 1
                else 0
            end
        ) AS avg_light
    FROM sensor_data
    GROUP BY device_id,
        bucket;

-- motion
CREATE VIEW motion_summary_minute WITH (timescaledb.continuous)
AS
    SELECT device_id,
        time_bucket(INTERVAL '1 minute', time) AS bucket,
        AVG(
            case
                when motion = 't' then 1
                else 0
            end
        ) AS avg_motion
    FROM sensor_data
    GROUP BY device_id,
        bucket;