[엑셀보다 쉽고 빠른 SQL] 5주차. Null값 처리와 심화 문법

Null 값 처리하기

• 조건절을 이용한 Null 값 제외 : where column is not null
• Null 값 대체
  - 조건문 이용 : if(조건, column, null 대체값)
  - COALESCE 함수 : coalesce(column, null 대체값) > 여러 개의 값 입력 가능

 

Pivot Table 만들기

• 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것
• 실습 예시) 음식점별 시간별 주문건수 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,
       hour (b.time) 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

▼ 결과

 

 

Window 함수

• 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어줌
• 기본 구조

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

• window_function : 기능명을 입력(rank, sum, avg 등)
• argument : 계산 대상이 되는 열이나 식, 함수에 따라 작성하거나 생략
• partition by : 그룹을 나누기 위한 기준
• order by : 윈도우 함수를 적용할 때 정렬 기준 컬럼을 적어줌

 

날짜 데이터 포맷

• DATE : 컬럼을 Date Type으로 변경, date(column)
• DATE_FORMAT : 날짜 및 시간 값을 특정 형식으로 조회, date_format(column, 날짜포맷)
• 날짜 포맷
  - 년 : Y(4자리), y(2자리)
  - 월 : M, m
  - 일 : d, e
  - 요일 : w

---

5주차 숙제

 

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

 

먼저 CASE를 사용하여 나이대별로 필터링을 해주고

주문건수 count를 음식타입별, 연령별로 그룹화하여 서브 쿼리를 만든다.

다음으로 피벗뷰를 만들기 위해 집계 기준 컬럼과 연령별 구분 컬럼을 입력하고

집계 기준 컬럼으로 한번 더 그룹화 해주어 쿼리문을 완성한다.

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(*) 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

▼ 결과