[SQL] 5. 분석 함수

분석 함수

- 개발 생산성 높임-빨리 응답

- SQL 튜닝으로 성능을 높임

- 쿼리를 간단하게 하여 가독성 높임

- DW 분야에 사용

- SELECT절에서 사용

Anlytic_Funtion
	OVER ( PARTITION BY column_list
    		ORDER BY column_list [ASC|DESC]
            Windowing)

* PARTITION BY : 열의 행을 그룹화. 분석 함수로 계산될 대상 행의 그룹을 지정.

* ORDER BY : 정렬 (가장 많이 사용). 파티션 안에서의 순서를 지정.

* Windowing : 윈도우 절. 파티션으로 분할 그룹화 된 그룹에 대해 더 상세한 그룹으로 분할 할 때 사용

 

1. 순위 구하기 함수 - RANK, DENSE_RANK, ROW_NUMBER

RNAK	순위 결정(중복순위 계산o) 같은 값이면 1,2,2,4위 순
DENSE_RANK	순위 결정(중복순위 계산x) 같은 값이면 1,2,2,3,4위 순
ROW_NUMBER	조건을 만족하는 모든 행의 번호를 제공 - select절에서 순서를 매기기 때문에 - order by로 정렬을 하면 순서가 바뀔 수 잇음 - DB에 데이터가 물리적으로 저장된 순서에 따라 다름.

select employee_id, department_id, salary,
rank() over(order by salary desc) sal_rank,
dense_rank() over(order by salary desc) sal_dense_rank,
row_number() over(order by salary desc) sal_number
from employees;

 

2. 가상순위와 분포 - CUME_DIST, PERCENT_RANK

CUME_DIST	최대값 1을 기준으로 분산된 값 제공. 첫번째 0이 아님.
PERCENT_RANK	최대값 1을 기준으로 백분율 값 제공. 첫번째는 0

select employee_id, department_id,salary,
cume_dist() over(order by salary desc) sal_cume_dist,
percent_rank() over(order by salary desc) sal_pct_rank
from employees;

 

3. 비율 함수 - RATIO_TO_REPORT

RATIO_TO_REPORT

해당 컬럼 값의 총 값 중 차지하는 비율을 백분율로 표현. 소수점으로 제공

select first_name, salary,
round(ratio_to_report(salary) over(), 4) as salary_ratio
from employees
where job_id ='IT_PROG';

 

4. 분배 함수 - NTILE

NTILE

전체 데이터의 분포를 n개의 구간으로 나누어 표시 만약 45명을 10개씩 나누면 4.5이므로 앞쪽부터 하나씩 더 가져감

select employee_id, department_id, salary,
ntile(10) over(order by salary desc) sal_quart_tile
from employees
where department_id='50';

 

5. LAG, LEAD

- 윈도우 구간 지정

LAG	윈도우의 이전 n번째 행의 값을 가져옴 LAG(salary,2,0) : salary에서 현재 행 이전 2번째 행의 값을 가져오고 값이 없으면 0 출력.
LEAD	윈도우의 이후 n번째 행의 값을 가져올 수 있음 - LEAD(hire_date, 1,0) : 현재 행 기준 다음 입사일 값을 가져오고 없으면 0 출력

select employee_id,
lag(salary,1,0) over(order by salary) as lower_sal,
salary,
lead(salary,1,0) over(order by salary) as higher_sal
from employees
order by salary;

6. LISTAGG

LISTAGG(expression, 'delimiter')
	WITHIN GROUP(ORDER BY column)

LISTAGG

함수의 expression을 delimiter로 연결해 여러 행을 하나의 행으로 변환해 출력하는 함수

select department_id, first_name
from employees
group by department_id;
--에러

select department_id, 
listagg(first_name,', ') within group(order by hire_date)
from employees
group by department_id;

 

 

윈도우 절

- 파티션으로 분할된 그룹에 대해 다시 그룹을 만드는 역할

{rows|range}
	{BETWEEN {
    	UNBOUNDED PRECEDING
        | CURRENT ROW
        | value_expr{PRECEDING|FOLLOWING}
    }
    AND {
    	UNBOUNDED FOLLOWING
        | CURRENT ROW
        | value_expr{PRECEDING|FOLLOWING}
    }
   |{UNBOUNDED PRECEDING
   	| CURRENT ROW
   	| value_Expr PRECEDING
    	}
    }

순위(RANK)	RANK, DENSE_RANK, ROW_NUMBER
집계(AGGREGATE)	SUM, MAX, MIN, AVG, COUNT
순서(ORDER)	LAG, LEAD, FIRST_VALUE, LAST_VALUE
그룹 내 비율(RATIO)	CUME_DIST, PERCENT_RANK, RATIO_TO_REPORT, NTILE
통계분석(ANALYTICS)	CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP

rows	데이터를 물리적 위치로 구분해 모든 행을 한 행으로 인식
range	논리적으로 구성하며 값이 같으면 묶어서 한 행으로 인식

1. 윈도우 절 사용

select department_id, first_name, salary,
sum(salary) over(partition by department_id
order by salary
rows between unbounded preceding
and current row) as sum_rows
from employees;

