Advented of SQL 2024 : 전력 소비량 이동 평균 구하기 (DAY 14)

문제 탐구

2017년 1월 1일 0시부터 2017년 2월 1일 0시까지 10분 단위로 3개 발전소 전력 소비량의 1시간 범위 단순 이동 평균을 계산하는 쿼리를 작성해주세요. 평균을 내기 위한 데이터의 범위가 1시간보다 작은 경우 해당 범위에 포함되는 측정 값만 평균 계산에 포함시켜주세요. 쿼리 결과에는 아래 컬럼이 포함되어 있어야 하고, 이동 평균 값은 소수점 셋째 자리에서 반올림 해 둘째 자리까지 표시되어야 합니다.

• 2017년 1월 1일 0시부터 2017년 2월 1일 0시까지 10분 단위로 3개 발전소 전력 소비량의 1시간 범위 단순 이동 평균을 계산하는 쿼리를 작성

• 평균을 내기 위한 데이터의 범위가 1시간보다 작은 경우 해당 범위에 포함되는 측정 값만 평균 계산에 포함

• 전력 소비량 측정은 매 10분마다 시작해 10분간 진행하는 방식

• measured_at 컬럼에 측정 시작 시각이 기록

컬럼 설명

• end_at: 이동 평균 범위의 끝 시각
• zone_quads: Quads 지역 전력 소비량의 1시간 단순 이동 평균
• zone_smir: Smir 지역 전력 소비량의 1시간 단순 이동 평균
• zone_boussafou: Boussafou 지역 전력 소비량의 1시간 단순 이동 평균

case 1 오답 코드

WITH time_ranges AS (
    SELECT 
        measured_at,
        -- 1시간 전 값 출력
        DATETIME(measured_at, '-1 hour') AS start_time
    FROM power_consumptions
)
SELECT
    -- concat(strftime('%Y-%m-%d',t1.measured_at),'T',strftime('%H:%M:%S',t1.measured_at)) as end_at,
    t1.measured_at as end_at,
    (SELECT round(AVG(p.zone_quads),2) 
     FROM power_consumptions p
     WHERE p.measured_at BETWEEN t1.start_time AND t1.measured_at
    ) as zone_quads,
    (SELECT round(AVG(p.zone_smir),2) 
     FROM power_consumptions p
     WHERE p.measured_at BETWEEN t1.start_time AND t1.measured_at
    ) as zone_smir,
        (SELECT round(AVG(p.zone_boussafou),2) 
     FROM power_consumptions p
     WHERE p.measured_at BETWEEN t1.start_time AND t1.measured_at
    ) as zone_boussafou
FROM time_ranges t1
where strftime('%Y-%m-%d %H:%M:%S',measured_at) >= '2017-01-01 00:10:00' and strftime('%Y-%m-%d %H:%M:%S',measured_at) <= '2017-02-01 00:00:00';

case 2 정답 코드

✔1. 모든 datetime 이 기존 measured_at 의 10분 뒤
-> 10분 뒤의 값을 end_at로 출력
✔2. 1시간 데이터를 구하기
-> window 함수 preceding 을 활용
-> 5개 이전의 행부터 현재행까지의 평균을 구함
✔3. measured_at기준 1월 31일 데이터 출력
-> strftime을 이용해 where 구문을 길게 작성
-> 2017년 1월 데이터로 한정해서 간단하게 구문 작성 가능

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 strftime('%Y-%m-%d %H:%M:%S',measured_at) >= '2017-01-01 00:00:00' and strftime('%Y-%m-%d %H:%M:%S',measured_at) <= '2017-01-31 23:50:00'

주의사항

새롭게 알게 된 함수 & 계속 헷갈리는 함수

1. interval datetime

• 현재시간에 1년 더하기
  select date_add(now(), interval 1 year)

• 현재시간에 1시간 빼기
  select date_add(now(), interval -1 hour)

SQlite은 date_add 함수가 지원되지 않음
select datetime(now(), '-1 hour')

2.윈도우함수(rows between a nad b)

-> sum(),avg(), row_number()이 있음

rows between 범위 지정키워드

• unbounded preceding (맨 처음부터)
• n preceding (n개 앞부터)
• current row (현재행)
• n following (n개 뒤까지)
• unbounded following (맨끝까지)

예시
sum(sales) over (order by date rows between 2 preceding and current row)
: 2행 전부터 ~ 현재 행까지의 합계를 구하기

avg(sales) over (order by date rows between current row and unbounded following)
: 현재행부터 맨마지막 행 사이의 평균 구하기