본문 바로가기
카테고리 없음

3주차 SQL 강의 요약 및 코드카타 문풀

by 그 해 우리는 2024. 11. 25.

 

 

02. 업무 필요한 문자 포맷이 다를 때, SQL로 가공하기 (REPLACE, SUBSTRING, CONCAT)

출처 입력

 

2) 특정 문자를 다른 문자로 바꾸기 (실습 포함)

스파르타 코딩 클럽

  1. REPLACE
대표사진 삭제

사진 설명을 입력하세요.

[실습2] (주소의 ‘문곡리’ 를 ‘문가리’ 로 바꾸기)

대표사진 삭제

사진 설명을 입력하세요.

2. SUBSTRING

3) 원하는 문자만 남기기 (실습 포함)

대표사진 삭제

사진 설명을 입력하세요.

substr(조회 할 컬럼, 시작 위치, 글자 수)

 

3. CONCAT

4) 여러 컬럼의 문자를 합치기 (실습 포함)

원하는 문자가 여러 컬럼에 있을 때, 하나로 합쳐서 업무에 필요한 형태로 만들 수 있습니다

concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)

대표사진 삭제

사진 설명을 입력하세요.

03. [실습] 문자 데이터를 바꾸고, GROUP BY 사용하기

스파르타 코딩 클럽

1) [실습] 서울 지역의 음식 타입별 평균 음식 주문금액 구하기 (출력 : ‘서울’, ‘타입’, ‘평균 금액’)

 

  1. 어떤 함수 (수식) 을 이용해야 하는가
  • 1.Query 를 적기 전에 흐름을 정리해보기 (해답)
  1. 어떤 테이블에서 데이터를 뽑을 것인가 → 주문 테이블
  2. 어떤 컬럼을 이용할 것인가 → 주문 금액, 음식 타입, 주소
  3. 어떤 조건을 지정해야 하는가 → 서울 지역
  4. 어떤 함수 (수식) 을 이용해야 하는가 → 평균 구하는 수식, 특정 문자만 뽑는 기능
  • 2.구문으로 만들기
  1. 어떤 테이블에서 데이터를 뽑을 것인가
  2. 어떤 컬럼을 이용할 것인가
  3. 어떤 조건을 지정해야 하는가
  4. 어떤 함수 (수식) 을 이용해야 하는가
  • 2.구문으로 만들기 (해답)
  1. 어떤 테이블에서 데이터를 뽑을 것인가 → from food_orders
  2. 어떤 컬럼을 이용할 것인가 → price, cuisine_type, addr
  3. 어떤 조건을 지정해야 하는가 → where addr like ‘%서울%’
  4. 어떤 함수 (수식) 을 이용해야 하는가 → avg(price), substring(addr, 1, 2)
대표사진 삭제

사진 설명을 입력하세요.

2) [실습] 이메일 도메인별 고객 수와 평균 연령 구하기

대표사진 삭제

사진 설명을 입력하세요.

대표사진 삭제

사진 설명을 입력하세요.

04. 조건에 따라 포맷을 다르게 변경해야한다면 (IF, CASE)

스파르타 코딩클럽

1) Group by 처럼 조건도 카테고리별로 줄 수 있을까?

- 범주별로 값을 구할 때는 group by 를 썻죠. 범주별로 다른 연산 (계산, 문자 바꾸기) 을 적용할 수도 있을까요?

- SQL 은 조건에 따라 연산을 적용할 수 있는 기능을 제공합니다 ’내가 원하는 범주’ 를 조건으로 주고, 해당 범주에 적용하고 싶은 것을 지정해 주는 방식입니다.

대표사진 삭제

사진 설명을 입력하세요.

2) 조건에 따라 다른 방법을 적용하고 싶을 때 - If 문 기초 (실습 포함)

대표사진 삭제

사진 설명을 입력하세요.

EX)

select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",

count(customer_id) "고객 수",

avg(age) "평균 연령"

from customers

group by 1

대표사진 삭제

사진 설명을 입력하세요.

3) 조건을 여러가지 지정하고 싶을 때 - Case 문 기초 (실습 포함)

