DB 고급 함수
1. Rank
select empno, ename, sal,
rank() over (order by sal desc) '순위'
from emp;

2. Dense_rank
중복 값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 중복 값 개수와 상관없이 순차적인 순위 값을 출력하도록 하는 함수
select empno, ename, sal,
dense_rank() over (order by sal desc) '순위'
from emp;

3. Row_number
select empno, ename, sal,
row_number() over (order by sal desc) '순위'
from emp;

Ex. emp 테이블에서 본인의 월급과 상사의 월급 합의 순위를 내림차순으로 구하기
- Outer Join
select e1.ename '직원명' , e1.sal '직원월급' , e2.ename '상사명' , e2.sal '상사월급' , e1.sal+e2.sal '월급의합'
from emp e1 left outer join emp e2
on e1.mgr = e2.empno;

e1.sal 과 e2.sal 을 그냥 더하면 KING의 월급의 합에 null이 들어가므로 ifnull() 필요
select e1.ename '직원명' , e1.sal '직원 월급' , e2.ename '상사명' , e2.sal '상사 월급',
e1.sal+ifnull(e2.sal, 0) '월급의 합'
from emp e1 left outer join emp e2
on e1.mgr = e2.empno;

- rank() over
select e1.ename '직원명', e2.ename '상사명', e1.sal '직원월급', e2.sal '상사월급',
e1.sal+ifnull(e2.sal, 0) '월급의합',
rank() over (order by e1.sal+ifnull(e2.sal, 0) desc) '순위'
from emp e1 left outer join emp e2 on e1.mgr = e2.empno;
-- Inline view
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;

4. Set
set @rownum := 0; -- 변수
select ename, sal, @rownum := @rownum+1
from
(
select ename, sal
from emp
order by sal desc
) nemp;

5. Partition
GROUP BY와 PARTITION BY의 차이
GROUP BY | PARTITION BY | |
사용 | 그룹 외부에서 묶어 순위 및 그룹별 집계를 구할 떄 사용 | 그룹 내 순위 및 그룹별 집계를 구할 때 사용 |
결과값 | 특정 원하는 컬럼에 대해서 추출해 결과값 보여줌 | 전체 데이터에서 원하는 결과값 보여줌 |
Ex. partition by 사용하여 나이별 키순위 구하기
select name, substr(jumin,1,2) 'age', height,
rank() over (partition by substr(jumin,1,2) order by height desc ) '순위'
from student;
-- Inline veiw
select name, age, height,
rank() over (partition by substr(jumin,1,2) order by height desc )
from (
select name, substr(jumin,1,2) 'age', height
from student
) st;

Rollup (집계함수)
Group by를 잘 이해하기
Job 기준 정렬
select job, deptno, avg(sal) sal, count(*) cnt
from emp
group by job, deptno with rollup;

Deptno 기준 정렬
select deptno,job, avg(sal) sal, count(*) cnt
from emp
group by deptno, job with 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;
-- Union all
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'CLERK'
group by job, deptno
union all
select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'CLERK'
group by job
union all
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'ANALYST'
group by job, deptno
union all
select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'ANALYST'
group by job
union all
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'MANAGER'
group by job, deptno
union all
select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'MANAGER'
group by job
union all
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'SALESMAN'
group by job, deptno
union all
select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'SALESMAN'
group by job
union all
select job, deptno, avg(sal) sal, count(*) cnt
from emp
where job = 'PRESIDENT'
group by job, deptno
union all
select job, null 'deptno', avg(sal) avg_sal, count(*) 'cnt'
from emp
where job = 'PRESIDENT'
group by job
union all
select null, null, avg(sal) avg_sal, count(*)
from emp;

Pivot
데이터의 통계나 집계를 하기 위하여 많이 쓰이는 형식
테이블의 데이터를 행에서 열로 변환하는 프로세스
값을 컬럼으로 끌어올림
특정 열을 행으로 만들고, 조건에 따라 값을 합산하여 새로운 열을 생성합니다.
- 그룹화는 지정된 열을 기준으로 이루어집니다
- 집계함수와 조건문을 이용하면 중복되는 열의 값들을 순환해서 해당 열들을 여러 열로 반환하여 집계
Ex. Pivot을 이용하여 부서별로 각 직업의 직원수를 열로 만들기
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;

Ex. 달력만들기
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;

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=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
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=3
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=4
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=5;
Ex. 달력만들기 2_week 만들기
create table cal2
as
select day,num_day
from cal;
select * from cal2;

select day, num_day, 1 cnt
from cal2;

num_day를 7로 나누었을 때!
- 값이 1 이하: 1
- 값이 1 초과 2 이하: 2
- 값이 1 초과 2 이하: 2
- 값이 1 초과 2 이하: 2
- 값이 1 초과 2 이하: 2
ceil() 함수 사용
는 소수점 자리의 숫자를 무조건 올리는 함수이다.
select day, num_day, ceil(num_day/7) week
from cal2;

select ceil(num_day/7) 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 cal2
group by week;

Share article