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

김주희

Mar 10, 2025

Contents

1. RANK 2. RANK 문제 3. 순위 직접 구하기 4. 파티션 5. 파티션 문제 6. RollUp 7. Pivot

1. RANK

❗

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

1. rank()


-- DB 고급 함수

-- 1. RANK
select empno, ename, sal, 1 '순위번호'
from emp
order by sal desc;

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

2. dense_rank()


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

+)row_number()


-- row_number() (넘버링에 가깝다.)
select empno, ename, sal,
row_number() over (order by sal desc) '순위'
from emp;

2. RANK 문제

emp 테이블에서 본인의 월급과 상사의 월급의 합의 순위를 내림차순으로 구하시오


select *, rank() over (order by total.sum desc) '순위'
from(
select e1.empno '사원번호', e1.ename '사원명', e1.sal '사원 월급', e2.ename '상사명', e2.sal '상사 월급', e1.sal+e2.sal 'sum'
from emp e1 left outer join emp e2 
on e1.mgr = e2.empno) total;

풀이


select e1.ename '사원명', e2.ename '상사명', e1.sal '사원월급', e2.sal '상사월급', e1.sal+ifnull(e2.sal, 0) 'sum'
from emp e1 left outer join emp e2 on e1.mgr = e2.empno;

-- 인라인뷰
select 사원명, 상사명, 사원월급, 상사월급, sum,
dense_rank() over (order by sum desc) '순위' 
from (
select e1.ename '사원명', e2.ename '상사명', e1.sal '사원월급', e2.sal '상사월급', e1.sal+ifnull(e2.sal, 0) 'sum'
from emp e1 left outer join emp e2 on e1.mgr = e2.empno) nemp;

-- 인라인 뷰 사용X (별칭 사용 불가 -> 가독성이 떨어짐)
select e1.ename '사원명', e2.ename '상사명', e1.sal '사원월급', e2.sal '상사월급', e1.sal+ifnull(e2.sal, 0) 'sum',
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;

3. 순위 직접 구하기


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

-- 실행할때마다 증가 -> 매번 0으로 초기화 해줘야 됨.
-- 정렬 먼저 하고 순위 매기기
select ename, sal, @rownum := @rownum+1
from 
(
select ename, sal 
from emp
order by sal desc
) nemp;

4. 파티션

❗

partition by


-- 4. 파티션
select deptno, ename, sal,
rank() over (partition by deptno order by sal desc) '순위'
from emp
order by deptno;

5. 파티션 문제

jumin에 있는 75, 76 등 나이별 키(height) 순위를 구하시오


-- 5. 파티션 연습 (jumin에 있는 75, 76 등 나이별 키(height) 순위를 구하시오)
select name, height, substr(jumin,1,2) '출생년도',
rank() over (partition by substr(jumin,1,2) order by height desc) '키 순위' 
from student;

풀이


select name, age, height, 
rank() over (partition by age order by height desc)
from
(
select name, substr(jumin,1,2) 'age', height
from student
) st;

6. RollUp

❗

group by를 잘 이해하자

1. rollup 없이 구하기


-- 아래 예제를 union all 을 적절히 활용
-- job, deptno 그룹 집계
select job, deptno, avg(sal) avg_sal, count(*) cnt
from emp
where job = 'CLERK'
group by job, deptno;


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


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

2. rollup


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

3. 내가 짠 rollup 없이 구현하기 코드


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

union all

select* from (
select job, deptno, avg(sal) , count(deptno) 
from emp
where job = 'ANALYST'
group by job, deptno
order by deptno asc) t1
union all
select * from (
select job, null, avg(sal)  ,count(*) 
from emp
where job = 'ANALYST'
group by job) t2

union all

select* from (
select job, deptno, avg(sal) , count(deptno) 
from emp
where job = 'MANAGER'
group by job, deptno
order by deptno asc) t1
union all
select * from (
select job, null, avg(sal)  ,count(*) 
from emp
where job = 'MANAGER'
group by job) t2

union all

select* from (
select job, deptno, avg(sal) , count(deptno) 
from emp
where job = 'SALESMAN'
group by job, deptno
order by deptno asc) t1
union all
select * from (
select job, null, avg(sal)  ,count(*) 
from emp
where job = 'SALESMAN'
group by job) t2

union all

select* from (
select job, deptno, avg(sal) , count(deptno) 
from emp
where job = 'PRESIDENT'
group by job, deptno
order by deptno asc) t1
union all
select * from (
select job, null, avg(sal)  ,count(*) 
from emp
where job = 'PRESIDENT'
group by job) t2

union all

select null, null, avg(sal), count(*)
from emp;

7. Pivot

❗

