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

문제

solvesql.com

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

SQLite

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'