mariadb 윈도우함수 dense_rank 활용하기

안녕하세요.

 

이번 포스팅에서는

mariadb 윈도우 함수 중 dense_rank에 대해

파헤쳐 볼까 합니다.

 

윈도우 함수에 대해 모르신다면

아래 클릭하셔서 개념을 보고 오세요.

 

 

 

DENSE_RANK 사용법

 

dense_rank는 순번 혹은 순위를 표시해주는 윈도우 함수입니다.

row_number와의 차이점은 값이 같을 경우 동일 순위를 표현해 준다는 점이죠

dense_rank의 사용 문법은 아래와 같습니다.

 

DENSE_RANK() OVER (
  [ PARTITION BY partition_expression ]
  [ ORDER BY order_list ]
)

 

 

의외로 사용법이 간단합니다.

PARTITION BY 절로 그룹을 나눈 후에

ORDER BY 절로 순서를 정합니다.

 

 

DENSE_RANK 실제 사용 예시

 

dense_rank를 실제로 사용해보겠습니다.

테이블 생성과 데이터 생성은 아래 DDL 및 DML을 활용해주세요.

 

create table sales_performance(
 empname varchar(100) default '' not null comment '사원이름',
 year varchar(4) default '' not null comment '영업 년',
 month varchar(2) default '' not null comment '영업 월',
 sales int(20) default 0 not null comment '판매실적, 단위 천원'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO sales_performance VALUES('홍길동', '2023', '05', 115242);
INSERT INTO sales_performance VALUES('홍길동', '2023', '06', 104234);
INSERT INTO sales_performance VALUES('홍길동', '2023', '07', 148531);
INSERT INTO sales_performance VALUES('홍길동', '2023', '08', 95414);
INSERT INTO sales_performance VALUES('이순진', '2023', '05', 104785);
INSERT INTO sales_performance VALUES('이순진', '2023', '06', 104234);
INSERT INTO sales_performance VALUES('이순진', '2023', '07', 85414);
INSERT INTO sales_performance VALUES('이순진', '2023', '08', 121457);
INSERT INTO sales_performance VALUES('김기섭', '2023', '05', 128541);
INSERT INTO sales_performance VALUES('김기섭', '2023', '06', 114569);
INSERT INTO sales_performance VALUES('김기섭', '2023', '07', 91423);
INSERT INTO sales_performance VALUES('김기섭', '2023', '08', 154123);
INSERT INTO sales_performance VALUES('윤미소', '2023', '05', 100045);
INSERT INTO sales_performance VALUES('윤미소', '2023', '06', 112345);
INSERT INTO sales_performance VALUES('윤미소', '2023', '07', 91423);
INSERT INTO sales_performance VALUES('윤미소', '2023', '08', 114578);

 

해당 테이블은 매월 사원들의 판매실적을 저장하는 테이블이고

2분기 (5월~8월) 실적만 데이터를 만들었습니다.

 

 

자 그럼, 이 테이블에서 유의한 데이터를 어떻게 조회 해볼 수 있을까요?

 

Q1. 년월별마다 사월별로 가장 높은 판매액순으로 데이터 추출

 

select
 dense_rank() over(partition by year, month order by sales desc) as rank, 
 year, month, empname, sales 
from sales_performance

 

over 의 괄호안에

년월별로 파트를 만들고 ( partition by year,month )

판매액 오름차순으로 나오게 하면 됩니다. ( order by sales desc )

 

Q2. 2분기에 년월 구분없이 가장 높은 판매액순으로 데이터 추출

 

select
 dense_rank() over(order by sales desc) as rank,
 year, month, empname, sales 
from sales_performance

 

Q1 보다는 간단합니다.

행 별로 순위를 매겨서 데이터 집합을 조회하는 방법입니다.

 

굳이 순위를 안매기실거면

order by절만 써서 아래 쿼리처럼 해도 됩니다.

 

select * from sales_performance order by sales desc

 

Q3. 동일한 판매액을 달성한 년월,사원,순위,판매액 추출하기

 

group by와 having 그리고 group_concat을 같이 사용하면 데이터 추출이 가능합니다.

 

select 
rank, year, month, group_concat(empname) as empnames, sales 
from (
 select 
  dense_rank() over(
   partition by year, month order by sales desc
  ) as rank, 
  year, month, empname, sales 
  from sales_performance
) a group by year, month, sales having count(*) > 1

 

 

SQL문을 익힐때는

직접 데이터를 만들고 직접 SQL문을 짜보셔야

실력이 금방금방 늘 수 있습니다.

 

눈으로만 보시지 마시고

꼭 실습을 해보시길 권장드려요 ^^

 

이상입니다.

 

 

감사합니다.