[Day 14] solvesql | Advent of SQL 2024 | 전력 소비량 이동 평균 구하기💡

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

전력 소비량은 순간순간 크게 변화하는 값이기 때문에 트렌드를 보거나 수요 예측을 하기 위해 단순 이동 평균을 주로 사용한다고 함

 

문제 조건

1. 2017년 1월 1일 0시부터 2017년 2월 1일 0시까지 10분 단위로 1시간 범위의 단순 이동 평균을 계산

2. 이동 평균값은 소수점 셋째 자리에서 반올림하여 둘째 자리까지 표시

3. 쿼리 결과에 아래 컬럼이 포함

   

    end_at: 이동 평균 범위의 끝 시각

    zone_quads : Quads 지역 전력 소비량의 1시간 단순 이동 평균

    zone_smir : Smir 지역 전력 소비량의 1시간 단순 이동 평균

    zone_boussafou : Boussafou 지역 전력 소비량의 1시간 단순 이동 평균

 

Solution

 

✅MySQL 

/*
이동 평균! 나오면 -> 기본적으로 ROWS BETWEEN A AND B 구문이 딱 생각나게끔

원본 measured_at : 기록의 시작 시간 -> 이동평균 범위의 끝 시각
1월 1일 00:00:00의 경우 이동평균 범위 끝이 결국 00:00:00 아닌가? 앞에 5개 raw가 없는 건 평균을 내기 위한 범위가 1시간보다 작은 경우에 해당 되는거구...

쨌든 이 문제의 핵심은 1)'이동평균' 개념을 이해하는 것, 2)'이동평균' 개념을 SQL 쿼리로 구현하는 것
*/

SELECT DATE_ADD(measured_at, INTERVAL 10 MINUTE) AS end_at -- ADDTIME(measured_at, '00:10:00')
     , ROUND(AVG(zone_quads) OVER (ORDER BY measured_at ROWS 5 PRECEDING), 2) AS zone_quads
     , ROUND(AVG(zone_smir) OVER (ORDER BY measured_at ROWS 5 PRECEDING), 2) AS zone_smir
     , ROUND(AVG(zone_boussafou) OVER (ORDER BY measured_at ROWS 5 PRECEDING), 2) AS zone_boussafou
FROM power_consumptions
WHERE measured_at BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59' -- DATE_SUB('2017-02-01', INTERVAL 1 SECOND)

 

✔️POINT 1
이동평균 범위의 끝 시각을 출력해야 하기 때문에 DATE_ADD 함수를 활용하여 10분씩 더한다

 

✔️POINT2
 1시간 범위 단순 이동 평균을 구하기 위해, ROWS BETWEEN ~ AND ~구문을 응용한다.

 

📌Tip

PostgreSQL : measured_at + '10 minute'

 

✅SQLite

SELECT DATETIME(measured_at, '+10 minutes') 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_smir) OVER (ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 2) as zone_smir,
       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 BETWEEN '2017-01-01 00:00:00' AND '2017-02-01 00:00:00'

 

✔️POINT 1

datetime 타입의 'measure_at' 컬럼은 (YYYY-MM-DD HH:MM:SS)으로 저장된 문자열이므로 비교 연산이 가능하다.

 

✔️POINT 2
이동 평균 범위의 끝 시각이 출력되어야 하기 때문에 datetime 함수를 사용하여 시간 요소를 추가 감산한다.

 

✔️POINT 3

단순 이동 평균을 계산하기 위해 윈도우 함수와 ROWS BETWEEN 구문을 적절하게 활용한다. 

 

피드백

 

단순 이동 평균

시계열 데이터의 변동성을 줄이고 추세를 파악하기 위해 특정 기간 동안의 데이터를 평균 내는 방법이다.

주로 금융, 기상, 에너지 등 다양한 분야에서 사용된다.

 

▶ 계산 방식 : 특정 시점에서 이전 n 개의 값의 산술 평균을 계산

    모든 데이터가 동일한 가중치를 가진다.

    단순하고 계산이 쉽지만, 급격한 변화에 민감할 수 있다.

 

▶ 활용 사례 : 

 

    1) 금융
    주식 가격의 단기/장기 추세 분석

    예: 50일, 200일 이동 평균을 통해 골든크로스나 데드크로스 확인

 

    2) 기상

    특정 지역의 평균 기온 변화 파악

    예: 하루 단위로 7일 이동 평균 기온을 계산해 일주일 추세 분석

 

SQL 구현

SQL에서는 단순 이동 평균을 계산하기 위해 WINDOW 함수와 ROWS BETWEEN 구문을 사용한다.

<집계 함수> OVER (
    ORDER BY <정렬 기준 컬럼>
    ROWS BETWEEN <범위 시작> AND <범위 끝>
)

 

▶ 범위 지정 방식 : 

 

    1) ROWS BETWEEN n PRECEDING AND CURRENT ROW

    현재 행에서 이전 n 개의 데이터와 현재 행을 포함

    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW는 현재 행 포함 이전 6개의 데이터를 선택

 

    2) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

     = ROWS UNBOUNDED PRECEDING

    현재 행 포함, 가장 첫 번째 행부터 데이터를 선택

   

    3) ROWS BETWEEN CURRENT ROW AND n FOLLOWING

    현재 행 포함, 이후 n 개의 데이터를 선택 

    4) ORDER BY 'col'

     = ORDER BY 'col' ROWS UNBOUNDED PRECEDING

     = ORDER BY 'col' ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    현재 행까지의 범위만 고려

 

 

✔️ 0211 1015 복습