[사전캠프/SQL] 포맷 함수,window function, 5주차 숙제

1. 4주차 복습(JOIN,SUBQUERY)

• 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.공통컬럼명

• SUBQUERY
  • Query 결과를 Query 에 다시 활용하는 것

SELECT column1, special_column 
FROM ( 
/* subquery */ 
SELECT column1, column2 special_column 
FROM table1 ) a

---

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

• [방법1] 없는 값을 제외해주기
  • 가장 쉬운 방법 중 하나
  • NULL(데이터가 아에 없다)
  • mySQL에서는 사용할 수 없는 값들은 제외 처리함( 0으로 간주)

SELECT restaurant_name,
       AVG(rating) average_of_rating,
       AVG(IF(rating<>'Not given', rating, NULL)) average_of_rating2
FROM food_orders
GRUOP BY 1

여기서 첫번째 AVG는 사용할 수 없는 값들을 다 0으로 간주해서 평균 값을 내고,

두번째 AVG는 'Not given'이 아닌 값만 rating에 넣고 그 rating의 평균을 측정한 것 ( 사용할 수 없는 값들은 제외됨 )

만약 rating의 값이 'Not given'라면 NULL 처리를 한다.

null은 데이터가 아에 없다는 뜻이기 때문에 사용할 수 없는 값 -> 제외 처리

---

그럼 만약 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

IS NOT NULL을 사용해주면 된다. IS NOT NULL은 NULL에 해당하는 값들을 다 제거해준다고 생각하면 된다.

JOIN을 사용했을시에는 INNER JOIN을 생각하면 된다.(값이 없는 데이터들은 JOIN되지 않음)

---

• [방법2] 다른 값을 대신 사용하기
  
  • 데이터 분석 시에 평균값 혹은 중앙값 등 대표값을 이용하여 대체하기도함
  • 다른 값으로 변경하고 싶을 때, 아래 두가지 문법 사용 가능
    • 다른 값이 있을 때 조건문 이용하기 : if(rating>=1, rating, 대체값)
    • null 값일 때 : coalesce(age, 대체값)
  • null 을 다른 값으로 대체한 쿼리문을 실행했을 시,
    • customer 테이블에 없는 데이터 중에 age 만 20으로 채워짐

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

우선 IS NULL은 NULL인 것들만 뽑아줘 라는 뜻

coalesce(b.age, 20) => b.age에 값을 가지고 있지 않다면 20으로 대체를 해줘

 

---

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

• 상식적이지 않은 데이터의 예시
  
  • 케이스1 - 주문 고객의 나이
    • 보통 음식을 주문한 고객은 20세 이상인 성인인 경우가 많은데, 데이터에 2세와 같이 상식적이지 않은 값이 있음
  • 케이스2 - 결제 일자
    • 결제의 경우, 비교적 최근인 일자가 있어야하는데, 데이터에 1970년대와 같이 상식적이지 않은 값이 있는 경우가 있음

---

그럼 이렇게 이상한 데이터들의 값을 어떻게 변경해주나? -> 조건문으로 값의 범위 지정하기

• [방법 1] 조건문으로 값의 범위를 지정하기
  • 조건문으로 가장 큰 값, 가장 작은 값의 범위를 상식적 수준에 맞게 지정해줌
  • 위의 나이(케이스1과 같은 경우) 아래와 같은 범위를 지정해 줄 수 있다 
    •  범위를 지정해준 결과, 15세 미만이거나 80세 초과인 경우 15, 80으로 각각 대체된 것 확인

SELECT customer_id, 
	name, 
	email, 
	gendor, 
	age, 
	CASE WHEN age<15 THEN 15 
		WHEN age>80 THEN 80 
		ELSE age END "범위를 지정 age" 
FROM customers

애매한 값이 있는 경우, 평균치의 값을 정해서(상식적인 수준) 조건문으로 값을 변경해주는 방법인 것 같다.

---

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

• Pivot table 구조 소개
  • Pivot table 이란? : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것
  • Pivot table 의 기본 구조

• Pivot table 의 예시
  • 집계 기준 : 일자, 시간

---

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

• 음식점별, 시간별 주문건수 조회

