23.12.27. D7

주제: 엑셀보다 쉽고 빠른 SQL 5weeks 학습

소주제

조회한 데이터에 값이 없을 때

메모

• 없는 값 제외하기
• 대체값 사용하기
• 없는 값 제외하기

  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 
  
  * 사용할 수 없는 값일 때 해당 값을 연산에서 제외 → 0으로 간주

• 대체값 사용하기

  • 다른 값이 있을 때 조건문 이용하기 : if(rating>=1, rating, 대체값)

  • null 값일 때 : coalesce(age, 대체값)

    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으로 대체해줘"

---

소주제

조회한 데이터가 상식적이지 않은 값을 가지고 있다면?

메모

• 조건문으로 값의 범위를 지정하기
• ...
• 방법

  select customer_id, name, email, gendor, age,
         case when age<15 then 15
              when age>80 then 80
              else age end "범위를 지정해준 age"
  from customers
  
  *조건문으로 가장 큰 값, 가장 작은 값의 범위를 지정

---

소주제

Pivot Table

메모

• 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하는 것
• ...
• 5-4 실습 1

  [실습] 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)
  1. 음식점별, 시간별 주문건수 집계
  
  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
  
  * substring 으로 시간에 해당하는 앞 두 글자만 사용
  * 15~20시에 해당하는 정보만 보기 위해 where절에 substr 모두 적어서 between 사용
  * 두 가지 조건 묶기 위해 group by
  
  2. Pivot view 구조 만들기
  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
  
  * 앞서 만든 쿼리를 서브쿼리로 묶는다.
  * if문 이용해 행 축에는 음식점명이 오고 컬럼에 시간이 나오도록 쿼리 작성
  * Pivot View 를 깔끔하게 정리하기 위해 max로 묶어서 작성
  * 계산 함수가 들어갈 때 필요한 group by 사용
  * 일곱 번째 컬럼을 기준으로 내림차순 -> order by 7 desc 작성

---

• 5-4 실습 2 [실습] 성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)

  1. 성별, 연령별 주문 건수 집계
  select b.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)
  from food_orders a inner join customers b on a.customer_id=b.customer_id
  where b.age between 10 and 59
  group by 1, 2
  
  * 성별과 연령(customers), 주문건수(food_orders) 사용 위해 테이블 묶기
  * case when 문 활용해 범위별로 연령 묶기
  * where절로 10~59세까지의 데이터만을 활용할 것을 명시
  
  2. Pivot View 구조 만들기
  select age,
         max(if(gender='male', order_count, 0)) male,
         max(if(gender='female', order_count, 0)) female
  from 
  (
  select b.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) order_count
  from food_orders a inner join customers b on a.customer_id=b.customer_id
  where b.age between 10 and 59
  group by 1, 2
  ) t
  group by 1
  order by 1 desc
  
  * Pivot view로 만들기 위해 if문을 max로 묶어주기
  * 내림차순 위해 order by 1 desc 사용

소주제

Window Function - RANK, SUM

메모

• 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어준다.
• 기본 구조 :
  window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

---

• window_function : 기능 명을 사용해줍니다. (sum, avg 와 같이 기능명이 있습니다)
• argument : 함수에 따라 작성하거나 생략합니다.
• partition by : 그룹을 나누기 위한 기준입니다. group by 절과 유사하다고 생각해주시면 됩니다.
• order by : window function 을 적용할 때 정렬 할 컬럼 기준을 적어줍니다.
• 5-5 실습 1 [실습1] N 번째까지의 대상을 조회하고 싶을 때, Rank 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

  1. 음식 타입별, 음식점별 주문 건수 집계하기 
  
  select cuisine_type, restaurant_name, count(1) order_count
  from food_orders
  group by 1, 2
  
  2. Rank 함수 적용하기
  
  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
  
  * rank() over : 괄호 필수, 연산을 하는 것이 아니므로 괄호 안에 아무것도 안 적어도 됨
                 over 꼭 붙이기
  * partition by : 단위별로 묶어주기 위해 사용 "음식 타입별로 랭킹을 구한다"
  * order by : 순서 정하기 위해 사용 "주문건수 많은 순서로 랭킹 구한다" 
  
  3. 3위까지 조회하고, 음식 타입별, 순위별로 정렬하기
  
  SELECT cuisine_type,
         restaurant_name,
         cnt_order,
         ranking
  from
  (
  SELECT cuisine_type,
         restaurant_name,
         cnt_order,
         rank() over (partition by cuisine_type order by cnt_order desc) ranking
  from
  (
  SELECT cuisine_type,
         restaurant_name,
         count(1) cnt_order
  FROM food_orders
  group by 1, 2
  ) a
  ) b
  where ranking<=3
  
  * where 절 사용해서 3위까지만 데이터 추출!

