01. 조회한 데이터에 값이 없는 경우

  • Mysql 에서는 사용할 수 없는 값일 때 해당 값을 연산에서 제외함 > 0으로 간주
  • null 문법을 이용해 명확하게 연산을 지정할 수 있음
  • ex) 테이블에 잘못된 값이 들어있는 경우, LEFT JOIN을 사용했을 때 값이 없는 경우

방법1) 없는 값 제외 

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

 

방법2) 다른 값을 대신 사용하기

  • 다른 값이 있을 때 조건문 이용하기 : if(rating>=1, rating, 대체값)
  • null 값일 때 : coalesce(age, 대체값)
select a.price,
       b.name,
       b.age,
       coalesce(b.age, 20) "null 제거"
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.age is null

 

02. Pivot Table

  • 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것 

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

① 컬럼을 가지고 있는 테이블을 join 함.

② substr을 사용해 시간을 추출하고, count를 사용해 주문건수를 계산함 

③ 계산한 테이블을 기준으로 max(if)를 사용해 음식점별 시간별 주문건수를 계산함.

④ order by 로 20시 주문건수 기준으로 내림차순 계산 > 7번째 컬럼이기때문에 order by 7 desc 

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

 

03. Window Function - RANK, SUM

  • Window Function 은 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어줌 
  • 기본 SQL 구조로 해결하기 위해서는 복잡하게 Subquery 문을 이용하거나, 여러번의 연산을 수행해줘야 하지만, Window function 으로 간편하게 수행 가능함.
window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)
# window_function : 기능 명을 사용. (sum, avg 등)
# argument : 함수에 따라 작성하거나 생략.
# Partition by : 그룹을 나누기 위한 기준. group by 절과 유사함.
# order by : window function 을 적용할 때 정렬 할 컬럼 기준을 적어줌.

 

1) RANK : 특정 기준으로 순위를 매겨주는 기능 

 

예시) 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

rank() over (partition by cuisine_type order  by order_count desc)

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

 

2) 날짜 포맷 

  • 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있으며 목적에 따라 포맷을 변경할 수 있음. 
년  Y (4자리), y(2자리)
월 M, m
일 d, e
요일 w

 

select date(date) date_type,
       date
from payments

 

예시)  3월 조건으로 지정하고, 년도별로 정렬하기

select date_format(date(date), '%Y') y,
       date_format(date(date), '%m') m,
       count(1) order_count
from food_orders a inner join payments b on a.order_id=b.order_id
where date_format(date(date), '%m')='03'
group by 1, 2
order by 1

 

티스토리툴바