[SQL] 전력 소비량 이동평균 계산

1. 문제 요약

모로코 테투안 도시의 3개 지역(Quads, Smir, Boussafou)에 대한 전력 소비량 데이터가 10분 단위로 주어질 때, 1시간 범위의 단순 이동 평균(SMA)을 구해야 한다.

• 기간: 2017년 1월 1일 0시 ~ 2017년 2월 1일 0시 (미만)
• 조건: 이동 평균은 소수점 셋째 자리에서 반올림하여 둘째 자리까지 표시. 데이터 범위가 1시간보다 작은 경우(시작 지점 등)는 존재하는 값만으로 평균을 낸다.
• 출력: end_at (측정 종료 시각), 각 지역별 이동 평균 컬럼.

2. 문제 해결 아이디어

이 문제의 핵심은 '최근 1시간 데이터의 평균'을 어떻게 구하느냐이다. 데이터가 10분 간격으로 쌓이고 있으므로, 1시간은 총 6개의 행(Row)을 의미한다. 즉, 현재 행을 포함하여 직전 5개 행까지의 평균을 구하면 된다. 이를 위해 윈도우 함수의 ROWS 옵션을 사용한다.

3. 정답 쿼리

SQL

 

SELECT
    -- 측정 시작 시각에서 10분을 더해 종료 시각 생성
    DATE_ADD(measured_at, INTERVAL 10 MINUTE) AS end_at,
    -- Quads 지역 1시간(6개 행) 이동 평균
    ROUND(AVG(zone_quads) OVER (ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 2) AS zone_quads,
    -- Smir 지역 1시간(6개 행) 이동 평균
    ROUND(AVG(zone_smir) OVER (ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 2) AS zone_smir,
    -- Boussafou 지역 1시간(6개 행) 이동 평균
    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 >= '2017-01-01 00:00:00'
    AND measured_at < '2017-02-01 00:00:00'
ORDER BY
    end_at;

4. 상세 풀이

1) DATE_ADD를 이용한 end_at 구하기 문제에서 measured_at은 측정 시작 시각이고, 측정은 10분간 진행된다고 했다. 결과로 요구하는 end_at은 측정 종료 시각이므로 DATE_ADD(measured_at, INTERVAL 10 MINUTE)를 사용하여 10분을 더해준다.

2) 윈도우 함수 프레임 설정 (핵심) 이동 평균을 구하기 위해 AVG(...) OVER (...) 구문을 사용한다. 여기서 가장 중요한 것은 윈도우 프레임 설정이다.

• ORDER BY measured_at: 시간 순서대로 데이터를 정렬한다.
• ROWS BETWEEN 5 PRECEDING AND CURRENT ROW:
  • 데이터 간격이 10분이므로 1시간은 6개의 데이터 포인트가 필요하다.
  • CURRENT ROW(현재 행) + 5 PRECEDING(직전 5개 행) = 총 6개 행(60분)이 된다.
  • 문제 조건 중 "데이터 범위가 1시간보다 작은 경우 해당 값만 포함"하라는 조건은 윈도우 함수가 자동으로 처리해 준다. 예를 들어 첫 번째 행은 직전 데이터가 없으므로 자기 자신만으로 평균을 내고, 두 번째 행은 직전 1개와 자신을 합쳐 평균을 낸다.

3) ROUND 함수로 소수점 처리 AVG로 계산된 결과는 소수점이 길게 나올 수 있다. 문제 조건에 맞춰 ROUND(..., 2)를 감싸주어 소수점 셋째 자리에서 반올림해 둘째 자리까지 표기한다.

4) WHERE 절의 기간 필터링 2017년 1월 1일 0시부터 2월 1일 0시까지의 데이터를 조회한다. measured_at 기준으로 범위를 설정하면 된다. 2월 1일 0시는 포함하지 않아야 하므로 < 연산자를 사용했다.

이 문제는 윈도우 함수의 ROWS BETWEEN 옵션을 알고 있다면 간단하게 해결할 수 있지만, 이를 모른다면 서브쿼리나 조인을 사용해야 해서 난이도가 급격히 올라가는 문제이다. 시계열 데이터 분석에서 이동 평균은 자주 쓰이므로 이 구문은 외워두는 것이 좋다.