오늘 한 일
- SQL 5주차 강의
- SQL 문제 풀기(21~30)
SQL 5주차
NULL 처리하기(COALESCE)
SQL을 사용하다보면 NULL이 필요한 경우가 있고 NULL이 아닌 값이 필요한 경우가 있다.
| rating(char) |
|---|
| Not given |
| 5 |
| 4 |
위와 같은 컬럼에서 AVG 함수를 사용하면 MySQL에서는 Not given을 0으로 처리하여 의도하지 않은 값이 나올 수 있다. 이러한 경우 Not given 행을 연산에서 제외하기 위해 Not given을 NULL로 바꾸어 줘야 한다.
반대로 NULL값을 원하는 값으로 변경하고 싶은 경우에는 COALESCE 함수를 사용한다.
COALESCE(컬럼, 대체값)
COALESCE 함수는 입력한 컬럼에 있는 NULL 값을 대체값으로 모두 바꿔 준다.
Pivot Table
엑셀의 Pivot Table 과 같은 의미로 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것을 의미한다.
SELECT
집계기준 컬럼, ← 행(row)
MAX(IF(조건문, 동결, 0) 구분 컬럼1 ┒
... 열(col)
MAX(IF(조건문, 동결, 0) 구분 컬럼3 ┚
FROM
집계를 해주는 Subquery 문
GROUP BY 1
Window Function(RANK, SUM)
음식 종류별 주문건수가 많은 순으로 순위를 매기는 경우
한식 식당 전체 주문건수 중 A 식당이 차지하는 비율
위 예시처럼 복잡한 Subquery 문이나 연산을 여러번 해야하는 경우 이를 자체적으로 제공해주는 Window Function을 사용해보자.
- 기본 구조
window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)
window_fuction과 over는 한 세트라고 생각하기
- 예시
# 음식 종류별 주문건수가 많은 순으로 순위를 매기는 경우
select
음식 타입,
식당 이름,
rank() over (partition by cuisine_type order by order_count desc) rn,
order_count
from
(
select
음식 타입,
식당 이름,
count(1) order_count
from 테이블명
group by 1, 2
) a음식 타입이 한식, 중식, 일식이라 할 때 Window_function 중 RANK 함수는 아래 Subquery에서 집계한 테이블에서 전체 식당의 랭킹이 아닌 한식 식당 랭킹, 중식 식당 랭킹, 일식 식당 랭킹으로 각각의 그룹별로 연산된다.
RANK 함수는 argument 없이 RANK() 로 사용할 수 있다. (SUM은 필요함)
SUM 함수는 누적합으로 사용
날짜 포맷(DATE, DATE_FORMAT)
숫자형, 문자형 데이터가 있듯이 날짜도 데이트 타입으로 정의할 수 있다.
SELECT DATE(컬럼) date_type
FROM 테이블
데이트 데이터는 필요에 따라 년, 월, 일 등의 포맷을 변경할 수 있다. (시간도 가능)
SELECT
DATE_FORMAT(DATE(컬럼), '%(옵션)')
FROM 테이븖명
'옵션'은 년도만 사용하고 싶다면 '%Y', 월은 '%m' 등 매우 많기 때문에 자주 사용하는 것만 외우고 나머지는 구글링을 사용하는게 편하다.
5주차 끝&숙제
음식 타입별, 연령별 주문건수 pivot view 만들기(연령은 10~59세 사이)
-
Subquery
음식 타입, 연령: food_orders, customers 테이블 JOIN
연령대 나누기: CASE
주문건수 집계: COUNT
타입별, 연령별: GROUP BY -
Pivot
Subquery에서 나눈 연령대별로 구분 컬럼 만들기
SELECT
cuisine_type,
MAX(IF(age=10, cnt_orders, 0)) '10대',
MAX(IF(age=20, cnt_orders, 0)) '20대',
MAX(IF(age=30, cnt_orders, 0)) '30대',
MAX(IF(age=40, cnt_orders, 0)) '40대',
MAX(IF(age=50, cnt_orders, 0)) '50대'
FROM
(
SELECT
fo.cuisine_type,
CASE
WHEN c.age BETWEEN 10 AND 19 THEN 10
WHEN c.age BETWEEN 20 AND 29 THEN 20
WHEN c.age BETWEEN 30 AND 39 THEN 30
WHEN c.age BETWEEN 40 AND 49 THEN 40
ELSE 50
END age,
COUNT(1) cnt_orders
FROM food_orders fo INNER JOIN customers c ON fo.customer_id=c.customer_id
WHERE c.age BETWEEN 10 and 59
GROUP BY 1,2
) a
GROUP BY 1