๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ

SQL

[SQL] SQL๋กœ ์†Œ์ˆ˜(Prime Number) ์ถœ๋ ฅํ•˜๊ธฐ(HackerRank - Print Prime Numbers ๋ฌธ์ œ)

๋ฐ˜์‘ํ˜•

๐Ÿ”Š ๋ณธ ํฌ์ŠคํŒ…์—์„œ ์‚ฌ์šฉ๋˜๋Š” ํ…Œ์ด๋ธ”์˜ ์ž๋ฃŒ์™€ ์ถœ์ฒ˜๋Š” HackerRank ์ž„์„ ๋ฐํž™๋‹ˆ๋‹ค. ๋” ๋‹ค์–‘ํ•œ SQL ๋ฌธ์ œ๋ฅผ ํ’€์–ด๋ณด์‹œ๋ ค๋ฉด HackerRank ์‚ฌ์ดํŠธ๋ฅผ ๋ฐฉ๋ฌธํ•ด ๋ณด์„ธ์š”!

 

HackerRank ์‚ฌ์ดํŠธ

 

์ด๋ฒˆ ํฌ์ŠคํŒ…์—์„œ๋Š” SQL๋กœ ์†Œ์ˆ˜(Prime Number)๋ฅผ ์ถœ๋ ฅํ•ด๋ณด๋Š” ๋ฌธ์ œ๋ฅผ ํ’€์–ด๋ณด๋ ค๊ณ  ํ•œ๋‹ค. ํ•ด๋‹น ๋ฌธ์ œ๋ฅผ ํ’€๊ธฐ ์œ„ํ•ด์„œ MySQL์˜ Stored Procedure๋ฅผ ์ฐธ๊ณ ํ•ด์„œ ํ’€๊ธฐ๋„ ํ–ˆ์ง€๋งŒ ์ผ๋ฐ˜์ ์ธ ์ฟผ๋ฆฌ๋ฌธ์„ ํ†ตํ•ด ํ•ด๊ฒฐํ•  ์ˆ˜ ์žˆ๋Š” ํ’€์ด๋ฅผ ์†Œ๊ฐœํ•˜๊ธฐ ์œ„ํ•ด Discussion์˜ ๋‹ค๋ฅธ ๋ถ„์˜ ๋›ฐ์–ด๋‚œ ํ’€์ด๋ฅผ ์ฐธ๊ณ ํ–ˆ๋‹ค.

 

ํ•˜์ง€๋งŒ ๋ฌธ์ œ๋ฅผ ํ•ด๊ฒฐํ•ด๋‚˜๊ฐ€๋ฉด์„œ MySQL์˜ Procedure๋ผ๋Š” ๊ฒƒ์— ๋Œ€ํ•ด ์•Œ๊ฒŒ ๋˜์—ˆ๊ณ  Procedure ์•ˆ์—์„œ WHILE, LOOP, ITERATE, IF ๋“ฑ ๋‹ค์–‘ํ•œ ๋ฌธ๋ฒ•์„ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋‹ค๋Š” ์‚ฌ์‹ค๋„ ์•Œ๊ฒŒ ๋˜์—ˆ๋‹ค. ์ด์— ๋Œ€ํ•ด ์ž์„ธํžˆ ์•Œ๊ณ  ์‹ถ๋‹ค๋ฉด ์—ฌ๊ธฐ๋ฅผ ํด๋ฆญํ•ด ํ™•์ธํ•ด๋ณด์ž. ๊ทธ๋ฆฌ๊ณ  ์ด Procedure๋ฅผ ํ™œ์šฉํ•ด ์†Œ์ˆ˜๋ฅผ ๊ตฌํ•˜๋Š” ํ’€์ด์˜ ํ”„๋ ˆ์ž„์€ ์—ฌ๊ธฐ๋ฅผ ํ†ตํ•ด ์‚ดํŽด๋ณด์ž.

 

์ด๋ฒˆ ํฌ์ŠคํŒ…์—์„œ ํ•ด๊ฒฐํ•˜๋Š” ๋ฐฉ๋ฒ•์€ ํ‰์†Œ์— ์‚ฌ์šฉํ•ด์™”๋˜ ์ผ๋ฐ˜์ ์ธ ์ฟผ๋ฆฌ์™€ ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ์ด์šฉํ•ด ํ’€์–ด๋ณด๋ ค ํ•œ๋‹ค. ๋‹จ์ˆœํžˆ SQL ๊ตฌ๋ฌธ๋งŒ ๋ณด์•„์„œ๋Š” ์ง๊ด€์ ์œผ๋กœ ํ’€์ด๊ฐ€ ์™€ ๋‹ฟ์ง€ ์•Š์œผ๋ฏ€๋กœ ๋ฌธ์ œ ์›๋ณธ์— ๋“ค์–ด๊ฐ€ ์ฟผ๋ฆฌ๋ฅผ ํ•˜๋‚˜ํ•˜๋‚˜์”ฉ ์ถœ๋ ฅํ•ด ์‚ดํŽด๋ณด๋Š” ๊ฒƒ์„ ๊ถŒ์žฅํ•œ๋‹ค.

