[DB] 분석 함수

개발/database

[DB] 분석 함수

yun000 2024. 12. 17. 12:41

<분석 함수>

=SQL 튜닝으로 성능 향상. 쿼리를 간단하게 하여 가독성을 향상

구조

over(partition by ___
	order by ___ [ASC|DESC]
    Windowing)

🍏 순위 함수

RANK	우선순위 결정(중복 순위 계산함)
DENSE_RANK	우선순위 결정(중복 순위 계산 안함)
ROW_NUMBER	모든 행 번호 제공

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
oreder by sal_number;

🍏 비율 함수

CUME_DIST	현재 행 이하 값들 누적 백분위. (값이 현재행 이하인 것 개수 /전체 행 수)
PERCENT_RANK	그룹내 순위를 백분위로 표현 (rank-1)/(total_rows-1)

select salary,
    round(cume_dist() over(order by salary ASC),4) sal_cume_dist,
    round(percent_Rank() over(order by salary ASC),4) sal_pct_rank
from employees
order by salary;

RATIO_TO_REPORT

전체 합에 대한 현재 행 비율 계산 (현재행값/전체합)

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

NTILE

전체 데이터를 n개의 구간으로 나누어 표시.

ex) salary를 기준으로 10개의 구간으로 나누어라

select employee_id, department_id, salary, 
    NTILE(10) OVER(order by salary desc) as sal_quart_title
from employees
where department_id=50;

🍏 순서 함수

LAG(column,n)	지정한 행 이전의 행 가져온다. ex) LAG(salary, 2,0) 현재 행-2 번째 행의 값을 가져온다. 값 없으면 0 출력
LEAD(column,n)	지정한 행 이후의 행 가져온다. ex) LEAD(hire_date,1,-1) 현재 행+1 번째 행 값을 가져온다. 값 없으면 -1 출력

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

LISTAGG

그룹 내의 내용을 한줄로 출력

ex) 부서별 사원의 이름을 출력하기

select department_id,
    LISTAGG(first_name,',') within group(order by hire_date) as names
from employees
group by department_id;

<윈도우 절>

= 행과 행간의 관계 정의, 비교, 연산

윈도우절 구조

rows=물리적 결과 행 기준

range=논리적 범위 지정

BETWEEN A AND B = window의 끝과 시작 지정

UNBOUNDED PRECEDING = 첫번째 행에서 윈도우 시작

UNBOUNDED FOLLOWING = 윈도우 마지막 위치는 마지막 행

CURRENT ROW=현재행

윈도우 절 함수

순위	rank, dense_rank, row_number
집계	sum, max,min,avg,count
순서	lag,lead, first_value, last_value
그룹 내 비율	cume_dist, percent_rank, ratio_to_report, ntile

상관계수 = 두 변수가 얼마나 상관이 있는지 계산하는 수

윈도우절 예제

ex) 급여의 누적합 계산

hint- 부서아이디로 분할 후 급여를 오름차순 정렬하고 윈도우 영역을 각 분할의 첫 행부터 현재 행까지 설정

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

<선형 회귀 함수>

=어떤 변수가 다른 변수에 어느정도의 영향을 주는지 계산하는 것

독립 변수=영향을 주는 변수 x (x가 여러개일 경우 X로 표시. column이 여러개인 것)

종속 변수=영향 받는 변수 y

결정계수=weight, bias가 얼마나 데이터를 잘 표현하지는 나타내는 수치 (R^2)

+) 둘 다 x,y가 null이 아니여야지 계산이 가능하다

REGR_AVGX(y,x) = x에 대한 평균 , null값은 무시

REGR_AVGY(y,x) = y에 대한 평균, null값은 무시

ex) 모든 사원의 급여 평균, 보너스 받는 사람의 급여 평균

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

REGR_COUNT(y,x) = y,x둘 다 null이 아닌 경우 개수를 센다.

-- 회귀 모델에 사용할 수 있는지 확인 용도

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

REGR_SLOPE(y,x)

=weight 계산(회귀 직선 기울기)

COVAR_POP(y,x) / VAR_POP(x) == 공분산/분산

ex)세일즈 부서 사원들의 근물일에 따른 급여 직무별 기울기

REGR_INTERCEPT(y,x)

=bias 반환(회귀 직선 y절편)

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

REGR_R2(y,x)

=회귀 분석 결정계수 반환

y=2.52*wd -1661.62

<피벗 테이블>

OLAP데이터, long format data

- 주로 DB로 저장하는 방식, 분석하기 편하다

OLTP 데이터, wide format data

-주로 엑셀에 저장하는 방식, 읽기 편하다

🍏PIVOT

=long format data를 wide format data로 변경. 엑셀에서 보기 좋은 테이블로 변경

PIVOT함수 구조

select ________
from tables, 
PIVOT
( 
	aggregate_function(value column) 
        for variable colmn 
        in(___,___,____)
)
where ____
order by _____;

PIVOT 예제

select * from sales_log
pivot
(
    sum(quantity)
    for week_day in ('SALES_MON','SALES_TUE','SALES_WED','SALES_THU','SALES_FRI')
)
order by employee_id,week_id;

🍏UNPIVOT

=PIVOT의 반대

열이름의 값이 quantity

UNPIVOT 예제

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));