[Data Base] 10. Statistics Query Functions

김미숙's avatar
Mar 05, 2025
[Data Base] 10. Statistics Query Functions
‼️
DB 고급 함수

1. Rank

‼️

rank() over

중복 값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 중복 개수만큼 떨어진 순위로 출력하도록 하는 함수
select empno, ename, sal, rank() over (order by sal desc) '순위' from emp;
notion image

2. Dense_rank

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

3. Row_number

‼️

row_number()

중복 값들에 대해서도 순차적인 순위를 표시하도록 출력하는 함수
순서보단 넘버링에 가깝다
select empno, ename, sal, row_number() over (order by sal desc) '순위' from emp;
notion image
 

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

  1. 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;
notion image
‼️
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;
notion image
  1. 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;
notion image

4. Set

‼️

@rownum

Set을 활용해 새로운 칼럼을 만들어주고, 새로운 변수를 할당
set @rownum := 0; -- 변수 select ename, sal, @rownum := @rownum+1 from ( select ename, sal from emp order by sal desc ) nemp;
notion image
 

5. Partition

‼️

partition by

논리적으로 하나의 테이블이지만 실제로는 여러 개의 테이블로 분리해서 관리하는 기능
마치 동일한 형식의 한 개의 테이블을 사용하고 있는 것 같지만, 실제적으로는 여러 개의 물리 파일로 데이터들을 분할하여 보관하는 방식
목적: 주로 대용량의 테이블을 물리적으로 여러 개의 소규모 테이블로 분산
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;
notion image

Rollup (집계함수)

‼️
Group by를 잘 이해하기
Job 기준 정렬
select job, deptno, avg(sal) sal, count(*) cnt from emp group by job, deptno with rollup;
notion image
Deptno 기준 정렬
select deptno,job, avg(sal) sal, count(*) cnt from emp group by deptno, job with rollup;
notion image
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;
notion image

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;
notion image

Ex. 달력만들기

select * from cal;
notion image
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;
notion image
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;
notion image
select day, num_day, 1 cnt from cal2;
notion image
‼️
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;
notion image
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;
notion image
Share article

parangdajavous