조건을 지정하다보면, 두 개 이상 지정을 해야 할 경우가 생깁니다. 이 때는 case 문을 이용하여 여러번의 if 문을 적용 한 효과를 낼 수 있습니다

대표사진 삭제

사진 설명을 입력하세요.

select restaurant_name,

cuisine_type AS "원래 음식 타입",

case when (cuisine_type='Korean') then '한식'

else '기타'

end as " 음식 타입"

from food_orders

대표사진 삭제

사진 설명을 입력하세요.

4) 조건을 사용할 수 있는 경우 알아보기

05. [실습] SQL로 간단한 User Segmentation 해보기

스파르타 코딩 클럽

select name,

age,

gender,

case when (age between 10 and 19) and gender='male' then "10대 남자"

when (age between 10 and 19) and gender='female' then "10대 여자"

when (age between 20 and 29) and gender='male' then "20대 남자"

when (age between 20 and 29) and gender='female' then "20대 여자" end "그룹"

from customers

where age between 10 and 29

 

06. [실습] 조건문으로 서로 다른 식을 적용한 수수료 구해보기

스파르타

select restaurant_name,

order_id,

delivery_time,

price,

addr,

case when delivery_time>25 and delivery_time<=30 then price*0.05*(if(addr like '%서울%', 1.1, 1))

when delivery_time>30 then price*1.1*(if(addr like '%서울%', 1.1, 1))

else 0 end "수수료"

from food_orders

 

select order_id,

price,

quantity,

day_of_the_week,

if(day_of_the_week='Weekday', 3000, 3500)*(if(quantity<=3, 1, 1.2)) "할증료"

from food_orders

 

07. SQL문에 문제가 없는 것 같은데 왜 오류가 나나요_ (Data Type 오류 해결하기)

출처 입력

대표사진 삭제

사진 설명을 입력하세요.

1. 코드카타

-- 코드를 입력하세요

SELECT NAME, count(name)

from animal_ins

where NAME IS NOT NULL

GROUP BY NAME

HAVING COUNT(NAME) >=2

order by name

select cuisine_type,

min(price) min_price,

max(price) max_price

from food_orders

group by cuisine_type

order by min(price) desc

 

select addr "원래 주소",

substr(addr, 1, 2) "시도"

from food_orders

where addr like '%서울특별시%'

 

select restaurant_name "원래 이름",

select case when (age between 10 and 19) and gender='male' then '10대 남성'

when (age between 10 and 19) and gender='female' then '10대 여성'

when (age between 20 and 29) and gender='male' then '20대 남성'

when (age between 20 and 29) and gender='female' then '20대 여성' end "고객분류",

name,

age,

gender

from customers

where age between 10 and 29

 

select restaurant_name,

price/quantity "단가",

cuisine_type,

order_id,

case when (price/quantity <5000) and cuisine_type='Korean' then '한식1'

when (price/quantity between 5000 and 15000) and cuisine_type='Korean' then '한식2'

when (price/quantity > 15000) and cuisine_type='Korean' then '한식3'

