[MySQL] 윈도우 함수

Database

[MySQL] 윈도우 함수 - OVER

비번변경 2023. 5. 14. 23:49

윈도우 함수 (Window Function)

행과 행 간의 관계를 정의하기 위해 제공되는 함수이다.

데이터베이스를 다룰 때는 GROUPY BY와 COUNT, SUM과 같은 집계 함수를 많이 사용하는데, 윈도우 함수도 GROUP BY와 비슷하게 데이터를 그룹화하여 순위, 합계, 평균 등을 구할 수 있다. 다만 집계된 결과만 보여주는 GROUP BY와 다르게 기존 데이터에 집계된 데이터를 추가하여 보여준다. 즉, GROUP BY는 결과 데이터의 행 수가 줄어드는 반면 윈도우 함수를 사용하면 결과 데이터 행 수가 줄어들지 않는다.

MySQL의 경우 8.0부터 윈도우 함수를 지원한다. 이 글에서는 간단히 윈도우 함수의 구조와 사용 예시를 정리한다.

구조

윈도우 함수는 아래와 같은 구조로 사용한다.

FUNCTION(column) OVER(PARITION BY column ORDER BY column)

- FUNCTION : COUNT, SUM, RANK 등 사용할 함수

- PARTITION BY : 컬럼을 기준으로 테이블을 그룹화한다. 생략하면 테이블 전체를 하나의 그룹으로 처리한다.

- ORDER BY : 컬럼을 기준으로 정렬한다.

예시

공장 이름과 위치, 생산량을 나타내는 데이터를 예시로 윈도우 함수를 사용해 본다.

| seq | factory_name | location | created_goods_cnt |
| --- | ------------ | -------- | ----------------- |
| 1   | Ubuntu       | Seoul    | 123               |
| 2   | CentOS       | Busan    | 568               |
| 3   | Apache       | Seoul    | 584               |
| 4   | Amazon       | Busan    | 625               |
| 5   | GCP          | Busan    | 322               |
| 6   | Snowflake    | Jeju     | 333               |
| 7   | Oracle       | Seoul    | 214               |
| 8   | MySQL        | Jeju     | 156               |

DDL은 접은글에 적어둔다.

create table factory (
    seq               int,
    factory_name      varchar(30),
    location          varchar(30),
    created_goods_cnt int
);
INSERT INTO factory (seq, factory_name, location, created_goods_cnt) VALUES (1, 'Ubuntu', 'Seoul', 123);
INSERT INTO factory (seq, factory_name, location, created_goods_cnt) VALUES (2, 'CentOS', 'Busan', 568);
INSERT INTO factory (seq, factory_name, location, created_goods_cnt) VALUES (3, 'Apache', 'Seoul', 584);
INSERT INTO factory (seq, factory_name, location, created_goods_cnt) VALUES (4, 'Amazon', 'Busan', 625);
INSERT INTO factory (seq, factory_name, location, created_goods_cnt) VALUES (5, 'GCP', 'Busan', 322);
INSERT INTO factory (seq, factory_name, location, created_goods_cnt) VALUES (6, 'Snowflake', 'Jeju', 333);
INSERT INTO factory (seq, factory_name, location, created_goods_cnt) VALUES (7, 'Oracle', 'Seoul', 214);
INSERT INTO factory (seq, factory_name, location, created_goods_cnt) VALUES (8, 'MySQL', 'Jeju', 156);

위 데이터를 이용해 지역 별 생산량 기준 공장 순위를 매긴다면 아래와 같이 사용할 수 있다.

- FUNCTION : RANK

- PARTITION BY : 지역 별이므로 location 컬럼을 기준으로 그룹화한다.

- ORDER BY : 생산량 기준 순위이므로 created_goods_cnt 컬럼을 기준으로 정렬한다. 내림차순으로 정렬한다.

SELECT *,
	RANK() OVER (PARTITION BY location ORDER BY created_goods_cnt DESC) output_rank_by_location
FROM factory
;

결과

| seq | factory_name | location | created_goods_cnt | output_rank_by_location |
| --- | ------------ | -------- | ----------------- | ----------------------- |
| 4   | Amazon       | Busan    | 625               | 1                       |
| 2   | CentOS       | Busan    | 568               | 2                       |
| 5   | GCP          | Busan    | 322               | 3                       |
| 6   | Snowflake    | Jeju     | 333               | 1                       |
| 8   | MySQL        | Jeju     | 156               | 2                       |
| 3   | Apache       | Seoul    | 584               | 1                       |
| 7   | Oracle       | Seoul    | 214               | 2                       |
| 1   | Ubuntu       | Seoul    | 123               | 3                       |