๐Ÿ”ฅ ๋‚ด์ผ๋ฐฐ์›€์บ ํ”„ DA

231213 WED SQL๋กœ ๊ฐ€๊ณตํ•˜๊ธฐ, ์กฐ๊ฑด๋ฌธ

ํ–‰ํŒฝ 2023. 12. 17. 01:13

๊ณต๋ถ€ 2์ผ์ฐจ. ๊ฐ•์˜๋Š” ํ•˜๋‚˜๋ฐ–์— ๋“ฃ์ง€ ๋ชปํ–ˆ์ง€๋งŒ ์ฝ”๋“œ์นดํƒ€๋ฅผ 10๊ฐœ ํ’€์—ˆ๋‹ค. ์ œ๋ฒ•,, ์•„๋‹ˆ ๋„ˆ๋ฌด ์žฌ๋ฐŒ๋”๋ผ๊ณ  ๐Ÿ˜Ž


 

Liked

 

  • ์ฝ”๋“œ์นดํƒ€๋Š” ํ’€๊ณ  ๋‚˜์„œ ๋‹ค๋ฅธ ์‚ฌ๋žŒ์ด ์ œ์ถœํ•œ ์ฝ”๋“œ๋ฅผ ๋ณด๋Š” ๊ฒŒ ์ œ์ผ ์žฌ๋ฐŒ๋‹ค.
  • ์ฑ—gpt ์‚ฌ์šฉ ์ด๋ž˜๋กœ ์ œ์ผ ์•ผ๋ฌด์ง€๊ฒŒ ์จ๋จน๋Š” ์ค‘์ด๋‹ค. ์ด ๋ถ€๋ถ„ ์ดํ•ด๊ฐ€ ์•ˆ ๋˜๋‹ˆ๊นŒ ๋‹ค๋ฅธ ์‚ฌ๋ก€๋ฅผ ๋“ค์–ด์„œ ๋” ์ž์„ธํ•˜๊ฒŒ ๋‹ค์‹œ ์„ค๋ช…ํ•ด ๋“ฑ๋“ฑ.

 


Lacked

 

  • ์ŠคํŠธ๋ ˆ์นญ์˜ ํ•„์š”์„ฑ์„ ์ ˆ๊ฐํ–ˆ๋‹ค.
  • ์‹ค์Šต ์œ„์ฃผ๋กœ ์ง„ํ–‰๋˜์–ด ์ด๋ก ์ ์ธ ๋ถ€๋ถ„์ด ์ข€ ๋ถ€์กฑํ•œ ๊ฒƒ ๊ฐ™๋‹ค. ๊ฐ„๋‹จํ•œ ์˜ˆ๋กœ, '๊ฐ’'์ด๋ผ๊ณ  ๋ถˆ๋Ÿฌ๋„ ๋˜๋Š”์ง€, '๋ฐ์ดํ„ฐ'๋ผ๊ณ  ๋ถˆ๋Ÿฌ๋„ ๋˜๋Š”์ง€, ์ข€ ์—„๋ฐ€ํ•œ ์›Œ๋”ฉ์„ ์•Œ ์ˆ˜๊ฐ€ ์—†์–ด ๊ฐ•์˜ ๋“ฃ๊ณ  ๋…ธ์…˜์— ํ•„๊ธฐํ•  ๋•Œ ๋ฉˆ์นซํ•˜๋Š” ์ˆœ๊ฐ„์ด ์žˆ๋‹ค.

 

 

Learned

 

1. SQL๋กœ ๊ฐ€๊ณตํ•˜๊ธฐ

replace(์ปฌ๋Ÿผ, ๋ฌธ์ž, ๋ฐ”๊พผ ํ›„ ๋ฌธ์ž)          #๋Œ€์ฒดํ•œ๋‹ค
substr(์ปฌ๋Ÿผ, ์‹œ์ž‘ ์œ„์น˜, ๊ธ€์ž ์ˆ˜)           #๋ฌธ์ž์—ด์„ ์„ผ๋‹ค
concat(๋ถ™์ด๊ณ  ์‹ถ์€ ๊ฐ’1, ๋ถ™~๊ฐ’2, ๋ถ™~๊ฐ’3, …) #์—ฐ์‡„์‹œํ‚จ๋‹ค(concatenate)

์ง๊ด€์ ์ด๋‹ค.

 

 

2. ์กฐ๊ฑด๋ฌธ

#if
if(์กฐ๊ฑด, ์กฐ๊ฑด ์ถฉ์กฑo ๊ฐ’, ์กฐ๊ฑด ์ถฉ์กฑx ๊ฐ’)

#case
case when ์กฐ๊ฑด1 then ๊ฒฐ๊ณผ1
     when ์กฐ๊ฑด2 then ๊ฒฐ๊ณผ2
     else ๊ทธ ์™ธ ๊ฒฐ๊ณผ
     end

์‰ฌ์›Œ ๋ณด์ด์ง€๋งŒ ๋‹ค์Œ ์ฝ”๋“œ๋“ค์ฒ˜๋Ÿผ ์„ž์ด๊ณ  ์„ž์ด๋ฉด ํ•œ๋„ ๋๋„ ์—†์ด ํ—ท๊ฐˆ๋ฆฌ๊ธฐ ์‰ฝ๋‹ค.

 

 

