구름 아카이브

저작권 문제로 인해, 직접 작성한 쿼리문만 공개.

14. 전력 소비량 이동 평균 구하기

https://solvesql.com/problems/moving-average-of-power-consumption/

SELECT 
    DATE_ADD(measured_at, INTERVAL 10 MINUTE) 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_boussafou) OVER (ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),2) AS zone_boussafou,
    ROUND(AVG(zone_smir) OVER (ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),2) AS zone_smir
FROM power_consumptions
WHERE measured_at BETWEEN '2017-01-01' AND '2017-01-31 23:50:00'
ORDER BY measured_at ASC