λ³Έλ¬Έ λ°”λ‘œκ°€κΈ°

Database

SQL μˆœμœ„ν•¨μˆ˜: RANK, DENSE_RANK, ROW_NUMBER, NTILE

λ¨Όμ € μ•Œμ•„κ°€κΈ°!

 

  • 짙은 μ΄ˆλ‘μƒ‰μœΌλ‘œ μž‘μ„± 된 뢀뢄은 μƒλž΅ κ°€λŠ₯ν•œ λΆ€λΆ„μž…λ‹ˆλ‹€.
  • νŠΉμ • 속성 λ³„λ‘œ κ΅¬λΆ„ν•˜κ³ μž ν•  λ•Œ(=GROUP BY 와 같은 λ™μž‘μ„ μ‹œν‚€κ³  싢을 λ•Œ) PARTITION BYλ₯Ό μ‚¬μš©ν•©λ‹ˆλ‹€.
  • κΈ°λ³Έ μ˜ˆμ‹œ 외에 μΆ”κ°€ μ˜ˆμ‹œλ₯Ό 보렀면 더보기λ₯Ό ν΄λ¦­ν•˜μ„Έμš” πŸ˜„!

 


 

 

RANK

쀑볡 값듀에 λŒ€ν•΄μ„œ 동일 μˆœμœ„λ‘œ ν‘œμ‹œν•˜κ³ , 쀑볡 μˆœμœ„ λ‹€μŒ 값에 λŒ€ν•΄μ„œλŠ” 쀑볡 개수만큼 떨어진 μˆœμœ„λ‘œ 좜λ ₯ν•˜λŠ” ν•¨μˆ˜

rank(κ°’) over (partition by λŒ€μƒ order by λŒ€μƒ)

 

select EMPNO,
       ENAME,
       SAL,
       rank() over (order by SAL desc) "RANK"
from EMP;
더보기

μ˜ˆμ‹œ 1)

-- ENAME λ“±μˆ˜ 좜λ ₯ν•˜κΈ° μœ„ν•œ 쿼리
/*select ename, rank() over (order by ENAME) "ENAME RANK"
from EMP;*/

-- ENAME λ“±μˆ˜ 쀑 'FORD'의 λ“±μˆ˜λ₯Ό 좜λ ₯ν•˜κΈ° μœ„ν•œ 쿼리
select rank('FORD') within group (order by ENAME) "RANK"
from EMP;

 

μ˜ˆμ‹œ 2) EMP ν…Œμ΄λΈ”μ„ μ‘°νšŒν•˜μ—¬ EMPNO, ENAME, SAL, λΆ€μ„œλ²ˆν˜Έ(DEPTNO) 별 κΈ‰μ—¬(SAL) λ“±μˆ˜ 좜λ ₯

 

select EMPNO, ENAME, SAL, DEPTNO, rank() over (partition by DEPTNO order by SAL desc) "RANK"
from EMP
order by DEPTNO, RANK;

 

μ˜ˆμ‹œ 3) EMP ν…Œμ΄λΈ”μ„ μ‘°νšŒν•˜μ—¬ EMPNO, ENAME, SAL, DEPTNO, 같은 λΆ€μ„œ λ‚΄ JOB별 κΈ‰μ—¬ μˆœμœ„λ₯Ό 좜λ ₯

 

select EMPNO, ENAME, SAL, DEPTNO, JOB, rank() over (partition by DEPTNO, JOB order by SAL desc) "RANK"
from EMP;

 

 

DENSE_RANK

쀑볡 값듀에 λŒ€ν•΄μ„œλŠ” 동일 μˆœμœ„λ‘œ ν‘œμ‹œν•˜κ³ , 쀑볡 μˆœμœ„ λ‹€μŒ 값에 λŒ€ν•΄μ„œλŠ” 쀑볡 κ°’ κ°œμˆ˜μ™€ 상관없이 순차적인 μˆœμœ„ 값을 좜λ ₯ν•˜λ„λ‘ ν•˜λŠ” ν•¨μˆ˜

dense_rank(κ°’) over (partition by λŒ€μƒ order by λŒ€μƒ)

 

select EMPNO,
       ENAME,
       SAL,
       dense_rank() over (order by SAL desc) "RANK"
from EMP;

 

 

ROW_NUMBER

쀑볡 값듀에 λŒ€ν•΄μ„œλ„ 순차적인 μˆœμœ„λ₯Ό ν‘œμ‹œν•˜λ„λ‘ 좜λ ₯ν•˜λŠ” ν•¨μˆ˜

row_number(κ°’) over (partition by λŒ€μƒ order by λŒ€μƒ)

 

select EMPNO,
       ENAME,
       SAL,
       row_number() over (order by SAL desc) "RANK"
from EMP;

 

Tip. RANK, DENSE_RANK, ROW_NUMBER ν•¨μˆ˜ 비ꡐ정리!

 

select ENAME,
       SAL,
       rank() over (order by SAL desc) "RANK",
       dense_rank() over (order by SAL desc) "DENSE_RANK",
       row_number() over (order by SAL desc) "ROW_NUMBER"
from EMP;
ν•¨μˆ˜ 쀑볡 κ°’ μˆœμœ„ λ‹€μŒ μˆœμœ„
RANK 동일 μˆœμœ„ +쀑볡 κ°’ 개수
DENSE_RANK 동일 μˆœμœ„ +1
ROW_NUMBER 순차적 μˆœμœ„

 

 

NTILE

μ§€μ •ν•œ 숫자 만큼 λ“±μˆ˜λ₯Ό λ“±λΆ„ν•˜μ—¬ 좜λ ₯ν•˜λŠ” ν•¨μˆ˜

ntile(숫자) orver(partition by λŒ€μƒ orver by λŒ€μƒ)

 

select EMPNO,
       ENAME,
       SAL,
       ntile(4) over (order by SAL desc) "RANK"
from EMP;

 

 


Thanks for πŸ™ˆ