Search

μœˆλ„μš° ν•¨μˆ˜

μœˆλ„μš° ν•¨μˆ˜

ν–‰κ³Ό ν–‰ κ°„μ˜ 관계λ₯Ό μ •μ˜ν•˜λŠ” ν•¨μˆ˜
μˆœμœ„, 합계, 평균 ν–‰ μœ„μΉ˜ λ“± 계산 κ²°κ³Όλ₯Ό μ‚¬μš©ν•  수 μžˆλ‹€.

μ’…λ₯˜

β€’
μˆœμœ„ ν•¨μˆ˜
β€’
집계 ν•¨μˆ˜
β€’
ν–‰ μˆœμ„œ ν•¨μˆ˜
β€’
λΉ„μœ¨ ν•¨μˆ˜

문법 ꡬ쑰

SELECT μœˆλ„μš°ν•¨μˆ˜( 인수 ) OVER ( [PARTITION BY 컬럼1, 컬럼2, ...] [ORDER BY 컬럼1 [ASC | DESC], 컬럼2 [ASC | DESC], ...] [WINDOWING 절] ) FROM ν…Œμ΄λΈ”;
SQL
볡사
β€’
κΈ°λ³Έ ꡬ성 μš”μ†Œ
ꡬ성 μš”μ†Œ
μ„€λͺ…
ν•¨μˆ˜
μœˆλ„μš° ν•¨μˆ˜μ˜ μ’…λ₯˜λ₯Ό λ‚˜νƒ€λƒ…λ‹ˆλ‹€. 예λ₯Ό λ“€μ–΄, SUM(), AVG(), ROW_NUMBER() 등이 μžˆμŠ΅λ‹ˆλ‹€.
OVER
μœˆλ„μš° ν•¨μˆ˜λ₯Ό μ‚¬μš©ν•˜κΈ° μœ„ν•œ ν‚€μ›Œλ“œμž…λ‹ˆλ‹€.
PARTITION BY
μ„ νƒμ μœΌλ‘œ μ‚¬μš©λ©λ‹ˆλ‹€. 데이터λ₯Ό νŒŒν‹°μ…˜μœΌλ‘œ λ‚˜λˆ„λŠ” 데 μ‚¬μš©λ©λ‹ˆλ‹€.
ORDER BY
μ„ νƒμ μœΌλ‘œ μ‚¬μš©λ©λ‹ˆλ‹€. μœˆλ„μš° ν•¨μˆ˜λ₯Ό μ μš©ν•  λ•Œ μ •λ ¬ μˆœμ„œλ₯Ό μ§€μ •ν•©λ‹ˆλ‹€. 기본적으둜 μ •λ ¬ μˆœμ„œλŠ” ν˜„μž¬ 행을 μ€‘μ‹¬μœΌλ‘œ μ μš©λ©λ‹ˆλ‹€.
β€’
WINDOWING 절
μš©μ–΄
μ„€λͺ…
ROWS
ROWS ν‚€μ›Œλ“œλŠ” μœˆλ„μš°μ—μ„œ ROWSλ₯Ό κΈ°μ€€μœΌλ‘œ λ²”μœ„λ₯Ό μ§€μ •ν•©λ‹ˆλ‹€. 예λ₯Ό λ“€μ–΄, ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING은 ν˜„μž¬ 행을 μ€‘μ‹¬μœΌλ‘œ μ•žμ˜ 1개의 ν–‰κ³Ό λ’€μ˜ 1개의 행을 ν¬ν•¨ν•˜λŠ” μœˆλ„μš° λ²”μœ„λ₯Ό μ •μ˜ν•©λ‹ˆλ‹€.
RANGE
RANGE ν‚€μ›Œλ“œλŠ” μœˆλ„μš°μ—μ„œ κ°’μ˜ λ²”μœ„λ₯Ό κΈ°μ€€μœΌλ‘œ λ²”μœ„λ₯Ό μ§€μ •ν•©λ‹ˆλ‹€. 주둜 μˆ«μžλ‚˜ λ‚ μ§œμ™€ 같은 μˆœμ„œν˜• 데이터 μœ ν˜•μ˜ μ»¬λŸΌμ— λŒ€ν•΄ μ‚¬μš©λ©λ‹ˆλ‹€.
BETWEEN~AND
BETWEEN~AND ν‚€μ›Œλ“œλŠ” μœˆλ„μš°μ˜ λ²”μœ„λ₯Ό 지정할 λ•Œ μ‚¬μš©λ©λ‹ˆλ‹€. 예λ₯Ό λ“€μ–΄, ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGκ³Ό 같이 μ‚¬μš©λ©λ‹ˆλ‹€.
UNBOUNDED PRECEDING
UNBOUNDED PRECEDING은 μœˆλ„μš°μ˜ μ‹œμž‘μ„ λ‚˜νƒ€λ‚΄λ©°, ν˜„μž¬ 행보닀 μ•žμ˜ λͺ¨λ“  행을 ν¬ν•¨ν•©λ‹ˆλ‹€.
UNBOUNDED FOLLOWING
UNBOUNDED FOLLOWING은 μœˆλ„μš°μ˜ 끝을 λ‚˜νƒ€λ‚΄λ©°, ν˜„μž¬ 행보닀 λ’€μ˜ λͺ¨λ“  행을 ν¬ν•¨ν•©λ‹ˆλ‹€.
CURRENT ROW
CURRENT ROWλŠ” μœˆλ„μš°μ—μ„œ ν˜„μž¬ 행을 λ‚˜νƒ€λƒ…λ‹ˆλ‹€.
PRECEDING
ν˜„μž¬ ν–‰ 이전에 μœ„μΉ˜ν•œ ν–‰λ“€ κ°€λ¦¬ν‚΅λ‹ˆλ‹€.
FOLLOWING
ν˜„μž¬ ν–‰ 이후에 μœ„μΉ˜ν•œ 행듀을 κ°€λ¦¬ν‚΅λ‹ˆλ‹€.