์šฐ์„  ๋ฌธ์ œ์—์„œ ์š”๊ตฌํ•˜๋Š” ์‚ฌํ•ญ์€ ๋‹ค์Œ๊ณผ ๊ฐ™๋‹ค.

 

Write a query to print allprime numbersless than or equal to 1000. Print your result on a single line, and use the ampersand ('&') character as your separator (instead of a space).
(1000 ์ดํ•˜์˜ ์ž์—ฐ์ˆ˜๋“ค ์ค‘ ๋ชจ๋“  ์†Œ์ˆ˜๊ฐ’๋“ค์„ ํ•˜๋‚˜์˜ ์ค„๋กœ ์ถœ๋ ฅํ•ด๋ผ. ์ด ๋•Œ ์†Œ์ˆ˜๊ฐ’๋“ค ์‚ฌ์ด์— ๊ตฌ๋ถ„์ž๋ฅผ '&'๋ฅผ ์‚ฝ์ž…ํ•ด ์ถœ๋ ฅํ•ด๋ผ.)

์ด ๋•Œ ์†Œ์ˆ˜๋ž€, 0.12, 0.567 ๊ณผ ๊ฐ™์€ ๋ถ€๋™์†Œ์ˆ˜์ ์„ ์–˜๊ธฐํ•˜๋Š” ๊ฒƒ์ด ์•„๋‹Œ ๋‚˜๋ˆŒ ์ˆ˜ ์žˆ๋Š” ์ˆ˜๊ฐ€ ์ž๊ธฐ ์ž์‹ ๊ณผ 1๋ฐ–์— ์—†๋Š” ์ˆซ์ž๋ฅผ ์˜๋ฏธํ•œ๋‹ค. ์˜ˆ๋ฅผ ๋“ค๋ฉด 7์€ ๋‘ ๊ฐœ์˜ ๊ณฑ์…ˆ์œผ๋กœ ๋‚˜๋ˆ„์–ด ๋ณธ๋‹ค๋ฉด 1 x 7 ์ด๋ผ๋Š” 1๊ฐ€์ง€ ์กฐํ•ฉ๋ฐ–์— ์กด์žฌํ•˜์ง€ ์•Š๋Š”๋‹ค. 17์ด๋‚˜ 13๋„ ๋งˆ์ฐฌ๊ฐ€์ง€๋‹ค. ๋†’์€ ์ˆซ์ž๋กœ ์˜ฌ๋ผ๊ฐ€๋ฉด 787๋„ ์†Œ์ˆ˜์ด๋‹ค.

 

๊ทธ๋Ÿฐ๋ฐ ํ•œ ๊ฐ€์ง€ ์ด์ƒํ•œ ์ ์ด ์žˆ๋‹ค. ํ…Œ์ด๋ธ”์ด ์ฃผ์–ด์ง€์ง€ ์•Š์•˜๋Š”๋ฐ ๋Œ€์ฒด ์–ด๋–ป๊ฒŒ ์ฟผ๋ฆฌ๋ฅผ ํ•˜๋ž€ ๋ง์ธ๊ฐ€? ์ด๋ฅผ ํ•ด๊ฒฐํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” MySQL ์ž์ฒด์— ๋‚ด์žฅ๋˜์–ด ์žˆ๋Š” ํ…Œ์ด๋ธ”๋“ค์ด ๋‹ด๊ฒจ ์žˆ๋Š” ์ฆ‰, ์šฐ๋ฆฌ๊ฐ€ ๋กœ์ปฌ์— MySQL์„ ๊น”๊ฒŒ ๋˜๋ฉด ์• ์ดˆ๋ถ€ํ„ฐ ์กด์žฌํ•˜๋Š” ํ…Œ์ด๋ธ”๋“ค์„ ๋‹ด๊ณ  ์žˆ๋Š” information_schema ๋ฅผ ์ด์šฉํ•ด์•ผ ํ•œ๋‹ค. information_schema์— ๋Œ€ํ•œ ์ž์„ธํ•œ ๋‚ด์šฉ์€ ์—ฌ๊ธฐ๋ฅผ ์‚ดํŽด๋ณด์ž.

 

