SQL : 5,6,7 분석함수, 선형회귀 함수, JOIN

김다린·2024년 4월 1일
sql

SQL

목록 보기
1/2
post-thumbnail

오늘은 sql 내의 분석함수 rank()와 선형회귀 REGR_SLOPE(y,x) 등을 정리하였습니다

5-01. 분석함수

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

    함수설명
    RANK해당 값에 대한 순위를 계산합니다. RANK는 중복순위를 계산합니다.
    DENSE_RANK해당 값에 대한 순위를 계산합니다. DENSE_RANK는 중복순위를 계산하지 않습니다. 즉 2등이 2명이더라도 다음 순위는 3등임
    ROW_NUMBER조건을 만족하는 모든 행의 번호를 제공합니다. 일련번호를 생성

  2. 누적분포와 백분율 순위- CUME_DIST, PERCENT_RANK

    함수설명
    CUME_DIST최댓값 1을 기준으로 누적분포(cumulative distribution)를 계산합니다. 누적분포는 최솟값과 최댓값 사이의 상대적인 위치를 의미합니다. 예를 들면 위 예에서는 첫 번째 값은 1/행수, 두 번째 값은 2/행수
    PERCENT_RANK최댓값 1을 기준으로 데이터 집합에서 특정 값의 백분율 순위(percentile

    ranking)를 제공합니다. CUME_DIST와 비슷하지만, 식이 다릅니다. 첫 번
    째 위치가 0부터 시작하고 두 번째 row부터의 위치는 (row의 rank-1) /
    (전체 row 개수 -1) |

  3. 비율 함수 – RATIO_TO_REPORT

    함수설명
    RATIO_TO_REPORT해당 열의 백분율을 소수점으로 제공합니다. 이를 이용하면 그룹 내에서 해당 값의 백분율을 구할 수 있습니다. 결과값은 0보다 크고 1.0보다 작거나 같은 값이 출력됩니다.
    SQL> SELECT	first_name,	salary,	
2 		ROUND(RATIO_TO_REPORT(salary)	OVER	(),	4)	AS	salary_ratio
3 FROM			employees
4 WHERE		job_id='IT_PROG';

  4. 분배 함수 – NTILE

    함수설명
    NTILE(n)전체 데이터의 분포를 n개의 구간으로 나누어 표시해 줍니다. 만일 row가 균등하게 나누어지지 않으면 위에서부터 추가

  5. LAG, LEAD(over (order by column) 으로 다른 행 참조)

    함수설명
    LAG(column, n, [init_value])윈도우의 이전 n번째 행의 값을 가져올 수 있습니다. LAG(salary, 2, 0) : 현재 행 이전 2번째 행의 값을 가져오고 값이 없으면 0을 출력
    LEAD(column, n, [init_value])윈도우의 이후 n번째 행의 값을 가져올 수 있습니다. LEAD(hire_date, 1) : 다음 입사일 값을 가져옵니다. LEAD(hire_date, 1, 0) : 다음 입사일 값을 가져옵니다. 없으면 0을출력

  6. LISTAGG

    함수설명
    LISTAGG함수의 expression을 delimiter로 연결해서 여러 행을 하나의 행으로 변환해 출력하는 함수
    • 예시
      select department_id,
    listagg(first_name,', ') within group(order by hire_date) as name
from employees
group by department_id;
      Untitled

5-02. 선형회귀 함수

REGR_SLOPE(y,x) REGR_INTERCEPT(y,x) REGR_R2(y,x)를 가장 많이 사용함

