SQL 실무 기초 (5) - 오류 대처, Pivot Table, 다양한 SQL 심화 문법
벌써 마지막 주차인 5주차..! 이제 어딜 가서도 SQL의 기본은 안다고 말할 수 있을 것 같다.
오늘은 바로 본론으로 들어가보자.
===========================
먼저 예상치 못한 데이터 결과값이 나오는 상황을 몇가지 살펴보고, 각 상황에 맞춰 대응하는 방법을 알아보자.
1. 조회한 데이터에 아무런 값이 없는 경우
지난번에 조금 다뤄본 내용이긴 하지만, 예를 들어 food_orders라는 테이블의 rating이라는 컬럼 값에 1~5 숫자값이 아닌 'Not Given'이라는 문자열 값이 저장되어 있는 경우가 있다고 하자.
내가 원했던 SQL 출력값은 한식 레스토랑의 평균 평점이었는데, 몇몇 한식당의 평점이 집계가 되지 않은 상황이어서 Not Given 값을 가지고 있었고, AVG 키워드를 써서 계산을 하다가 원하지 않은 결과값이 난 상황이라면, 어떻게 해결을 해야 할까?
두가지 해결책이 있다.
1) 데이터 값이 없다면 제외하기
2) 다른 값을 대신 사용하기
1) 데이터 값이 없다면 제외하기:
MYSQL에서는 사용할 수 없는 값일 때 해당 값을 연산에서 제외할 수 있다.
즉, rating 컬럼 값이 'Not Given'일 때, 이를 제외하고 평균을 계산하면 된다.
select restaurant_name,
avg(rating) average_of_rating,
avg(if(rating<>'Not given', rating, null)) average_of_rating2
from food_orders
group by 1
이 코드는 그냥 avg(rating)을 사용해서 평균값을 낸 코드와 avg() 내에서 if문을 사용해서 'Not Given' 값을 null값으로 만들어 평균을 계산할 때 제외하고 계산한 값을 동시에 보여준다.
average_of_rating하고 average_of_rating2를 비교하면, 전체적으로 봤을 때 average_of_rating2가 더 큰 값을 가진다.
이는 바로 0으로 간주되는 Not given 값을 포함해서 평균값을 계산한 average_of_rating과 그 값들을 제외하고 평균값을 제외한 average_of_rating2에서 차이가 나기 때문이다.
2) 다른 값을 대신 사용하기
사용할 수 없는 값을 대체해서 사용하는 방법도 있다.
예를 들어서 음식 주문을 할 때 손님의 나이 정보를 계산해서 정리한 테이블이 있다고 가정하자. 그런데 주문 고객의 나이가 적히지 않은 음식 주문이 있을 때, 가장 평균에 가까운 나이를 Default로 정해서 전체 평균을 계산할 수 있을 것이다.
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
coalesce(b.age, 20)은 바로 그 역할을 하는 코드인데, where b.age is null과 같이 사용해서 b.age의 값이 null일 경우에 20으로 바꾸는 작업을 수행한다.
=================================
두번째로 곤란한 상황은 바로 조회한 데이터가 상식적이지 않은 값을 가지고 있을 경우이다.
예를 들어서 주문 고객의 나이를 조사할 때, 2살이나 1살, 99살이나 105살 같은 주문 고객의 실수로 잘못 적은 것 같은 나이가 집계되었을 때, 혹은 렌트카의 반납 일자가 예약 일자보다 더 이르게 기록된 잘못된 테이블 정보가 있을 때, 조건문으로 출력할 값의 범위를 지정함으로써 테이블 정보를 제한적으로 출력할 수 있다.
select customer_id, name, email, gender, age,
case when age<15 then 15
when age>80 then 80
else age end "범위를 지정해준 age"
from customers
이 코드는 나이가 15세 미만일 때는 최소값인 15세로, 80세 이상일 땐 최대값인 80세로 나이 정보를 수정해서 값을 출력하는 코드이다.
==================================
다음으로는 엑셀에서 많이 사용해 봤을 Pivot Table을 SQL에서 만들어 보는 방법을 배워보자!
Pivot Table이란,
2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것을 의미한다.
데이터 양이 많을 때 일반적인 SQL 문처럼 스크롤을 많이 할 필요 없이 한눈에 데이터를 보기 쉽다는 장점이 있다.
예를 들어 이런 형태를 가진다.
예를 들어서 연령별, 그리고 성별 별로 주문건수 Pivot Table 뷰를 만들어야 할 때, 이런 식으로 코드를 짤 수 있다.
먼저 나이를 기준으로 subquery를 적으면,
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
age를 CASE문을 사용해서 10대, 20대, ... , 50대로 구분할 수 있고, count 와 group by를 사용해서 데이터를 집계할 수 있다.
그리고 이 pivot table의 base가 되는 데이터를 찾았다면, 이걸 이용해서 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 age
신경 써서 봐야 할 부분은 바로 max() 함수이다. 이렇게 구분 컬럼으로써 사용할 컬럼들에게 있어 max()함수로 감싸줘야만 pivot table의 형태를 만들 수 있다. 그리고 그 앞에 age는 집계 기준 데이터로써 활용한다.
이 코드를 수행하면, 아래와 같은 pivot table을 얻을 수 있다.
===================================
마지막으로 알아볼 SQL 심화 문법 두가지를 소개하자면, 바로 Rank()와 Sum() 함수이다.
Sum() 함수는 조금 익숙하긴 하지만, 조금 다른 사용 방법이 있으니 한번 알아보자.
다음과 같은 상황에서 사용을 고려해 볼 수 있다.
1) 한식 식당 중에서 주문건수가 많은 순으로 순위를 매기고 싶을 때
2) 한식 식당 전체 주문건수 중 A 식당이 차지하는 비율을 알고 싶을 때
3) 2건 이상 주문을 한 소비자 중 처음 주문한 식당과 2번째로 주문한 식당을 같이 조회할 때
기본 SQL 구조로 해결하기 위해서는 복잡한 Subquery 문을 사용해야 하지만, window function으로서 자체적으로 제공해 주는 기능을 활용하면 조금 더 편리하게 query를 작성할 수 있다.
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
먼저 음식 타입, 그리고 음식점 별 주문 건수를 집계한 코드이다.
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
그리고 이건 Rank() 함수를 사용한 코드인데, 구조를 살펴보자.
rank() 함수 내부에는 값을 쓰지 않아도 되지만, 항상 이 함수는 뒤에 over 키워드가 따라다니니 잊지 않도록 주의하자.
그리고 어떤 값의 랭킹을 매길 때 어느 값들의 묶음을 랭킹을 매길 것인지 정할 Column을 partition by 뒤에 적어주고, 그 값들 중 어떤 기준을 사용해서 랭킹을 매길 지를 정해 order by 뒤에 적어준다.
그리고 마지막으로...
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
적었던 subquery를 다시 한번 크게 다른 query로 감싼 후 where 절을 사용해서 음식 타입별 Top 3로 선정된 음식점들만을 집계하는 코드이다.
결과가 잘 나오는 것을 확인할 수 있다.
==================================
두번째로 카데고리별 합과 카데고리별 누적 총합을 구하는 방법이다.
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
다시 한번 이 코드를 가져와서 음식 타입별, 음식점별 주문 건수를 집계한 상황을 예시로 들어보자.
select cuisine_type,
restaurant_name,
order_count,
sum(order_count) over (partition by cuisine_type) sum_cuisine_type,
sum(order_count) over (partition by cuisine_type order by order_count, restaurant_name) cumulative_sum
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a
sum() 함수를 주의깊게 살펴보면, rank()와 형태가 유사하게 생겼다.
여기에서는 주문 건수의 누적 총합, 그리고 주문 건수의 카데고리별 총합을 구하는 상황이므로 sum() 내부에 order_count를 넣어주고, sum() 함수 역시 over 키워드와 함께 사용한 후 총합을 구할 카데고리를 partition by 뒤에 적어주면 카데고리별 총합을 구할 수 있다.
그리고 누적 총합을 구하기 위해서는 order by 문 다음으로 어떤 컬럼 값을 기준으로 합을 구할 건지를 나타내는 컬럼을 적어주면 된다.
이 외에도 Window Function은 많으니, 여러가지를 검색해서 사용해보자!
SQL 실무 기본 과정은 여기서 끝났지만, 앞으로도 SQL을 더 많이 사용해보며 스킬을 보완해 나가야겠다. ^^