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

 

 

 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. RANK, DENSE_RANK - 데이터 순위 

 

 

 

✅ 윈도우 함수 RANK, DENSE_RANK을 사용하여 'sales' 테이블에서 순위 매기기

CREATE TABLE sales (
  order_id INT PRIMARY KEY,
  region VARCHAR(50),
  sales INT
);

INSERT INTO sales (order_id, region, sales)
VALUES (1, 'A', 100),
       (2, 'A', 200),
       (3, 'B', 300),
       (4, 'B', 400),
       (5, 'B', 500),
       (6, 'C', 500);

 

 

 

SELECT
    region,
    sales,
    ROW_NUMBER() OVER(ORDER BY sales DESC) AS 'row_number',
    RANK() OVER(ORDER BY sales DESC) AS 'RANK',
    DENSE_RANK() OVER(ORDER BY sales DESC) AS 'dense_rank'
FROM sales;

 

 

row_number 컬럼의 경우는

ROW_NUMBER() 윈도우 함수를 사용하여 sales열을 내림차순 정렬한 후, 행마다 일련번호를 부여했다.

 

RANK 컬럼의 경우는 RANK( ) 윈도우 함수를 사용하여 sales열의 데이터에 순위를 매겼다.

OVER 함수 안에 ORDER BY로 sales를 내림차순하여 순위를 매겼기 때문에 sales의 값이 큰 행에 1등을 부여한다. 

또한 중복 값에는 중복 순위를 부여하기 때문에 1등이 2개이며 RANK 함수는 중복 순위를 부여하지만 위의 결과와 같이 2등을 건너뛰고 3등부터 부여한다. 

 

dense_rank 컬럼의 경우 RANK() 함수와 동일하게 중복 순위를 부여하지만

순위를 건너뛰지 않고 1, 1, 2, 3등처럼 순차적으로 순위를 매긴다.

 

 

 

 

✅ 윈도우 함수 RANK, DENSE_RANK을 사용하여 지역별 'sales' 순위 매기기

 

• 그룹별로 순위를 매기기 위해서는 ✨PARTITION BY절을 사용하면 됨.

SELECT
    region,
    sales,
    ROW_NUMBER ( ) OVER(PARTITION BY  region ORDER BY sales DESC) AS 'row_number',
    RANK() OVER(PARTITION BY  region ORDER BY sales DESC) AS 'RANK',
    DENSE_RANK() OVER(PARTITION BY  region ORDER BY sales DESC) AS 'dense_rank'
FROM sales;

 

region 별로 sales가 큰 순서대로 순위가 매겨졌다. 

 

 

 

 

 

 

 

 

 

 

---

출처 및 참고 문헌(reference)

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

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