[내일배움캠프] 사전캠프 (7일차)

2/20 보충

오늘 한 일

• SQL (5-1 ~ 5-7)

5-1 Subquery, JOIN 복습하고, 이번 수업 내용 맛보기

Subquery : Query 결과를 Query에 다시 활용하는 것

select column1, special_column
from
    ( /* subquery */
    select column1, column2 special_column
    from table1
    ) a

 

 

JOIN : 두 개 이상의 테이블을 결합하여 사용하는 것

- Left join, Inner join 등이 있음

-- LEFT JOIN
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명

-- INNER JOIN
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명

 

 

5-2 조회한 데이터에 아무 값이 없다면 어떻게 해야할까

데이터가 없을 때의 연산 결과 변화 케이스

-사용할 수 없는 데이터가 들어있거나, 값이 없는 경우

 

방법 1) 없는 값 제외

- 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

 

방법 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

- customer 테이블에 없는 데이터 중에 age 만 20으로 채워짐

 

 

5-3 조회한 데이터가 상식적이지 않은 값을 가지고 있다면 어떻게 해야할까

상식적이지 않은 데이터의 예시

ex)

- 음식을 주문한 고객의 나이 - 2세

- 결제 일자가 1970년대

 

조건문으로 값의 범위 지정

- 조건문으로 가장 큰 값, 가장 작은 값의 범위 지정 가능 -> 상식적인 수준 안에서 범위 지정

 

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

ex) 나이의 경우

 

5-4 [실습] SQL로 Pivot Table 만들어보기

Pivot tavble 이란? : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것을 의미

ex)

 

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

- 음식점별, 시간별 주문건수 집계

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

 

- Pivot view 구조 만들기

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

 

 

[실습] 성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)

- 성별, 연령별 주문건수 집계하기

select b.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)
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age between 10 and 59
group by 1, 2

 

 

- Pivot Table 구조 만들기

select age,
       max(if(gender='male', order_count, 0)) male,
       max(if(gender='female', order_count, 0)) female
from 
(
select b.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) order_count
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age between 10 and 59
group by 1, 2
) t
group by 1
order by 1 desc

 

5-5 업무 시작을 단축시켜 주는 마법의 문법(Window Function-RANK, SUM)

Window Function : 각 행의 관계를 정의하기 위한 함수, 그룹 내의 연산을 쉽게 만들어줌

 

기본 구조

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

window_function : 기능 명을 사용해 줌(sum, avg와 같이 기능명이 있음)

argument : 함수에 따라 작성하거나 생략

partition by : 그룹을 나누기 위한 기준. group by 절과 유사하다

order by : window function을 적용할 때 정렬 할 컬럼 기준을 적어줌

 

 

 [실습] N번째 까지의 대상을 조회하고 싶을 때, Rank

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

- 음식 타입별, 음식점별 주문 건수 집계하기

select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2

 

 

- Rank 함수 적용하기

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

 

 

- 3위까지 조회하고, 음식 타입별, 순위별로 정렬하기

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

 

 

[실습] 전체에서 차지하는 비율, 누적합을 구할 때, Sum

• 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기

- 음식 타입별, 음식점별 주문 건수 집계하기

select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2

 

- 카테고리별 합, 카테고리별 누적합 구하기

select cuisine_type,
       restaurant_name,
       cnt_order,
       sum(cnt_order) over (partition by cuisine_type) sum_cuisine,
       sum(cnt_order) over (partition by cuisine_type order by cnt_order) cum_cuisine
from
(
select cuisine_type, 
	restaurant_name, 
	count(1) cnt_order
from food_orders
group by 1, 2
) a
order by cuisine_type , cnt_order

 

-누적합 고쳐보기

select cuisine_type,
       restaurant_name,
       cnt_order,
       sum(cnt_order) over (partition by cuisine_type) sum_cuisine,
       sum(cnt_order) over (partition by cuisine_type order by cnt_order, restaurant_name) cum_cuisine
from
(
select cuisine_type, 
	restaurant_name, 
	count(1) cnt_order
from food_orders
group by 1, 2
) a
order by cuisine_type, cnt_order, cum_cuisine

 

 

5-6 날짜 포맷과 조건까지 SQL로 한 번에 끝내기 (포맷 함수)

날짜 데이터 : 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있음. 목적에 따라 '월', '주', '일' 등으로 포맷 변경 가능

 

[실습] 날짜 데이터의 여러 포맷

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

select date(date) date_type,
       date
from payments

 

- 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

 

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

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

 

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

select date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), 'Y%m') "년월",
       count(1) "주문건수"
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-7 실습문제

음식 타입별, 연령별 주문건수 pivot view 만들기

select cuisine_type,
	   max(if(age_group=10,order_count,0)) as '10대',
	   max(if(age_group=20,order_count,0)) as '20대',
	   max(if(age_group=30,order_count,0)) as '30대',
	   max(if(age_group=40,order_count,0)) as '40대',
	   max(if(age_group=50,order_count,0)) as '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_group,
		count(1) 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 cuisine_type, age_group 
) a
group by cuisine_type