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

1. RANK

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. 파티션

-- 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

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를 구할 수 있음

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;