[Advent of SQL 2024 🎄] 전력 소비량 이동 평균 구하기 (Day 14)

난이도4

 

 

Power Consumption of Tetouan City 데이터베이스는 모로코 북부 도시 중 하나인 테투안 지역의 전력 소비량과 기상 관측치가 들어있는 데이터베이스 입니다. 테투안 지역에는 Quads, Smir, Boussafou 총 3개 지역에 발전소가 있으며 해당 발전소로 부터의 전력 소비량이 각각 측정되어 있습니다. 전력 소비량 측정은 매 10분마다 시작해 10분간 진행하는 방식이고, measured_at 컬럼에 측정 시작 시각이 기록되어 있습니다. 전력 소비량은 순간순간 크게 변화하는 값이기에 트렌드를 보거나 수요 예측을 하기 위해서 단순 이동 평균을 자주 사용 합니다.

2017년 1월 1일 0시부터 2017년 2월 1일 0시까지 10분 단위로 3개 발전소 전력 소비량의 1시간 범위 단순 이동 평균을 계산하는 쿼리를 작성해주세요. 평균을 내기 위한 데이터의 범위가 1시간보다 작은 경우 해당 범위에 포함되는 측정 값만 평균 계산에 포함시켜주세요. 쿼리 결과에는 아래 컬럼이 포함되어 있어야 하고, 이동 평균 값은 소수점 셋째 자리에서 반올림 해 둘째 자리까지 표시되어야 합니다.

• end_at: 이동 평균 범위의 끝 시각
• zone_quads: Quads 지역 전력 소비량의 1시간 단순 이동 평균
• zone_smir: Smir 지역 전력 소비량의 1시간 단순 이동 평균
• zone_boussafou: Boussafou 지역 전력 소비량의 1시간 단순 이동 평균

 

WITH averages AS (
    SELECT 
        strftime('%Y-%m-%d %H:%M:%S', measured_at, '+10 minutes') AS end_at,
        ROUND(AVG(zone_quads) OVER (ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 2) AS zone_quads,
        ROUND(AVG(zone_smir) OVER (ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 2) AS zone_smir,
        ROUND(AVG(zone_boussafou) OVER (ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 2) AS zone_boussafou
    FROM 
        power_consumptions
    WHERE 
        measured_at BETWEEN '2017-01-01 00:00:00' AND '2017-02-01 00:00:00'
)
SELECT 
    end_at,
    zone_quads,
    zone_smir,
    zone_boussafou
FROM 
    averages
ORDER BY 
    end_at;