[DB] 10. 통계쿼리 함수들

편준민

Feb 27, 2025

Contents

1. Rank 2. Partition 3. Rollup 4. Pivot

⭐

rank() over, row_number(), set @rownum :=0

변수 설정하는 법


-- 참고 (변수를 from절에 초기화 하는법)
SELECT @num as num
from emp, (select @num:=50) t;

-- 참고 (변수를 만드는 법)
set @rownum := 0;
select empno, ename, sal, (@rownum := @rownum+1)
from emp;

1. Rank

❗

(1) `rank`

내가 원하는 데이터의 순위를 나타낼 수 있다.


-- 기본문법
select empno, ename, sal,
rank() over (order by 컬럼명 desc/asc) '순위'
from emp;

select empno, ename, sal,
rank() over (order by sal desc) '순위'
from emp;

위의 결과는 공동순위일 경우 같은 순위를 출력하고 그 다음 순위가 없어졌다. 하지만 dense_rank를 사용하면 공동순위 다음 바로 다음 숫자로 순위를 표시해준다.

(2) `dense_rank`


select empno, ename, sal,
dense_rank() over (order by sal desc) '순위'
from emp;

(3) `row_number`

row_number는 오름차 또는내림차 순에 따라 번호를 차례대로 부여하는 함수이다. 순위를 매길 때에는 공동 순위가 있으면 밑에 있는 사람이 억울해 질 수 있으니 사용을 유의해야한다.


-- 순차적으로 숫자 부여
select empno, ename, sal,
row_number() over (order by sal desc) '순위'
from emp;

(4) `rank`문제

절차


-- 문제(emp테이블에서, 본인의 월급과 상사의 월급의 합의 순위를 내림차순으로 구하시오)
select *
from emp;

-- 본인과 상사 나열하기
select e1.empno, e1.ename '나' , e1.sal '내 월급' , e2.sal '상사 월급'
from emp e1 left outer join emp e2 
on e1.mgr = e2.empno;

-- 본인과 상사의 월급 더하기
select e1.empno, e1.ename '나' , e1.sal '내 월급' , e2.sal '상사 월급',
e1.sal+ifnull(e2.sal, 0) '월급의 합'
from emp e1 left outer join emp e2 
on e1.mgr = e2.empno;

SubQuery사용


-- 월급의 합의 순위 구하기
select 나, 내월급, 상사월급, 월급의합,
dense_rank() over (order by 월급의합 desc) '합의순위'
from
(
select e1.ename '나' , e1.sal '내월급' , e2.sal '상사월급',
e1.sal+ifnull(e2.sal, 0) '월급의합'
from emp e1 left outer join emp e2 on e1.mgr = e2.empno
) nemp;

SubQuery미사용


select e1.ename '나' , e1.sal '내 월급' , e2.sal '상사 월급', e1.sal+ifnull(e2.sal, 0) '월급의 합',
dense_rank() over (order by e1.sal+ifnull(e2.sal,0 ) desc) '합의순위'
from emp e1 left outer join emp e2 on e1.mgr = e2.empno;

Rank함수가 없을 때 직접 변수를 지정해서 만들기


-- 3. 순위 구하기 (직접)
set @rownum := 0;

select ename, sal, @rownum := @rownum+1
from 
(
select ename, sal
from emp
order by sal desc
) nemp;

2. Partition

❗

해당 부서의 월급 순위를 구하고 싶을 때 사용하는 함수. 각 그룹의 순위를 구하고 싶지만, group by를 사용하면 한 행 밖에 나오지 않기 때문에 사용하지 못한다. partition을 사용하여 그룹 별 순위를 조회 할 수 있다.


select deptno, ename, sal,
rank() over (partition by deptno order by sal desc) '순위'
from emp
order by deptno;


-- 문제 나이별 키 순위를 구하시오
select name, substr(jumin,1,2) '년생', height,
rank() over (partition by substr(jumin,1,2) order by height desc) '키순위'
from student
order by '년생';

-- SubQuery 사용
select 이름, 나이, 키,
rank() over (partition by 나이 order by 키 desc) '키순위'
from
(
select name 이름, substr(jumin,1,2) '나이', height '키'
from student
order by '나이'
) nst;

3. Rollup

❗

Rollup은 한 번의 여러 집계 함수를 사용하여 테이블을 만들 때 사용한다.