๋จผ์ € ํ•ด๋‹น ๋ฌธ์ œ๋ฅผ ํ’€์ดํ•œ SQL ๊ตฌ๋ฌธ๋ถ€ํ„ฐ ์‚ดํŽด๋ณด์ž.

 

SELECT GROUP_CONCAT(NUMB SEPARATOR '&')
FROM (SELECT @num:=@num+1 AS NUMB
      FROM information_schema.tables t1,
           information_schema.tables t2,
           (SELECT @num:=1) tmp
      ) tempNum
WHERE NUMB <= 1000
AND NOT EXISTS (SELECT *
                FROM (SELECT @nu:=@nu+1 AS NUMA
                      FROM information_schema.tables t1,
                           information_schema.tables t2,
                           (SELECT @nu:=1) tmp1
                      LIMIT 1000
                      ) tempNum1
                WHERE FLOOR(NUMB/NUMA) = (NUMB/NUMA)
                AND NUMA < NUMB
                AND 1 < NUMA
               )

 

๋งŽ์ด ๋ณต์žกํ•˜๋‹ค.. ์šฐ์„  ๊ฐ€์žฅ ์ฒซ ๋ฒˆ์งธ์— ์žˆ๋Š” ์„œ๋ธŒ์ฟผ๋ฆฌ ํ˜•ํƒœ๋ถ€ํ„ฐ ํ•˜๋‚˜ํ•˜๋‚˜ ์ถœ๋ ฅํ•ด๋ณด์ž. ๋‹ค์Œ์˜ SQL ๊ตฌ๋ฌธ์„ ์ถœ๋ ฅํ•˜๋ฉด ๋‹ค์Œ๊ณผ ๊ฐ™๋‹ค.

 

SELECT *
FROM (SELECT @num:=@num+1 AS NUMB
      FROM information_schema.tables t1,
           information_schema.tables t2
      ) tempNum

 

๊ฒฐ๊ณผ ํ™”๋ฉด

 

์œ„ ํ™”๋ฉด์„ ๋ณด๋ฉด ๋ชจ๋‘ Null ๊ฐ’์ด ๋˜์–ด ์žˆ๋‹ค. ์ด ๋•Œ information_schema๋ฅผ 2๊ฐœ๋กœ ์„ค์ •ํ•œ ๊ฒƒ์€ information_schema๋ฅผ 1๊ฐœ๋กœ๋งŒ ์„ค์ •ํ•ด์„œ ๋ชจ๋“  row๋“ค์„ ์ถœ๋ ฅํ•˜๋ฉด 1000๊ฐœ์˜ ๊ฐœ์ˆ˜๋ณด๋‹ค ์ ๊ฒŒ ๋‚˜์˜ค๊ธฐ ๋•Œ๋ฌธ์ด๋‹ค. ๋”ฐ๋ผ์„œ ์ถ”ํ›„์— 1000๊ฐœ๋ณด๋‹ค ๋„˜๋Š” row๋“ค ์ค‘ 1000๊ฐœ๋กœ LIMIT ํ•˜๊ธฐ ์œ„ํ•ด 2๊ฐœ๋กœ ์„ค์ •ํ•œ๋‹ค๋Š” ๊ฒƒ์„ ์•Œ์•„๋‘์ž. ์œ„ ์ฟผ๋ฆฌ์—์„œ Null ๊ฐ’ ๋Œ€์‹  1๋ณด๋‹ค ํฐ ์ฆ‰ 2๋ถ€ํ„ฐ 1์”ฉ ์ปค์ง€๋Š” ๊ฐ’์„ ๋„ฃ์–ด๋ณด์ž.

 

SELECT *
FROM (SELECT @num:=@num+1 AS NUMB
      FROM information_schema.tables t1,
           information_schema.tables t2,
           (SELECT @num:=1) tmp
      ) tempNum

 

๊ฒฐ๊ณผ ํ™”๋ฉด

 

์šฐ๋ฆฌ๊ฐ€ ์›ํ•˜๋Š” ๋ฐ๋กœ ๊ฐ’์ด ์‚ฝ์ž…๋˜์—ˆ๋‹ค. ์‚ฌ์‹ค ์œ„์— SELECT ๋ฌธ๋งŒ ๋ถ™์—ฌ์„œ ๊ฐ’์ด ์‚ฝ์ž… ๋œ๋‹ค๋Š” ์‚ฌ์‹ค์ด ์‹ ๊ธฐ ํ–ˆ๋‹ค. @num ์ด๋ผ๋Š” ์ด๋ฏธ ์‚ฌ์šฉ๋œ ๋ณ€์ˆ˜๊ฐ€ ๋“ค์–ด๊ฐ€ ์žˆ๊ธฐ ๋•Œ๋ฌธ์— ์•Œ์•„์„œ ์‚ฝ์ž…๋˜๋Š” ๊ฑด์ง€.. ์ด์— ๋Œ€ํ•ด์„œ๋Š” ์ถ”๊ฐ€์ ์ธ ๊ณต๋ถ€๊ฐ€ ํ•„์š”ํ•  ๊ฒƒ ๊ฐ™๋‹ค.

 

