๐ ์์ ํจ์
mysql์์ ํธ๋ฆฌํ๊ฒ ์์๋ฅผ ๋งค๊ธธ ์ ์๋๋ก ์ฌ๋ฌ ํจ์๋ฅผ ์ง์ํ๋ค
over์ ๊ณผ ํจ๊ป ์ฌ์ฉํด์ผ ํ๋ค
๐ ์ข ๋ฅ
โญ RANK()
ํ์ ์์๋ฅผ ๋ฐํํ๋ ํจ์
๋์ ์ธ ๊ฒฝ์ฐ ๋์ผํ ์์๋ฅผ ๋งค๊ธด๋ค
๊ณต๋ ์์๊ฐ ์กด์ฌํ๋ ๊ฒฝ์ฐ, ๋ค์ ์์๋ ํด๋น ์์์ ์๋งํผ ๊ฑด๋๋ฐ์ด ๋งค๊ฒจ์ง๋ค (ex_ 1,1,3,4,...)
๐ฅ ์ฌ์ฉ ๋ฐฉ๋ฒ
/* [-]๋ ์๋ต ๊ฐ๋ฅ */
RANK() OVER([PARTITION BY column_name] ORDER BY column_name [ASC|DESC])
๐ฅ ์์1
SELECT
member_no, price, RANK() OVER(ORDER BY price DESC) AS ranking
FROM
member_payment
| member_no | price | ranking |
| --------- | --------- | --------- |
| 846 | 13000.00 | 1 |
| 16 | 3000.00 | 2 |
| 747 | 2200.00 | 3 |
| 1 | 2000.00 | 4 |
| 855 | 2000.00 | 4 |
| 119 | 1000.00 | 6 |
๐ฅ ์์2
PARTITION BY๋ฅผ ์ฌ์ฉํ๋ฉด ์ง์ ํ ์ปฌ๋ผ ๋ณ๋ก ์์๋ฅผ ๋งค๊ธธ ์ ์๋ค
SELECT
member_no, price, gender, RANK() OVER(PARTITION BY gender ORDER BY price DESC) AS ranking
FROM
member_payment
| member_no | price | gender | ranking |
| --------- | --------- | --------- | --------- |
| 1 | 2000.00 | \N | 1 |
| 119 | 1000.00 | F | 1 |
| 16 | 3000.00 | M | 1 |
| 846 | 13000.00 | U | 1 |
| 747 | 2200.00 | U | 2 |
| 855 | 2000.00 | U | 3 |
โญ DENSE_RANK()
RANK() ํจ์์ ๋น์ทํ๊ฒ ํ์ ์์๋ฅผ ๋ฐํํ๊ณ ๋์ ์ ๊ฒฝ์ฐ ๋์ผํ ์์๋ฅผ ๋งค๊ธด๋ค
ํ์ง๋ง, ๊ณต๋ ์์๊ฐ ์กด์ฌํ๋ ๊ฒฝ์ฐ ๋ค์ ์์๋ ๊ฑด๋๋ฐ์ง ์๊ณ ๋ค์ ์์๋ก ์ด์ด์ ธ ๋งค๊ฒจ์ง๋ค (ex_ 1,1,2,3...)
๐ฅ ์ฌ์ฉ ๋ฐฉ๋ฒ
/* [-]๋ ์๋ต ๊ฐ๋ฅ */
DENSE_RANK() OVER([PARTITION BY column_name] ORDER BY column_name [ASC|DESC])
๐ฅ ์์1
SELECT
member_no, price, DENSE_RANK() OVER(ORDER BY price DESC) AS ranking
FROM
member_payment
| member_no | price | ranking |
| --------- | --------- | --------- |
| 846 | 13000.00 | 1 |
| 16 | 3000.00 | 2 |
| 747 | 2200.00 | 3 |
| 1 | 2000.00 | 4 |
| 855 | 2000.00 | 4 |
| 119 | 1000.00 | 5 |
โญ PERCENT_RANK()
๋ฐฑ๋ถ์จ ์์๋ฅผ ๊ณ์ฐํด์ฃผ๋ ํจ์
๋ฐฑ๋ถ์จ ์์๋ฅผ ๊ตฌํ๋ ๊ณต์ : (rank - 1) / (row์ - 1)
๋ฐํ๊ฐ์ ๋ฒ์๋ 0~1์ ๊ฐ์ง๊ฒ ๋๋ค
๐ฅ ์ฌ์ฉ ๋ฐฉ๋ฒ
/* [-]๋ ์๋ต ๊ฐ๋ฅ */
PERCENT_RANK() OVER([PARTITION BY column_name] ORDER BY column_name [ASC|DESC])
๐ฅ ์์1
SELECT
member_no, price, PERCENT_RANK() OVER(ORDER BY price DESC) AS ranking
FROM
member_payment
| member_no | price | ranking |
| --------- | --------- | ------------- |
| 846 | 13000.00 | 0.0000000000 |
| 16 | 3000.00 | 0.2000000000 |
| 747 | 2200.00 | 0.4000000000 |
| 1 | 2000.00 | 0.6000000000 |
| 855 | 2000.00 | 0.6000000000 |
| 119 | 1000.00 | 1.0000000000 |
โญ ROW_RANK()
ํ ๋ฒํธ๋ฅผ ๋ถ์ฌ์ฃผ๋ ํจ์
ํ๋ง๋ค ๋ค๋ฅธ ๊ฐ์ ํ ๋นํ๋ฉฐ, ์ค๋ณต๋ ๊ฐ์ ํ์ฉํ์ง ์๋๋ค
๐ฅ ์ฌ์ฉ ๋ฐฉ๋ฒ
/* [-]๋ ์๋ต ๊ฐ๋ฅ */
ROW_NUMBER() OVER([PARTITION BY column_name] ORDER BY column_name [ASC|DESC])
๐ฅ ์์1
SELECT
member_no, price, ROW_NUMBER() OVER(ORDER BY price DESC, member_no) AS ranking
FROM
member_payment
| member_no | price | ranking |
| --------- | --------- | --------- |
| 846 | 13000.00 | 1 |
| 16 | 3000.00 | 2 |
| 747 | 2200.00 | 3 |
| 1 | 2000.00 | 4 |
| 855 | 2000.00 | 5 |
| 119 | 1000.00 | 6 |
โญ NTILE()
ํ๋ค์ N๋ฑ๋ถํ๋ ํจ์
๐ฅ ์ฌ์ฉ ๋ฐฉ๋ฒ
/* [-]๋ ์๋ต ๊ฐ๋ฅ */
NTILE(number) OVER ([PARTITION BY column_name] ORDER BY column_name [ASC|DESC])
๐ฅ ์์1
| member_no | price | ranking |
| --------- | --------- | --------- |
| 846 | 13000.00 | 1 |
| 16 | 3000.00 | 1 |
| 747 | 2200.00 | 2 |
| 1 | 2000.00 | 2 |
| 855 | 2000.00 | 3 |
| 119 | 1000.00 | 3 |