[SQL문제풀기] 전력 소비량 이동 평균 구하기

silver

Mar 22, 2025

Contents

문제 SQLite

문제

solvesql.com

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

SQLite

💡

이동평균

이동 평균은 일정한 시간 동안의 데이터 평균을 구한 후, 이 평균값을 점진적으로 이동시키면서 계산하는 방식으로 새로운 값이 추가되면 가장 오래된 값을 제외하고 다시 평균을 계산하는 것이다. → 이 문제에서는 1시간이라는 범위 내에서 10분 단위로 새로운 값을 추가되고 오래된 값은 제외하고 평균을 계산하면 된다.


AVG(컬럼) OVER (PARTITION BY 그룹컬럼 ORDER BY 정렬컬럼 ROWS 범위)

SELECT 
    measured_at,
    zone_quads,
    AVG(zone_quads) OVER (
        ORDER BY measured_at 
        ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ) zone_quads
FROM power_consumptions;

내가 작성한 오답

결과 데이터 예시에 T가 포함되어 넣었더니 오답으로 처리되었다.


select 
    strftime('%Y-%m-%dT%H:%M:%S', measured_at, '+10 minutes') end_at,
    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'

내가 작성한 정답


select  
    strftime('%Y-%m-%d %H:%M:%S', measured_at, '+10 minutes') end_at,
    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시간 동안 평균 개수가 부족한 경우 처리

초반에는 데이터가 부족하므로 ROWS BETWEEN 5 PRECEDING이 아니라 가능한 만큼만 포함되도록 해야 한다.

→ ROWS BETWEEN은 자동으로 이걸 처리하기 때문에

2017-01-01 00:10:00에서는 자기 자신 + 이전 값만 평균 계산하기 때문에 별도로 조정하지 않아도 알아서 정상적으로 동작한다.

AVG(컬럼) OVER (PARTITION BY 그룹컬럼 ORDER BY 정렬컬럼 ROWS 범위) SELECT measured_at, zone_quads, AVG(zone_quads) OVER ( ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW ) zone_quads FROM power_consumptions;

select strftime('%Y-%m-%dT%H:%M:%S', measured_at, '+10 minutes') end_at, 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'

select strftime('%Y-%m-%d %H:%M:%S', measured_at, '+10 minutes') end_at, 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'