이동 평균, 누적 합계

이동 평균, 누적 합계

MySQL에서 이동 평균과 누적 합계를 구하는 쿼리를 알아보자

 

1. 이동 평균

• 각 행의 값과 이전 n개, 이후 n개의 행 값을 포함하여 (2*n + 1)일 이동 평균을 계산
• ROWS BETWEEN n PRECEDING AND n FOLLOWING

# 테이블(my_table)
+-------------+--------------+
| date_column | value_column |
+-------------+--------------+
| 2024-01-01  | 10           |
| 2024-01-02  | 15           |
| 2024-01-03  | 20           |
| 2024-01-04  | 25           |
| 2024-01-05  | 30           |
+-------------+--------------+

# [3일 이동 평균]
SELECT date_column,
       value_column,
       AVG(value_column) OVER (ORDER BY date_column 
       			       ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWNG) AS moving_avg_3_days
FROM my_table;

# 결과
+-------------+--------------+-------------------+
| date_column | value_column | moving_avg_3_days |
|-------------|--------------|-------------------|
| 2024-01-01  | 10           | 12.5              |
| 2024-01-02  | 15           | 15                |
| 2024-01-03  | 20           | 20                |
| 2024-01-04  | 25           | 25                |
| 2024-01-05  | 30           | 27.5              |
+-------------+--------------+-------------------+

 

2. 이동 평균

• 각 행의 값과 이전 n일, 이후 n일의 행 값을 포함하여  (2*n + 1)일 이동 평균을 계산
• 데이터에 공백이 있는 경우 사용
• RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND INTERVAL 2 DAY FOLLOWING
  현재 행까지를 계산할 경우 (NTERVAL 2 DAY FOLLOWING → CURRENT ROW 변경)

# 테이블(my_table)
+-------------+--------------+
| date_column | value_column |
+-------------+--------------+
| 2024-01-01  | 10           | 
| 2024-01-02  | 15           |
| 2024-01-03  | NULL         | 
| 2024-01-04  | 25           | 
| 2024-01-05  | 30           | 
| 2024-01-06  | 35           | 
| 2024-01-07  | 40           |
| 2024-01-08  | 45           |
| 2024-01-09  | NULL         |
| 2024-01-10  | 55           |
+-------------+--------------+

# [3일 이동 평균]
-- 5일 이동 평균 계산 쿼리
SELECT date_column,
       value_column,
       AVG(value_column)
       	OVER (ORDER BY date_column
              RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND INTERVAL 2 DAY FOLLOWING) AS moving_avg_5_days
FROM my_table;

# 결과
+-------------+--------------+-------------------+
| date_column | value_column | moving_avg_5_days |
+-------------+--------------+-------------------+
| 2024-01-01  | 10           | 12.5              |
| 2024-01-02  | 15           | 16.6667           |
| 2024-01-03  | NULL         | 20                |
| 2024-01-04  | 25           | 26.25             |
| 2024-01-05  | 30           | 32.5              |
| 2024-01-06  | 35           | 35                |
| 2024-01-07  | 40           | 37.5              |
| 2024-01-08  | 45           | 43.75             |
| 2024-01-09  | NULL         | 46.6667           |
| 2024-01-10  | 55           | 50                |
+-------------+--------------+-------------------+

 

3. 누적 합계

• 현재 행부터 이전 모든 행까지의 값을 포함하여 누적 합계를 계산
• ROWS UNBOUNDED PRECEDING

# 테이블(my_table)
+-------------+--------------+
| date_column | value_column |
+-------------+--------------+
| 2024-01-01  | 10           |
| 2024-01-02  | 15           |
| 2024-01-03  | 20           |
| 2024-01-04  | 25           |
| 2024-01-05  | 30           |
+-------------+--------------+

SELECT date_column,
       value_column,
       SUM(value_column) OVER (ORDER BY date_column
       			       ROWS UNBOUNDED PRECEDING) AS cumulative_sum
FROM my_table;

# 결과
+-------------+--------------+----------------+
| date_column | value_column | cumulative_sum |
|-------------|--------------|----------------|
| 2024-01-01  | 10           | 10             |
| 2024-01-02  | 15           | 25             |
| 2024-01-03  | 20           | 45             |
| 2024-01-04  | 25           | 70             |
| 2024-01-05  | 30           | 100            |
+-------------+--------------+----------------+