집계 합계는 SUM, AVG, COUNT 함수 등이 있다.

Rollup을 이해하기 위한 노가다 코드

(1) 샘플링 하기 (직업을 ‘CLERK’ 만 구하기)


select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'CLERK'
group by job, deptno
union all
select job , null, avg(sal) sal, count(*) cnt
from emp
where job = 'CLERK'

(2) 전체로 구하기


select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'CLERK'
group by job, deptno
union all
select job , null, avg(sal) sal, count(*) cnt
from emp
where job = 'CLERK'
union all
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'ANALYST'
group by job, deptno
union all
select job , null, avg(sal) sal, count(*) cnt
from emp
where job = 'ANALYST'
union all
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'MANAGER'
group by job, deptno
union all
select job , null, avg(sal) sal, count(*) cnt
from emp
where job = 'MANAGER'
union all
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'SALESMAN'
group by job, deptno
union all
select job , null, avg(sal) sal, count(*) cnt
from emp
where job = 'SALESMAN'
union all
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'PRESIDENT'
group by job, deptno
union all
select job , null, avg(sal) sal, count(*) cnt
from emp
where job = 'PRESIDENT'
union all
select null, null, avg(sal) sal, count(*) cnt
from emp;

함수를 사용하는 코드


-- 집계
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'CLERK'
group by job, deptno;

-- 소계
select job , null, avg(sal) sal, count(*) cnt
from emp
where job = 'CLERK';

-- 총계 
select null, null, avg(sal) sal, count(*) cnt
from emp;

-- rollup
select job, deptno, avg(sal), count(*) cnt
from emp
group by job, deptno with rollup;

4. Pivot

❗

행의 값을을 열로 변환 하는 것

day에 해당하는 값을 열로 바꾸는 방식을 Pivot이라고 한다


select week '주',
sum(if(day = '일', num_day ,0)) '일',
sum(if(day = '월', num_day ,0)) '월',
sum(if(day = '화', num_day ,0)) '화',
sum(if(day = '수', num_day ,0)) '수',
sum(if(day = '목', num_day ,0)) '목',
sum(if(day = '금', num_day ,0)) '금',
sum(if(day = '토', num_day ,0)) '토'
from cal
group by week;

몇 주인지 나오지 않았을 때.


select 주,
max(if(day = '일', num_day ,0)) '일',
max(if(day = '월', num_day ,0)) '월',
max(if(day = '화', num_day ,0)) '화',
max(if(day = '수', num_day ,0)) '수',
max(if(day = '목', num_day ,0)) '목',
max(if(day = '금', num_day ,0)) '금',
max(if(day = '토', num_day ,0)) '토'
from
(
select day, num_day, if(day='일', ((num_day-1)/7)+1, floor((num_day-1)/7)+1) '주'
from cal2
) ncal
group by 주;

PIVOT 연습문제

🔹 원본 테이블 (emp)

empno	ename	deptno	job	sal
1	A	10	CLERK	3000
2	B	10	MANAGER	5000
3	C	20	ANALYST	6000
4	D	20	CLERK	2800
5	E	20	CLERK	3200

부서(depto)별로 각 직업(job)의직원 수를 열(Column)로 만들고 싶다면?

🔹 PIVOT된 결과

deptno	CLERK	MANAGER	ANALYST
10	1	1	0
20	2	0	1


SELECT DEPTNO,
SUM(IF(JOB='ANALYST',1,0))  'ANALYST',
SUM(IF(JOB='CLERK',1,0))  'CLERK',
SUM(IF(JOB='MANAGER',1,0))  'MANAGER',
SUM(IF(JOB='PRESIDENT',1,0))  'PRESIDENT',
SUM(IF(JOB='SALESMAN',1,0))  'SALESMAN'
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

See more posts

[DB] 10. 통계쿼리 함수들

1. Rank

(1) rank

(2) dense_rank

(3) row_number

(4) rank문제

2. Partition

3. Rollup

Rollup을 이해하기 위한 노가다 코드

함수를 사용하는 코드

4. Pivot

PIVOT 연습문제

More articles

[DB] 12. 인덱스 (트리 생성 기법)

[DB] 11. DBMS 동작원리

[DB] 9. 집합

[DB] 8. Join

(1) `rank`

(2) `dense_rank`

(3) `row_number`

(4) `rank`문제