[TIL] 윈도우 함수에서 ROWS로 특정 범위내 데이터 지정하기 #SQL #SQLite #solvesql

solvesql - 전력 소비량 이동 평균 구하기
👉 문제 바로가기(solvesql)

-- 2017년 1월 1일 0시부터 2017년 2월 1일 0시까지
-- 10분 단위로 3개 발전소 전력 소비량의 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', measured_at) BETWEEN '2017-01-01 00' AND '2017-01-31 23'

[논리 과정]

• 이동 평균을 구하기 위해 자신을 기준으로 자신 제외 5개 이전 데이터까지 그룹핑(중앙 이동 평균이 아닌 기본 이동 평균)
• 그룹별 평균 산출하여 이동 평균 산출
• 2017-01-01 0시 ~ 2017-02-01 0시 날짜 필터링

[문제 상황]

1. 처음에는 이동 평균 개념이 단순히 원하는 범위로 묶어서 평균을 산출하는 것이라고 이해해서 아래와 같이 년-월-일 시로 시간 타입을 변경하여 그룹핑 후 평균 산출했음

-- 오답 코드 1
SELECT
  MAX(measured_at) as end_at,
  ROUND(AVG(zone_quads),2) AS zone_quads,
  ROUND(AVG(zone_smir),2) AS zone_smir,
  ROUND(AVG(zone_boussafou),2) AS zone_boussafou
FROM
  power_consumptions
WHERE
  strftime('%Y-%m-%d %H', measured_at) BETWEEN '2017-01-01 00' AND '2017-02-01 00'
GROUP BY
  strftime('%Y-%m-%d %H', measured_at)

2. 이후 이동 평균 개념을 확실하게 이해하고 산출했으나 end_at 컬럼, 이동 평균 그룹의 마지막 값을 산출하는 것에서 정답과 달랐음

-- 오답 코드 2
SELECT 
  MAX(measured_at) OVER(ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) 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', measured_at) BETWEEN '2017-01-01 00' AND '2017-01-31 23'

[원인과 해결]

• 문제1의 원인 : 이동 평균은 자기 자신을 기준으로 원하는 범위만큼의 이전 데이터와의 그룹 평균을 의미한다.
• 해결 : 윈도우 절의 ROWS 문법을 사용하여 원하는 범위만큼 데이터를 지정해서 평균 산출
• 문제2의 원인 : end_at 컬럼값으로 해당 데이터의 마지막 측정 시기(measure_at)를 사용했는데 실제로는 이 값을 기준으로 10분뒤까지 측정한 데이터이므로 마지막 측정시기에서 10분을 더해줘야 평균을 구한 그룹의 끝나는 시간대가 된다.
• 해결 : measure_at에 10분을 더한다.

[회고]

• 윈도우 함수의 활용법인 ROWS에 대해 배웠다.
• 데이터의 트렌드를 분석하는데 용이한 이동평균의 개념에 대해서 자세히 알게 되었다.

[핵심 개념]

• 기본 구조

ROWS BETWEEN 시작 AND 끝

• 시작 와 끝 에 사용될 수 있는 키워드
  • CURRENT ROW: 현재 레코드
  • n PRECEDING: n개 이전 레코드에서 현재 레코드까지(이전 n개)
  • n FOLLOWING: 현재 레코드에서 n개 이후 레코드까지(이후 n개)
  • UNBOUNDED PRECEDING: 처음부터 현재 레코드까지(처음~현재)
  • UNBOUNDED FOLLOWING: 현재 레코드부터 마지막까지(현재~끝)
  • PRECEDING은 '시작'에, FOLLWING은 '끝'에 들어갈 수 있음