select department_id, first_name, salary,
sum(salary) over(partition by department_id
order by salary
range between unbounded preceding
and current row) as sum_rows
from employees;

 

선형 회귀 함수

{REGR_SLOPE | REGR_INTERCEPT | REGR_COUNT | REGR_R2 |
REGR_AVGX | REGR_AVGY | REGR_SXX | REGR_SYY | REGR_SXY}
(y,x)
	[OVER (analytic_clause)]

 

 

1. REGR_AVGX(y,x), REGR_AVGY(y,x)

- 각 함수의 첫 번째 인수인 x,y가 null이면 계산에 포함하지 않는다.

select avg(salary), regr_avgx(commission_pct, salary), regr_avgy(commission_pct, salary)
from employees;

 

 

2. REGR_COUNT(y,x)

- 두 인수의 값이 not null인 쌍 수를 계산

select distinct department_id,
regr_count(manager_id, department_id)
over (partition by department_id) "REGR_COUNT"
from employees
order by department_id;

 

3. REGR_SLOPE(y,x), REGR_INTERCEPT(y,x)

* slope: 회귀 직선의 기울기 측정(weight)

- COVAR_POP(y,x) / VAR_POP(x)

*intercept : 회귀 직선의 y절편(bias)

- AVG(y) - REGR_SLOPE(y,x) * AVG(x)

select
job_id, employee_id, salary,
round(sysdate-hire_date) "working_day",
round(regr_slope(salary, sysdate-hire_date)
over(partition by job_id),2) "regr_slope",
round(regr_intercept(salary, sysdate-hire_date)
over(partition by job_id),2) "regr_intercept"
from employees
where department_id = 80
order by job_id, employee_id;

 

4. REGR_R2

- 회귀 분석에 대한 결정 계수(얼마나 잘 표현했냐)를 반환

- 적합도

- 종속 변수와 독립변수 사이에 상관관계가 높을수록 1에 가까워짐.

* 반환값

- VAR_POP(x) = 0 이면 null

- VAR_POP(y) =0 이고 VAR_POP(x) !=0이면 1

- VAR_POP(y) > 0 이고 VAR(x) !=0 이면 POWER(CORR(y,x),2)

select
distinct
job_id,
round(regr_slope(salary, sysdate-hire_date)
over(partition by job_id),2) "regr_slope",
round(regr_intercept(salary, sysdate-hire_date)
over(partition by job_id),2) "regr_intercept",
round(regr_r2(salary, sysdate-hire_date)
over(partition by job_id),2) "regr_r2"
from employees
where department_id=80;

=> 두 회귀모형 중 SA_MAN이 SA_REP보다 유용성이 더 높다고 판단할 수 있다.

 

피벗 테이블

 

1. 관계형 데이터베이스 형식과 스프레드시트 형식

create table sale_log(
employee_id number(6),
week_id number(2),
week_day varchar(10),
quantity number(8,2)
);

insert into sale_log values(1101, 4 , 'sales_mon', 100);
insert into sale_log values(1101, 4 , 'sales_tue', 150);
insert into sale_log values(1101, 4 , 'sales_wed', 80);
insert into sale_log values(1101, 4 , 'sales_thu', 60);
insert into sale_log values(1101, 4 , 'sales_fri', 120);

insert into sale_log values(1102, 5 , 'sales_mon', 300);
insert into sale_log values(1102, 5 , 'sales_tue', 300);
insert into sale_log values(1102, 5 , 'sales_wed', 230);
insert into sale_log values(1102, 5 , 'sales_thu', 120);
insert into sale_log values(1102, 5 , 'sales_fri', 150);
commit;
select * from sale_log;

create table sales(
employee_id number(6),
week_id number(2),
sales_mon number(8,2),
sales_tue number(8,2),
sales_wed number(8,2),
sales_thu number(8,2),
sales_fri number(8,2)
);

insert into sales values(1101,4,100,150,80,60,120);
insert into sales values(1102,5,300,300,230,120,150);
commit;
select * from sales;

 

2. PIVOT

SELECT col1, col2, ...var_col1, ... var_coln
FROM tables
PIVOT
(
	aggregate_function(value_column)
    FOR variable_column
    IN(expr1 [[AS] var_col1], ... , expr_n [[AS] var_coln])
)
WHERE conditions
ORDER BY expression [ASC | DESC];

select * from sale_log
pivot(
sum(quantity)
for week_day in(
'sales_mon' as sales_mon,
'sales_tue' as sales_tue,
'sales_wed' as sales_wed,
'sales_thu' as sales_thu,
'sales_fri' as sales_fri
)
)
order by employee_id, week_id;

 

3. UNPIVOT

SELECT col1, ... , variable_column, value_column
FROM tables
UNPIVOT(
	value_column
    FOR variable_column
    IN(expr1, expr2, ..., expr_n)
)
WHERE conditions
ORDER BY expression [ASC | DESC];

select employee_id, week_id, week_day, quantity
from sales
unpivot
(
quantity
for week_day
in(sales_mon, sales_tue, sales_wed, sales_thu, sales_fri)
);