when (price/quantity <5000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식1'

when (price/quantity between 5000 and 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식2'

when (price/quantity > 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식3'

when (price/quantity <5000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타1'

when (price/quantity between 5000 and 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타2'

when (price/quantity > 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타3' end "식당 그룹"

from food_orders

 

연산자
연산의 예
의미
>, <
N<10, N>10
N이 10 미만, N이 10 초과
>=, <=
N<=10, N>=10
N이 10 이하, N이 10 이상
=
N=10
N이 10인 값
!=
N!=10
N이 10이 아닌 값
LIKE
LIKE('%과자%') 맛있는 과자 과자는 살찐다
문자열이 ~와 같을 때(문자열을 기준으로 앞,뒤 문자열과 상관없이 볼 때 %를 앞뒤에 작성해줍니다.)
AND
A AND B
A 그리고 B를 모두 만족하는 값
OR
A OR B
A 또는 B인 값
NOT, !
NOT A, !A
A가 아닌 값
BETWEEN
A BETWEEN 10 AND 20
A가 10과 20 사이에 포함된 값
IN
A IN B
B에 A가 포함된 값
NOT IN
A NOT IN B
B에 A가 포함되지 않은 값
IS NULL
A IS NULL
A 값이 비어있는 값
IS NOT NULL
A IS NOT NULL
A 값이 비어있지 않는 값
  • 셀 병합
  • 행 분할
  • 열 분할
  • 너비 맞춤
  • 삭제

SELECT *

FROM basic.theglory

WHERE 날짜 IS NOT NULL

AND (성별!='M')

AND 직업 IN('아나운서','승무원','화가')

AND (나이 BETWEEN 30 AND 40) -- 30 이상, 40 이하

Limiti 1

 

# 4. ORDER BY 다중조건

# 날짜를 내림차순으로 우선 정렬하고, 날짜가 같다면! 나이를 기준으로 오름차순 정렬

select 날짜, 이름, 성별, 나이, 직업

from basic.theglory

order by 1 desc, 4 ASC;

 

select case when delivery_time>30 then price*0.1*if(addr like '%서울', 1.1, 1)

when delivery_time between 26 and 30 then price*0.05*if(addr like '%서울', 1.1, 1)

else 0 and "수수료"

from food_orders

 

select case when day_of_the_week='weekday' then 3000*if(quantity>3, 1.2, 1)

when day_of_the_week='weekend' then 3500*if(quantity>3, 1.2, 1)

end "배달할증료",

restaurant_name,

order_id,

price,

delivery_time,

addr

from food_orders

 

select order_id,restaurant_name,day_of_the_week, delivery_time,

case when day_of_the_week='weekday' AND delivery_time>25 THEN 'Late'

when day_of_the_week='weekend' AND delivery_time>30 THEN 'Late'

Else "On_time"

End as "지연여부"

from food_orders

 

select price/quantity

from

(

select price, quantity

from food_orders

) a

 

select order_id, restaurant_name, food_preparation_time

from

(

select order_id, restaurant_name, food_preparation_time

from food_orders

) a

 

select restaurant_name,

price_per_plate*ratio_of_add "수수료"

from

(

select restaurant_name,

case when price_per_plate<5000 then 0.005

when price_per_plate between 5000 and 19999 then 0.01

when price_per_plate between 20000 and 29999 then 0.02

else 0.03 end ratio_of_add,

price_per_plate

from

(

select restaurant_name, avg(price/quantity) price_per_plate

from food_orders

group by 1

) a

) b

 

select restaurant_name,

sido,

case when avg_delivery_time<=20 then '<=20'

when avg_delivery_time>20 and avg_delivery_time<=30 then '20<x<=30'

else '>30' end delivery_time_segment

from

(

select restaurant_name,

substr(addr, 1, 2) sido,

avg(delivery_time) avg_delivery_time

from food_orders

group by 1,2

) a

 

select cuisine_type,

total_quantity,

count_res,

case when count_res>=5 and total_quantity>=30 then 0.005

when count_res>=5 and total_quantity<30 then 0.008

when count_res<5 and total_quantity>=30 then 0.01

when count_res<5 and total_quantity<30 then 0.02 end rate

from

(

select cuisine_type,

sum(quantity) total_quantity,

count(distinct restaurant_name) count_res

from food_orders

group by 1

) a

 

* 할인율

select restaurant_name,

sum_price,

sum_quantity,

case when sum_quantity<=5 then 0.1

when sum_quantity>15 and sum_price>=30000 then 0.005

else 0.01 end discount_rate

from

(

select restaurant_name,

sum(price) sum_price,

sum(quantity) sum_quantity

from food_orders

group by 1

) a

(+). 코드카타 및 4주차 예고

join

select*

from food_orders inner join payments on food_orders.order_id=payments.order_id

 

select f.order_id,

f.customer_id

f.restaurant_name,

f.price,

c.name,

c.gender

from food_orders f left join customers c on f.customer_id=c.customer_id

 

저작자표시 비영리 변경금지

티스토리툴바