[ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 299307/301649 ๋Œ€์žฅ๊ท ์˜ ํฌ๊ธฐ์— ๋”ฐ๋ผ ๋ถ„๋ฅ˜ํ•˜๊ธฐ 1, 2 (CASE WHEN , WINDOW ํ•จ์ˆ˜)

2025. 2. 10. 17:41ยท๐Ÿ’ป ์•Œ๊ณ ๋ฆฌ์ฆ˜/SQL

 

ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค SQL ๊ณ ๋“์  Kit ์˜ SELECT ๋ฌธ์ œ ์ค‘์—์„œ ๋Œ€์žฅ๊ท ์˜ ํฌ๊ธฐ์— ๋”ฐ๋ผ ๋ถ„๋ฅ˜ํ•˜๊ธฐ 1,2๋ฅผ ํ’€์–ด๋ณด์•˜๋‹ค.

๊ฐ™์€ level3 ๋กœ ๋ฌถ์—ฌ์žˆ์œผ๋‚˜ ์ฒด๊ฐ์ƒ ๋ ˆ๋ฒจ ์ฐจ์ด๊ฐ€ ์žˆ๋‹ค๊ณ  ๋А๊ปด์กŒ๋‹ค..

case when ๊ณผ windowํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ํ’€์ดํ–ˆ๋‹ค.


๋ฌธ์ œ 1

https://school.programmers.co.kr/learn/courses/30/lessons/299307

 

ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค

SW๊ฐœ๋ฐœ์ž๋ฅผ ์œ„ํ•œ ํ‰๊ฐ€, ๊ต์œก, ์ฑ„์šฉ๊นŒ์ง€ Total Solution์„ ์ œ๊ณตํ•˜๋Š” ๊ฐœ๋ฐœ์ž ์„ฑ์žฅ์„ ์œ„ํ•œ ๋ฒ ์ด์Šค์บ ํ”„

programmers.co.kr

 

๋Œ€์žฅ๊ท  ๊ฐœ์ฒด์˜ ํฌ๊ธฐ๊ฐ€ 100 ์ดํ•˜๋ผ๋ฉด 'LOW', 100 ์ดˆ๊ณผ 1000 ์ดํ•˜๋ผ๋ฉด 'MEDIUM', 1000 ์ดˆ๊ณผ๋ผ๋ฉด 'HIGH'๋ผ๊ณ  ๋ถ„๋ฅ˜ํ•ฉ๋‹ˆ๋‹ค. ๋Œ€์žฅ๊ท  ๊ฐœ์ฒด์˜ ID(ID)์™€ ๋ถ„๋ฅ˜(SIZE)๋ฅผ ์ถœ๋ ฅํ•˜๋Š” SQL ๋ฌธ์„ ์ž‘์„ฑํ•ด ์ฃผ์„ธ์š”. ์ด๋•Œ ๊ฒฐ๊ณผ๋Š” ๊ฐœ์ฒด์˜ ID์— ๋Œ€ํ•ด ์˜ค๋ฆ„์ฐจ์ˆœ ์ •๋ ฌํ•ด ์ฃผ์„ธ์š”.

 

 

๋ฌธ์ œ์— ์•ž์„œ...

๋ฌธ์ œ์˜ ์กฐ๊ฑด์— ๋งž๊ฒŒ select๋ฅผ ํ•˜๊ธฐ ์œ„ํ•ด SQL์˜ ์กฐ๊ฑด๋ฌธ ์ค‘ ํ•˜๋‚˜์ธ case when์„ ์‚ฌ์šฉํ•œ๋‹ค.

case when ์กฐ๊ฑด๋ฌธ

๊ธฐ๋ณธ ๋ฌธ๋ฒ•์€ ์•„๋ž˜์™€ ๊ฐ™๋‹ค.

CASE 
    WHEN ์กฐ๊ฑด1 THEN ๊ฒฐ๊ณผ1
    WHEN ์กฐ๊ฑด2 THEN ๊ฒฐ๊ณผ2
    ELSE ๊ธฐ๋ณธ๊ฐ’
END

 

์ถ”๊ฐ€๋กœ case when ์ ˆ์€ ์•„๋ž˜์™€ ๊ฐ™์€ ํŠน์ง•์„ ๊ฐ€์ง€๊ณ  ์žˆ๋‹ค.

โœ… ์กฐ๊ฑด์— ๋”ฐ๋ผ ๋‹ค๋ฅธ ๊ฐ’ ๋ฐ˜ํ™˜ ๊ฐ€๋Šฅ
โœ… ์ง‘๊ณ„ ํ•จ์ˆ˜์™€ ํ•จ๊ป˜ ์‚ฌ์šฉ ๊ฐ€๋Šฅ
โœ… ์ •๋ ฌ ๊ธฐ์ค€์„ ๋™์ ์œผ๋กœ ๋ณ€๊ฒฝ ๊ฐ€๋Šฅ
โœ… ์œˆ๋„์šฐ ํ•จ์ˆ˜์™€ ํ•จ๊ป˜ ์‚ฌ์šฉ ๊ฐ€๋Šฅ

 

 

๋ฌธ์ œ ํ’€์ด

1. ์กฐ๊ฑด์— ๋งž๋Š” case when ์กฐ๊ฑด๋ฌธ์„ ์ž‘์„ฑํ•œ๋‹ค.

select case when size_of_colony <=100 then "LOW"
	when size_of_colony <=1000 then "MEDIUM"
	else "HIGH"
    	end as size
from ecoli_data

 

 

์ฝ”๋“œ

select id, case when size_of_colony <=100 then "LOW"
                when size_of_colony <=1000 then "MEDIUM"
                else "HIGH"
                end as size
from ecoli_data
order by id

 

ํฌ๊ฒŒ ์–ด๋ ต์ง€ ์•Š์€ ๋ฌธ์ œ์˜€๋‹ค.

 

 


๋ฌธ์ œ 2

https://school.programmers.co.kr/learn/courses/30/lessons/301649

 

ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค

SW๊ฐœ๋ฐœ์ž๋ฅผ ์œ„ํ•œ ํ‰๊ฐ€, ๊ต์œก, ์ฑ„์šฉ๊นŒ์ง€ Total Solution์„ ์ œ๊ณตํ•˜๋Š” ๊ฐœ๋ฐœ์ž ์„ฑ์žฅ์„ ์œ„ํ•œ ๋ฒ ์ด์Šค์บ ํ”„

programmers.co.kr

 

๋Œ€์žฅ๊ท  ๊ฐœ์ฒด์˜ ํฌ๊ธฐ๋ฅผ ๋‚ด๋ฆผ์ฐจ์ˆœ์œผ๋กœ ์ •๋ ฌํ–ˆ์„ ๋•Œ ์ƒ์œ„ 0% ~ 25% ๋ฅผ 'CRITICAL', 26% ~ 50% ๋ฅผ 'HIGH', 51% ~ 75% ๋ฅผ 'MEDIUM', 76% ~ 100% ๋ฅผ 'LOW' ๋ผ๊ณ  ๋ถ„๋ฅ˜ํ•ฉ๋‹ˆ๋‹ค. ๋Œ€์žฅ๊ท  ๊ฐœ์ฒด์˜ ID(ID) ์™€ ๋ถ„๋ฅ˜๋œ ์ด๋ฆ„(COLONY_NAME)์„ ์ถœ๋ ฅํ•˜๋Š” SQL ๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”. ์ด๋•Œ ๊ฒฐ๊ณผ๋Š” ๊ฐœ์ฒด์˜ ID ์— ๋Œ€ํ•ด ์˜ค๋ฆ„์ฐจ์ˆœ ์ •๋ ฌํ•ด์ฃผ์„ธ์š” . ๋‹จ, ์ด ๋ฐ์ดํ„ฐ์˜ ์ˆ˜๋Š” 4์˜ ๋ฐฐ์ˆ˜์ด๋ฉฐ ๊ฐ™์€ ์‚ฌ์ด์ฆˆ์˜ ๋Œ€์žฅ๊ท  ๊ฐœ์ฒด๊ฐ€ ์„œ๋กœ ๋‹ค๋ฅธ ์ด๋ฆ„์œผ๋กœ ๋ถ„๋ฅ˜๋˜๋Š” ๊ฒฝ์šฐ๋Š” ์—†์Šต๋‹ˆ๋‹ค.

 

 

 

๋ฌธ์ œ์— ์•ž์„œ...

์ด์ „ ํฌ์ŠคํŒ…์—์„œ window ํ•จ์ˆ˜์— ๋Œ€ํ•ด ๊ฐ„๋‹จํžˆ ์ •๋ฆฌํ–ˆ์—ˆ๋‹ค.

2025.02.07 - [SQL] - [ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 299310 ์—ฐ๋„๋ณ„ ๋Œ€์žฅ๊ท  ํฌ๊ธฐ์˜ ํŽธ์ฐจ ๊ตฌํ•˜๊ธฐ (์„œ๋ธŒ์ฟผ๋ฆฌ, Window ํ•จ์ˆ˜)

ํ•ด๋‹น ๋ฌธ์ œ์—์„œ๋Š” ๊ทธ ์ค‘์—์„œ๋„ percent_rank() ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•œ๋‹ค.

PERCENT_RANK() OVER (
  PARTITION BY partition_expression 
  ORDER BY 
    sort_expression [ASC | DESC]
)

 

 

๋ฌธ์ œ ํ’€์ด

์•ž์„  ๋ฌธ์ œ๋ณด๋‹ค ์กฐ๊ฑด์ด ์กฐ๊ธˆ ๊ตฌ์ฒดํ™”๋˜์—ˆ๋‹ค.

window ํ•จ์ˆ˜๋ฅผ case when๊ณผ ํ•จ๊ป˜ ์‚ฌ์šฉํ•˜์˜€๋‹ค.

select id, percent_rank() over (order by size_of_colony) r from ecoli_data

size_of_colony๋ฅผ ๊ธฐ์ค€์œผ๋กœ ๋ฐฑ๋ถ„์œจ ์ˆœ์œ„๋ฅผ ๊ตฌํ•œ๋‹ค.

๊ตฌํ•œ r ๊ฐ’์„ ๊ธฐ์ค€์œผ๋กœ case when์„ ์ž‘์„ฑํ•˜๋ฉด ๋œ๋‹ค.

 

 

์ฝ”๋“œ

select id, 
    case when r <=0.25 then "LOW"
    when r<=0.5 then "MEDIUM"
    when r<=0.75 then "HIGH"
    else "CRITICAL"
    end as colony_name
from (select id, percent_rank() over (order by size_of_colony) r from ecoli_data) ranking
order by id

window ํ•จ์ˆ˜๋ฅผ ํ†ตํ•ด percent_rank๋ฅผ ๊ตฌํ•˜์—ฌ ์„œ๋ธŒ์ฟผ๋ฆฌ๋กœ ์‚ฌ์šฉํ•œ๋‹ค.

 

 

 

 

์ƒˆ๋กœ์šด ๋ฌธ์ œ๋“ค์ด ์ถ”๊ฐ€๋˜๊ฒ ์ง€๋งŒ.. ์ด์ œ ๋ช‡ ๋ฌธ์ œ ์•ˆ ๋‚จ์•˜๋”ฐ.!!! ๐Ÿ˜

'๐Ÿ’ป ์•Œ๊ณ ๋ฆฌ์ฆ˜ > SQL' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€

[ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 301650 ํŠน์ • ์„ธ๋Œ€์˜ ๋Œ€์žฅ๊ท  ์ฐพ๊ธฐ (์ž๊ธฐ ์ฐธ์กฐ, ์„œ๋ธŒ ์ฟผ๋ฆฌ)  (0) 2025.02.17
[ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 299310 ์—ฐ๋„๋ณ„ ๋Œ€์žฅ๊ท  ํฌ๊ธฐ์˜ ํŽธ์ฐจ ๊ตฌํ•˜๊ธฐ (์„œ๋ธŒ์ฟผ๋ฆฌ, Window ํ•จ์ˆ˜)  (0) 2025.02.07
[ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 298519 ํŠน์ • ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜๋Š” ๋ฌผ๊ณ ๊ธฐ๋ณ„ ์ˆ˜์™€ ์ตœ๋Œ€ ๊ธธ์ด ๊ตฌํ•˜๊ธฐ - SQL ๊ณ ๋“์  Kit  (4) 2025.02.06
'๐Ÿ’ป ์•Œ๊ณ ๋ฆฌ์ฆ˜/SQL' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€
  • [ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 301650 ํŠน์ • ์„ธ๋Œ€์˜ ๋Œ€์žฅ๊ท  ์ฐพ๊ธฐ (์ž๊ธฐ ์ฐธ์กฐ, ์„œ๋ธŒ ์ฟผ๋ฆฌ)
  • [ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 299310 ์—ฐ๋„๋ณ„ ๋Œ€์žฅ๊ท  ํฌ๊ธฐ์˜ ํŽธ์ฐจ ๊ตฌํ•˜๊ธฐ (์„œ๋ธŒ์ฟผ๋ฆฌ, Window ํ•จ์ˆ˜)
  • [ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 298519 ํŠน์ • ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜๋Š” ๋ฌผ๊ณ ๊ธฐ๋ณ„ ์ˆ˜์™€ ์ตœ๋Œ€ ๊ธธ์ด ๊ตฌํ•˜๊ธฐ - SQL ๊ณ ๋“์  Kit
.๋ฐ.
.๋ฐ.
  • .๋ฐ.
    Do IT
    .๋ฐ.
  • ์ „์ฒด
    ์˜ค๋Š˜
    ์–ด์ œ
    • All (40)
      • ๐Ÿ’ป ์•Œ๊ณ ๋ฆฌ์ฆ˜ (21)
        • PS (16)
        • SQL (4)
        • ์ด๋ก  (5)
      • ๐ŸŽˆcapstone (2)
      • ๐Ÿ’ชBackend (12)
        • Django (8)
        • Spring (4)
  • ๋ธ”๋กœ๊ทธ ๋ฉ”๋‰ด

    • ํ™ˆ
    • ํƒœ๊ทธ
    • ๋ฐฉ๋ช…๋ก
  • ๋งํฌ

  • ๊ณต์ง€์‚ฌํ•ญ

  • ์ธ๊ธฐ ๊ธ€

  • ํƒœ๊ทธ

    python
    ์ฝ”ํ…Œ
    bruteforce
    ๋ฐฑ์ค€
    ์•Œ๊ณ ๋ฆฌ์ฆ˜
    ๋‹ค์ค‘์กฐ์ธ
    ์Šคํ”„๋ง๋ฐฐ์น˜
    SQL
    windowํ•จ์ˆ˜
    Django
    MYSQL
    springscheduler
    ETL
    ํŒŒ์ด์ฌ
    ์žฌ๊ท€
    responsecustomclass
    PS
    programmers
    BFS
    Batch
    ์‘๋‹ตํ˜•์‹
    ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค
    ์„œ๋ธŒ์ฟผ๋ฆฌ
    BOJ
    ์Šค์ผ€์ค„๋Ÿฌ
    ์ž๋ฐ”
    resposneentity
    apiresponse
    ๋ฌธ์ œํ’€์ด
    crud
  • ์ตœ๊ทผ ๋Œ“๊ธ€

  • ์ตœ๊ทผ ๊ธ€

  • hELLOยท Designed By์ •์ƒ์šฐ.v4.10.3
.๋ฐ.
[ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 299307/301649 ๋Œ€์žฅ๊ท ์˜ ํฌ๊ธฐ์— ๋”ฐ๋ผ ๋ถ„๋ฅ˜ํ•˜๊ธฐ 1, 2 (CASE WHEN , WINDOW ํ•จ์ˆ˜)
์ƒ๋‹จ์œผ๋กœ

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