R2 : corr(x,y)^2가 상관계수가 됨, 결정계수가 높으면 정확도 올라감

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

    함수설명
    REGR_AVGX(y, x)두 개의 숫자 입력을 사용하여 회귀 직선의 두 번째인수(독립 변수)의 평균을 계산합니다. 인수가 Null인 값 쌍을 완전히 무시합니다. y가 Null이 아니라면 x의 평균을 계산하므로 AVG(x)와 같습니다. y가 Null일 경우 x는 평균에서 제외
    REGR_AVGY(y, x)두 개의 숫자 입력을 사용하여 회귀 직선의 첫 번째

    인수(종속 변수)의 평균을 계산합니다. 인수가 Null인 값 쌍을 완전히 무
    시합니다. x가 Null이 아니라면 y의 평균을 계산하므로 AVG(y)와 같습니
    다. x가 Null일 경우 y는 평균에서 제외 |

  2. REGR_COUNT(y, x)

    함수설명
    REGR_COUNT(y, x)두 인수의 값이 NOT NULL인 쌍 수를 계산

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

    함수설명
    REGR_SLOPE(y, x)회귀 직선의 기울기를 측정하는 데 사용됩니다. 계산 공식은 COVAR_POP(y, x) / VAR_POP(x)
    REGR_INTERCEPT(y, x)회귀 직선의 y 절편을 반환합니다. 계산 공식
    은 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;
      Untitled SA_MAN의 근무일과 급여에 관한 방정식 y = 2.34*x + 1929.32이므로 SA_MAN의 경우 입사 후 5000일째 되는 날의 급여는 2.34*5000 +1929.32 = $13,629.32

  4. REGR_R2(y, x)

    함수설명
    REGR_R2(y, x)회귀 분석에 관한 결정 계수(R-제곱 또는 적합도)를 반환합니다. 두 개의 숫자 입력을 사용하고 회귀 직선이 얼마나 잘 맞는지 나타내는 숫자 출력을 반환합니다. 반환 값은 아래 표시된 조건에 따라 결정됩니다.

    VAR_POP(x) = 0이면 NULL
    VAR_POP(y) = 0이고 VAR_POP(x) != 0이면 1
    VAR_POP(y) > 0이고 VAR_POP(x) != 0이면 POWER(CORR(y,x), 2)
    종속 변수와 독립 변수 사이에 상관관계가 높을수록 1에 가까워집니다. |

    • 예시
      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;
      Untitled

5-03. 피벗테이블

OLAP 데이터, 롤 포맷

: 로그(기록)를 저장하는 경우가 많음

Untitled

OLTP 데이터, 와이드 포맷(wide format)

: 알아보기 편하지만 분석하기 어려움

Untitled

Untitled

Pivot 테이블

SELECT	column1,	...,	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	];

aggregate_function 집계 함수가 필요함

value column : 피벗할 값이 들어오는 열

variable column : column값이 피봇되어 만들어짐

  • 예시
    select *
from sales
pivot(
    sum(quantity)
    for weekday 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;

Unpivot 테이블

SELECT	column1,	...,	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)
);
    Untitled

6-00. 조인

하나 이상의 테이블로부터 데이터를 질의하기 위해서 조인을 사용, WHERE절에 조인 조건을 작성함. 하나 이상의 테이블에 똑같은 열 이름이 있을 때 열 이름 앞에 테이블 이름을 붙임.

오라클 조인과 ANSI 조인 구문이 있다.

ORACLE JOIN

Oracle 조인설명
Equi 조인서로 다른 테이블에 공통된 열이 존재할 때 사용합니다. 동등 연산자(=)를 이용해서 조인 조건을 설정합니다.
Non-Equi 조인서로 다른 테이블에 공통된 열이 존재하지 않을 때 사용합니다. 동등 연산자가 아닌 연산자를 이용해서 조인 조건을 설정합니다.
Self 조인같은 테이블을 이용해서 조인하는 것을 의미합니다. 같은 테이블의 서로 다른 두 열을 이용합니다. 예를 들면 사원의 매니저 이름을 알고 싶을 때 사용합니다.
Outer 조인어느 한쪽에 데이터가 존재하지 않을 때(NULL) 사용

1. CARTESIAN PRODUCT

  • 조인 조건이 생략, 잘못된 경우
  • 첫번째 테이블의 모든 행이 두번째 테이블의 모든 행과 조인되는 경우

Untitled

2. EQUI JOIN

2개 이상의 테이블이 공통되는 열에 의해 논리적으로 결합되는 조인기법이다. WHERE절에 사용된 열들이 동등 연산자(equal, =)에 의해 비교됨.

SELECT e.first_name, e.department_id,	d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id;

3. SELF JOIN

셀프 조인은 하나의 테이블을 논리적으로 두 개 테이블로 보고 조인을 합니다. 사원의 이름
과 매니저의 이름을 알기를 원한다면 사원 테이블(E)과 매니저 테이블(M)이 있는 것으로
간주하고 쿼리를 작성

SELECT e.first_name AS employee_name,	
				m.first_name AS manager_name
FROM employees e,	employees	m
WHERE	e.manager_id=m.employee_id	AND	e.employee_id=103;

4. NON-EQUI JOIN

2개 이상의 테이블이 조인될 때 WHERE 절에 의해 공통되는 열에 의한 결합이 발생하지 않은 경우

공통되는 열이 정의되지 않았다는 것은 테이블 전체 행의 전체 열이 조인에 사용된다는 것을 의미하기 때문에 2개 이상의 테이블에 존재하는 모든 데이터가 검색결과가 되는 경우

