20251216 TIL

Today I Learn

: 엑셀보다 쉽고 빠른 SQL 챕터 1, 2, 3, 4

 

- SQL

데이터베이스와 대화를 하기 위한 언어

Query: SQL 언어를 이용하여 데이터베이스에 요청을 하는 질의

 

- 데이터베이스

데이터가 저장되어있는 큰 폴더

 

- 별명(alias)

• 별병 지정 방법
  • 컬럼1 as 별명1
  • 컬럼2 별명2
• 지을 때 유의사항
  • 영문, 언더바: 별명만 적음
  • 특수문자, 한글: 큰 따옴표 안에 적어줌

- WHERE 필터링

• 숫자 ex) where age = 21
• 문자 ex) where gender = 'female' : 작은 따옴표 사용

- 필터링 표현

• <> : 같지 않다 (다르다)    ex) age<>21
• between a and b            ex) where age between 10 and 20
• in: 포함하는 조건             ex) age in (15, 21, 31) / cuisine_type in ('Korean', 'Japanese')
• like: 완전 똑같지는 않지만, 비슷한 값 조건
  • 특정한 문자로 시작하는 경우     ex) name like '김%' : '김'으로 시작하는 이름
  • 특정한 문자를 포함하는 경우     ex) restaurant_name like '%Next%': 'Next'를 포함하는 식당이름
  • 특정한 문자로 끝나는 경우        ex) name like '%임': '임'으로 끝나는 이름    

- 데이터 갯수 구하기 

• 데이터 갯수: count(컬럼) / count(*)
• 몇개의 값을 가지고 있는지 구할 때: 

- 업무에 필요한 문자 포맷이 다를 때

• replace(바꿀 컬럼, 현재 값, 바꿀 값)

select restaurant_name "원래 상점명",
       replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"
from food_orders
where restaurant_name like '%Blue Ribbon%'

 

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

select addr "원래 주소",
       substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'
# '서울'만 출력

 

• concat(붙이고 싶은 컬럼1, 붙이고 싶은 컬럼2, ..., )

select restaurant_name "원래 이름",   
       addr "원래 주소",
       concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'

 

• 실습3

select concat('[', substring(addr, 1, 2), '] ', restaurant_name, ' (', cuisine_type, ')') "바뀐이름",
       count(1) "주문건수"
from food_orders
group by 1  -- 첫번째 열을 기준으로 그룹화하기

 

- 조건에 따라 포맷을 다르게 변경할 때

• if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

select restaurant_name,
       cuisine_type "원래 음식 타입",
       if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders

select addr "원래 주소",
       if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"
from food_orders
where addr like '%문곡리%'

select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",
       count(customer_id) "고객 수",
       avg(age) "평균 연령"
from customers
group by 1

• case when

case when 조건1 then 값(수식)1
     when 조건2 then 값(수식)2
     else 값(수식)3
end

select case when cuisine_type='Korean' then '한식'
		    when cuisine_type in ('Japanese', 'Chinese') then '아시아'
		    else '기타' end "음식 타입",
       cuisine_type 
from food_orders

select restaurant_name,
       addr,
       case when addr like '%경기도%' then '경기도'
            when addr like '%특별%' or addr like '%광역%' then substring(addr, 1, 5)
            else substring(addr, 1, 2) end "변경된 주소"
from food_orders

• 실습 - 수수료 계산

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

 

- Data Type 오류

• 숫자로 변경: cast(변경할 값 as decimal)
• 문자로 변경: cast(변경할 값 as char)

-- 숫자로 변경
# case(if(rating='not given', '1', rating) as decimal) 
-- 문자로 변경
# concat(restaurant_name,'-',cast(order_id as char)

select cast(if(rating='not given', '1', rating) as decimal) as rating,
		concat(restaurant_name,'-',cast(order_id as char)) as restaurant
from food_orders

 

 

- User Segmentation와 조건별 수수료를 Subquery로 결합하기

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

 

- JOIN

• Left Join: 오른쪽 테이블엔 없어도 왼쪽에 있는 테이블에 있는 값 모두 포함, 오른쪽에만 있는 값은 포함x, 열은 모두 합함
• Inner Join: 양 테이블 모두에 있는 값(행)만 포함, 열은 모두 합함

-- LEFT JOIN
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명

-- INNER JOIN
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명

select distinct c.name,
       c.age,
       c.gender,
       f.restaurant_name
from food_orders f left join customers c on f.customer_id=c.customer_id
order by c.name

• distinct: 여기서 네 열 모두에 적용이 되어서 중복이 없도록 조회함

select cuisine_type,
       sum(price) price,
       sum(price*discount_rate) discounted_price
from 
(
select f.cuisine_type,
       f.price,
       c.age,
       (c.age-50)*0.005 discount_rate
from food_orders f left join customers c on f.customer_id=c.customer_id
where c.age>=50
) a
group by 1
order by SUM(price - (price * discount_rate)) desc