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

๐Ÿ“ ์•Œ๊ณ ๋ฆฌ์ฆ˜/Programmers - SQL14

[MySQL/PGS] Lv.2 : ๋ถ€๋ชจ์˜ ํ˜•์งˆ์„ ๋ชจ๋‘ ๊ฐ€์ง€๋Š” ๋Œ€์žฅ๊ท  ์ฐพ๊ธฐ https://school.programmers.co.kr/learn/courses/30/lessons/301647 ํ”„๋กœ๊ทธ๋ž˜๋จธ์ŠคSW๊ฐœ๋ฐœ์ž๋ฅผ ์œ„ํ•œ ํ‰๊ฐ€, ๊ต์œก, ์ฑ„์šฉ๊นŒ์ง€ Total Solution์„ ์ œ๊ณตํ•˜๋Š” ๊ฐœ๋ฐœ์ž ์„ฑ์žฅ์„ ์œ„ํ•œ ๋ฒ ์ด์Šค์บ ํ”„programmers.co.krํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค ๋ ˆ๋ฒจ 2.mysql, ๋น„ํŠธ ์—ฐ์‚ฐ์ž ์‚ฌ์šฉ ๋ฌธ์ œ (&, |) ์ฒ˜์Œ์— ์•ˆ ํ’€๋ ค์„œ ํ’€์ด๋ฅผ ์ฐพ์•„๋ดค๋Š”๋ฐ,, ๋น„ํŠธ์—ฐ์‚ฐ์ž๋ฅผ ์“ฐ๋ผ๋Š”๊ฑฐ์ž„?๊ทผ๋ฐ ์™œ ์“ฐ๋Š”๊ฑด์ง€ ์ดํ•ด๊ฐ€ ์•ˆ๋ผ์„œ ๊ณ ๋ฏผํ•˜๋‹ค๊ฐ€ ๊ฒฐ๊ตญ ์ดํ•ดํ•ด๋ฒ„๋ ธ๋‹ค *ใ…*! ๊ทธ๋‹ˆ๊นŒ๋ถ€๋ชจ bit ๊ฐ€ 1์ด๋ฉด ์ž์‹์ด ํ•ญ์ƒ 1์ด์–ด์•ผ ํ•˜๊ณ  (1&1 = 1)๋ถ€๋ชจ๊ฐ€ 0์ด๋ฉด, ์ž์‹์€ 1์ด๋“  0์ด๋“  ์ƒ๊ด€์—†์œผ๋‹ˆ (0&1 = 0, 0&0 = 0)๋ถ€๋ชจ & ์ž์‹ ์—ฐ์‚ฐ ๊ฒฐ๊ณผ๊ฐ’ => ๋ถ€๋ชจ๊ฐ€ ๋˜๋ฉด, ์ž์‹์€ ๋ถ€๋ชจ์˜ ๋ชจ๋“  1์„ ํฌํ•จํ•˜๋Š” ๊ฒƒ์ด๋‹คโ€ผ๏ธex.. 2025. 5. 7.
[MySQL/PGS] Lv.2 : ์—ฐ๋„๋ณ„ ๋Œ€์žฅ๊ท  ํฌ๊ธฐ์˜ ํŽธ์ฐจ ๊ตฌํ•˜๊ธฐ ๐ŸชŸ https://school.programmers.co.kr/learn/courses/30/lessons/299310 ํ”„๋กœ๊ทธ๋ž˜๋จธ์ŠคSW๊ฐœ๋ฐœ์ž๋ฅผ ์œ„ํ•œ ํ‰๊ฐ€, ๊ต์œก, ์ฑ„์šฉ๊นŒ์ง€ Total Solution์„ ์ œ๊ณตํ•˜๋Š” ๊ฐœ๋ฐœ์ž ์„ฑ์žฅ์„ ์œ„ํ•œ ๋ฒ ์ด์Šค์บ ํ”„programmers.co.krํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค ๋ ˆ๋ฒจ 2.์ฒ˜์Œ์—๋Š” ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ํ†ตํ•ด์„œ ํ…Œ์ด๋ธ” 2๊ฐœ๋ฅผ ์ƒˆ๋กœ ์ƒ์„ฑํ•˜๊ณ , ์กฐ์ธ์„ ํ†ตํ•ด ํ•ด๊ฒฐํ–ˆ๋‹ค. ๊ทผ๋ฐ ๋„ˆ๋ฌด ๋น„ํšจ์œจ์ ์ธ ๊ฒƒ ๊ฐ™์•„์„œ ๋ฐฉ๋ฒ•์„ ์ฐพ์•„๋ณด๋‹ˆ '์œˆ๋„์šฐ ํ•จ์ˆ˜'๋ผ๋Š” ๊ฑธ ์‚ฌ์šฉํ•˜๋ฉด ๋œ๋‹ค๊ณ  ํ•จ.๊ทธ๋ž˜์„œ MAX() OVER(PARTITION BY ...) ๋ฅผ ํ™œ์šฉํ•˜์—ฌ ๋‹ค์‹œ ํ’€์–ด๋ณด์•˜๋‹ค!์œˆ๋„์šฐ ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜๋ฉด ์ง‘๊ณ„ ํ•จ์ˆ˜๋ฅผ ํ–‰๋ณ„๋กœ ๊ณ„์‚ฐํ•˜๋ฉด์„œ๋„ ๊ทธ๋ฃนํ™”๋ฅผ ์œ ์ง€ํ•  ์ˆ˜ ์žˆ๋‹ค๊ณ  ํ•œ๋‹ค. ๋Œ€์žฅ๊ท  ์‹œ๋ฆฌ์ฆˆ ํ’€๋ฉด์„œ ์ƒˆ๋กœ์šด ํ•จ์ˆ˜ ๋งŽ์ด ์•Œ์•„๊ฐ‘๋‹ˆ๋‹ค,, ๋‚˜์˜ ํ’€์ด (1)sel.. 2025. 5. 7.
[MySQL/PGS] Lv.1 : ํŠน์ • ํ˜•์งˆ์„ ๊ฐ€์ง€๋Š” ๋Œ€์žฅ๊ท  ์ฐพ๊ธฐ ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค ๋ ˆ๋ฒจ 1 (์ด๊ฒŒ ์™œ....)mysql์ด์ง„์ˆ˜ ๋ณ€ํ™˜์„ ํ™œ์šฉํ•ด์•ผ ํ•˜๋Š” ๋ฌธ์ œ. ๋‚˜๋Š” BIN ํ•จ์ˆ˜๋ฅผ ์ด์šฉํ•ด์„œ ํ’€์ดํ–ˆ๋‹ค.BIN()์€ ๋ฐ˜ํ™˜๊ฐ’์ด string์ด๊ธฐ ๋•Œ๋ฌธ์—, substr๋กœ ์ธ๋ฑ์Šค ์ ‘๊ทผํ•˜๋ฉด ๋จ๊ทผ๋ฐ ์ด๊ฒƒ๋ณด๋‹ค ๊ฐ„๋‹จํ•œ ํ’€์ด๊ฐ€ ๋งŽ์•˜๋‹ค,,, ๋น„ํŠธ์—ฐ์‚ฐ์ž ํ™œ์šฉ ํ’€์ด (select-from ์ƒ๋žต)WHERE GENOTYPE & 2 = 0 AND ( (GENOTYPE & 1) > 0 OR (GENOTYPE & 4) > 0 ); ๋‚˜์˜ ํ’€์ดselect count(*) as COUNTfrom ecoli_datawhere substr(bin(GENOTYPE),length(bin(GENOTYPE))-1,1) = 0 and (substr(bin(GENOTYPE),length(bin(GENOTYPE)),1.. 2025. 5. 7.
[MySQL/PGS] Lv.3 : ๋Œ€์žฅ๊ท ์˜ ํฌ๊ธฐ์— ๋”ฐ๋ผ ๋ถ„๋ฅ˜ํ•˜๊ธฐ 2 https://school.programmers.co.kr/learn/courses/30/lessons/301649 ํ”„๋กœ๊ทธ๋ž˜๋จธ์ŠคSW๊ฐœ๋ฐœ์ž๋ฅผ ์œ„ํ•œ ํ‰๊ฐ€, ๊ต์œก, ์ฑ„์šฉ๊นŒ์ง€ Total Solution์„ ์ œ๊ณตํ•˜๋Š” ๊ฐœ๋ฐœ์ž ์„ฑ์žฅ์„ ์œ„ํ•œ ๋ฒ ์ด์Šค์บ ํ”„programmers.co.krํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค ๋ ˆ๋ฒจ 3 mysql ๋น„์œจ(๋ฐฑ๋ถ„์œ„)์— ๋”ฐ๋ผ ๋“ฑ๊ธ‰์„ ๋‚˜๋ˆ„๋Š” ์ฟผ๋ฆฌ๋Š”NTILE() ๋˜๋Š” PERCENT_RANK() ๊ฐ™์€ ํ•จ์ˆ˜๋กœ ๊ตฌํ˜„์ด ๊ฐ€๋Šฅํ•˜๋‹ค.์ด๋ฒˆ์— ์ฒ˜์Œ ์•Œ์•˜๋Š”๋ฐ..์™ธ์›Œ๋‘์ž ๐Ÿฅน ๋“ฑ๊ธ‰ ๋‚˜๋ˆ„๊ธฐ = case when then + ntile ์‘์šฉ!! ๋‚˜์˜ ํ’€์ดselect ID, CASE NTILE(4) OVER (order by SIZE_OF_COLONY desc) when 1 then 'CRITICAL' when 2 then 'H.. 2025. 5. 7.
[MySQL/PGS] Lv.2 : ๋ถ„๊ธฐ๋ณ„ ๋ถ„ํ™”๋œ ๋Œ€์žฅ๊ท ์˜ ๊ฐœ์ฒด ์ˆ˜ ๊ตฌํ•˜๊ธฐ https://school.programmers.co.kr/learn/courses/30/lessons/299308 ํ”„๋กœ๊ทธ๋ž˜๋จธ์ŠคSW๊ฐœ๋ฐœ์ž๋ฅผ ์œ„ํ•œ ํ‰๊ฐ€, ๊ต์œก, ์ฑ„์šฉ๊นŒ์ง€ Total Solution์„ ์ œ๊ณตํ•˜๋Š” ๊ฐœ๋ฐœ์ž ์„ฑ์žฅ์„ ์œ„ํ•œ ๋ฒ ์ด์Šค์บ ํ”„programmers.co.krํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค ๋ ˆ๋ฒจ 2.๋ฌธ์ž์—ด, ๋‚ ์งœํ˜• ๋ฌธ๋ฒ• ์‘์šฉ ๋ฌธ์ž๋ฅผ ์ด์–ด ๋ถ™์ด๋ ค๋ฉด concat ํ•จ์ˆ˜ ์‚ฌ์šฉ,๋‚ ์งœ์˜ ๋ถ„๊ธฐ๋ฅผ ๊ตฌํ•˜๋ ค๋ฉด quarter ํ•จ์ˆ˜ ์‚ฌ์šฉ!! ๋‚˜์˜ ํ’€์ดselect concat(QUARTER(DIFFERENTIATION_DATE),'Q') as QUARTER, count(*) as ECOLI_COUNTfrom ecoli_datagroup by QUARTERorder by QUARTER; 2025. 5. 7.
[MySQL/PGS] Lv.4 : ํŠน์ • ์„ธ๋Œ€์˜ ๋Œ€์žฅ๊ท  ์ฐพ๊ธฐ https://school.programmers.co.kr/learn/courses/30/lessons/301650 ํ”„๋กœ๊ทธ๋ž˜๋จธ์ŠคSW๊ฐœ๋ฐœ์ž๋ฅผ ์œ„ํ•œ ํ‰๊ฐ€, ๊ต์œก, ์ฑ„์šฉ๊นŒ์ง€ Total Solution์„ ์ œ๊ณตํ•˜๋Š” ๊ฐœ๋ฐœ์ž ์„ฑ์žฅ์„ ์œ„ํ•œ ๋ฒ ์ด์Šค์บ ํ”„programmers.co.krํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค ๋ ˆ๋ฒจ 4.mysql ๋‚˜๋Š” ์ œ์ผ ์‹ฌํ”Œํ•˜๊ฒŒ.. ๋™์ผ ํ…Œ์ด๋ธ”์„ ์—ฌ๋Ÿฌ๊ฐœ ์„ ์–ธํ•ด์„œ ๊ฐ’์„ ๊ฒ€์‚ฌํ•˜๋Š” ๋ฐฉ๋ฒ•์œผ๋กœ ํ•ด๊ฒฐํ–ˆ๋‹ค.JOIN์„ ๋‘ ๋ฒˆ ์‚ฌ์šฉํ•˜๊ฑฐ๋‚˜, exists, in ๋“ฑ์„ ์‚ฌ์šฉํ•˜๋Š” ๋“ฑ ๋‹ค์–‘ํ•œ ํ•ด๊ฒฐ ๋ฐฉ๋ฒ•์ด ์žˆ๋Š” ๋“ฏ ํ•˜๋‹ค ๋‚˜์˜ ํ’€์ดselect a.id as IDfrom ecoli_data a, ecoli_data b, ecoli_data cwhere a.parent_id = b.id and b.parent_id = c.id and .. 2025. 5. 7.
728x90
๋ฐ˜์‘ํ˜•