행의 값을 열로 변환한 것

1. 예제


-- pivot: 값을 컬럼으로 끌어올린것
select *
from cal;


select 
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
where week = 1
union all
select 
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
where week = 2
;


-- week로 grouping
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;


-- create as: select 결과를 가지고 table을 만드는 것
create table cal2
as
select day, num_day
from cal;

select * from cal2;

-- moduler 연산을 통해 week를 구할 수 있음

❗

week 구하기

ceil -> 7 / 7은 2가 되므로 0 + 1이 아닌 ceil로

1 / 7 = ceil(0.x) 1 2 / 7 = ceil(0.x) 1 3 / 7 = ceil(0.x) 1 4 / 7 = ceil(0.x) 1 5 / 7 = ceil(0.x) 1 6 / 7 = ceil(0.x) 1 7 / 7 = ceil(0.x) 1

2. 문제


-- 부서별 각 직업의 직원 수를 열로 만들고 싶다면?
select deptno, 
sum(if(job = 'CLERK', 1, 0)) 'CLERK',
sum(if(job = 'MANAGER', 1, 0)) 'MANAGER',
sum(if(job = 'ANALYST', 1, 0)) 'ANALYST',
sum(if(job = 'SALESMAN', 1, 0)) 'SALESMAN',
sum(if(job = 'PRESIDENT', 1, 0)) 'PRESIDENT'
from emp
group by deptno;

전체 코드


-- DB 고급 함수

-- 1. RANK
select empno, ename, sal, 1 '순위번호'
from emp
order by sal desc;

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

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

-- row_number() (넘버링에 가깝다.)
select empno, ename, sal,
row_number() over (order by sal desc) '순위'
from emp;

-- 2. 문제(emp 테이블에서 본인의 월급과 상사의 월급의 합의 순위를 내림차순으로 구하시오)
select *, rank() over (order by total.sum desc) '순위'
from(
select e1.empno '사원번호', e1.ename '사원명', e1.sal '사원 월급', e2.ename '상사명', e2.sal '상사 월급', e1.sal+e2.sal 'sum'
from emp e1 left outer join emp e2 -- 왼쪽에 있는 거 다 보이도록
on e1.mgr = e2.empno) total;

-- 풀이
select e1.ename '사원명', e2.ename '상사명', e1.sal '사원월급', e2.sal '상사월급', e1.sal+ifnull(e2.sal, 0) 'sum'
from emp e1 left outer join emp e2 on e1.mgr = e2.empno;

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

select e1.ename '사원명', e2.ename '상사명', e1.sal '사원월급', e2.sal '상사월급', e1.sal+ifnull(e2.sal, 0) 'sum',
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;

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

-- 실행할때마다 증가 -> 매번 0으로 초기화 해줘야 됨.
-- 정렬 먼저 하고 순위 매기기
select ename, sal, @rownum := @rownum+1
from 
(
select ename, sal 
from emp
order by sal desc
) nemp;


-- 4. 파티션
select deptno, ename, sal,
rank() over (partition by deptno order by sal desc) '순위'
from emp
order by deptno;

