조인을 했을때 쓸모없는 값이 있다면? IF문으로 NULL값을 제거해준다, COALESCE를 사용한다
사용 해야하는 이유
평균의 값이 왜이렇게 다른가?
MYSQL에서는 평균등을 매길때 Not given과 같은 이상한 값을 0으로 처리해서 계산을 한다. 즉, 별점을 평균내는데 점수를 매기지 않은 사람들이 있다면 자동으로 0이 되어버림. NULL로 한다면 평균을 내지않음, 따라서 NULL로 바꾸는 과정이 필요함
IF문을 사용하기
select restaurant_name,
avg(rating) average_of_rating,
avg(if(rating<>'Not given', rating, null)) average_of_rating2
from food_orders
group by 1
IF(RATING이 NOT GIVEN이 아니라면, RATING값 그대로, NOTGIVEN이라면 NULL로 바꿔준다.
NULL 사용하기 IS NULL, 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
CUSTOMER_ID가 NULL이 아닌 조건만 사용한다.
조인을 했는데 정상적이지 않은 값이 있다면? 조건문으로 값의 범위를 정해준다.
PIVOT TABLE 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
위 코드에서 내부쿼리는 각 레스토랑별 시간대별 주문 건수를 세는것을 볼 수 있다.
외부 쿼리에서는 내부쿼리에서 계산한 레스토랑별 시간대별 주문건수를 가져다쓰는데, 각 시간에 값이 있다면 CNT_ORDER값을 가져오고 없을시 0을 반환하는것을 볼 수 있다.
왜 MAX를 썼을까? 만에하나 그룹화된 결과에서 중복된 컬럼이 있을 경우를 대비한 것이다.
즉 PIVOT TABLE을 만들기 위해서는 SELECT문 최 상단에 열을, 그다음 행만큼의 조건을 써내려가면된다..
위의 문제의 경우에는 SELECT 퀴진타입, 10대조건, 20대조건, 30대조건, 40대조건, 50대조건. 6개가 들어가게된다.
SELECT
CUISINE_TYPE,
MAX(IF(AGE_GROUP = 10, ORDER_CNT, 0)) AS "10대",
MAX(IF(AGE_GROUP = 20, ORDER_CNT, 0)) AS "20대",
MAX(IF(AGE_GROUP = 30, ORDER_CNT, 0)) AS "30대",
MAX(IF(AGE_GROUP = 40, ORDER_CNT, 0)) AS "40대",
MAX(IF(AGE_GROUP = 50, ORDER_CNT, 0)) AS "50대"
FROM
(
SELECT
fo.CUISINE_TYPE,
CASE
WHEN cu.AGE >= 10 AND cu.AGE < 20 THEN 10
WHEN cu.AGE >= 20 AND cu.AGE < 30 THEN 20
WHEN cu.AGE >= 30 AND cu.AGE < 40 THEN 30
WHEN cu.AGE >= 40 AND cu.AGE < 50 THEN 40
WHEN cu.AGE >= 50 AND cu.AGE < 60 THEN 50
END AS AGE_GROUP,
COUNT(1) AS ORDER_CNT
FROM
food_orders fo
INNER JOIN customers cu ON cu.customer_id = fo.customer_id
WHERE
cu.AGE BETWEEN 10 AND 59
GROUP BY
fo.CUISINE_TYPE, AGE_GROUP
) AS A
GROUP BY
CUISINE_TYPE
ORDER BY
CUISINE_TYPE;
완성된 코드는 위와 같다.
SELECT fo.CUISINE_TYPE, cu.AGE
FROM food_orders fo
INNER JOIN customers cu ON cu.customer_id = fo.customer_id
두개의 테이블의 값을 불러오기 위해 조인을 하였다.
SELECT fo.CUISINE_TYPE,
CASE
WHEN cu.AGE >= 10 AND cu.AGE < 20 THEN 10
WHEN cu.AGE >= 20 AND cu.AGE < 30 THEN 20
WHEN cu.AGE >= 30 AND cu.AGE < 40 THEN 30
WHEN cu.AGE >= 40 AND cu.AGE < 50 THEN 40
WHEN cu.AGE >= 50 AND cu.AGE < 60 THEN 50
END AS AGE_GROUP
FROM food_orders fo
INNER JOIN customers cu ON cu.customer_id = fo.customer_id
연령대를 그룹화 하기 위해 CASE WHEN절을 사용하였다.
SELECT fo.CUISINE_TYPE,
CASE
WHEN cu.AGE >= 10 AND cu.AGE < 20 THEN 10
WHEN cu.AGE >= 20 AND cu.AGE < 30 THEN 20
WHEN cu.AGE >= 30 AND cu.AGE < 40 THEN 30
WHEN cu.AGE >= 40 AND cu.AGE < 50 THEN 40
WHEN cu.AGE >= 50 AND cu.AGE < 60 THEN 50
END AS AGE_GROUP,
COUNT(1) AS ORDER_CNT
FROM food_orders fo
INNER JOIN customers cu ON cu.customer_id = fo.customer_id
WHERE cu.AGE BETWEEN 10 AND 59
GROUP BY fo.CUISINE_TYPE, AGE_GROUP
음식종류별, 연령대별 주문건수를 세기 위해 COUNT절을 달아주고, GROUP BY를 사용하였다. 또한 나이 10~59세만 가져오기 위하여 WHERE절을 추가하였다.
외부쿼리는 피벗을 만들기위해 SELECT절, 음식 종류를 묶기위해 GROUP BY절을 사용하였다
WINDOW FUNCTION이란? 원래의 행 데이터를 보존하면서도, 추가적인 계산을 할 수 있게 하는것. SUM이나, AVG함수라면 결과값이 그룹화된 하나의 값으로 나타나겠지만 WINDOW FUNCTION은 그대로 유지해준다.
위와같은 EMPLOYEES 테이블이 있다고 가정해보자
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;
집계함수 SUM을 사용해서 나타낸다면
쿼리의 결과는 위와같이 될것이다.
SELECT employee_id, department_id, salary,
SUM(salary) OVER (PARTITION BY department_id) AS total_salary
FROM employees;
윈도우함수 SUM() OVER ~을 사용하였다면
쿼리의 결과는 이전과 동일하되, TOTAL_SALARY란만 늘어난것을 볼 수 있다.
사용법 window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준) window_function : 사용할 함수 ROW_NUMBER(), RANK(), SUM(), AVG(), COUNT() argument : 함수가 적용될 열, ROW_NUMBER(), RANK()는 없어도 되지만, SUM이나 AVG, COUNT는 들어가야한다. OVER : WINDOW함수를 쓰기위한 필수 키워드 PARTITION BY : 데이터를 나누는 기준, 결과값을 나타낼때 그룹하는 기준. ORDER BY : 데이터를 정렬하는 기준.
위 SUM ~ OVER절을 분석해보자
윈도우 함수로 SUM을 사용했으며, 함수가 적용될 열(계산할 열)은 SALARY이다. department_id기준으로 데이터를 나누었다.
ROW_NUMBER() - 각 파티션 별로 고유한 순위 번호를 나타낼 때 RANK() - 각 파티션 내 순위를 매기지만, 동일한 값을 가지면 동일한 순위를 부여, 다음번호는 건너뛴다.