SELECT e.first_name, e.salary, j.job_title
FROM employees e, jobs j
WHERE e.salary
BETWEEN	j.min_salary AND j.max_salary
ORDER	BY e.first_name;

5. OUTER JOIN

NULL 또는 참조되지 않는 행들도 보기 위해서 Outer 조인을 사용함

Outer 조인 연산자는 더하기 기호(+) 

SELECT table.column,table.column
FROM table1, table2
WHERE table1.column = table2.column(+);--LEFT JOIN
SELECT table.column,table.column
FROM table1, table2
WHERE table1.column(+) = table2.column;--RIGHT JOIN

ANSI JOIN

1. CROSS JOIN

크로스 조인은 두 개의 테이블에 대한 Cartesian Product와 같은 결과를 출력합니다. 의도
적으로 데이터를 복제하기 위해 카티션 프로덕트를 사용한 것이 아니라면, 크로스 조인은
사용하지 않는 것이 바람직

2. NATURAL JOIN

자동으로 두 테이블에서 같은 이름을 가진 모든 열에 Equi 조인을 수행합니다. 이때, 조인 열
들은 같은 데이터 유형이어야 하며, Alias나 테이블 명과 같은 접두사를 붙일 수 없습니다.

SELECT table1.column1,	table2.column2
FROM table1
NATURAL	JOIN table2;

3. USING JOIN

Natural 조인에서는 자동으로 이름이 일치되는 모든 열에 대해 조인이 이루어지지만, USING 절을 이용하면 원하는 열에 대해서만 선택적으로 Equi 조인을 할 수 있습니다. 만일 여러 개의 열이 이름은 같지만, 데이터 타입이 모두 일치하지 않거나, 몇 개의 열만 선 택적으로 조인 조건에 사용하고자 할 때는 Using 절을 이용하여 Equi 조인에 사용될 열들을 지정할 수 있음.

  • 예시
    SELECT first_name, department_name
FROM employees	
JOIN departments	
USING (department_id);

4. ON JOIN

ON 절을 이용하면 JOIN 이후에 논리 연산과 서브쿼리와 같은 추가 서술을 할 수 있습니
다. Natural 조인과 달리 ON 조인은, 임의의 조인 조건을 지정하거나, 이름이 다른 열끼리
조인 조건으로 사용하거나, 조인할 열을 명시하기 위해서 사용

6장 연습문제

-- 184p 연습문제 1번, 오라클 조인
select e.first_name, d.department_name, l.city
from employees e, departments d, locations l
where e.first_name='John'
    and e.department_id=d.department_id
    and d.location_id=l.location_id;
    
-- 연습문제 1번, 안시조인
select e.first_name, d.department_name, l.city
from employees e
join departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
where e.first_name='John';
--연습문제 2번, self join 안시조인사용하기
select e.employee_id, e.first_name, e.salary, m.first_name, d.department_name
from employees e
join employees m
on e.manager_id=m.employee_id
join departments d
on m.department_id=d.department_id
where e.employee_id=103;
--연습문제 3번,오라클 조인
select e.employee_id, e.first_name, e.salary, m.first_name, m.salary, d.department_name
from employees e, employees m, departments d
where e.department_id=90
    and e.manager_id=m.employee_id(+)
    and m.department_id=d.department_id(+);
    
--연습문제 3번, 안시조인
select e.employee_id, e.first_name, e.salary, m.first_name, m.salary, d.department_name
from employees e
left join employees m
on e.manager_id=m.employee_id
left join departments d
on m.department_id=d.department_id
where e.department_id=90;
--연습문제 4번
select e.employee_id, l.city
from employees e
join departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
where e.employee_id=103;
--연습문제 5번
select l.city as "Department Location", j.job_title as "Manager's Job"
from employees e
join departments d on e.department_id=d.department_id
join locations l on d.location_id=l.location_id
join employees m on e.manager_id=m.employee_id
join jobs j on m.job_id=j.job_id
where e.employee_id=103;
--연습문제 6번
select e.employee_id, e.first_name, e.last_name, e.phone_number, e.hire_date, 
        j.job_title, e.salary, e.commission_pct, m.first_name as "MANAGER_FIRST_NAME",
        m.last_name as "MANAGER_LAST_NAME", d.department_name as "DEPARTMENT_NAME"
from employees e
left join departments d on e.department_id=d.department_id
left join employees m on d.department_id=m.department_id
join jobs j on m.job_id=j.job_id;

7-00 서브쿼리

다른 SELECT 문장의 절에 내장된 SELECT 문장

