[정리] 엑셀보다 쉽고 빠른 SQL - 2

#수강 강의 : 엑셀보다 쉽고 빠른 SQL
3주차 ~ 5주차

📝 이전 내용 보강

# SQL 문법 위치
select 기준컬럼, 컬럼, 계산함수(), 출력값 수정, 조건문
from [테이블 | 서브쿼리 | 조인]
where 조건
group by 기준컬럼
order by 컬럼 [desc]

# group by 사용법 종류
select 컬럼 "별명" from 테이블
group by 컬럼 #1 #`별명` ✨

---

📃 개념 정리

1. 대체

cast : 타입 변경

• 숫자로 변경 : decimal
• 문자로 변경 : char

cast(if(rating='Not given', '1', rating) as decimal) 

cast(컬럼 as char)

1.1. NULL 처리

이상치 나올경우, 조건문으로 값의 범위를 정하기

## NULL값 출력 제외
where b.customer_id is not null 

coalesce : 값 변경

coalesce(컬럼, 대체값)

2. 분할

Subquery

Subquery : Query 안에 sub 로 들어간 구문 - 여러 연산 기능

select column1, special_column
from
    ( /* subquery */
    select column1, column2 special_column
    from table1
    ) a
/* subquery */
where column1 = (select col1 from table2)

3. 병합

JOIN

JOIN : 여러 테이블에서 데이터를 불러오는 방법

• LEFT JOIN : 왼쪽 테이블에 값이 없더라도 모두 조회
• INNER JOIN : 모두에 있는 값만 조회
  

select 컬럼
from 테이블1 A [left | inner] join 테이블2 B on A.공통컬럼명=B.공통컬럼명
# 기본값: inner

4. 피벗 테이블

Pivot table : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여줌

집계 기준 : age, gender

#2.Pivot view 구조 만들기
select age,
	max(if(gender='female', cnt, 0)) female,
	max(if(gender='male', cnt, 0)) male
	#상대적으로 많이 써야하는 것을 행으로 둠
from
(
#1.성별, 연령별 주문건수 집계하기 
  select c.gender,
      case when age between 10 and 19 then 10
          when age between 20 and 29 then 20
          when age between 30 and 39 then 30
          when age between 40 and 49 then 40
          when age between 50 and 59 then 50 end age,
     count(1) cnt
  from customers c inner join food_orders fo on c.customer_id = fo.customer_id
  where c.age between 10 and 59
  group by 1,2
  order by c.age DESC
) a
group by 1

5. 윈도우 함수

Window Function : rank() / sum(컬럼)

window_function([argument]) over (partition by 기준컬럼 order by 정렬기준)

• 각 행의 관계를 정의하기 위한 함수
• 그룹 내의 연산을 쉽게 만들어줌
  복잡한 서브쿼리문, 여러번의 연산 -> 윈도우 함수로 제공됨

-- cuisine_type 별로 랭킹 구하기 : order_count가 많을 때(desc) 1위
rank() over (partition by cuisine_type order by order_count desc)

select cuisine_type, restaurant_name, cnt,
       # cnt을 모두 합함 : cuisine_type별로 --> 타입별 총합
       sum(cnt) over (partition by cuisine_type) sum_cuisine,
       # cnt을 모두 합함 : cuisine_type별로 --> 타입별 누적합
       -- cnt가 작은 순서대로 하나씩 계산 + cnt가 같으면 restaurant_name로 하나씩 
       sum(cnt) over (partition by cuisine_type order by cnt, restaurant_name) cum_cuisine
from (
	select cuisine_type, restaurant_name, count(1) cnt
	from food_orders group by 1, 2
) a
order by cuisine_type , cnt, cum_cuisine

6. 포맷 함수

date(컬럼) : yyyy-mm-dd 형식의 컬럼을 date 타입으로 변경

select date(date) date_type,
       date_format(date(date), '%Y') "년", # 1978
       date_format(date(date), '%y') "년", # 78
       --
       date_format(date(date), '%M') "월", # August
       date_format(date(date), '%m') "월", # 08
       --
       date_format(date(date), '%D') "일", # 2nd
       date_format(date(date), '%d') "일", # 02
       date_format(date(date), '%e') "일", # 2
       --
       date_format(date(date), '%W') "요일", # Wednesday
       date_format(date(date), '%w') "요일" # 3
from payments

---

📚 실습 코드

3.05.1) 10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기 (이름도 같이 출력)

select name,
       age,
       gender,
	if(age<20 , if(gender='male' , '10대 남성', '10대 여성'),
		if(gender='male' , '20대 남성', '20대 여성')
	) "나이와 성별"
from customers
where age between 10 and 29

3.05.2) 음식 단가, 음식 종류 별로 음식점 그룹 나누기

select restaurant_name,
       price/quantity "단가",
       cuisine_type,
case when (cuisine_type ='korean') then
	case when (price/quantity < 5000) then '한식 미만'
	when (price/quantity between 5000 and 14999) then '한식 중간'
	else '한식 이상'
	end
WHEN cuisine_type IN ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') THEN
	case when (price/quantity < 5000) then '아시아식 미만'
	when (price/quantity between 5000 and 14999) then '아시아식 중간'
	else '아시아식 이상'
	end
else
	case when (price/quantity < 5000) then '기타 미만'
	when (price/quantity between 5000 and 14999) then '기타 중간'
	else '기타 이상'
	end
end '그룹'
from food_orders

3.06.1) 지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력)
(지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음
시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%)

select restaurant_name '식당 이름', order_id '주문 번호',substr(addr,1,2) '주소', price,
	case when delivery_time>30 then price*1.1*(if(addr like '%서울%', 1.1, 1))
		when delivery_time>20 then price*0.05*(if(addr like '%서울%', 1.1, 1))
		else 0 end "수수료"
from food_orders

3.06.2) 주문 시기와 음식 수를 기반으로 배달할증료 구하기
(주문 시기 : 평일 기본료 = 3000 / 주말 기본료 = 3500
음식 수 : 3개 이하이면 할증 없음 / 3개 초과이면 기본료 * 1.2)

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