SQL | MySQL | Window Functions (2) - 집계 함수

 

 

 

 

 01. Window Functions - 윈도우 함수

 

 

• MySQL 8.0부터 Window Functions이 도입됨 
• OVER( ) 절을 사용해 지정된 윈도우 프레임에서 연산을 수행하는 함수

<window function> OVER (
    [PARTITION BY <partition expression>]
    [ORDER BY <order expression> [ASC|DESC], ... ]
    [<window frame clause>]
)

 

• <window function>은 윈도우 함수를 넣으면 됨.
• PARTITION BY은 GROUP BY와 같은 기능을 함

 

 👉 대표적인  윈도우 함수들

•  ROW_NUMBER( )  :  각 행에 일련번호 부여 
•  RANK( ) : 각 행의 순위를 할당(중복이 있을 경우 중복 값은 같은 순위 부여, 1,1,3등으로 넘어감)
•  DENSE_RANK(  ) : 각 행의 순위를 할당(중복가능, 1, 1, 2,3... 순차적으로 순위부여)
•  SUM( ), AVG( ), MIN( ), MAX( ), COUNT( ) : 윈도우 내에서 지정된 열의 합계, 평균, 최솟값, 최댓값, 개수 계산
•  LEAD( ), LAG( ) : 현재 행 이전 또는 이후의 행의 값을 가져옴

 

 

---

 

 

02. 윈도우 함수 - 집계 함수

 

 

✅ 예제 테이블은 이전 포스팅 참고

https://boring9.tistory.com/49

SQL | MySQL | Window Functions (1) - 데이터 순위

 

 

✅ SUM 함수를 이용해 파티션별 윈도우의 합을 구할 수 있음.

 

✔️ 같은 지역에 있는 sale_amount의 합

SELECT 
    sale_region, sale_date, sale_amount,
    SUM(sale_amount) OVER(PARTITION BY sale_region) AS sum_sales
FROM  sales;

 

 

 

✔️ 같은 지역에 있는 sale_amount의 누적합

SELECT 
    sale_region, sale_date, sale_amount,
    SUM(sale_amount) OVER(PARTITION BY sale_region ORDER BY sale_amount) AS sum_sales
FROM  sales;

 

 

 

 

 

 

✅ MAX 함수를 이용해 파티션별 윈도우의 최댓값을 구할 수 있음.

 

✔️ 같은 지역에서 sale_amount의 최댓값

SELECT 
    sale_region, sale_date, sale_amount,
    MAX(sale_amount) OVER(PARTITION BY sale_region ) AS max_sales
FROM  sales;

 

 

 

✔️ 같은 지역에서 sale_amount의 최댓값을 가진 행만 추출

SELECT sale_region, sale_date, sale_amount
FROM (SELECT 
           sale_region, sale_date, sale_amount,
           MAX(sale_amount) OVER(PARTITION BY sale_region ) AS max_sales
           FROM  sales) AS aa
WHERE sale_amount = max_sales  ;

서브쿼리를 사용해서 지역별로 최댓값을 계산한 후 where문을 사용해 지역별 sale_amount가 최댓값인 행만 추출

 

 

하지만 위의 예제는 쿼리 성능이 저하될 수 있다.

파티션별 최댓값을 가진 행만 추출할 때는 RANK() 순위 함수를 사용하는게 적절

SELECT sale_region, sale_date, sale_amount
FROM (SELECT 
           sale_region, sale_date, sale_amount,
           RANK() OVER(PARTITION BY sale_region ORDER BY sale_amount DESC ) AS sale_rank
           FROM  sales) AS aa
WHERE sale_rank = 1  ;

 

 

 

 

 

 

✅ MIN 함수를 이용해 파티션별 윈도우의 최솟값을 구할 수 있음.

 

✔️ 같은 지역에서 sale_amount의 최솟값

SELECT 
    sale_region, sale_date, sale_amount,
    MIN(sale_amount) OVER(PARTITION BY sale_region) AS max_sales
FROM  sales;

 

 

 

 

✅ AVG 함수를 이용해 파티션별 윈도우의 평균을 구할 수 있음.

 

✔️ 같은 지역에서 sale_amount의 평균

SELECT 
    sale_region, sale_date, sale_amount,
    AVG(sale_amount) OVER(PARTITION BY sale_region) AS avg_sales
FROM  sales;

 

 

 

 

✅ COUNT 함수를 이용해 파티션별 윈도우의 평균을 구할 수 있음.

 

 

✔️ 밑의 'orders' 테이블은 각 고객의 주문수와 주문 액수 데이터이다.

✔️ COUNT() 함수와 윈도우 함수를 사용해 각 고객의 주문수를 계산

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATE,
  order_amount DECIMAL(10,2)
);

INSERT INTO orders (order_id, customer_id, order_date, order_amount) VALUES
(1, 1, '2022-01-01', 100.00),
(2, 1, '2022-01-02', 50.00),
(3, 2, '2022-01-01', 75.00),
(4, 2, '2022-01-03', 200.00),
(5, 2, '2022-01-05', 150.00);

 

orders 테이블

 

SELECT
  customer_id,
  COUNT(*) OVER(PARTITION BY customer_id) AS order_count, /* 고객별 주문건수 */
  SUM(order_amount) OVER(PARTITION BY customer_id) AS total_amount /*고객별 총주문량*/
FROM orders;

 

 

 

✔️ DISTINCT 문을 사용해서 고객 id의 중복을 없앨 수 있음.

SELECT
  DISTINCT customer_id,
  COUNT(*) OVER(PARTITION BY customer_id) AS order_count,
  SUM(order_amount) OVER(PARTITION BY customer_id) AS total_amount
FROM orders;

 

 

 

 

 

 

 

 

 

---

출처 및 참고 문헌(reference)

https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

MySQL :: MySQL 8.0 Reference Manual :: 12.21 Window Functions