22일차 - 분석함수 & join

은채의 성장통·2025년 6월 26일

oracle sql

KCC정보통신

목록 보기

25/30

노션정리 링크

OVER가 들어가는 것을 알아야함

1. 순위 관련 함수: `RANK()`, `DENSE_RANK()`, `ROW_NUMBER()`

함수	설명	예제 코드
`RANK()`	동일한 값은 동일 순위, 건너뛰는 순위 부여	`RANK() OVER (ORDER BY salary DESC)`
`DENSE_RANK()`	동일한 값은 동일 순위, 순위 건너뛰지 않음	`DENSE_RANK() OVER (ORDER BY salary DESC)`
`ROW_NUMBER()`	중복 없이 고유 번호 부여 (정렬 기준 순서대로)	`ROW_NUMBER() OVER (ORDER BY salary DESC)`

예시 실행 결과:

이름	salary	rank	dense_rank	row_number
Alice	9000	1	1	1
Bob	9000	1	1	2
Carla	8500	3	2	3
Daniel	8500	3	2	4
Evan	7000	5	3	5

2. 분포 관련 함수: `CUME_DIST()`, `PERCENT_RANK()`

함수	설명	예제 코드
`CUME_DIST()`	현재 행 이하의 비율 반환 (누적 분포)	`CUME_DIST() OVER (ORDER BY salary DESC)`
`PERCENT_RANK()`	백분위 순위 계산	`PERCENT_RANK() OVER (ORDER BY salary DESC)`

예시 실행 결과:

이름	salary	cume_dist	percent_rank
Alice	9000	0.4	0.0
Bob	9000	0.4	0.0
Carla	8500	0.8	0.5
Daniel	8500	0.8	0.5
Evan	7000	1.0	1.0

3. 비율 함수: `RATIO_TO_REPORT()`

함수	설명	예제 코드
`RATIO_TO_REPORT()`	전체 합계 대비 현재 값의 비율	`RATIO_TO_REPORT(salary) OVER ()`

예시 실행 결과:

이름	salary	비율
Alice	9000	0.277
Bob	9000	0.277
Carla	8500	0.262
Daniel	8500	0.262
Evan	7000	0.215

4. 분배 함수: `NTILE(n)`

함수	설명	예제 코드
`NTILE(n)`	데이터를 n개의 그룹으로 균등 분할	`NTILE(4) OVER (ORDER BY salary DESC)`

예시 실행 결과 (NTILE(4) 사용 시):

이름	salary	그룹번호
Alice	9000	1
Bob	9000	1
Carla	8500	2
Daniel	8500	2
Evan	7000	3

5. 선후행 함수: `LAG()`, `LEAD()`

함수	설명	예제 코드
`LAG()`	이전 행의 값을 참조	`LAG(salary) OVER (ORDER BY salary DESC)`
`LEAD()`	다음 행의 값을 참조	`LEAD(salary) OVER (ORDER BY salary DESC)`

예시 실행 결과:

이름	salary	prev_salary	next_salary
Alice	9000	NULL	9000
Bob	9000	9000	8500
Carla	8500	9000	8500
Daniel	8500	8500	7000
Evan	7000	8500	NULL

6. 문자열 집계 함수: `LISTAGG()`

함수	설명	예제 코드
`LISTAGG()`	그룹 내 문자열을 하나로 합쳐 나열	`LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name)`

예시 테이블: 직원 테이블 EMPLOYEES

부서	이름
HR	Alice
HR	Bob
HR	Carla
IT	Daniel
IT	Evan

예시 쿼리:

SELECT department,
       LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS employee_list
FROM employees
GROUP BY department;

예시 실행 결과:

department	employee_list
HR	Alice, Bob, Carla
IT	Daniel, Evan

7. 윈도우 절 (Window Clause)

개념	설명
윈도우 함수	각 행에 대해 지정한 윈도우 범위(행 집합) 내에서 계산을 수행하는 함수
`OVER()`	어떤 범위(Window)에서 함수를 적용할지 지정하는 절
`PARTITION BY`	그룹을 나누는 기준 (마치 `GROUP BY`처럼 작동하지만 결과는 각 행마다 유지됨)
`ORDER BY`	정렬 기준
`ROWS BETWEEN ... AND ...`	윈도우 프레임의 범위 지정 (→ 여기에 `UNBOUNDED`, `PRECEDING`, `CURRENT ROW` 등이 쓰임)

예시 테이블: 직원 급여

이름	부서	급여
Alice	HR	3000
Bob	HR	3500
Carla	HR	3200
Daniel	IT	4000
Evan	IT	4200