• 5-5 실습 2 [실습2] 전체에서 차지하는 비율, 누적합을 구할 때, Sum 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기

  1. 음식 타입별, 음식점별 주문 건수 집계하기
  
  select cuisine_type, restaurant_name, count(1) cnt_order
  from food_orders
  group by 1, 2
  
  2.카테고리별 합, 카테고리별 누적합 구하기
  
  SELECT cuisine_type,
         restaurant_name,
         cnt_order,
         sum(cnt_order) over (partition by cuisine_type) sum_cuisine,
         sum(cnt_order) over (partition by cuisine_type order by cnt_order) cum_cuisine
  from
  (
  SELECT cuisine_type,
         restaurant_name,
         count(1) cnt_order
  FROM food_orders
  group by 1, 2
  ) a
  order by cuisine_type, cnt_order
  
  * sum() over () : rank over 와 같이 over 꼭 써주기
  * "cnt_order를 cuisine_type별(partition by)로 더한다(sum)"
  * order by cnt_order : cnt_order를 기준으로 정렬하고, 오름차순으로 누적해서 더하기 위해 사용

소주제

날짜 포맷

메모

• 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있으며 목적에 따라 ‘월’, ‘주’, ‘일’ 등으로 포맷을 변경 가능
• ...
• 5-6 실습 1

  1. yyyy-mm-dd 형식의 컬럼을 date type 으로 변경하기
  
  select date(date) date_type,
         date
  from payments
  
  * date() : date 함수를 활용하여 날짜에 관련된 컬럼을 "date type"으로 변경 (시계모양 확인!)
  
  2.date type 을 date_format 을 이용하여 년, 월, 일, 주 로 조회해보기
  
  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 함수를 이용하여 문자형 컬럼을 날짜형 컬럼으로 변경
  * date_format : date_type의 컬럼에 형식을 지정
  * %Y : 컬럼 중 year에 해당하는 것만 표시
  * %W : 0 ; 일요일 / 1; 월요일

• 5-6 실습 2 년도별 3월의 주문건수 구하기

  1. 년도, 월을 포함하여 데이터 가공하기
  
  SELECT date_format(date(date), '%Y') "년",
         date_format(date(date), '%m') "월",
         date_format(date(date), '%Y%m') "년월",
         count(1) "주문건수"
  from food_orders f  inner join payments p on f.order_id=p.order_id
  group by 1, 2, 3
  
  2. 3월 조건으로 지정하고, 년도별로 정렬하기
  
  SELECT date_format(date(date), '%Y') "년",
         date_format(date(date), '%m') "월",
         date_format(date(date), '%Y%m') "년월",
         count(1) "주문건수"
  from food_orders f  inner join payments p on f.order_id=p.order_id
  where date_format(date(date), '%m')='03'
  group by 1, 2, 3 
  order by 1
  
  * 데이터 포맷도 where절을 이용해 지정 가능
  * 연도별 정렬을 위해 order by 1

---

• 과제

  <aside>
  💡 음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)
  
  </aside>

  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 f.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 f inner join customers c on f.customer_id=c.customer_id
  where age between 10 and 59
  group by 1, 2
  ) a
  group by 1