SELECT a.restaurant_name,
       SUBSTR(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 SUBSTR(b.time, 1, 2) BETWEEN 15 AND 20
GROUP BY 1, 2

처음에 SUBSTR을 왜 사용해야할까 고민을 하다, 조회해보면 알지않을까? 하는 마음에 조회를 해보았더니

값이 이런 식으로 나왔다, 시간별 주문 건수를 조회해야하는데 시간이 초단위 까지 나와있어서, 보기 쉽게 배열이 안될 것 같다고 느꼈고,

피봇 테이블로 변경할 때 조건범위가 너무 방대해질 것 같아서 시간<만 뽑아오기 위해 SUBSTR을 쓰는 것을 알게 되었다...

• 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,
       SUBSTR(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 SUBSTR(b.time, 1, 2) BETWEEN 15 AND 20
GROUP BY 1, 2
) a
GROUP BY 1
ORDER BY 7 DESC

---

4-2. [실습] 성별, 연령별 주문건수 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

where은 문제에서 나이는 10~59세 사이라고 조건이 있기 때문에, 조건을 넣어주었고

case when은 연령별 << 주문건수를 구해야 하기때문에 10대~50대까지 연령별로 나누어 주었다.

group by는 1, 2컬럼인데 성별과 연령이기 때문에 성별, 연령별로 구분이 되었고,

count(1)은 전체 갯수를 나타내준다.

---

• Pivot view 구조 만들기

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 age

MAX가 무엇을 뜻 하는 건지 정확히 강의에 나오지 않아서 따로 검색해봐야 할 것 같다.

---

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

• 사례
  • Window Function 은 각 행의 관계를 정의하기 위한 함수 그룹 내의 연산을 쉽게 만들어줌
  • 자체적으로 제공해주는 기능을 이용하면 조금 더 편리 →  Window function 으로 제공
• 기본 구조
  • window_function : 기능 명을 사용해줌 (sum, avg 와 같이 기능명이 있음)
  • argument : 함수에 따라 작성하거나 생략
  • partition by : 그룹을 나누기 위한 기준( group by 절과 유사)
  • order by : SQL order by처럼 정렬

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

---

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

• ‘특정 기준으로 순위를 매겨주는’ 기능

---

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

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

하나하나 분석하면 이해가 갈 것같긴하다, 우선 rank() over 부분의 기본 구조를 이해하면 다른 부분은 다 기본 sql 쿼리랑 같아서

괜찮을 것 같다. 따로 분석한 포스팅을 해야겠다.

---

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

• 합계를 구하는 기능

---

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

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

rank보다 sum이 원래 있던 기능이라 쉬울 줄 알았는데 sum부분이 조금 더 어려웠다.

---

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

• 날짜 데이터의 이해
  
  • 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있으며 목적에 따라 ‘월’, ‘주’, ‘일’ 등으로 포맷을 변경할 수있음
• 왜 필요한가?
  • 숫자 형식을 시간 형식의 컬럼으로 변경시, '월','주','일' 등등 '시','분','초'와 같이 따로 따로 뽑기 편함

---

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

select date(date) date_type,
       date
from payments

date(date)는 date()는 함수를 뜻하고 , (date)는 date라는 컬럼명이다.

---

• date type 을 date_format 을 이용하여 년, 월, 일, 주 로 조회해보기
  • 년 : Y (4자리), y(2자리)
  • 월 : M, m
  • 일 : d, e
  • 요일 : w

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

date_format은 date타입에 형식을 지정해준다 생각하면 된다.

결국 date()함수를 사용하여 yyyy-mm-dd의 시간형식으로 변경 후 y에 해당하는 값만 지정(date_format)한다! 생각하면된다

---

7. 5주차 숙제

• 음식 타입별, 연령별 주문건수 pivot view 만들기(연령은 10~59세 사이)

위의 피봇 테이블 했을 때, 실습 2가지와 비슷한 값이 많아 보면서 풀었다.!

select cuisine_type,
       max(if(age=10, order_count, 0)) "10대",
       max(if(age=20, order_count, 0)) "20대",
       max(if(age=30, order_count, 0)) "30대",
       max(if(age=40, order_count, 0)) "40대",
       max(if(age=50, order_count, 0)) "50대"
from 
(
select a.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) order_count
from food_orders a inner join customers b on a.customer_id=b.customer_id
where age between 10 and 59
group by 1, 2
) a
group by 1

---

8. 회고

음음.. 피봇 테이블 쪽에서 막혔다.

피봇 테이블이 무엇을 말하고, 어떻게 하라는건지 사실 감이 오는거 같기도하고 아닌 것 같기도하고 애매하다.

아직 쿼리 짜는게 익숙치 않아서 더 그런 것 같다, 쿼리를 많이 짜보는게 답인것 같다. 오늘부로 5주차 SQL강의는 끝났고

SQL 퀘스트랑 프로그래머스 SQL문제를 조금씩 풀어 나가면서, 쿼리를 푸는 감을 잃지 않도록 하는게 좋을 것 같다.

window function쪽도 아직은 어렵다. 어떤 식으로 흘러가는지 잘 모르겠다. 우선 내용과 예시는 정리했지만,

RANK나 SUM 부분의 문법 자체가 조금 달라서 어렵게 느껴진다. 

사실 음식점별, 시간별 주문건수 조회 라는 것만 보고 혼자서 쿼리를 짤 줄 알아야하는데, 아직까지도 막힌다는게 참 ..ㅠㅠㅠ.. 공부 부족인가 싶기도하다. 

피봇 테이블에 MAX가 왜 나오는지는 따로 더 공부해야할 듯 하다.

대문자로 적는걸 선호했었는데 소문자로 적는데 타이핑 속도나 신경쓰이는게 덜해서 소문자로 적으려고 한다 ㅎㅠ..