[solvesql - day 14] lv4 전력 소비량 이동 평균 구하기

👉 문제 바로가기 : solvesql - Advent of SQL 2024 day 14
저작권 문제로 링크만 첨부한다.

✔️문제 풀이에 필요한 점

답안의 구성 자체는 window 함수의 행 제한 문법(ROWS BETWEEN)만 알면 간단하다.
day 13의 문제가 구성은 더 복잡했던듯

다만 지문의 설명이 A부터 Z까지 다 풀어주는 느낌은 아니라서
예시로 보여주는 출력문을 보고 정확히 어떤 조건인지 파악해야한다.

✍️답안

WITH withnull as (
  SELECT
    measured_at "end_at"
    , round(
      sum(zone_quads) OVER(ORDER BY measured_at ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING)
      /sum(1) OVER(ORDER BY measured_at ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING)
      , 2
    ) "zone_quads"
    , round(
      sum(zone_smir) OVER(ORDER BY measured_at ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING)
      /sum(1) OVER(ORDER BY measured_at ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING)
      , 2
    ) "zone_smir"
    , round(
      sum(zone_boussafou) OVER(ORDER BY measured_at ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING)
      /sum(1) OVER(ORDER BY measured_at ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING)
      , 2
    ) "zone_boussafou"
  FROM
    power_consumptions
  WHERE
    julianday(measured_at) <= julianday('2017-02-01T00:00:00')
)
SELECT *
FROM withnull
WHERE zone_quads IS NOT NULL
;

답안 설명

• 임시테이블에서 99% 해결하고 본문에선 null만 제거한다.

• 윈도우 함수는 6번째 전 행부터 1번째 전 행까지만 더하고 그 갯수만큼 나눈다.(평균내기)

• ROWS BETWEEN 6 preceding AND 1 preceding
  → 6행 전 부터 1행 전 까지

  • 예를 들어 현재 행의 인덱스가 10이라면, 4부터 9까지만 더하고 평균내는것.

• preceding은 '이전'을 뜻하는 구문이다. preceding 대신 following을 쓴다면 '이후'를 지정한다

  ex) ROWS BETWEEN 6 PRECEDING AND 1 FOLLOWING → 6행 전부터 1행 후까지
  (현재 행이 10일 경우 4부터 11까지 윈도우 함수 대상으로 지정)

지금보니 sum대신 avg함수를 쓰면 되긴 하다💦