SQL | SOVESQL 2024 🎄: Day 14. 전력 소비량 이동 평균 구하기

 

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

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

WITH T1 AS (
  SELECT measured_at AS end_at
    , ROUND(AVG(zone_quads) OVER(ORDER BY measured_at ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING),2) AS zone_quads
    , ROUND(AVG(zone_smir) OVER(ORDER BY measured_at ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING),2) AS zone_smir
    , ROUND(AVG(zone_boussafou) OVER(ORDER BY measured_at ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING),2) AS zone_boussafou
  FROM power_consumptions AS pc
  WHERE measured_at BETWEEN '2017-01-01 00:00:00' AND '2017-02-01 00:00:00'
)

SELECT *
FROM T1
WHERE zone_quads IS NOT NULL

❗PRECEDING 을 사용해서 가장 레코드가 비어있음. 
-> WHERE 절 사용해서 NULL이 아닌 것만 추출

 

 

 

+) WITH 절 : T1

❗AVG() OVER() 윈도우 함수 사용해서 이동 평균 구하기

• PRECEDING 사용해서 범위 지정
• PRECEDING, FOLLOWING 사용하려 했는데 FOLLOWING이 오류나서 PRECEDING만 사용
• 그래서 앞에 6일, 앞에 1일로 함

❗WHERE 절에 BETWEEN 사용해서 시기 지정