[데이터베이스] 10. 통계 쿼리 함수

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;

• dense_rank : 순위를 무조건 순서대로 매김 (공동 n위 다음 n+1위로 표시)

-- 공동 n위 다음 n+1위로 표시
select empno, ename, sal,
dense_rank() over (order by sal desc) '순위'
from emp;

• row_number : 행 순서대로 순위를 매김 (공동 순위 없음) = numbering

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

문제

• emp 테이블에서 본인의 월급과 상사의 월급의 합의 순위를 내림차순으로 출력

-- 문제 : emp 테이블에서 본인의 월급과 상사의 월급의 합의 순위를 내림차순으로 구하시오.
select e1.empno '사원번호', e1.ename '사원명', e1.deptno '부서번호', e2.ename '상사명', ifnull(e1.sal + e2.sal, e1.sal) '월급 합',
rank() over (order by ifnull(e1.sal + e2.sal, e1.sal) desc) '순위'
from emp e1 left outer join emp e2
on e1.mgr = e2.empno;

• Subquery를 활용하여 별칭을 사용
• 결과는 동일

select 나, 상사, 내월급, 상사월급, 
rank() over (order by 상사월급 desc) '순위'
from
(
select e1.ename '나', e2.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;

2. 순위 직접 구하기

• 변수 선언
• set 변수명 := value;

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

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

3. Partition

• 그룹 별로 쪼개어서 순위, numbering 등을 가능하게 함

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

-- 문제. 나이별 학생의 키 순위를 구하시오.
select concat(substr(year(birthday),3,2),'년생') '출생년도', name, height,
rank() over (partition by year(birthday) order by height desc) '키 순위'
from student;

4. Rollup

• 집계 함수

노가다 (Union All 사용 시)

-- 4. 집계 함수 (rollup)
-- 부서의 집계 using union all
select job, deptno, round(avg(sal),0) sal, count(deptno) cnt 
from emp
where job = 'clerk'
group by job, deptno
union all
select distinct job, null, round(avg(sal),0) sal, count(deptno)
from emp
where job = 'clerk'

union all

select job, deptno, round(avg(sal),0) sal, count(deptno) cnt 
from emp
where job = 'analyst'
group by job, deptno
union all
select 'ANALYST', null, round(avg(sal),0) sal, count(deptno)
from emp
where job = 'analyst'

union all

select job, deptno, round(avg(sal),0) sal, count(deptno) cnt 
from emp
where job = 'manager'
group by job, deptno
union all
select 'MANAGER', null, round(avg(sal),0) sal, count(deptno)
from emp
where job = 'manager'

union all

select job, deptno, round(avg(sal),0) sal, count(deptno) cnt 
from emp
where job = 'salesman'
group by job, deptno
union all
select 'SALESMAN', null, round(avg(sal),0) sal, count(deptno)
from emp
where job = 'salesman'

union all

select job, deptno, round(avg(sal),0) sal, count(deptno) cnt 
from emp
where job = 'president'
group by job, deptno
union all
select 'PRESIDENT', null, round(avg(sal),0) sal, count(deptno)
from emp
where job = 'president'

union all

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

결과

Rollup 사용 시

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

결과

5. Pivot

• 값을 Column으로 옮겨 출력

-- 5. Pivot
select *
from cal;

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;

문제 1

• 요일과 일만 적힌 테이블 cal2에서 달력을 만들어 출력하시오.

-- 요일과 일만 적힌 테이블 cal2에서 달력 만들기
-- 몇 주인지 만들기 : modular 연산
-- 나눈 값을 올려서 계산 : ceil(일 / 7)

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 *, ceil(num_day / 7) week
from cal2) ncal
group by week;

문제 2

• 부서별 직업별 사원의 수를 피벗 형태로 출력하시오.

-- 부서별 각 직업의 직원 수를 피벗 형태로 나타내어라.
-- 직원 수 : 조건이 맞을 경우 count(), 아니면 null (0도 값이 존재하므로 count에 포함)

select deptno, 
count(if(job = 'CLERK', 1, null)) 'CLERK',
count(if(job = 'ANALYST', 1, null)) 'ANALYST',
count(if(job = 'MANAGER', 1, null)) 'MANAGER',
count(if(job = 'SALESMAN', 1, null)) 'SALESMAN',
count(if(job = 'PRESIDENT', 1, null)) 'PRESIDENT'
from emp
group by deptno;