[SQL] solvesql | 전력 소비량 이동 평균 구하기 | rows 와 range의 차이

문제

https://solvesql.com/problems/moving-average-of-power-consumption/

https://solvesql.com/problems/moving-average-of-power-consumption/

 

코드

SELECT
  datetime(measured_at, '+10 minutes') end_at, -- SQLite에서 시간 더하는 함수
  round(avg(zone_quads) OVER (ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 2) zone_quads,
  round(avg(zone_smir) OVER (ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 2) zone_smir,
  round(avg(zone_boussafou) OVER (ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 2) zone_boussafou
FROM power_consumptions
WHERE measured_at BETWEEN '2017-01-01 00:00:00' AND '2017-02-01 00:00:00'

 

풀이

1. window 함수 범위 지정해서 이동평균 구하기

measured_at은 10분 단위이다.  따라서, 1시간 이동평균을 구하기 위해 이전 5개의 행부터 현재 행까지의 평균을 구하면 된다.

end_at (이동 평균 범위의 끝 시각)의 첫 행이 '2017-01-01 00:10:00' 이기 때문에 현재 행에서 following (다음 행)의 평균이 아닌 preceding (이전 행)을 사용해야 함을 알 수 있다. 이 부분이 꽤나 헷갈렸다.

 

또 range between을 사용해봤는데 결과값이 달랐다. 사실 rows 와 range의 차이를 이해하지 못하고 있었는데

이제 비로소 이해했다. 아래에 설명을 추가하겠다

 

2. measured_at 날짜 범위 지정하기

end_at(measured_at + 10분) 의 마지막 행이 '2017-02-01 00:00:00'이기 때문에

where절에서 measured_at의 날짜 범위의 끝을 '2017-01-31 23:50:00'으로 설정해야 하는 게 아닌가? 생각했는데 아니였다

이상하게 '2017-02-01 00:00:00'으로 설정해야지 '2017-01-31 23:50:00'까지 결과가 나온다...

between은 양 끝 값을 포함하는 조건으로 알고 있는데 왜 위에처럼 해야하는지 아직 의문이다 ...

 

3. measured_at에 시간 더하기

시간을 더하는 함수로 dateadd을 사용해서 dateadd(minute, 10, measured_at)을 적었지만 결과는 오류

SQLite는 datetime(measured_at, '+10 minutes') 이런 식으로 시간을 더해준다.

 

💡 Rows 와 Range의 차이

Rows
- 행 (Row) 단위로 계산
- 현재 행을 기준으로 이전 5행부터 현재행까지 포함하여 평균을 계산

Range
- 값 (Value) 범위 기반으로 계산
- ORDER BY measured_at 기준에서 현재 행의 measured_at 값에서 5초 이전까지의 값들을 포함하여 평균을 계산
- measured_at에 동일한 값을 가진 행이 있으면 한 행으로 취급

Range을 사용해서 이 문제를 풀려면
      RANGE BETWEEN INTERVAL '50 minutes' PRECEDING AND CURRENT ROW
이렇게 measured_at 값의 단위(여기서는 시간 단위)를 기반으로 범위를 계산해야 한다.

 

 

느낀 점

window 함수 범위 지정하는 문법에 더 익숙해져야겠다

 

참고

https://gent.tistory.com/473

[Oracle] ROWS와 RANGE 차이 및 사용법 (WINDOWING 절)

 

이 블로그가 rows와 range의 차이를 직관적으로 잘 이해되게 설명해놨다 ! 참고 바람 !