누적 급여 합계 구하기 (윈도우 함수 사용)

SELECT
  name,
  department,
  salary,
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY salary
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_salary
FROM employees;

예시 결과

이름	부서	급여	cumulative_salary
Alice	HR	3000	3000
Carla	HR	3200	6200
Bob	HR	3500	9700
Daniel	IT	4000	4000
Evan	IT	4200	8200

윈도우 프레임 용어 정리

키워드	의미
`UNBOUNDED PRECEDING`	윈도우의 시작점이 맨 처음 행부터 시작한다는 의미
`CURRENT ROW`	현재 행까지만 포함한다는 의미
`1 PRECEDING`	현재 행 바로 이전의 행까지 포함
`1 FOLLOWING`	현재 행 다음의 행까지 포함
`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	"지금 이 행까지 과거의 모든 행" (누적 합계나 누적 평균 구할 때 자주 사용)

다른 예: 직원별 자신의 이전 급여와 다음 급여 보기

SELECT
  name,
  department,
  salary,
  LAG(salary) OVER (PARTITION BY department ORDER BY salary) AS prev_salary,
  LEAD(salary) OVER (PARTITION BY department ORDER BY salary) AS next_salary
FROM employees;

8. 선형 회귀 함수: `REGR_` 계열(사실 쓸모없음)

함수	설명	예제 코드
`REGR_AVGX(x, y)`	X 값의 평균	`REGR_AVGX(sales, profit)`
`REGR_AVGY(x, y)`	Y 값의 평균	`REGR_AVGY(sales, profit)`
`REGR_COUNT(y, x)`	유효 데이터 쌍의 개수	`REGR_COUNT(profit, sales)`
`REGR_SLOPE(y, x)`	회귀선의 기울기	`REGR_SLOPE(profit, sales)`
`REGR_INTERCEPT(y, x)`	회귀선의 y절편	`REGR_INTERCEPT(profit, sales)`
`REGR_R2(y, x)`	결정계수(R², 설명력)	`REGR_R2(profit, sales)`

예시 테이블: 제품 판매 데이터 `SALES_DATA`

product	sales	profit
A	100	20
B	200	45
C	300	60
D	400	80

예시 쿼리

SELECT
  REGR_AVGX(sales, profit)       AS avg_sales,
  REGR_AVGY(sales, profit)       AS avg_profit,
  REGR_COUNT(profit, sales)      AS count_values,
  REGR_SLOPE(profit, sales)      AS slope,
  REGR_INTERCEPT(profit, sales)  AS intercept,
  REGR_R2(profit, sales)         AS r_squared
FROM sales_data;

실행 결과 (예시)

avg_sales	avg_profit	count_values	slope	intercept	r_squared
250	51.25	4	0.2	1.25	0.998

REGR_AVGX / REGR_AVGY: 회귀분석에 사용된 X, Y 값의 평균
REGR_SLOPE: 매출(sales)이 1 증가할 때 이익(profit)이 평균적으로 얼마나 변하는지 (이 예에서는 0.2)
REGR_INTERCEPT: 매출이 0일 때 이익의 이론적 예측값 (이 예에서는 1.25)
REGR_R2: 회귀 모델이 종속 변수(Y)를 얼마나 잘 설명하는지를 나타내는 지표. 0.998은 거의 완벽한 선형 관계를 의미함
REGR_COUNT: 회귀에 사용된 유효한 쌍의 수

9. 피벗(PIVOT)과 언피벗(UNPIVOT) 함수

개념	설명
피벗 (PIVOT)	행 단위 데이터를 열로 전개하여 읽기 쉬운 구조로 변경
언피벗 (UNPIVOT)	열 단위 데이터를 행으로 전개하여 분석하기 쉬운 구조로 변경
variable column	열 이름이 될 값 (UNPIVOT 시 컬럼 이름으로 이동)
value column	실제 측정된 데이터 값

예시 1: 피벗

원본 테이블: SALES

year	region	revenue
2022	A	100
2022	B	150
2023	A	120
2023	B	180

피벗 쿼리:

SELECT *
FROM (
  SELECT year, region, revenue
  FROM sales
)
PIVOT (
  SUM(revenue) FOR region IN ('A' AS region_a, 'B' AS region_b)
);

실행 결과:

year	region_a	region_b
2022	100	150
2023	120	180

해설: 행에 있던 ‘region’ 값이 열(region_a, region_b)로 변경됨

예시 2: 언피벗(잘 쓸일은 없지만 피벗의 역방향)

열 구조 테이블:

product	jan_sales	feb_sales	mar_sales
A	100	120	140
B	80	90	110

언피벗 쿼리:

SELECT *
FROM (
  SELECT product, jan_sales, feb_sales, mar_sales
  FROM monthly_sales
)
UNPIVOT (
  sales FOR month IN (jan_sales AS 'Jan', feb_sales AS 'Feb', mar_sales AS 'Mar')
);

실행 결과:

product	month	sales
A	Jan	100
A	Feb	120
A	Mar	140
B	Jan	80
B	Feb	90
B	Mar	110

해설: 열(jan_sales 등)이 행(month과 sales)으로 전환됨

JOIN

1. Equi Join (동등 조인)

설명: 두 테이블에 공통 열(column)이 존재하고, 동등 연산자(=)를 사용하여 데이터를 연결

예제 테이블

EMPLOYEES(employee_id, name, department_id)
DEPARTMENTS(department_id, department_name)

예제 쿼리

SELECT e.name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

2. Non-Equi Join (비동등 조인)

설명: 조인 조건에 동등(=) 연산자가 아니라 범위나 비교 연산자(<, >, BETWEEN) 등을 사용하는 경우

예제 테이블

EMPLOYEES(salary)
SALARY_GRADE(grade, min_sal, max_sal)

예제 쿼리

SELECT e.name, s.grade
FROM employees e
JOIN salary_grade s
ON e.salary BETWEEN s.min_sal AND s.max_sal;

3. Self Join (자기 자신과 조인)

설명: 하나의 테이블 안에서 자기 자신과 조인하는 방식으로 계층 구조나 비교를 표현할 때 사용

예제 테이블

EMPLOYEES(employee_id, name, manager_id)

예제 쿼리

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;

4. Outer Join (외부 조인)

설명: 한쪽 테이블에 데이터가 없어도 결과에 포함시키는 조인

LEFT OUTER JOIN: 왼쪽 테이블 데이터는 모두 유지, 오른쪽은 매칭되는 것만
RIGHT OUTER JOIN: 오른쪽 테이블 데이터 모두 유지
FULL OUTER JOIN: 양쪽 모두 유지

예제 쿼리

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

요약 정리

조인 종류	조건 사용 방식	누락 데이터 처리	주 용도
Equi Join	공통 열, `=`	모두 존재해야 함	일반적인 테이블 연결
Non-Equi Join	조건 연산자 (`<`, `>`)	모두 존재해야 함	범위 기반 연결
Self Join	같은 테이블 조인	-	상하위 관계, 같은 테이블 비교
Outer Join	조건 성립하지 않아도 연결	유지 (한쪽 또는 모두)	누락된 데이터도 포함

4. ANSI join

1. INNER JOIN (Equi Join)

테이블 A: employees

emp_id	name	dept_id
1	Alice	10
2	Bob	20
3	Carla	30

테이블 B: departments

dept_id	dept_name
10	HR
20	Engineering
40	Marketing

쿼리

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

결과

name	dept_name
Alice	HR
Bob	Engineering

2. LEFT OUTER JOIN

쿼리

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

결과

name	dept_name
Alice	HR
Bob	Engineering
Carla	NULL

설명: Carla는 부서 정보가 없지만 employees 테이블의 데이터는 유지됨

3. RIGHT OUTER JOIN

쿼리

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

결과

name	dept_name
Alice	HR
Bob	Engineering
NULL	Marketing

설명: Marketing 부서는 직원이 없지만 departments 테이블의 데이터는 유지됨

4. CROSS JOIN

쿼리

SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;

결과

name	dept_name
Alice	HR
Alice	Engineering
Alice	Marketing
Bob	HR
Bob	Engineering
Bob	Marketing
Carla	HR
Carla	Engineering
Carla	Marketing

설명: 총 3 × 3 = 9개의 조합 생성

5. NATURAL JOIN(이거 쓸때쯤 되면 뭔가 이상해진거임, 만약 두 테이블에서 같은 칼럼이 여러개면 전부다 동일해야 출력됨)

조건: 두 테이블 모두 동일한 이름의 컬럼이 존재해야 함

쿼리

SELECT *
FROM employees
NATURAL JOIN departments;

결과

emp_id	name	dept_id	dept_name
1	Alice	10	HR
2	Bob	20	Engineering

설명: dept_id를 기준으로 자동 조인됨

6. JOIN ... USING(사실 잘 안씀, 여러개의 칼럼이 키가 될떄 하나만을 지정하는 것)

쿼리

SELECT name, dept_name
FROM employees
JOIN departments USING (dept_id);

결과

name	dept_name
Alice	HR
Bob	Engineering

설명: dept_id는 출력 결과에 보이지 않으며 공통 컬럼으로 조인됨

은채의 성장통

인생 별거 없어

이전 포스트

20일차 - GROUP함수

다음 포스트

22일차 - 분석함수 & join

KCC정보통신

OVER가 들어가는 것을 알아야함

1. 순위 관련 함수: `RANK()`, `DENSE_RANK()`, `ROW_NUMBER()`

2. 분포 관련 함수: `CUME_DIST()`, `PERCENT_RANK()`

3. 비율 함수: `RATIO_TO_REPORT()`

4. 분배 함수: `NTILE(n)`

5. 선후행 함수: `LAG()`, `LEAD()`

6. 문자열 집계 함수: `LISTAGG()`

7. 윈도우 절 (Window Clause)

예시 테이블: 직원 급여

누적 급여 합계 구하기 (윈도우 함수 사용)

예시 결과

윈도우 프레임 용어 정리

다른 예: 직원별 자신의 이전 급여와 다음 급여 보기

8. 선형 회귀 함수: `REGR_` 계열(사실 쓸모없음)

예시 테이블: 제품 판매 데이터 `SALES_DATA`

예시 쿼리

실행 결과 (예시)

9. 피벗(PIVOT)과 언피벗(UNPIVOT) 함수

예시 1: 피벗

예시 2: 언피벗(잘 쓸일은 없지만 피벗의 역방향)

JOIN

1. Equi Join (동등 조인)

2. Non-Equi Join (비동등 조인)

3. Self Join (자기 자신과 조인)

4. Outer Join (외부 조인)

요약 정리

4. ANSI join

1. INNER JOIN (Equi Join)

2. LEFT OUTER JOIN

3. RIGHT OUTER JOIN

4. CROSS JOIN

5. NATURAL JOIN(이거 쓸때쯤 되면 뭔가 이상해진거임, 만약 두 테이블에서 같은 칼럼이 여러개면 전부다 동일해야 출력됨)

6. JOIN ... USING(사실 잘 안씀, 여러개의 칼럼이 키가 될떄 하나만을 지정하는 것)

20일차 - GROUP함수

22일차 - 서브쿼리

0개의 댓글

22일차 - 분석함수 & join

KCC정보통신

OVER가 들어가는 것을 알아야함

1. 순위 관련 함수: RANK(), DENSE_RANK(), ROW_NUMBER()

2. 분포 관련 함수: CUME_DIST(), PERCENT_RANK()

3. 비율 함수: RATIO_TO_REPORT()

4. 분배 함수: NTILE(n)

5. 선후행 함수: LAG(), LEAD()

6. 문자열 집계 함수: LISTAGG()

7. 윈도우 절 (Window Clause)

예시 테이블: 직원 급여

누적 급여 합계 구하기 (윈도우 함수 사용)

예시 결과

윈도우 프레임 용어 정리

다른 예: 직원별 자신의 이전 급여와 다음 급여 보기

8. 선형 회귀 함수: REGR_ 계열(사실 쓸모없음)

예시 테이블: 제품 판매 데이터 SALES_DATA

예시 쿼리

실행 결과 (예시)

9. 피벗(PIVOT)과 언피벗(UNPIVOT) 함수

예시 1: 피벗

예시 2: 언피벗(잘 쓸일은 없지만 피벗의 역방향)

JOIN

1. Equi Join (동등 조인)

2. Non-Equi Join (비동등 조인)

3. Self Join (자기 자신과 조인)

4. Outer Join (외부 조인)

요약 정리

4. ANSI join

1. INNER JOIN (Equi Join)

2. LEFT OUTER JOIN

3. RIGHT OUTER JOIN

4. CROSS JOIN

5. NATURAL JOIN(이거 쓸때쯤 되면 뭔가 이상해진거임, 만약 두 테이블에서 같은 칼럼이 여러개면 전부다 동일해야 출력됨)

6. JOIN ... USING(사실 잘 안씀, 여러개의 칼럼이 키가 될떄 하나만을 지정하는 것)

20일차 - GROUP함수

22일차 - 서브쿼리

0개의 댓글

1. 순위 관련 함수: `RANK()`, `DENSE_RANK()`, `ROW_NUMBER()`

2. 분포 관련 함수: `CUME_DIST()`, `PERCENT_RANK()`

3. 비율 함수: `RATIO_TO_REPORT()`

4. 분배 함수: `NTILE(n)`

5. 선후행 함수: `LAG()`, `LEAD()`

6. 문자열 집계 함수: `LISTAGG()`

8. 선형 회귀 함수: `REGR_` 계열(사실 쓸모없음)

예시 테이블: 제품 판매 데이터 `SALES_DATA`