2024.06.30 TIL - SQL 강의 (join | subquery 실습, 없는값 제외(null처리 - if, where), null 대신 다른값 사용(coalesce), Pivot view 구조, window함수(rank, sum), date())

join, subquery 실습

• 문제 : 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
  평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과
  평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
  두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬

select restaurant_name,
       case when price <=5000 then 'price_group1'
            when price >5000 and price <=10000 then 'price_group2'
            when price >10000 and price <=30000 then 'price_group3'
            when price >30000 then 'price_group4' end price_group,
       case when age <30 then 'age_group1'
            when age between 31 and 39 then 'age_group2'
            when age between 40 and 49 then 'age_group3'
            else 'age_group4' end age_group
from
(
select a.restaurant_name,
       avg(price) price,
       avg(age) age
from food_orders a inner join customers b on a.customer_id=b.customer_id
group by 1
) t
order by 1

---

없는 값 제외 (null)

if문으로 null처리

select restaurant_name,
       avg(rating) average_of_rating,
       avg(if(rating<>'Not given', rating, null)) average_of_rating2
from food_orders
group by 1

// <> : 비교연산자 (같지 않음)

• null처리 해서 연산한 데이터와 기본적인 연산은 값이 다르다!!
  • if문 없이 연산할 경우 : Not given 값을 0으로 치고 연산하기 때문에, 평균값 구할 때 총 개수에 Not given 데이터도 포함됨
  • if문으로 null처리 한 경우 : Not given을 아예 없는 값으로 침
    -> 평균값 구할 때 총 개수에 Not given은 포함되지 XXX

where절로 null은 제외하기

select a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.customer_id is not null 

---

다른값 대신 사용하기

다른 값이 있을 때 (if)

if(rating>=1, rating, 대체값)

// rating이 1보다 크거나 같으면 기존의 rating값을, 아니라면 대체값을 출력해줘

null 값일 때 (coalesce)

select a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       coalesce(b.age, 20) "null 제거",
       b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.age is null

// b테이블의 age에 값이 없다면 20으로 대체해줘

---

Pivot view 구조 만들기

예시 1)

• 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)

select restaurant_name,
       max(if(hh='15', cnt_order, 0)) "15",
       max(if(hh='16', cnt_order, 0)) "16",
       max(if(hh='17', cnt_order, 0)) "17",
       max(if(hh='18', cnt_order, 0)) "18",
       max(if(hh='19', cnt_order, 0)) "19",
       max(if(hh='20', cnt_order, 0)) "20"
from 
(
select a.restaurant_name,
       substring(b.time, 1, 2) hh,
       count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1
order by 7 desc

// cnt_order의 cnt : count
// ⭐️ pivot view 할때는 꼭 if문을 max로 묶어주기!! (이해영역X, 암기영역)
// ⭐️ max, sum 등 오면 필연적으로 와야하는 것 => group by!!

예시 2)

• 문제 : 음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)

select cuisine_type,
       max(if(age=10, order_count, 0)) "10대",
       max(if(age=20, order_count, 0)) "20대",
       max(if(age=30, order_count, 0)) "30대",
       max(if(age=40, order_count, 0)) "40대",
       max(if(age=50, order_count, 0)) "50대"
from 
(
select a.cuisine_type,
       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) order_count
from food_orders a inner join customers b on a.customer_id=b.customer_id
where age between 10 and 59
group by 1, 2
) t
group by 1

---

window 함수

window 기본구조

window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

rank()

• 문제 : 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

  • 1) rank 함수 적용

    select cuisine_type,
         restaurant_name,
         // ⭐️ rank() over는 한 쌍!! (window 함수의 가장 기본!)
         // partition by : 어떤 단위로 묶을거야? (어떤 단위로 순위 매길지?)
         // order by : 어떤 순서로 순위를 매길지?
         rank() over (partition by cuisine_type order by order_count desc) rn,
         order_count
    from
    (
    select cuisine_type, restaurant_name, count(1) order_count
    from food_orders
    group by 1, 2
    ) a

  • 2) 3위까지 조회하고, 음식 타입별, 순위별로 정렬하기

    select cuisine_type,
         restaurant_name,
         order_count,
         rn "순위"
    from
    (
    select cuisine_type,
         restaurant_name,
         rank() over (partition by cuisine_type order by order_count desc) rn,
         order_count
    from
    (
    select cuisine_type, restaurant_name, count(1) order_count
    from food_orders
    group by 1, 2
    ) a
    ) b
    where rn<=3
    order by 1, 4

---

date() 함수

날짜 형식의 데이터가 따로 있다!

• 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있으며, 목적에 따라 ‘월’, ‘주’, ‘일’ 등으로 포맷을 변경할 수도 있음

• 예시

select date(date) date_type,
       date
from payments

// date() : 괄호 안의 값을 date형식으로 형 변환

• 날짜 데이터 예시

select date(date) date_type,
       date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), '%d') "일",
       date_format(date(date), '%w') "요일"
from payments

// date_format() : 날짜 형식을 지정된 문자열 형식으로 변환하는 함수
// 요일 : 일요일이 0, 월요일이 1, ...