μˆœμœ„ ν•¨μˆ˜

κ²°κ³Ό 집합 λ‚΄μ—μ„œ ν–‰μ˜ μˆœμœ„λ₯Ό κ²°μ •ν•˜λŠ” ν•¨μˆ˜
ν•¨μˆ˜λͺ…
μ„€λͺ…
RANK
λ™μΌν•œ 값에 λŒ€ν•œ μˆœμœ„λ₯Ό λΆ€μ—¬ν•˜κ³  같은 μˆœμœ„μ— λ™μΌν•œ 값이 μžˆλ‹€λ©΄ λ‹€μŒ μˆœμœ„λŠ” κ±΄λ„ˆλ›°κ³  κ·Έ λ‹€μŒ μˆœμœ„λ₯Ό λΆ€μ—¬ν•œλ‹€.
DENSE_RANK
λ™μΌν•œ 값에 λŒ€ν•œ μˆœμœ„λ₯Ό λΆ€μ—¬ν•˜κ³  같은 μˆœμœ„μ— λ™μΌν•œ 값이 μžˆλ‹€λ©΄ λ‹€μŒ μˆœμœ„λ„ κ·Έ λ‹€μŒ μˆœμœ„μ™€ λ™μΌν•œ 값을 가진닀.
ROW_NUMBER
μˆœμ„œλŒ€λ‘œ 숫자λ₯Ό λΆ€μ—¬ν•˜μ—¬ 각 행에 λŒ€ν•œ μœ μΌν•œ μˆœμœ„λ₯Ό λΆ€μ—¬ν•œλ‹€.

μˆœμœ„ ν•¨μˆ˜ μ˜ˆμ‹œ μ½”λ“œ

β€’
RANK
β€’
DENSE_RANK
β€’
ROW_NUMBER

RANK

β€’
κΈ‰μ—¬ λ‚΄λ¦Όμ°¨μˆœμœΌλ‘œ μˆœμœ„λ₯Ό κ΅¬ν•˜μ‹œμ˜€. (곡동 μˆœμœ„λ©΄ λ‹€μŒ μˆœμœ„ μ—†μ•°)
SELECT employee_id, salary , RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;
SQL
볡사

DENSE_RANK

β€’
κΈ‰μ—¬ λ‚΄λ¦Όμ°¨μˆœμœΌλ‘œ μˆœμœ„λ₯Ό κ΅¬ν•˜μ‹œμ˜€. (곡동 μˆœμœ„κ°€ μžˆμ–΄λ„ λ‹€μŒ μˆœμœ„ μ‚΄λ¦Ό)
SELECT employee_id ,salary ,DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_salary_rank FROM employees;
SQL
볡사

ROW_NUMBER

