[TIL159] solvesql - 전력소비량 이동평균 구하기

정답코드 

-- 소비량 측정 10분간 측정 / 단순 이동평균
-- 2017-01-01 00:00 ~ 2017-02-01 00:00 까지 10분단위 3개 발전소 전력 소비량
-- 1시간 범위 이동평균 
-- ROUND(,2)
with a as (SELECT measured_at,
date_add(measured_at, interval 10 minute) 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
)
SELECT end_at, 
zone_quads,
zone_smir,
zone_boussafou
from a 
where measured_at >= '2017-01-01' AND end_at <='2017-02-01'

1. 이동평균(Moving Average)
2. 누적합(Cumulative Sum)
3. 이전값 비교(LAG)
4. ROWS vs RANGE

 

 

---

이동평균 핵심 공식

최근 N행 평균

AVG(값) OVER(
    ORDER BY 날짜
    ROWS BETWEEN N-1 PRECEDING
    AND CURRENT ROW
)

---

예)

최근 3개 주문 평균

AVG(amount) OVER(
    ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING
    AND CURRENT ROW
)

---

그림

주문금액

100

200

300

400

500

---

현재 400 위치

200
300
400

평균

(200+300+400)/3
=300

---

이동평균 문제 1

주문 5건 이동평균

문제

주문별 금액과 최근 5건 주문의 평균금액을 구하시오.

테이블

Orders
(order_id, order_date, amount)

---

정답

SELECT
    order_id,
    order_date,
    amount,

    ROUND(
        AVG(amount) OVER(
            ORDER BY order_date
            ROWS BETWEEN 4 PRECEDING
            AND CURRENT ROW
        ),
        2
    ) AS ma_5

FROM orders;

---

이동평균 문제 2

고객별 최근 3건 구매 평균

문제

고객별 최근 3건 주문 평균금액

---

정답

SELECT
    customer_id,
    order_date,
    amount,

    AVG(amount) OVER(
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING
        AND CURRENT ROW
    ) AS ma_3

FROM orders;

---

여기서 중요

PARTITION BY customer_id

없으면

모든 고객이 섞임

---

이동평균 문제 3

최근 7일 평균 매출

문제

매출 테이블에서

현재 날짜 포함 최근 7일 평균 매출

---

정답

SELECT
    sales_date,
    sales,

    AVG(sales) OVER(
        ORDER BY sales_date
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING
        AND CURRENT ROW
    ) AS ma_7d

FROM sales;

---

여기서 중요한 것

ROWS

ROWS BETWEEN 6 PRECEDING

의미

직전 6행

---

RANGE

RANGE BETWEEN INTERVAL 6 DAY PRECEDING

의미

직전 6일

---

기업 면접 단골

최근 7개 주문

→ ROWS

최근 7일 매출

→ RANGE

---

이동평균 문제 4

발전소 문제 변형

문제

각 발전소별 최근 2시간 이동평균

측정주기

10분

---

2시간

120분

---

필요 행 수

120 / 10

=12행

---

현재 포함

11 PRECEDING

---

정답

---

 

이동평균

AVG() OVER(...)

---

이동합계

SUM() OVER(...)

---

예

최근 7일 매출

SUM(amount) OVER(
    ORDER BY order_date
    RANGE BETWEEN INTERVAL 6 DAY PRECEDING
    AND CURRENT ROW
)

---

암기 카드

최근 N행

ROWS BETWEEN N-1 PRECEDING
AND CURRENT ROW

---

최근 N일

RANGE BETWEEN INTERVAL N-1 DAY PRECEDING
AND CURRENT ROW

---

이동평균

AVG() OVER(...)

---

이동합계

SUM() OVER(...)

---

누적합

SUM() OVER(
    ORDER BY 날짜
)

---

전월 비교

LAG() OVER(
    ORDER BY 날짜
)