์–ด์จ‹๋“  ์ด์ œ 1000๋ณด๋‹ค ํฐ ์ˆซ์ž๋“ค์ด ๋ชจ๋‘ ์‚ฝ์ž…๋˜์—ˆ์œผ๋‹ˆ ์กฐ๊ฑด์„ ๋ถ™์—ฌ์ฃผ์ž. ๊ทธ๋ฆฌ๊ณ  ์กฐ๊ฑด๋ฌธ์— ๋ฐฉ๊ธˆ ๋งŒ๋“ค์—ˆ๋˜ 1000๊ฐœ์˜ ์ˆซ์ž๊ฐ€ ์‚ฝ์ž…๋œ ํ…Œ์ด๋ธ”์„ ํ•˜๋‚˜ ๋” ๋งŒ๋“ค์–ด์„œ ์–ด๋–ค ์ˆ˜๊ฐ€ '์†Œ์ˆ˜'์ธ์ง€ ํ•„ํ„ฐ๋งํ•ด๋ณด์ž.

 

SELECT GROUP_CONCAT(NUMB SEPARATOR '&')
FROM (SELECT @num:=@num+1 AS NUMB
      FROM information_schema.tables t1,
           information_schema.tables t2,
           (SELECT @num:=1) tmp
      ) tempNum
WHERE NUMB <= 1000
AND NOT EXISTS (SELECT *
                FROM (SELECT @nu:=@nu+1 AS NUMA
                      FROM information_schema.tables t1,
                           information_schema.tables t2,
                           (SELECT @nu:=1) tmp1
                      LIMIT 1000
                      ) tempNum1
                WHERE FLOOR(NUMB/NUMA) = (NUMB/NUMA)
                AND NUMA < NUMB
                AND 1 < NUMA
               )

 

์ฃผ๋ชฉํ•ด์•ผ ํ•  ๋ถ€๋ถ„์€ FLOOR(NUMB/NUMA) = (NUMB/NUMA)๋ฅผ ๋งŒ์กฑํ•˜๋Š” NUMB ์ˆซ์ž๋“ค์ด ๋ฐ”๋กœ ์†Œ์ˆ˜๊ฐ€ ์•„๋‹ˆ๋ผ๋Š” ์ ์ด๋‹ค. ์ด์— ๋Œ€ํ•ด์„œ ๊ฐ„๋‹จํ•œ ์˜ˆ์‹œ๋ฅผ ๋“ค์–ด๋ณด์ž.

 

NUMB NUMA FLOOR(NUMB/NUMA) NUMB/NUMA
7 4 1 1.75
6 2 3 3
11 5 2 2.2
10 5 2 2

 

์œ„ ํ…Œ์ด๋ธ”์„ ๋ณด๋ฉด FLOOR(NUMB/NUMA) ์™€ NUMB/NUMA ๊ฐ€ ๋™์ผํ•˜๋‹ค๋Š” ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜๋Š” NUMB ์ˆ˜๋“ค์€ ์†Œ์ˆ˜๊ฐ€ ์•„๋‹Œ ์ˆ˜๋“ค์ž„์„ ์•Œ ์ˆ˜ ์žˆ๋‹ค. ์ฆ‰, ํ•ด๋‹น ์กฐ๊ฑด์„ ๋ถˆ๋งŒ์กฑํ•˜๋Š” NUMB ์ˆ˜๋“ค์ด ๋ฐ”๋กœ ์†Œ์ˆ˜๋ผ๋Š” ๊ฒƒ์ด๋‹ค.

 

๋”ฐ๋ผ์„œ ์ด๋Ÿฌํ•œ ์กฐ๊ฑด์ด์™ธ์— ์ถ”๊ฐ€์ ์œผ๋กœ NUMA๊ฐ€ 1๋ณด๋‹ค ํฌ๊ณ  NUMA < NUMB ์ด์–ด์•ผ ํ•˜๋Š” ์กฐ๊ฑด 2๊ฐ€์ง€๋ฅผ ๋” ๋ถ™์—ฌ ์›ํ•˜๋Š” ๊ฐ’๋“ค์ธ 1000 ์ดํ•˜์˜ ์†Œ์ˆ˜๊ฐ’๋“ค์„ ์ถœ๋ ฅํ•  ์ˆ˜ ์žˆ๋‹ค.

 

