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

1. RANK() OVER() 함수

1. rank() over(order by)

select 
	직원명, 직원월급, 상사이름, 상사월급, 합계,
    rank() over(order by 합계 desc) '순위'
from
	(
    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
    )s;

• select절 안에 다 작성할 때

select 
	e1.ename '직원', e1.sal '월급', e2.ename '상사', 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;

2. 직접 순위를 지정 + 변수 생성 방법

• set 을 사용해서 변수를 만들 수 있음

set @rownum := 0;

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

• 정렬을 한 다음 숫자를 붙인다.

3. partition 파티션 사용 rank() over(partition by)

• partition은 group by와 비슷한 방식

• group by는 그룹 별 세로 연산

• partition은 묶음 별 순서를 매길 때 사용

• 지정한 컬럼에 값을 묶어서 표현한다.

select
	deptno, ename, sal
from emp
where deptno = 10
union all
select
	deptno, ename, sal
from emp
where deptno = 20
union all
select
	deptno, ename, sal
from emp
where deptno = 30
order by deptno, sal desc;

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

• 나이 별 키 순위를 나타내라

select 
	name, height, 나이,
    rank() over(partition by 나이 order by height desc) '키_순위'
from
	(
    select 
		name, height, 2025 - substr(birthday,1,4) '나이'
	from
		student
	order by 나이 desc
    ) t;

2. ROLLUP

1. union all을 사용한 집계

1. 테이블 확인

select *
from emp

2. job이 CLERK인 데이터 추출

select *
from emp
where job = 'CLERK';

3. job, deptno, sal 만 추출

select job, deptno, sal
from emp
where job = 'CLERK';

4. deptno별 sal평균, 인원수를 추출

select job, deptno, avg(sal), count(*)
from emp
where job = 'CLERK'
group by deptno;

5. job별 sal의 평균, 인원수의 합계

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

6. 4번과 5번 테이블의 집합

select job, deptno, avg(sal), count(*)
from emp
where job = 'CLERK'
group by deptno
union all
select job, null deptno, avg(sal) avg, count(*) cnt
from emp
where job = 'CLERK'
group by job;

7. 다른 직업도 추가

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

8. 모든 직업의 sal평균, 인원수

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

9. 최종테이블

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

2. rollup를 사용해 해결

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

3. Pivot

1. cal 테이블로 연습

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
8 / 7 = ceil(0.x) 2
9 / 7 = ceil(0.x) 2

.
.
.
.
.

1. 달력 테이블 확인

select *
from cal;

2. 컬럼으로 만들고 싶은 day 데이터 확인

select distinct day
from cal;

3. day를 피봇형태로 만들어 보기

select '일','월','화','수','목','금','토'
from cal;

4. week를 기준으로 값을 출력하기

1. 전체 week별 달력 그려보기

select
	week,
    '일',
    '월',
    '화',
    '수',
    '목',
    '금',
    '토'
from cal
group by week;

2. select 마다 행 표기 방법

select
	1 'week',
	1 '일'
union all
select
	2 'week',
	8 '일';

4. group by 로 week별 계산

• 1 week를 그룹바이로 묶었기 때문에 맨 왼쪽 1를 압축 한다

• 그렇다면 1~7의 값중 1개의 값만 나와야 한다

• 표기하고 싶은 요일별 값 1개만 나오게 하자

• 예) 일요일이라면 숫자 1만 남겨야 한다
• group by 함수중 sum(), max() 둘중 하나를 사용해 1만 남게 하자

• day를 압축 할 수 있도록 요일에 해당하면 그 값을 넣고 아니면 0을 넣어보자

select week, day, num_day, if(day= '일', num_day,0) '일요일'
from cal
where week = 1;

• week컬럼과 요일 값 컬럼만 표기하자

select week, if(day= '일', num_day,0) '일요일'
from cal
where week = 1;

select week, if(day= '월', num_day,0) '월요일'
from cal
where week = 1;

• group by 로 압축해보자

select week, max(if(day= '일', num_day,0)) '일요일'
from cal
where week = 1
group by week;

select week, max(if(day= '월', num_day,0)) '월요일'
from cal
where week = 1
group by week;

• where을 제거하고 그룹별로 표기하자

select week, max(if(day= '일', num_day,0)) '일요일'
from cal
group by week;

• 다른 요일도 붙여보자

select
	week,
    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 cal
group by week;

2. emp 테이블 피봇 해보기

select *
from emp;

• deptno, job 컬럼만 추출

select deptno, job
from emp;

• job컬럼에 데이터가 어떤 종류가 있는지 확인

• 해당 데이터들을 컬럼으로 만듦

select distinct job
from emp;

• 10번 부서에 직업이 어떤게 있나 확인

select deptno, job
from emp
where deptno = 10;

• 각 직업에 인원이 있으면 숫자 1 아니면 0 으로 표기

select 
	deptno,
    if(job = 'CLERK',1,0) 'CLERK'
from emp
where deptno = 10;

• group by로 표기

select 
	deptno,
  sum(if(job = 'CLERK',1,0)) 'CLERK'
from emp
where deptno = 10
group by deptno;

• where을 제거하고 그룹별 직업 인원수 확인

select 
	deptno,
  sum(if(job = 'CLERK',1,0)) 'CLERK'
from emp
group by deptno;

• 다른 직업 추가해서 확인하기

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