[SQL] SQL Cookbook - 07. 숫자 작업

 

1. 평균 계산하기

부서 평균 급여 계산하기 (부서별)
AVG : 

-- 전체
select avg(sal) as avg_sal
  from emp
  
-- 부서별
 select deptno, avg(sal) as avg_sal
  from emp
 group by deptno

 

 

2. 열에서 최댓값, 최솟값 찾기

주어진 열에서 가장 높은 값과 가장 낮은 값 찾기

MIN : 

MAX : 

-- 전체
select min(sal) as min_sal, max(sal) as max_sal
  from emp
  
-- 부서별
select deptno, min(sal) min_sal, max(sal) max_sal
  from emp
 group by deptno

 

 

3. 열의 값 집계하기

열의 모든 값의 합계를 계산

SUM : 

-- 전체
select sum(sal)
  from emp
  
-- 부서별
select deptno, sum(sal) total_dept
  from emp
 group by deptno

 

 

4. 테이블의 행 수 계산하기

테이블의 행 수를 세거나 열의 값 수를 알아보기

COUNT : 

-- 전체
select count(*)
  from emp
  
-- 부서별
select deptno, count(*)
  from emp
 group by deptno

 

 

5. 열의 값 세어보기

열에 있는 NULL이 아닌 값의 수를 알아보기

- count(*)을 하면 NULL을 포함한 모든 값이 계산되지만 count(comm) 특정 컬럼을 지정하면 NULL이 아닌 값만 계산된다.

select count(comm)
  from emp

 

 

6. 누계 생성하기

열에 있는 값의 누계 계산하기

OVER : 윈도우 함수가 적용될 데이터의 범위를 정의하는 키워드로 윈도우 함수를 어떤 순서로 정렬하고, 어떤 행들을 포함시킬 것인지를 명시하는 역할을 한다.

즉, sal을 기준으로 정렬된 윈도우에서 sal의 누적 합을 계산한다.

select ename, sal,
        sum(sal) over (order by sal, empno) as running_total
  from emp
  order by 2

 

 

7. 누적곱 생성하기

윈도우 함수 SUM OVER를 사용하고 로그를 추가하여 곱셈을 시뮬레이션 할 수 있다.

EXP : 숫자의 지수 함수를 계산하는 함수이다. (e^x)

LN : 숫자의 자연로그를 계산하는 함수이다.

*SUM 연산자를 OVER와 같이 윈도우 함수로 사용하면 각 행에 대한 이전 행까지의 합을 나타낼 수 있다.

select empno, ename, sal,
        exp(sum(ln(sal)) over(order by sal, empno)) as run_prod
from emp
where deptno = 10

 

 

8. 일련의 값 평활화 하기

월별 판매 수치와 같은 시간에 따라 나타나는 일련의 값을 더 잘 식별하기 위해 가중실행평균과 같은 단순 평활을 구현한다.

SELECT date1, sales, lag(sales, 1) over(order by date1) as salesLagOne, lag(sales, 2) over(order by date1) as salesLagTwo,
	(sales
	+ (lag(sales, 1) over(order by date1)) 
	+ lag(sales, 2) over(order by date1)) /3 as MovingAverage
FROM sales

 

 

9. 최빈값 계산하기

주어진 데이터 집합에서 가장 자주 나타나는 요소 찾기. 

윈도우 함수 DENSE_RANK를 사용하여 급여 횟수의 순위를 매겨 쉽게 최빈값을 추출할 수 있다.

DENSE_RANK : 

select sal
from (
  select sal, dense_rank()over( order by cnt desc) as rnk
  from (
   select sal, count(*) as cnt
   from emp
   where deptno = 20
   group by sal
       ) x
      ) y
where rnk = 1

 

10. 중앙값 계산하기

정렬된 요소 집합의 중간에 속한 값 찾기.

윈도우 함수 PERCENTILE_CONT를 사용하여 중앙값을 찾을 수 있다.

PERCENTILE_CONT : 주어진 백분위 수에 해당하는 값을 계산한다. ex > percentile_cont(0.5) => 50%

within group(order by ?) 함수는 위의 윈도우 함수와 같이 사용되며, 데이터를 정렬하고 백분위 수를 계산하기 위해 순서를 정의한다.

 

select percentile_cont(0.5) within group( order by sal)
from emp
where deptno = 20

 

11. 총계에서의 백분율 알아내기

특정 열의 값이 총계에 대해 나타내는 백분율 확인하기.

모든 급여에서 몇 퍼센트가 DEPTNO 10의 급여인지, 즉 DEPTNO 10의 급여가 총계에서 차지하는 백분율이 얼마인지?

- DEPTNO 10의 급여 합계를 모든 급여의 합산한 값으로 나눈다.

select( sum(
	  case when deptno = 10 then sal end) / sum(sal)
      	  ) * 100 as pct
from emp

 

 

 

reference.

[SQL Cookbook] - 한빛미디어