mooon๐ŸŒ™
STUDY
mooon๐ŸŒ™
์ „์ฒด ๋ฐฉ๋ฌธ์ž
์˜ค๋Š˜
์–ด์ œ
  • ๋ถ„๋ฅ˜ ์ „์ฒด๋ณด๊ธฐ (170)
    • ์ฝ”๋”ฉ ํ…Œ์ŠคํŠธ (147)
      • ๋ฐฑ์ค€ ์•Œ๊ณ ๋ฆฌ์ฆ˜ (53)
      • [ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค] Lv1 (13)
      • [ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค] ์ฝ”๋”ฉ ํ…Œ์ŠคํŠธ ์ž…๋ฌธ (54)
      • [ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค] SQL ๊ณ ๋“์  Kit (27)
    • BACK (4)
      • Spring (3)
      • PHP (0)
    • FRONT (5)
    • DevOps (8)
      • Jenkins (8)
    • GitHub๐ŸŒฑ (2)
    • ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค (1)
      • SQL (1)
    • Error ๐Ÿ’ฅ (0)
      • php (2)
    • ๐Ÿ˜‹ (1)

๋ธ”๋กœ๊ทธ ๋ฉ”๋‰ด

  • โญ ๊นƒํ—ˆ๋ธŒ
  • ๐Ÿ’• ๋ฐฉ๋ช…๋ก
  • ๐Ÿ’ฅ ํƒœ๊ทธ

์ธ๊ธฐ ๊ธ€

ํƒœ๊ทธ

  • ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค
  • ๋‹ค์ด๋‚˜๋ฏน ํ”„๋กœ๊ทธ๋ž˜๋ฐ
  • DevOps
  • Python
  • c++
  • ์ฝ”๋”ฉํ…Œ์ŠคํŠธ ์ž…๋ฌธ
  • ์•Œ๊ณ ๋ฆฌ์ฆ˜
  • SQL
  • SQL ๊ณ ๋“์  Kit
  • ๋ฐฑ์ค€

์ตœ๊ทผ ๊ธ€

250x250
hELLO ยท Designed By ์ •์ƒ์šฐ.
mooon๐ŸŒ™
๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค/SQL

[MySQL] ๋žญํ‚น(์ˆœ์œ„) ๊ตฌํ•˜๊ธฐ

๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค/SQL

[MySQL] ๋žญํ‚น(์ˆœ์œ„) ๊ตฌํ•˜๊ธฐ

2023. 6. 16. 14:27
728x90

๐Ÿ“Œ ์ˆœ์œ„ ํ•จ์ˆ˜

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 		|
728x90
์ €์ž‘์žํ‘œ์‹œ ๋น„์˜๋ฆฌ ๋ณ€๊ฒฝ๊ธˆ์ง€ (์ƒˆ์ฐฝ์—ด๋ฆผ)
  • ๐Ÿ“Œ ์ˆœ์œ„ ํ•จ์ˆ˜
  • ๐Ÿ˜Ž ์ข…๋ฅ˜
  • โญ RANK()
  • โญ DENSE_RANK()
  • โญ PERCENT_RANK()
  • โญ ROW_RANK()
  • โญ NTILE()
mooon๐ŸŒ™
mooon๐ŸŒ™
๊ฐœ๋ฐœ ๊ณต๋ถ€ ๊ธฐ๋ก

ํ‹ฐ์Šคํ† ๋ฆฌํˆด๋ฐ”

๋‹จ์ถ•ํ‚ค

๋‚ด ๋ธ”๋กœ๊ทธ

๋‚ด ๋ธ”๋กœ๊ทธ - ๊ด€๋ฆฌ์ž ํ™ˆ ์ „ํ™˜
Q
Q
์ƒˆ ๊ธ€ ์“ฐ๊ธฐ
W
W

๋ธ”๋กœ๊ทธ ๊ฒŒ์‹œ๊ธ€

๊ธ€ ์ˆ˜์ • (๊ถŒํ•œ ์žˆ๋Š” ๊ฒฝ์šฐ)
E
E
๋Œ“๊ธ€ ์˜์—ญ์œผ๋กœ ์ด๋™
C
C

๋ชจ๋“  ์˜์—ญ

์ด ํŽ˜์ด์ง€์˜ URL ๋ณต์‚ฌ
S
S
๋งจ ์œ„๋กœ ์ด๋™
T
T
ํ‹ฐ์Šคํ† ๋ฆฌ ํ™ˆ ์ด๋™
H
H
๋‹จ์ถ•ํ‚ค ์•ˆ๋‚ด
Shift + /
โ‡ง + /

* ๋‹จ์ถ•ํ‚ค๋Š” ํ•œ๊ธ€/์˜๋ฌธ ๋Œ€์†Œ๋ฌธ์ž๋กœ ์ด์šฉ ๊ฐ€๋Šฅํ•˜๋ฉฐ, ํ‹ฐ์Šคํ† ๋ฆฌ ๊ธฐ๋ณธ ๋„๋ฉ”์ธ์—์„œ๋งŒ ๋™์ž‘ํ•ฉ๋‹ˆ๋‹ค.