문법
SUBQUERY
연산이 한 번에 끝나면 참 좋은데 아닐 때가 많다. 문자를 변경한 다음,.. 숫자 연산을 하고 그 결과로 또 연산을 해야할 때... 너무 긴 쿼리문보다는 조금 더 효율적이고 알아보기 쉽게 Subquery를 사용해보자!
- 언제 사용할까?
→ 여러번의 연산을 수행해야 할 때.
→ 조건문에 연산 결과를 사용해야 할 때.
→ 조건에 Query 결과를 사용하고 싶을 때.
- 기본 구조
Sub 라는 명칭에서 알 수 있듯이, Query안에 sub로 들어간 구문이라고 생각하면 쉽다.
select column1, special_column
from
( /* subquery */
select column1, column2 special_column
from table1
) Subquery_name
- 예시. 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기.
(할인조건 - 수량이 5개 이하 → 10% / 수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5% / 이 외 일괄 1%)
SELECT restaurant_name,
total_quantity,
sum_price,
CASE when total_quantity <= 5 then 0.1
when total_quantity > 15 and sum_price >= 300000 then 0.005
else 0.01 end "수수료 할인율"
FROM(
SELECT restaurant_name,
sum(quantity) as total_quantity,
sum(price) as sum_price
FROM food_orders fo
GROUP BY 1
) a
JOIN
필요한 데이터가 꼭 하나의 테이블에 모여있지는 않다. 이럴 때 여러 테이블에서 데이터를 불러오는 방법을 알아보자.
- 원리
JOIN은 엑셀의 Vlookup과 유사하다. (해당 테이블에 다른 테이블의 정보를 가져오는 기능)
- 종류
공통적으로 원리는 같지만 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.공통컬럼명
- 예시. 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation하기.
SELECT a.restaurant_name,
CASE when a.price <= 5000 then "price_group1"
when a.price <= 10000 then "price_group2"
when a.price <= 30000 then "price_group3"
else "price_group4" end as "price_group",
CASE when a.age < 30 then "age_group1"
when a.age < 40 then "age_group2"
when a.age < 50 then "age_group3"
else "age_group4" end as "age_group"
FROM
(
SELECT fo.restaurant_name,
AVG(fo.price) price,
AVG(c.age) age
FROM food_orders fo inner join customers c on fo.customer_id = c.customer_id
GROUP BY 1
) a
ORDER BY 1
조회한 데이터에 아무 값이 없다면 어떻게 해야할까?
- 없는 / 잘못된 값 제외하기
Mysql에서 사용할 수 없는 값일 때 해당 값을 연산에서 제외해준다. → 0으로 간주.
예시. rating 데이터가 Not given이 아닐 경우 rating 데이터 값을 그대로 쓰고, Not given일 경우 null값을 넣어준다.
SELECT restaurant_name,
AVG(rating) as avg_rating,
AVG(IF(rating <> 'Not given', rating, null)) as avg_rating2
FROM food_orders fo
GROUP BY 1
- 빈 데이터 부분 없애주기
null을 제거하고 테이블 띄운다.
예시. left join을 사용할 경우 null값인 부분을 제외하고 불러온다.
select a.order_id,
a.customer_id,
a.restaurant_name,
a.price,
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.customer_id is not null
- 다른 값을 대신 사용하기
다른 값이 있을 때 조건문 이용하기: if(rating>=1, rating, 대체값)
null 값일 때: coalesce(age, 대체값)
예시. 해당 값이 null인 경우 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
조회한 데이터가 상식적이지 않은 값을 가지고 있다면 어떻게 해야할까?
조건문으로 값의 범위를 지정한다.
예시. 15세 미만이거나 80세 초과인 경우 15, 80으로 각각 대체해보기.
SELECT customer_id,
name,
age,
CASE when age<15 then 15
when age>80 then 80
else age end "범위 지정"
FROM customers
Pivot Table
2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것을 의미한다.
예시. 음식점별, 시간별 주문건수 집계.
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 뷰 만들기.
SELECT age,
MAX(if(gender='male', cnt_order, 0)) "male",
MAX(if(gender='female', cnt_order, 0)) "female"
FROM
(
SELECT c.gender,
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
when c.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 c.age BETWEEN 10 and 59
GROUP BY 1, 2
) a
GROUP BY 1
ORDER BY 1 DESC
WINDOW FUNCTION
Window Function은 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어준다. 기본 SQL 구조로 해결하기 위해서는 복잡하게 Subquery문을 이용하거나, 여러 번의 연산을 수행해줘야 하지만, Window function으로 제공해주는 기능을 이용하면 조금 더 편리하다.
기본 구조
window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)
window_function : 기능 명을 사용. (sum, avg 와 같은 기능명)
argument : 함수에 따라 작성하거나 생략.
partition by : 그룹을 나누기 위한 기준. (group by 절과 유사하다고 생각하면 편하다.)
order by : window function을 적용할 때 정렬할 컬럼 기준.
window_function에는 항상 over가 붙는다!!!
RANK
특정 기준으로 순위를 매겨주는 기능.
예시. 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회해보자.
SELECT b.cuisine_type,
b.restaurant_name,
b.cnt_order,
b.ranking
FROM
(
SELECT a.cuisine_type,
a.restaurant_name,
a.cnt_order,
RANK() over (PARTITION BY a.cuisine_type ORDER BY a.cnt_order DESC) ranking
FROM
(SELECT cuisine_type,
restaurant_name,
COUNT(1) cnt_order
FROM food_orders fo
GROUP BY 1, 2
) a
) b
WHERE b.ranking <= 3
SUM
전체에서 차지하는 비율, 누적합을 구할 때 window_function의 SUM을 사용한다. 앞서 공부했던 합계를 구하는 SQL문의 SUM과 기능이 동일하지만, 누적합이 필요하거나 카테고리별 합계 컬럼와 원본 컬럼을 함께 이용할 때 유용하다.
예시. 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율과 주문건이 낮은 순으로 정렬했을 때 누적 합을 구해보자.
SELECT a.cuisine_type,
a.restaurant_name,
a.cnt_order,
SUM(a.cnt_order) over (PARTITION BY a.cuisine_type) sum_cuisine,
SUM(a.cnt_order) over (PARTITION BY a.cuisine_type ORDER BY a.cnt_order) cum_cuisine
FROM
(SELECT cuisine_type,
restaurant_name,
COUNT(1) cnt_order
FROM food_orders fo
GROUP BY 1, 2
) a
ORDER BY 1, 3
날짜 데이터
문자 타입, 숫자 타입과 같이 날짜 데이터도 특정한 타입을 가진다.
년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있고, 목적에 따라 월, 주, 일 등으로 포맷을 변경 가능하다.
예시. 3월 조건으로 지정하고, 년도별로 정렬해보자.
SELECT DATE_FORMAT(DATE(date), '%Y') as "년",
DATE_FORMAT(DATE(date), '%m') as "월",
DATE_FORMAT(DATE(date), '%Y%m') as "년월",
COUNT(1) "주문건수"
FROM food_orders fo inner join payments p on fo.order_id = p.order_id
WHERE DATE_FORMAT(DATE(date), '%m')='03'
GROUP BY 1, 2, 3
ORDER BY 1