β€’
쀑볡 μˆœμœ„ 상관없이, μœ μΌν•œ μˆœμ„œ 번호 말 κ·ΈλŒ€λ‘œ ν–‰λ²ˆν˜Έλ₯Ό 좜λ ₯
SELECT employee_id ,salary ,ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num FROM employees;
SQL
볡사

집계 ν•¨μˆ˜

μ—¬λŸ¬ ν–‰μ˜ 값을 κ·Έλ£Ήν™”ν•˜κ³  μ§‘κ³„ν•˜μ—¬ 단일 κ²°κ³Ό 값을 λ°˜ν™˜ν•˜λŠ” ν•¨μˆ˜
ν•¨μˆ˜λͺ…
μ„€λͺ…
SUM
μˆ«μžν˜• λ°μ΄ν„°μ˜ 합을 κ³„μ‚°ν•©λ‹ˆλ‹€.
AVG
μˆ«μžν˜• λ°μ΄ν„°μ˜ 평균을 κ³„μ‚°ν•©λ‹ˆλ‹€.
COUNT
κ²°κ³Ό μ§‘ν•©μ΄λ‚˜ 그룹의 ν–‰ 수λ₯Ό κ³„μ‚°ν•©λ‹ˆλ‹€.
MAX
μˆ«μžλ‚˜ λ‚ μ§œ λ°μ΄ν„°μ˜ μ΅œλŒ“κ°’μ„ μ°ΎμŠ΅λ‹ˆλ‹€.
MIN
μˆ«μžλ‚˜ λ‚ μ§œ λ°μ΄ν„°μ˜ μ΅œμ†Ÿκ°’μ„ μ°ΎμŠ΅λ‹ˆλ‹€.

집계 ν•¨μˆ˜ μ˜ˆμ‹œ μ½”λ“œ

β€’
SUM
β€’
AVG
β€’
COUNT
β€’
MAX
β€’
MIN

SUM

SELECT SUM(salary) AS total_salary FROM employees;
SQL
볡사

AVG

SELECT AVG(salary) AS average_salary FROM employees;
SQL
볡사

COUNT

SELECT COUNT(*) AS total_employees FROM employees;
SQL
볡사

MAX

SELECT MAX(salary) AS max_salary FROM employees;
SQL
볡사

MIN

SELECT MIN(salary) AS min_salary FROM employees;
SQL
볡사

ν–‰ μˆœμ„œ ν•¨μˆ˜

κ²°κ³Ό 집합 λ‚΄μ—μ„œ νŠΉμ • ν–‰μ˜ μœ„μΉ˜λ‚˜ μˆœμ„œλ₯Ό κ²°μ •ν•˜λŠ” ν•¨μˆ˜
ν•¨μˆ˜λͺ…
μ„€λͺ…
FIRST_VALUE
κ·Έλ£Ή λ‚΄μ—μ„œ 첫 번째 ν–‰μ˜ 값을 λ°˜ν™˜ν•©λ‹ˆλ‹€.
LAST_VALUE
κ·Έλ£Ή λ‚΄μ—μ„œ λ§ˆμ§€λ§‰ ν–‰μ˜ 값을 λ°˜ν™˜ν•©λ‹ˆλ‹€.
LAG
ν˜„μž¬ ν–‰ μ΄μ „μ˜ 값을 κ°€μ Έμ˜΅λ‹ˆλ‹€.
LEAD
ν˜„μž¬ ν–‰ λ‹€μŒμ˜ 값을 κ°€μ Έμ˜΅λ‹ˆλ‹€.

ν–‰ μˆœμ„œ ν•¨μˆ˜ μ˜ˆμ‹œ μ½”λ“œ

β€’
FIRST_VALUE
β€’
LAST_VALUE
β€’
LAG
β€’
LEAD

FIRST_VALUE

β€’
각 λΆ€μ„œλ³„λ‘œ 첫 번째둜 μž…μ‚¬ν•œ μ§μ›μ˜ κΈ‰μ—¬λ₯Ό 쑰회
SELECT department_id , employee_id , salary ,FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS first_salary FROM employees;
SQL
볡사

LAST_VALUE

β€’
각 λΆ€μ„œλ³„λ‘œ λ§ˆμ§€λ§‰μœΌλ‘œ μž…μ‚¬ν•œ μ§μ›μ˜ κΈ‰μ—¬λ₯Ό 쑰회
SELECT department_id , employee_id , salary , LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary FROM employees;
SQL
볡사