์ด์ œ ๋งˆ์ง€๋ง‰์œผ๋กœ ๊ตฌ๋ถ„์ž์ธ &๋ฅผ ์‚ฌ์ด์— ๋„ฃ์–ด์„œ ํ•œ ์ค„๋กœ ์ถœ๋ ฅํ•ด์ฃผ์–ด์•ผ ํ•˜๋Š”๋ฐ ์ด ๋•Œ๋Š” GROUP_CONCAT ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜๋ฉด ๋œ๋‹ค. ํ•„์ž๋„ ์ด์— ๋Œ€ํ•ด์„œ๋Š” ์ฒ˜์Œ ์•Œ์•˜๋Š”๋ฐ ๋ธ”๋กœ๊ทธ๋ฅผ ์ฐพ์•„๋ณด๋‹ˆ ์œ ์šฉํ•œ ๊ธฐ๋Šฅ์ด ๋งŽ์•˜๋‹ค. ํ•œ ์ค„๋กœ ์ถœ๋ ฅํ•ด์ค„ ๋•Œ ์ค‘๋ณต์„ ์ œ๊ฑฐ(DISTINCT) ํ•˜๊ฑฐ๋‚˜ ์ •๋ ฌ ์ˆœ์„œ(ORDER BY)๋ฅผ ์ •ํ•ด์ฃผ๊ธฐ, ์ง€๊ธˆ ๋ฌธ์ œ ํ’€์ด์— ์‚ฌ์šฉ๋  ๊ตฌ๋ถ„์ž ์„ค์ • ๋“ฑ ๋‹ค์–‘ํ•œ ๊ธฐ๋Šฅ์ด ์žˆ์—ˆ๋‹ค. ์ถ”ํ›„์— ์œ ์šฉํ•˜๊ฒŒ ์‚ฌ์šฉํ•ด๋ณผ ๋งŒํ•œ ๊ธฐ๋Šฅ์ธ ๊ฒƒ ๊ฐ™๋‹ค.

 

์ด์ œ GROUP_CONCAT ๊นŒ์ง€ ์„ค์ •ํ•œ ํ›„ ์ตœ์ข… ๊ฒฐ๊ณผ๋ฅผ ํ…Œ์ŠคํŠธ ํ•ด๋ณด๋ฉด ์ •๋‹ต ์ฒ˜๋ฆฌ๊ฐ€ ๋œ๋‹ค. ๋์œผ๋กœ ๋ฌธ๋‘์— ์–ธ๊ธ‰ํ–ˆ๋˜ Procedure์„ ์ด์šฉํ•œ ํ’€์ด๋„ ์ฒจ๋ถ€ํ•ด๋†“์œผ๋ ค ํ•œ๋‹ค. ๋‹จ, ์ถœ๋ ฅ ๋ฐ์ดํ„ฐ ๊ธธ์ด๊ฐ€ ๊ธธ๊ธฐ ๋•Œ๋ฌธ์— ์ถœ๋ ฅ ๊ธธ์ด๋ฅผ ์„ค์ •ํ•˜๋Š” OUT ๋ถ€๋ถ„์— HackerRank ์—์„œ ์ œ๊ณตํ•˜๋Š” ์ตœ๋Œ“๊ฐ’์ธ 16,383 ๊ธธ์ด๋ฅผ ์„ค์ •ํ•ด์•ผ ํ•œ๋‹ค.

 

-- Stored Procedure ๋งŒ๋“ค๊ธฐ
DELIMITER $$
CREATE PROCEDURE getPrime(IN n INT,
                          OUT result VARCHAR(16383))
BEGIN
DECLARE j, i, flag INT;
SET j:=2;
SET result:= ' ';
WHILE (j<n) DO
SET i:=2;
SET flag:=0;

WHILE (i<=j) DO
IF (j%i=0) THEN
SET flag:=flag+1;
END IF;
SET i:=i+1;
END WHILE;

IF (flag=1) THEN
SET result:=CONCAT(result, j, '&');
END IF;
SET j:=j+1;
END WHILE;

END $$
-- ๋งŒ๋“  Stored Procedure ํ˜ธ์ถœ(call)ํ•˜๊ธฐ
CALL getPrime(1000, @result);
SELECT SUBSTR(@result, 1, LENGTH(@result)-1);
๋ฐ˜์‘ํ˜•