![[DB] 10. 통계쿼리 함수들](https://image.inblog.dev?url=https%3A%2F%2Fsource.inblog.dev%2Ffeatured_image%2F2025-03-16T03%3A00%3A24.911Z-d1357e08-1945-4d5a-8964-45f71e6a3f72&w=3840&q=75)
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;
Share article