엑셀보다 쉽고 빠른 SQL 5주차
1. 조회한 데이터에 아무 값이 없다면 어떻게 해야할까? (NULL)
2. [실습] SQL로 PIVOT TABLE 만들어보기
3. 업무 시작을 단축시켜주는 마법의 문법(Window Function -RANK,SUM)
4. 날짜 포맷과 조건까지 SQL로 한 번에 끝내기 (포맷 함수)
5. 숙제 및 후기
1. 조회한 데이터에 아무 값이 없다면 어떻게 해야할까? (NULL)
SELECT restaurant_name, AVG(rating) avg_rating, AVG(IF(rating<>'Not given',rating,NULL))
FROM food_orders
GROUP BY restaurant_name;
MySQL에서는 사용할수 없는값(문자열 등)을 0으로 치환 후 계산을 한다
평균 값등 연산에 문제가 발생 할 수있다.
그러므로 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 <- 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
coalesce(b.age, 20)
: b.age가 null이라면 값을 20으로 대체
2. [실습] SQL로 PIVOT TABLE 만들어보기
1) Pivot tavle 이란 : 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
MAX를 써줘야 깔끔하게 피봇테이블을 만들 수 있다.(행)
[실습] 성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)
SELECT age,
MAX(if(gender='male',cnt_order,0)) 'male',
MAX(if(gender='female',cnt_order,0)) 'female'
FROM
(
SELECT 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) cnt_order
FROM food_orders fo INNER JOIN customers c ON fo.customer_id=c.customer_id
WHERE age BETWEEN 10 AND 59
GROUP BY 1,2) a
GROUP BY 1
ORDER BY 1 DESC;
3. 업무 시작을 단축시켜주는 마법의 문법(Window Function -RANK,SUM)
윈도우 함수 기본 구조
: window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)
[실습1] N 번째까지의 대상을 조회하고 싶을 때, Rank
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] 전체에서 차지하는 비율, 누적합을 구할 때, Sum
SELECT cuisine_type, restaurant_name,order_count,
sum(order_count) over(PARTITION BY cuisine_type) sum_cuisine,
sum(order_count) over(PARTITION BY cuisine_type order by order_count) cum_cuisine
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a
order by cuisine_type,order_count
4. 날짜 포맷과 조건까지 SQL로 한 번에 끝내기 (포맷 함수)
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
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
5. 숙제 및 후기
음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)
- SQL 기본구조 작성하기
- Pivot view 를 만들기 위해 필요한 데이터 가공하기
- Pivot view 문법에 맞추어 수정하기
SELECT cuisine_type,
MAX(if(age=10,cnt,0)) '10대' ,
MAX(if(age=20,cnt,0)) '20대' ,
MAX(if(age=30,cnt,0)) '30대' ,
MAX(if(age=40,cnt,0)) '40대' ,
MAX(if(age=50,cnt,0)) '50대'
FROM
(
SELECT fo.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) cnt
FROM food_orders fo INNER JOIN customers c ON fo.customer_id=c.customer_id
WHERE age BETWEEN 10 AND 59
GROUP BY 1,2) a
GROUP BY 1
후기 :
마지막 강의라 그런지 쿼리문도 많이 길어지고 윈도우 함수나 피벗 테이블이 많이 이해가 안된다....
다양한 문제들 더 많이 풀어봐야 이해를 할 수 있을 거 같다!