advent S14 전력 소비량 이동 평균 구하기

문제

(생략)

코드

WITH cleaning AS (
  SELECT
  datetime(measured_at) as "measured_at"
  , zone_boussafou
  , zone_quads
  , zone_smir
  , row_number() over () as "rn"
  FROM
    power_consumptions
  WHERE
    measured_at <= '2017-02-01 00:00:00'
)
SELECT
  COALESCE(LEAD(measured_at, 1) over (), '2017-02-01 00:00:00') as "end_at"
  , round(avg(zone_quads) over (rows between 5 PRECEDING and current row), 2) as "zone_quads"
  , round(avg(zone_smir) over (rows between 5 PRECEDING and current row), 2) as "zone_smir"
  , round(avg(zone_boussafou) over (rows between 5 PRECEDING and current row), 2) as "zone_boussafou"
FROM
  cleaning
;

알게된 점

SQL에서 이동 평균 구하기

-SQL에서 이동평균 구현하는 법
-[SQLP] 분석함수,윈도우함수 RANGE / UNBOUNDED PRECEDING / CURRENT ROW / UNBOUNDED FOLLOWING
이 문제의 핵심은 각각의 row에 대해서, 해당 row를 포함해 6개의 row의 평균을 지속적으로 구하는 것이다

처음에는 row_number와 LEAD/LAG 함수를 통해 구하려했으나, 윈도우 함수의 preceding을 통하면 훨씬 쉽게 이동평균을 구현할 수 있다
avg(column) over (order by column ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
위처럼 작성했을 시,
2 PRECEDING : 현재 행에서 2번 위의 행을 지정
CURRENT ROW : 현재 행을 지정
이 둘을 BETWEEN으로 묶었기에 총 3개의 행을 지정하게 된다

추가로 공부할 점

마지막 행이 null

코드 작성 중간에 있었던 문제인데, COALESCE를 사용하지 않았을 때는 마지막 행의 end_at이 null로 나왔다
그래서 일단은 문제를 풀기 위해 하드코딩을 해서 날짜를 지정해줬다

다만 내가 생각하기에는 윈도우 함수로 이동 평균을 구한 코드이기에, 마지막 row도 end_at이 나와야 할 테인데 어째서인지 계속 null이 나왔다
나름 조건도 일부러 2월 1일까지 출력시켰는데도.

이 부분에 대해서는 이번주 중에 시간이 된다면 추가로 학습을 해보고 하드코딩을 피하는 방향으로 작성할 수 있는지 찾아볼 필요가 있겠다