LAG

β€’
이전 μ§μ›μ˜ 정보λ₯Ό 쑰회
SELECT employee_id, first_name, hire_date, LAG(first_name) OVER (ORDER BY hire_date) AS previous_name, LAG(hire_date) OVER (ORDER BY hire_date) AS previous_hire_date FROM employees;
SQL
볡사

LEAD

β€’
λ‹€μŒ μ§μ›μ˜ 정보λ₯Ό 쑰회
SELECT employee_id, first_name, hire_date, LEAD(first_name) OVER (ORDER BY hire_date) AS next_first_name, LEAD(hire_date) OVER (ORDER BY hire_date) AS next_hire_date FROM employees;
SQL
볡사

λΉ„μœ¨ ν•¨μˆ˜

κ²°κ³Ό 집합 λ‚΄μ—μ„œ νŠΉμ • κ°’μ˜ λΉ„μœ¨μ΄λ‚˜ λ°±λΆ„μœ¨μ„ κ³„μ‚°ν•˜λŠ” ν•¨μˆ˜
ν•¨μˆ˜λͺ…
μ„€λͺ…
CUME_DIST
κ²°κ³Ό μ§‘ν•©μ—μ„œ ν˜„μž¬ ν–‰μ˜ λˆ„μ  뢄포 값을 κ³„μ‚°ν•©λ‹ˆλ‹€.
PERCENT_RANK
κ²°κ³Ό μ§‘ν•©μ—μ„œ ν˜„μž¬ ν–‰μ˜ λ°±λΆ„μœ„ μˆœμœ„λ₯Ό κ³„μ‚°ν•©λ‹ˆλ‹€.
NTILE
κ²°κ³Ό 집합을 μ§€μ •λœ 개수의 λ™μΌν•œ 크기의 그룹으둜 λ‚˜λˆ„κ³ , 각 행에 κ·Έλ£Ή 번호λ₯Ό ν• λ‹Ήν•©λ‹ˆλ‹€.
RATIO_TO_REPORT
κ²°κ³Ό μ§‘ν•©μ˜ 각 κ·Έλ£Ήμ—μ„œ ν˜„μž¬ ν–‰μ˜ λΉ„μœ¨μ„ κ³„μ‚°ν•©λ‹ˆλ‹€.

λΉ„μœ¨ ν•¨μˆ˜ μ˜ˆμ‹œ μ½”λ“œ

β€’
CUME_DIST
β€’
PERCENT_RANK
β€’
NTILE
β€’
RATIO_TO_REPORT

CUME_DIST

β€’
이 ν•¨μˆ˜λŠ” κ²°κ³Ό μ§‘ν•©μ—μ„œ ν˜„μž¬ ν–‰μ˜ λˆ„μ  뢄포 값을 계산
SELECT employee_id , salary , CUME_DIST() OVER (ORDER BY salary DESC) AS cumulative_distribution FROM employees;
SQL
볡사

PERCENT_RANK

β€’
이 ν•¨μˆ˜λŠ” κ²°κ³Ό μ§‘ν•©μ—μ„œ ν˜„μž¬ ν–‰μ˜ λ°±λΆ„μœ„ μˆœμœ„λ₯Ό 계산
SELECT employee_id , salary , PERCENT_RANK() OVER (ORDER BY salary DESC) AS percent_rank FROM employees;
SQL
볡사

NTILE

β€’
이 ν•¨μˆ˜λŠ” κ²°κ³Ό 집합을 μ§€μ •λœ 개수의 λ™μΌν•œ 크기의 그룹으둜 λ‚˜λˆ„κ³ , 각 행에 κ·Έλ£Ή 번호λ₯Ό ν• λ‹Ή
SELECT employee_id , salary , NTILE(4) OVER (ORDER BY salary DESC) AS quartile FROM employees;
SQL
볡사

RATIO_TO_REPORT

β€’
이 ν•¨μˆ˜λŠ” κ²°κ³Ό μ§‘ν•©μ˜ 각 κ·Έλ£Ήμ—μ„œ ν˜„μž¬ ν–‰μ˜ λΉ„μœ¨μ„ 계산
SELECT department_id , employee_id , salary , RATIO_TO_REPORT(salary) OVER (PARTITION BY department_id) AS salary_ratio FROM employees;
SQL
볡사