sql, outer join과 집계 함수의 활용(null 대응)
join 과 집계 함수
- inner join 의 경우 두 개의 테이블을 특정 기준으로 비교하여 두 테이블에 레코드가 모두 존재하는 경우만 출력한다.
- outer join 의 경우 기준이 되는 테이블 전체의 출력을 보장한다.
- left join을 할 경우 왼쪽 테이블 전체의 출력을 보장한다.
- 왼쪽 테이블의 어떤 레코드가 오른쪽의 레코드와 연결되는 값이 없으면 오른쪽 레코드가 들어갈 칼럼에 null을 채운다.
- null의 존재유무가 테이블 간 비교를 위한 중요한 기준이 된다.
- 하지만 레코드를 reduce하는 sum 등의 집계함수는 null 등을 어떻게 처리해야 하는가?
종이박스와 상자에 있는 상품의 갯수를 합치기
- https://leetcode.com/problems/count-apples-and-oranges/
- 오렌지와 사과를 담는 상자chests와 종이박스boxes가 있다.
- boxes에는 chest_id가 있다. chests에는 chest_id를 fk로 가진다.
- 요구사항은 boxes를 기준으로 모든 레코드의 사과와 오렌지의 갯수를 구하는 것이다. 다만 chest_id를 가진 boxes의 경우 chest 테이블에 있는 사과와 오렌지의 값이 boxes에 있는 사과와 오렌지와 별도로 추가된다.
Input:
Boxes table:
+--------+----------+-------------+--------------+
| box_id | chest_id | apple_count | orange_count |
+--------+----------+-------------+--------------+
| 2 | null | 6 | 15 |
| 18 | 14 | 4 | 15 |
| 19 | 3 | 8 | 4 |
| 12 | 2 | 19 | 20 |
| 20 | 6 | 12 | 9 |
| 8 | 6 | 9 | 9 |
| 3 | 14 | 16 | 7 |
+--------+----------+-------------+--------------+
Chests table:
+----------+-------------+--------------+
| chest_id | apple_count | orange_count |
+----------+-------------+--------------+
| 6 | 5 | 6 |
| 14 | 20 | 10 |
| 2 | 8 | 8 |
| 3 | 19 | 4 |
| 16 | 19 | 19 |
+----------+-------------+--------------+
Output:
+-------------+--------------+
| apple_count | orange_count |
+-------------+--------------+
| 151 | 123 |
+-------------+--------------+
Explanation:
box 2 has 6 apples and 15 oranges.
box 18 has 4 + 20 (from the chest) = 24 apples and 15 + 10 (from the chest) = 25 oranges.
box 19 has 8 + 19 (from the chest) = 27 apples and 4 + 4 (from the chest) = 8 oranges.
box 12 has 19 + 8 (from the chest) = 27 apples and 20 + 8 (from the chest) = 28 oranges.
box 20 has 12 + 5 (from the chest) = 17 apples and 9 + 6 (from the chest) = 15 oranges.
box 8 has 9 + 5 (from the chest) = 14 apples and 9 + 6 (from the chest) = 15 oranges.
box 3 has 16 + 20 (from the chest) = 36 apples and 7 + 10 (from the chest) = 17 oranges.
Total number of apples = 6 + 24 + 27 + 27 + 17 + 14 + 36 = 151
Total number of oranges = 15 + 25 + 8 + 28 + 15 + 15 + 17 = 123
해소방안1. outer join과 집계함수 실패
- chests와 boxes를 left join하였다. 그리고 집계함수를 활용했다.
- 하지만 실패했다.
- 그 이유는 null을 처리하지 못하기 때문이다.
select
sum(b.apple_count+c.apple_count) apple_count
, sum(b.orange_count+c.orange_count) orange_count
from boxes b
left join chests c
on b.chest_id = c.chest_id
- 아래 쿼리를 작성하면 실제로 null이 반환됨을 확인할 수 있다.
select
b.apple_count a
, c.apple_count b
, b.apple_count + c.apple_count
from boxes b
left join chests c
on b.chest_id = c.chest_id
[6, null, null]
[4, 20, 24]
[8, 19, 27]
[19, 8, 27]
[12, 5, 17]
[9, 5, 14]
[16, 20, 36]
해소방안2. inner join과 null의 별도 처리
- 두 개의 작업을 수행한다 : chests를 가진 레코드와 없는 레코드
- chests와 boxes를 join하여 chests가 있는 레코드를 관리한다.
- chests가 없는 boxes를 별도로 계산한다.
- 두 작업을 합친다.
select
sum(apple_count) apple_count
, sum(orange_count) orange_count
from (
select
sum(c.apple_count+b.apple_count) apple_count
, sum(c.orange_count+b.orange_count) orange_count
from boxes b
join chests c
on b.chest_id = c.chest_id
union
select
sum(apple_count) apple_count
, sum(orange_count) orange_count
from boxes
where chest_id is null
)tb
해소방안3. outer join과 집계함수 성공
- 아래의 방법을 사용하면 더 간단하다. ifnull을 사용하여 null을 0으로 대체한다.
select
ifnull(sum(boxes.apple_count), 0) + ifnull(sum(chests.apple_count), 0) as apple_count
, ifnull(sum(boxes.orange_count), 0) + ifnull(sum(chests.orange_count), 0) as orange_count
from boxes
left join chests on chests.chest_id = boxes.chest_id