● null제거법 (where b.customer_id is not 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
●값의 변경
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
● if가 할 수 있는 것은 case when이 모두 가능
●비정상적 데이터 정상화 방식
select customer_id, name, email, gender, age,
case when age<15 then 15
when age>80 then 80
else age end "범위를 지정해준 age"
from customers
●pivot table 예시
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 정렬 기준)
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
●날짜형식 데이터로 변경법
select date('날짜') date_type,
'날짜'
from payments
●날짜 예시
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
●FULL OUTER JOIN 작성법(기초편)
select
from basic.s1 as a left join basic.s2 as b
on a.name=b.name
union
select
from basic.s1 as a right join basic.s2 as b
on a.name=b.name
● union/union all 기본구조(UNION → 중복 제거함, UNION ALL → 중복도 전부 포함함)
컬럼 순서가 같고, 그 형식이 같아야 함
각 컬럼의 이름은 달라도 상관없음 맨 위 SELECT 절 뒤에 있는 컬럼명으로 통일됨
select 컬럼1, 컬럼2, 컬럼3..
from 테이블명1
union (all) #수직결합 명시
select 컬럼1, 컬럼2, 컬럼3..
from 테이블명2
