TIL - SQL (5주차)

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

 

1) 없는 값 제외

예)

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

 

2) 다른 값 사용

- 조건문 이용: 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

 

 

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

 

>> 조건문으로 값의 범위를 지정 ( 상식적인 수준 안에서)

 

예)

select customer_id, name, email, gender, age,
       case when age<15 then 15
            when age>80 then 80
            else age end "범위를 지정해준 age"
from customers

 

 

피벗 테이블

:  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,
       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

 

 

Window Function

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

• window_function : 기능 명을 사용해
• argument : 함수에 따라 작성하거나 생략
• partition by : 그룹을 나누기 위한 기준

 

날짜 포맷 함수

 

1. yyyy-mm-dd 형식의 컬럼을 date type 으로 변경하기

select date(date) date_type,
       date
from payments

 

 

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

 

3. 년도, 월을 포함하여 데이터 가공하기

select date_format(date(date), '%Y') y,
       date_format(date(date), '%m') m,
       order_id
from food_orders a inner join payments b on a.order_id=b.order_id

 

4. 년도, 월별 주문건수 구하기

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
group by 1, 2