3. ๋ฏน์Šค๋งค์น˜

 

โ‘  if๋ฌธ, substr๋ฌธ

SELECT substr(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) as "์ด๋ฉ”์ผ ๋„๋ฉ”์ธ",
       count(customer_id) "๊ณ ๊ฐ ์ˆ˜",
       avg(age) "ํ‰๊ท  ์—ฐ๋ น"
       # if๋ฌธ : email ์ด gmail์„ ํฌํ•จํ•  ๋•Œ, gmail์„ @gmail๋กœ ์จ์ค˜.
       # substr๋ฌธ : if๋ฌธ์ด ํ•ด๋‹น๋˜๋Š” ์ปฌ๋Ÿผ์—์„œ, 10๋ฒˆ์งธ ๋ฌธ์ž๋ถ€ํ„ฐ ์ถœ๋ ฅํ•ด์ค˜.
FROM customers
group by 1

 

์‹คํ–‰ ๊ฒฐ๊ณผ

 

 

โ‘ก if๋ฌธ, ์—ฐ์‚ฐ์ž

SELECT case when delivery_time >30 then price*0.1*(if(addr like '%์„œ์šธ%', 1.1, 1))
            when delivery_time >25 then price*0.05*(if(addr like '%์„œ์šธ%', 1.1, 1))
            else 0 end as "๋ฐฐ๋‹ฌ์ˆ˜์ˆ˜๋ฃŒ"
            # ๋ฐฐ๋‹ฌ ์‹œ๊ฐ„์ด 25๋ถ„ ์ดˆ๊ณผ 30๋ถ„ ์ดํ•˜๋ฉด, ์ˆ˜์ˆ˜๋ฃŒ๋Š” ๊ฐ€๊ฒฉ*0.05
            # ๋ฐฐ๋‹ฌ ์‹œ๊ฐ„์ด 30๋ถ„ ์ดˆ๊ณผ๋ฉด, ์ˆ˜์ˆ˜๋ฃŒ๋Š” ๊ฐ€๊ฒฉ*0.1
            # ๋‹จ, ์ง€์—ญ์ด ์„œ์šธ์ผ ๊ฒฝ์šฐ, ์ถ”๊ฐ€๋กœ ๊ฐ€๊ฒฉ*1.1
FROM food_orders

 

์‹คํ–‰ ๊ฒฐ๊ณผ

 

 

4. (์ฝ”๋“œ์นดํƒ€ ์ค‘ ์•Œ๊ฒŒ ๋œ) having

 

SELECT NAME,
       COUNT(NAME)       #๊ทธ๋ฃน๋งˆ๋‹ค ๋™์ผํ•œ ์ด๋ฆ„์„ ๊ฐ€์ง„ ๋™๋ฌผ์ด ๋ช‡ ๋งˆ๋ฆฌ ์žˆ๋Š”์ง€ ํ™•์ธ
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME            # ๋™์ผํ•œ ์ด๋ฆ„์„ ๊ฐ€์ง„ ๋™๋ฌผ๋“ค ๋ฌถ๊ธฐ
HAVING COUNT(NAME) >= 2  #COUNT ํ•จ์ˆ˜๋กœ ์„ผ ๊ฒฐ๊ณผ์—์„œ ์ด๋ฆ„์ด ๋‘ ๋ฒˆ ์ด์ƒ ๋‚˜ํƒ€๋‚œ ๊ฒฝ์šฐ๋งŒ ์„ ํƒ
ORDER BY NAME;

 

 

5. (์ฝ”๋“œ์นดํƒ€ ์ค‘ ์•Œ๊ฒŒ ๋œ) limit

 

SELECT name
FROM ANIMAL_INS
ORDER BY datetime limit 1

 

  • ๋‚˜๋Š” ์ฒ˜์Œ์— ์•„๋ฌด ์ƒ๊ฐ ์—†์ด where ์ ˆ์— min() ํ•จ์ˆ˜๋ฅผ ์ผ๋‹ค. 
  • min() ํ•จ์ˆ˜๋Š” ์„œ๋ธŒ์ฟผ๋ฆฌ๋‚˜ ์กฐ๊ฑด์ ˆ์—์„œ ์‚ฌ์šฉ.
SELECT name
FROM animal_ins
WHERE min(datetime) #์˜ค๋ฅ˜

 

 

6. (์ฝ”๋“œ์นดํƒ€ ์ค‘ ์•Œ๊ฒŒ ๋œ) ์„œ๋ธŒ์ฟผ๋ฆฌ

 

SELECT name
FROM animal_ins
WHERE datetime = (SELECT MIN(DATETIME) FROM ANIMAL_INS)

 

 

 

Longed for

 

  • ํŒŒ์ด์ฌ ์ง„๋„๋„ ํ›„๋”ฑํ›„๋”ฑ ๋‚˜๊ฐ€๊ณ  ์‹ถ๋‹ค.