-- 5. 파티션 연습 (jumin에 있는 75, 76 등 나이별 키(height) 순위를 구하시오
select name, height, substr(jumin,1,2) '출생년도',
rank() over (partition by substr(jumin,1,2) order by height desc) '키 순위' 
from student;

select name, age, height, 
rank() over (partition by age order by height desc)
from
(
select name, substr(jumin,1,2) 'age', height
from student
) st;

-- 6. rollup (집계 함수)
-- job과 deptno별 평균값
-- group 함수가 아닌 컬럼은 group by절에 적어주세용.. 근데 이렇게 하면 안좋다
-- group 함수 세로 연산 -> 찌그러트릴수있는
select * from emp;

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

union all

select* from (
select job, deptno, avg(sal) , count(deptno) 
from emp
where job = 'ANALYST'
group by job, deptno
order by deptno asc) t1
union all
select * from (
select job, null, avg(sal)  ,count(*) 
from emp
where job = 'ANALYST'
group by job) t2

union all

select* from (
select job, deptno, avg(sal) , count(deptno) 
from emp
where job = 'MANAGER'
group by job, deptno
order by deptno asc) t1
union all
select * from (
select job, null, avg(sal)  ,count(*) 
from emp
where job = 'MANAGER'
group by job) t2

union all

select* from (
select job, deptno, avg(sal) , count(deptno) 
from emp
where job = 'SALESMAN'
group by job, deptno
order by deptno asc) t1
union all
select * from (
select job, null, avg(sal)  ,count(*) 
from emp
where job = 'SALESMAN'
group by job) t2

union all

select* from (
select job, deptno, avg(sal) , count(deptno) 
from emp
where job = 'PRESIDENT'
group by job, deptno
order by deptno asc) t1
union all
select * from (
select job, null, avg(sal)  ,count(*) 
from emp
where job = 'PRESIDENT'
group by job) t2

union all

select null, null, avg(sal), count(*)
from emp;

-- 아래 예제를 union all 을 적절히 활용
-- job, deptno 그룹 집계
select job, deptno, avg(sal) avg_sal, count(*) cnt
from emp
where job = 'CLERK'
group by job, deptno;

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

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

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

-- pivot: 값을 컬럼으로 끌어올린것
select *
from cal;

select 
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
where week = 1
union all
select 
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
where week = 2
;

-- week로 grouping
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;

-- create as: select 결과를 가지고 table을 만드는 것
create table cal2
as
select day, num_day
from cal;

select * from cal2;

-- moduler 연산을 통해 week를 구할 수 있음


-- 부서별 각 직업의 직원 수를 열로 만들고 싶다면?
select deptno, 
sum(if(job = 'CLERK', 1, 0)) 'CLERK',
sum(if(job = 'MANAGER', 1, 0)) 'MANAGER',
sum(if(job = 'ANALYST', 1, 0)) 'ANALYST',
sum(if(job = 'SALESMAN', 1, 0)) 'SALESMAN',
sum(if(job = 'PRESIDENT', 1, 0)) 'PRESIDENT'
from emp
group by deptno;

select *, rank() over (order by total.sum desc) '순위' from( select e1.empno '사원번호', e1.ename '사원명', e1.sal '사원 월급', e2.ename '상사명', e2.sal '상사 월급', e1.sal+e2.sal 'sum' from emp e1 left outer join emp e2 on e1.mgr = e2.empno) total;

select e1.ename '사원명', e2.ename '상사명', e1.sal '사원월급', e2.sal '상사월급', e1.sal+ifnull(e2.sal, 0) 'sum' from emp e1 left outer join emp e2 on e1.mgr = e2.empno; -- 인라인뷰 select 사원명, 상사명, 사원월급, 상사월급, sum, dense_rank() over (order by sum desc) '순위' from ( select e1.ename '사원명', e2.ename '상사명', e1.sal '사원월급', e2.sal '상사월급', e1.sal+ifnull(e2.sal, 0) 'sum' from emp e1 left outer join emp e2 on e1.mgr = e2.empno) nemp; -- 인라인 뷰 사용X (별칭 사용 불가 -> 가독성이 떨어짐) select e1.ename '사원명', e2.ename '상사명', e1.sal '사원월급', e2.sal '상사월급', e1.sal+ifnull(e2.sal, 0) 'sum', 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;

-- 3. 순위 구하기(직접) set @rownum := 0; -- @변수 -- 실행할때마다 증가 -> 매번 0으로 초기화 해줘야 됨. -- 정렬 먼저 하고 순위 매기기 select ename, sal, @rownum := @rownum+1 from ( select ename, sal from emp order by sal desc ) nemp;

-- 5. 파티션 연습 (jumin에 있는 75, 76 등 나이별 키(height) 순위를 구하시오) select name, height, substr(jumin,1,2) '출생년도', rank() over (partition by substr(jumin,1,2) order by height desc) '키 순위' from student;

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

select 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 where week = 1 union all select 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 where week = 2 ;

-- week로 grouping 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 deptno, sum(if(job = 'CLERK', 1, 0)) 'CLERK', sum(if(job = 'MANAGER', 1, 0)) 'MANAGER', sum(if(job = 'ANALYST', 1, 0)) 'ANALYST', sum(if(job = 'SALESMAN', 1, 0)) 'SALESMAN', sum(if(job = 'PRESIDENT', 1, 0)) 'PRESIDENT' from emp group by deptno;

-- DB 고급 함수 -- 1. RANK select empno, ename, sal, 1 '순위번호' from emp order by sal desc; select empno, ename, sal, rank() over (order by sal desc) '순위' from emp; -- dense_rank select empno, ename, sal, dense_rank() over (order by sal desc) '순위' from emp; -- row_number() (넘버링에 가깝다.) select empno, ename, sal, row_number() over (order by sal desc) '순위' from emp; -- 2. 문제(emp 테이블에서 본인의 월급과 상사의 월급의 합의 순위를 내림차순으로 구하시오) select *, rank() over (order by total.sum desc) '순위' from( select e1.empno '사원번호', e1.ename '사원명', e1.sal '사원 월급', e2.ename '상사명', e2.sal '상사 월급', e1.sal+e2.sal 'sum' from emp e1 left outer join emp e2 -- 왼쪽에 있는 거 다 보이도록 on e1.mgr = e2.empno) total; -- 풀이 select e1.ename '사원명', e2.ename '상사명', e1.sal '사원월급', e2.sal '상사월급', e1.sal+ifnull(e2.sal, 0) 'sum' from emp e1 left outer join emp e2 on e1.mgr = e2.empno; select 사원명, 상사명, 사원월급, 상사월급, sum, dense_rank() over (order by sum desc) '순위' from ( select e1.ename '사원명', e2.ename '상사명', e1.sal '사원월급', e2.sal '상사월급', e1.sal+ifnull(e2.sal, 0) 'sum' from emp e1 left outer join emp e2 on e1.mgr = e2.empno) nemp; select e1.ename '사원명', e2.ename '상사명', e1.sal '사원월급', e2.sal '상사월급', e1.sal+ifnull(e2.sal, 0) 'sum', 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; -- 3. 순위 구하기(직접) set @rownum := 0; -- @변수 -- 실행할때마다 증가 -> 매번 0으로 초기화 해줘야 됨. -- 정렬 먼저 하고 순위 매기기 select ename, sal, @rownum := @rownum+1 from ( select ename, sal from emp order by sal desc ) nemp; -- 4. 파티션 select deptno, ename, sal, rank() over (partition by deptno order by sal desc) '순위' from emp order by deptno; -- 5. 파티션 연습 (jumin에 있는 75, 76 등 나이별 키(height) 순위를 구하시오 select name, height, substr(jumin,1,2) '출생년도', rank() over (partition by substr(jumin,1,2) order by height desc) '키 순위' from student; select name, age, height, rank() over (partition by age order by height desc) from ( select name, substr(jumin,1,2) 'age', height from student ) st; -- 6. rollup (집계 함수) -- job과 deptno별 평균값 -- group 함수가 아닌 컬럼은 group by절에 적어주세용.. 근데 이렇게 하면 안좋다 -- group 함수 세로 연산 -> 찌그러트릴수있는 select * from emp; select* from ( select job, deptno, avg(sal) , count(deptno) from emp where job = 'CLERK' group by job, deptno order by deptno asc) t1 union all select * from ( select job, null, avg(sal) ,count(*) from emp where job = 'CLERK' group by job) t2 union all select* from ( select job, deptno, avg(sal) , count(deptno) from emp where job = 'ANALYST' group by job, deptno order by deptno asc) t1 union all select * from ( select job, null, avg(sal) ,count(*) from emp where job = 'ANALYST' group by job) t2 union all select* from ( select job, deptno, avg(sal) , count(deptno) from emp where job = 'MANAGER' group by job, deptno order by deptno asc) t1 union all select * from ( select job, null, avg(sal) ,count(*) from emp where job = 'MANAGER' group by job) t2 union all select* from ( select job, deptno, avg(sal) , count(deptno) from emp where job = 'SALESMAN' group by job, deptno order by deptno asc) t1 union all select * from ( select job, null, avg(sal) ,count(*) from emp where job = 'SALESMAN' group by job) t2 union all select* from ( select job, deptno, avg(sal) , count(deptno) from emp where job = 'PRESIDENT' group by job, deptno order by deptno asc) t1 union all select * from ( select job, null, avg(sal) ,count(*) from emp where job = 'PRESIDENT' group by job) t2 union all select null, null, avg(sal), count(*) from emp; -- 아래 예제를 union all 을 적절히 활용 -- job, deptno 그룹 집계 select job, deptno, avg(sal) avg_sal, count(*) cnt from emp where job = 'CLERK' group by job, deptno; -- 소계 select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt' from emp where job = 'CLERK' group by job; -- 총계 select null, null, avg(sal) avg_sal, count(*) from emp; -- rollup select job, deptno, avg(sal), count(*) cnt from emp group by job, deptno with rollup; -- pivot: 값을 컬럼으로 끌어올린것 select * from cal; select 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 where week = 1 union all select 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 where week = 2 ; -- week로 grouping 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; -- create as: select 결과를 가지고 table을 만드는 것 create table cal2 as select day, num_day from cal; select * from cal2; -- moduler 연산을 통해 week를 구할 수 있음 -- 부서별 각 직업의 직원 수를 열로 만들고 싶다면? select deptno, sum(if(job = 'CLERK', 1, 0)) 'CLERK', sum(if(job = 'MANAGER', 1, 0)) 'MANAGER', sum(if(job = 'ANALYST', 1, 0)) 'ANALYST', sum(if(job = 'SALESMAN', 1, 0)) 'SALESMAN', sum(if(job = 'PRESIDENT', 1, 0)) 'PRESIDENT' from emp group by deptno;