select(스칼라 서브쿼리(단일 값으로 바로 출력이 되기 때문)), from (인라인 뷰), where, having, order by, insert 문의 values절, update문의 set절, create table문의 as절 (값으로 사용되는 것들)

서브쿼리 사용시 지켜야할 사항

서브쿼리는 괄호로 둘러싸여야 함(CITAS제외)
서브쿼리는 비교 연산자의 오른쪽에 있어야함
서브쿼리에서는 두 종류의 비교 연산자를 사용함.
서브쿼리는 반드시 한개 행 혹은 0개행을 반환해야함

1. 다중행 서브쿼리

서브쿼리의 결과가 2개 행 이상일 경우 다중행 서브쿼리

IN목록의 어떤 값과 같은지 확인합니다.
EXISTS값이 있는지를 확인합니다. EXISTS 뒤에는 서브쿼리만 올 수 있습니다. EXISTS 뒤에는 반드시 서브쿼리만 와야함
ANY, SOME비교연산자와 같이 사용되어 값을 서브쿼리에 의해 반환된 각각의 값과 비교합니다. 하나라도 만족하면 됩니다
ALL비교 연산자와 같이 사용되어 값을 서브쿼리에 의해 반환된 모든 값과 비교합니다. 모든 값과 비교해서 만족해야 함.
ALL과 ANY의 차이점< ANY :가장 큰 값보다 작으면 됩니다. > ANY : 가장 작은 값보다 크면 됩니다. < ALL : 가장 작은 값보다 작으면 됩니다. >ALL : 가장 큰 값보다 크면 됩니다. = ANY : IN과 같은 역할을 함

2. 상호연관 서브쿼리

서브쿼리가 메인쿼리의 값을 이용하고, 그렇게 구해진 서브쿼리의값을 다시 메인쿼리가 이용하게 됩니다.

한 개의 행을 비교할 때마다 결과가 메인쿼리로 반환됩니다.

한 행을 처리할 때마다 서브쿼리로 주고 값을 처리한 후에 그 결과를 다시 메인쿼리로전달하는 방식으로 내부적으로 성능이 저하됩니다.

SELECT first_name, salary
FROM employees	a
WHERE salary > (SELECT	avg(salary)
FROM employees b
WHERE b.department_id=a.department_id);

3. 스칼라 서브쿼리

join과 스칼라서브쿼리 비교(실행계획 F10키로 확인)

select first_name , department_name
from employees 
left join departments 
on employees.department_id=departments.department_id;
select first_name, (select department_name from department d 
										where d.department_id=e.department_id) dept_name
from employees d;

스칼라 서브쿼리가 상대적으로 cost가 적게 든다.

4. 인라인 뷰

FROM 절에 서브쿼리가 온 것을 말합니다

뷰도 하나의 독립적인 SELECT 구문이므로 FROM 절에 사용하는 서브쿼리도 하나의 뷰로 볼 수 있습니다

SELECT row_number, first_name,	salary
FROM (SELECT	first_name,	salary,
				row_number()	OVER	(ORDER	BY	salary	DESC)	ASrow_number
				FROM	employees)
WHERE	row_number BETWEEN	1	AND	10;

5. 3중 쿼리와 Top-N 쿼리

rownum을 서브쿼리를 사용하여 할경우 첫번째 행부터 조회해야함 (between 6 and 10 불가능)

→삼중쿼리를 만들어야하나 효율이 떨어짐 →top-n 사용 or row_number()

SELECT	rnum,	first_name,	salary
FROM		(SELECT	first_name,	salary,	rownum	AS	rnum
				FROM	(SELECT	first_name,	salary
							 FROM employees
						   ORDER	BY salary DESC)
				)
WHERE	rnum	BETWEEN	11	AND	20;

3중 쿼리 구문은 서브쿼리가 다른 서브쿼리를 가질 수 있다는 것을 보여주는 예.

위 구문은 분석함수를 이용해서 다음 구문처럼 바꿀 수 있다

SELECT	row_number,	first_name,	salary
FROM		(SELECT	first_name,	salary,
					row_number() OVER (ORDER	BY salary	DESC)	AS row_number
					FROM employees)
WHERE		row_number	BETWEEN	11	AND	20;

Oracle 12c 버전부터 OFFSET과 FETCH 절을 사용해서 Top-N 쿼리가능

profile
김다린
한걸음씩 뚜벅뚜벅
다음 포스트

SQL : 7,8,9 계층형 쿼리, 데이터 조작(DML), 트랜잭션

0개의 댓글