159강이 표 하나에서 원하는 것을 꺼냈습니다. 이제 표 여럿을 잇고, 이은 것을 묶어 셉니다.
조인과 집계는 실무 질의의 대부분을 차지합니다. 그리고 가장 많은 합계 오류가 이 둘 사이에서 나옵니다.
조인의 정의는 한 줄입니다.
곱을 만든 뒤 조건으로 거르는 것입니다. 159강 문제 5의 밀어내리기가 바로 이 자리에서 가장 크게 작동합니다.
그런데 정의가 그렇다고 해서 실제로 곱을 만들지는 않습니다. 백만 행과 십만 행의 곱은 천억 행이며, 이것을 만드는 순간 질의는 끝나지 않습니다. 정의는 뜻이고, 실행은 별개라는 것이 문제 5의 주제입니다.
그리고 이 강의의 절반은 하나의 사고에 관한 것입니다.
| 언제 | 무슨 일이 일어나는가 |
|---|---|
| 한 주문에 배송이 여러 건일 때 | 조인이 주문 행을 복제합니다 |
| 그 상태로 금액을 합할 때 | 같은 금액이 여러 번 더해집니다 |
| 결과를 보고할 때 | 매출이 조용히 부풀어 있습니다 |
이것을 팬아웃(fan-out)이라 부릅니다. 오류 메시지가 없고, 질의는 성공하며, 숫자만 틀립니다. 문제 3이 이것을 다룹니다.
WHERE와 HAVING을 가려 쓸 수 있습니다.문제. 사용자 표 행과 주문 표 행이 있습니다.
(1) 두 표의 곱집합이 몇 행인지 구하세요.
(2)orders.user = users.id를 만족하는 행이 몇 개인지 세세요.
(3) 안쪽 조인의 결과와 선택도를 구하세요.
생각의 실마리. 두 표를 잇는다는 것은 한쪽의 각 행에 다른 쪽의 어느 행이 대응하는지 정하는 일입니다. 아무 정보가 없다면 모든 짝을 다 만들어 놓고 조건으로 거르는 수밖에 없습니다. 그 모든 짝이 곱집합입니다.
풀이. (1)(2)(3) 검산 결과입니다.
| 항목 | 값 |
|---|---|
| 곱집합 | 행 |
| 조건을 만족하는 행 | 개 |
| 안쪽 조인 결과 | 행 |
| 짝이 없어 빠진 주문 | 번호 |
| 선택도 |
주문 번은 사용자 번을 가리키는데 사용자 표에 가 없습니다. 158강 문제 5에서 외래키 참조 무결성 위반으로 잡았던 바로 그 행이며, 안쪽 조인은 그런 행을 말없이 버립니다.
선택도의 뜻이 중요합니다.
곱집합 행 중 행만 남았으므로 입니다. 최적화기는 이 값을 미리 추정해서 계획을 고릅니다. 추정이 틀리면 계획도 틀립니다.
이 문제에서 배우는 것. 조인은 곱을 만든 뒤 조건으로 거르는 것과 뜻이 같습니다. 다만 뜻만 같습니다. 실제로 곱을 만들면 백만 행과 십만 행이 천억 행이 되므로, 어떤 데이터베이스도 그렇게 실행하지 않습니다. 정의는 무엇을 원하는지를 적고, 실행 방법은 문제 5에서 따로 정합니다.
바로 확인 1.
확인 1-1. 조인을 곱과 선택으로 쓰세요.
답. 입니다.
확인 1-2. 검산에서 안쪽 조인이 행인 이유를 쓰세요.
답. 주문 번이 가리키는 사용자 번이 사용자 표에 없어서 버려지기 때문입니다.
확인 1-3. 선택도가 무엇인지 쓰세요.
답. 곱집합 중 조인 조건을 통과하는 비율이며 검산에서는 입니다.
문제. 주문 건 중 하나는 짝이 없고, 사용자 명 중 하나는 주문이 없습니다.
(1) 조인 네 종류의 결과 행 수를 각각 구하세요.
(2) 바깥쪽 조인 뒤의 집계가 무엇을 세는지 확인하세요.
(3) 조건을ON에 두는 것과WHERE에 두는 것의 차이를 보세요.
생각의 실마리. 안쪽 조인은 짝이 있는 행만 남깁니다. 그런데 실무에서는 짝이 없다는 사실 자체가 답인 경우가 많습니다. 주문한 적 없는 사용자를 찾는 질의가 그렇습니다. 그래서 짝 없는 행을 살려 두는 조인이 필요합니다.
풀이. (1) 검산 결과입니다.
| 조인 종류 | 결과 행 수 | 새로 생기는 값없음 |
|---|---|---|
| 안쪽 | ||
| 왼쪽 바깥쪽 | ||
| 오른쪽 바깥쪽 | ||
| 완전 바깥쪽 |
짝 없는 주문은 번호 이고, 주문 없는 사용자는 번입니다.
왼쪽 바깥쪽 조인은 왼쪽 표의 모든 행을 남깁니다. 주문 번이 살아남되 사용자 쪽 열이 전부 NULL이 됩니다. 오른쪽 바깥쪽은 반대이고, 완전 바깥쪽은 양쪽을 다 남깁니다.
(2) 오른쪽 바깥쪽 조인 뒤의 amount 열을 셉니다.
| 집계 | 값 |
|---|---|
| 행 수 | |
| 값이 있는 칸 | |
| 값없음인 칸 | |
COUNT(*) |
|
COUNT(amount) |
|
SUM(amount) |
AVG는 분모를 무엇으로 두느냐에 따라 또는 입니다. 앞은 이고 뒤는 입니다.
SQL의 AVG는 뒤를 택합니다. NULL을 세지 않으므로 분모가 입니다. 그런데 **"사용자 한 명당 평균 주문 금액"**을 물었다면 주문이 없는 사용자도 분모에 들어가야 하므로 앞이 맞습니다. 질문이 정해지기 전에는 어느 쪽도 옳지 않습니다.
(3) 조건의 위치를 바꿔 봅니다.
| 질의 | 결과 |
|---|---|
| 왼쪽 바깥쪽 조인 | 행 |
그 뒤 WHERE users.id IS NOT NULL |
행 |
바깥쪽 조인을 해 놓고 오른쪽 열에 WHERE를 걸면 안쪽 조인으로 되돌아갑니다. 방금 살려 낸 행을 그 다음 줄에서 다시 죽인 것입니다.
159강 문제 5의 실행 순서가 이유를 설명합니다. FROM이 조인을 마친 뒤 WHERE가 그 결과를 거르므로, WHERE는 NULL이 채워진 행을 그냥 하나의 행으로 봅니다. 조건을 살려 두려면 ON 절에 넣어야 하며, 그러면 조인하는 동안 적용되어 짝 없는 행은 그대로 남습니다.
이 문제에서 배우는 것. 바깥쪽 조인은 원래 자료에 없던 NULL을 만들어 냅니다. 그 NULL은 158강 문제 4의 결측과 성격이 다릅니다. 측정에 실패한 것이 아니라 짝이 없다는 사실의 표시입니다. 그런데 그 뒤의 집계 함수는 둘을 구별하지 못하므로, COUNT(*)와 COUNT(col) 중 무엇을 쓸지가 곧 무엇을 묻는지가 됩니다.
바로 확인 2.
확인 2-1. 왼쪽 바깥쪽 조인이 무엇을 보장하는지 쓰세요.
답. 왼쪽 표의 모든 행이 결과에 적어도 한 번 나타나는 것을 보장합니다.
확인 2-2. 검산에서 완전 바깥쪽 조인이 행인 이유를 쓰세요.
답. 안쪽 행에 짝 없는 주문 행과 주문 없는 사용자 행이 더해지기 때문입니다.
확인 2-3. 바깥쪽 조인 뒤 오른쪽 열에 WHERE를 걸면 무엇이 되는지 쓰세요.
답. NULL인 행이 걸러져 안쪽 조인과 같아집니다.
문제. 주문 건에 배송이 건 달려 있습니다.
(1) 조인 전후의 금액 합을 각각 구하세요.
(2)SUM(DISTINCT amount)로 고칠 수 있는지 확인하세요.
(3) 올바른 대책을 세우세요.
생각의 실마리. 조인은 짝을 만드는 연산입니다. 왼쪽 한 행에 오른쪽 세 행이 맞으면 결과에는 세 행이 생깁니다. 그러면 왼쪽 행에 있던 값도 세 번 복제됩니다. 복제된 값을 그대로 합하면 어떻게 될지 생각해 봅니다.
풀이. (1) 검산 결과입니다.
| 주문 | 금액 | 배송 건수 | 조인 뒤 금액이 더해지는 횟수 |
|---|---|---|---|
| 항목 | 값 |
|---|---|
| 조인 전 주문 | 행, 합 원 |
| 조인 뒤 | 행, 합 원 |
| 부푼 배수 | 배 |
각 금액이 배송 건수만큼 반복되어 더해졌습니다. 이것이 팬아웃입니다.
질의는 성공했고 오류도 없습니다. 매출이 원인데 보고서에는 원이 찍히며, 배송을 많이 한 주문일수록 더 크게 부풀기 때문에 왜곡의 방향까지 편향되어 있습니다.
(2) DISTINCT로 덮어 봅니다.
| 방법 | 결과 | 참값과의 차이 |
|---|---|---|
SUM(amount) |
||
SUM(DISTINCT amount) |
||
| 참값 |
이번에는 반대로 모자랍니다. 금액이 원인 주문이 건인데 DISTINCT가 값을 기준으로 중복을 지우므로 그중 하나만 남습니다.
부풀린 것을 지우려다 원래 있던 것까지 지웠습니다. DISTINCT는 행의 중복을 지우는 도구인데, 팬아웃이 만든 것은 값의 반복이라 도구가 문제와 맞지 않습니다.
(3) 올바른 대책은 먼저 집계한 뒤 조인하는 것입니다.
| 주문 | 금액 | 미리 센 배송 건수 |
|---|---|---|
배송 표를 주문별로 먼저 세면 주문마다 정확히 한 행이 되므로, 그것을 주문 표에 이어도 여전히 행이고 금액 합은 원 그대로입니다.
진단 방법도 함께 익힙니다. 조인 뒤 합계가 이상하면 먼저 행 수를 봅니다. 조인 전 행이 조인 뒤 행이면 일대다이고, 그 순간부터 왼쪽 표의 모든 수치 열은 믿을 수 없습니다.
이 문제에서 배우는 것. 조인의 낟알(granularity)이 바뀌면 집계의 뜻이 바뀝니다. 주문 단위 표를 배송 단위로 바꿔 놓고 주문 금액을 합하면, 그것은 매출이 아니라 **"배송 한 건마다 그 주문의 금액을 적어 놓고 모두 더한 값"**입니다. 질의가 답한 것과 내가 물은 것이 다릅니다. 대책은 언제나 하나이며, 집계하려는 낟알로 먼저 줄인 뒤에 잇습니다.
바로 확인 3.
확인 3-1. 팬아웃이 무엇인지 쓰세요.
답. 일대다 조인이 왼쪽 행을 복제해 그 값이 여러 번 집계되는 현상입니다.
확인 3-2. 검산에서 SUM(DISTINCT amount)가 참값보다 작은 이유를 쓰세요.
답. 금액이 원인 주문이 건인데 값 기준으로 중복을 지워 하나만 남기 때문입니다.
확인 3-3. 팬아웃의 올바른 대책을 쓰세요.
답. 많은 쪽 표를 원하는 낟알로 먼저 집계한 뒤 조인합니다.
문제. 주문 행을 도시별로 묶습니다. 금액 한 칸은 값이 없습니다.
(1) 도시별COUNT,SUM,AVG를 구하세요.
(2) 행이 하나도 없는 도시가 결과에 나오는지 확인하세요.
(3)WHERE와HAVING이 어떻게 다른지 수치로 보이세요.
생각의 실마리. 묶어 센다는 것은 여러 행을 한 행으로 접는 일입니다. 접을 때 무엇을 세느냐가 문제인데, 159강 문제 3에서 본 것처럼 값이 없는 칸이 하나만 있어도 분모가 흔들립니다.
풀이. (1) 검산 결과입니다.
| 도시 | COUNT(*) |
COUNT(amount) |
SUM |
AVG |
|---|---|---|---|---|
| 서울 | ||||
| 부산 | ||||
| 대구 | ||||
| 인천 |
부산만 두 개수가 다릅니다. 행은 개인데 금액이 있는 행은 개입니다.
| 부산의 평균을 구하는 방법 | 값 |
|---|---|
SUM을 COUNT(*)로 나눔 |
|
SUM을 COUNT(amount)로 나눔 |
차이가 퍼센트에 가깝습니다. SQL의 AVG는 뒤를 택하지만, 결측을 으로 보는 것이 맞는 상황이라면 앞이 맞습니다. 158강 문제 4의 교훈이 그대로입니다. 결측을 어떻게 볼지 정하는 것은 질의가 아니라 사람의 일입니다.
(2) 도시 목록에 광주가 있는데 주문은 하나도 없습니다.
| 항목 | 값 |
|---|---|
| 도시 목록 | 개 |
| 집계 결과에 나온 도시 | 개 |
광주는 아예 나오지 않습니다. GROUP BY는 있는 행을 묶는 연산이라 행이 없으면 그룹 자체가 만들어지지 않습니다.
그래서 없는 그룹의 SUM은 이 아닙니다. 행이 없다는 것과 합이 이라는 것은 다르며, 이것을 으로 보이게 하려면 도시 목록 표를 왼쪽에 두고 바깥쪽 조인을 한 뒤 NULL을 으로 바꿔야 합니다. 문제 2의 바깥쪽 조인이 여기 쓰입니다.
(3) 거르는 위치를 바꿔 봅니다.
| 방식 | 남는 도시 |
|---|---|
WHERE amount >= 10000으로 거른 뒤 묶기 |
서울, 부산, 대구, 인천 |
묶은 뒤 HAVING SUM(amount) >= 30000 |
서울, 부산, 대구 |
두 질의는 다른 질문에 답합니다. 앞은 만 이상인 주문이 하나라도 있는 도시를 찾고, 뒤는 합계가 만 이상인 도시를 찾습니다.
WHERE는 묶기 전에 행을 없애므로 합계 자체를 바꿉니다. 부산의 합은 에서 으로 바뀌는데, 원짜리 주문이 묶이기 전에 사라졌기 때문입니다.
이 문제에서 배우는 것. 집계는 언제나 "무엇을 몇 개로 나누는가"의 문제입니다. COUNT(*)와 COUNT(col)은 분모를 다르게 잡고, WHERE와 HAVING은 분자를 다르게 잡습니다. 질의가 문법적으로 맞는지는 아무것도 보장하지 않으며, 맞는지 확인하는 유일한 방법은 분모에 무엇이 들어 있는지 직접 세어 보는 것입니다.
바로 확인 4.
확인 4-1. COUNT(*)와 COUNT(col)의 차이를 쓰세요.
답. 앞은 행을 모두 세고 뒤는 값이 있는 칸만 셉니다.
확인 4-2. 검산에서 광주가 결과에 없는 이유를 쓰세요.
답. GROUP BY는 있는 행을 묶으므로 행이 없으면 그룹이 만들어지지 않기 때문입니다.
확인 4-3. WHERE가 HAVING과 다른 점을 쓰세요.
답. WHERE는 묶기 전 행을 걸러 합계 자체를 바꾸고 HAVING은 묶은 뒤 그룹을 거릅니다.
문제. 만, 만인 두 표를 잇습니다.
(1) 조인 알고리즘 넷의 연산 횟수를 구하세요.
(2) 어느 것이 언제 쓰이는지 정리하세요.
(3) 세 표를 이을 때 순서가 무엇을 바꾸는지 구하세요.
생각의 실마리. 문제 1의 정의를 그대로 실행하면 곱집합을 만들게 되며, 만 곱하기 만은 천억입니다. 그래서 정의와 다른 방법으로 같은 답을 내야 합니다. 오른쪽 표를 통째로 훑지 않고 짝을 찾는 방법이 무엇일지 생각해 봅니다.
풀이. (1) 검산 결과입니다.
| 알고리즘 | 연산 횟수 | 중첩 루프 대비 | 중첩 루프가 느린 배수 |
|---|---|---|---|
| 중첩 루프 | |||
| 색인 중첩 루프 | |||
| 정렬 병합 | |||
| 해시 |
각 항의 근거는 다음과 같습니다.
| 알고리즘 | 비용 |
|---|---|
| 중첩 루프 | |
| 색인 중첩 루프 | |
| 정렬 병합 | |
| 해시 |
해시 조인이 중첩 루프보다 만 배 빠릅니다. 오른쪽 표로 해시표를 만들어 두면 왼쪽 행마다 한 번의 조회로 짝을 찾으므로, 양쪽을 각각 한 번씩만 훑으면 끝납니다.
(2) 그런데 해시가 언제나 되는 것은 아닙니다.
| 조인 조건 | 쓸 수 있는 알고리즘 |
|---|---|
| 등호 | 넷 모두 가능하며 보통 해시 |
| 부등호나 범위 | 해시 불가, 정렬 병합이나 중첩 루프 |
| 조건 없음 | 곱집합뿐 |
해시는 같은 값을 같은 통에 넣는 원리라서 크다 작다를 표현할 수 없습니다. 그래서 부등호 조인은 정렬 병합으로 가며, 정렬해 두면 한쪽을 훑는 동안 다른 쪽 포인터를 앞으로만 밀 수 있습니다.
검산에서 정렬 병합 회는 색인 중첩 루프 회보다 많습니다. 정렬 비용이 앞에 붙기 때문인데, 이미 정렬되어 있거나 색인이 없으면 순서가 뒤집힙니다. 절대 우열이 아니라 상황의 문제입니다.
(3) 세 표를 잇습니다. 주문 가 만 행, 사용자 가 행, 등급 가 행이며, 주문 하나는 사용자 하나에, 사용자 하나는 등급 하나에 붙습니다.
| 계획 | 중간 결과 행 수 | 최종 행 수 |
|---|---|---|
최종 행 수는 만으로 같은데 중간 결과가 배 차이 납니다. 작은 표끼리 먼저 이으면 행짜리 중간 결과가 나오고, 큰 표부터 이으면 만 행을 만들어 놓고 다시 훑습니다.
같은 답을 주는 두 계획의 비용이 배 다릅니다. 조인이 개면 순서의 가짓수가 계승으로 늘어나므로, 최적화기는 모든 순서를 시도하지 않고 통계로 크기를 추정해 고릅니다. 추정이 틀리면 계획이 틀립니다.
이 문제에서 배우는 것. 무엇을 원하는지와 어떻게 할지는 완전히 분리되어 있습니다. 159강에서 SQL이 선언형이라고 한 말의 실체가 이것입니다. 같은 질의문이 통계에 따라 다른 계획으로 실행되며, 느린 질의를 고치는 일은 대개 질의문을 고치는 것이 아니라 최적화기가 옳은 추정을 하도록 돕는 일입니다. 그리고 선택 밀어내리기가 조인에서 가장 크게 작동합니다. 조인 앞에서 행을 하나 줄이면 조인 뒤에서 여러 행이 줄기 때문입니다.
바로 확인 5.
확인 5-1. 해시 조인이 부등호에 쓰이지 못하는 이유를 쓰세요.
답. 해시는 같은 값을 같은 통에 넣을 뿐이라 크기 순서를 표현하지 못하기 때문입니다.
확인 5-2. 검산에서 정렬 병합이 색인 중첩 루프보다 연산이 많은 이유를 쓰세요.
답. 양쪽을 정렬하는 비용이 앞에 붙기 때문이며 이미 정렬되어 있으면 뒤집힙니다.
확인 5-3. 조인 순서가 무엇을 바꾸는지 쓰세요.
답. 최종 행 수는 같고 중간 결과 크기가 바뀌며 검산에서는 배 차이가 났습니다.
| 조인 | SQL | 남기는 것 | 검산 행 수 |
|---|---|---|---|
| 안쪽 | INNER JOIN |
양쪽에 짝이 있는 행만 | |
| 왼쪽 바깥쪽 | LEFT JOIN |
왼쪽 전부와 짝 있는 오른쪽 | |
| 오른쪽 바깥쪽 | RIGHT JOIN |
오른쪽 전부와 짝 있는 왼쪽 | |
| 완전 바깥쪽 | FULL OUTER JOIN |
양쪽 전부 | |
| 곱 | CROSS JOIN |
모든 짝 |
| 집계 함수 | 값없음을 세는가 | 분모 |
|---|---|---|
COUNT(*) |
셉니다 | 행 전체 |
COUNT(col) |
세지 않습니다 | 값이 있는 칸 |
SUM |
건너뜁니다 | 해당 없음 |
AVG |
건너뜁니다 | 값이 있는 칸 |
COUNT(DISTINCT col) |
세지 않습니다 | 서로 다른 값 |
| 조인 알고리즘 | 비용 | 쓰이는 곳 |
|---|---|---|
| 중첩 루프 | 한쪽이 아주 작을 때 | |
| 색인 중첩 루프 | 색인이 있을 때 | |
| 정렬 병합 | 부등호이거나 이미 정렬됐을 때 | |
| 해시 | 등호 조인의 기본 |
| 자주 하는 실수 | 바로잡기 |
|---|---|
일대다 조인 뒤 그대로 SUM합니다 |
먼저 집계한 뒤 조인합니다 |
팬아웃을 DISTINCT로 덮습니다 |
같은 값인 다른 행까지 지웁니다 |
바깥쪽 조인 뒤 오른쪽 열에 WHERE를 겁니다 |
안쪽 조인으로 되돌아갑니다 |
COUNT(*)로 값 있는 칸을 셉니다 |
COUNT(col)을 씁니다 |
| 없는 그룹의 합을 으로 봅니다 | 그룹 자체가 없으므로 바깥쪽 조인이 필요합니다 |
WHERE에 집계 조건을 씁니다 |
HAVING에 씁니다 |
| 조인 순서를 손대지 않습니다 | 중간 결과가 배까지 달라집니다 |
문제 6. 조인을 곱과 선택으로 쓰세요.
답. 입니다.
문제 7. 검산에서 곱집합과 안쪽 조인의 행 수를 쓰세요.
답. 곱집합은 행이고 안쪽 조인은 행입니다.
문제 8. 조인 선택도의 정의를 쓰세요.
답. 조인 결과 행 수를 두 표의 행 수의 곱으로 나눈 값이며 검산에서는 입니다.
문제 9. 안쪽 조인이 버리는 행이 무엇인지 쓰세요.
답. 반대쪽에 짝이 없는 행이며 검산에서는 주문 번입니다.
문제 10. 네 조인의 결과 행 수를 검산 값으로 쓰세요.
답. 안쪽 , 왼쪽 바깥쪽 , 오른쪽 바깥쪽 , 완전 바깥쪽 입니다.
문제 11. 바깥쪽 조인이 만드는
NULL이 결측과 다른 점을 쓰세요.
답. 측정 실패가 아니라 짝이 없다는 사실의 표시입니다.
문제 12. 검산에서 팬아웃의 배수를 쓰세요.
답. 원이 원이 되어 배입니다.
문제 13. 팬아웃이 일어나는 조건을 쓰세요.
답. 조인 상대 표에 같은 키를 가진 행이 둘 이상 있을 때입니다.
문제 14.
SUM(DISTINCT amount)가 틀리는 이유를 쓰세요.
답. 행이 아니라 값의 중복을 지우므로 금액이 같은 서로 다른 주문이 사라집니다.
문제 15. 검산에서 부산의 두
COUNT가 다른 이유를 쓰세요.
답. 행은 개인데 금액이 있는 칸이 개이기 때문입니다.
문제 16. 광주가 집계 결과에 나오지 않는 이유를 쓰세요.
답. 행이 없으면 GROUP BY가 그룹을 만들지 않기 때문입니다.
문제 17. 검산에서
WHERE가 부산의 합을 어떻게 바꿨는지 쓰세요.
답. 에서 으로 바뀌었으며 원 주문이 묶이기 전에 사라졌습니다.
문제 18. 네 조인 알고리즘의 비용을 순서대로 쓰세요.
답. , , 정렬 비용의 합, 입니다.
심화 1. 조인 결과 크기를 어떻게 추정하는지 정리하세요.
최적화기가 계획을 고르려면 각 조인의 결과 크기를 미리 알아야 합니다. 실제로 실행해 볼 수는 없으므로 통계로 추정합니다.
키가 서로 다른 값을 개 가진다면, 값이 고르게 퍼져 있다는 가정 아래 다음이 표준 공식입니다.
외래키 조인이면 훨씬 간단합니다. 주문의 사용자 번호가 사용자 표의 기본키를 가리키면 주문 한 행은 정확히 사용자 한 행에 붙으므로 결과는 입니다. 문제 5의 가 만인 이유가 이것입니다.
이 가정이 깨지는 곳에서 계획이 무너집니다.
| 가정 | 현실에서 깨지는 방식 |
|---|---|
| 값이 고르게 퍼져 있습니다 | 특정 사용자가 주문의 절반을 차지합니다 |
| 열끼리 독립입니다 | 도시와 우편번호가 사실상 같은 정보입니다 |
| 통계가 최신입니다 | 대량 적재 뒤 갱신하지 않았습니다 |
치우친 분포가 가장 흔한 원인입니다. 평균으로 추정한 크기는 작은데 실제로는 한 값에 몰려 있어, 해시 조인의 한 통이 메모리를 넘기고 디스크로 쏟아집니다. 이때 예상 시간과 실제 시간이 수십 배 벌어집니다.
실행 계획을 볼 때는 추정 행 수와 실제 행 수를 나란히 봅니다. 둘의 비가 크게 어긋나는 첫 지점이 대개 원인입니다.
심화 2. 반조인과 반대조인을 정리하세요.
**"주문한 적 있는 사용자"**를 찾는 질의를 생각합니다. 조인으로 쓰면 주문이 여러 건인 사용자가 여러 번 나오므로 DISTINCT가 필요한데, 이것이 문제 3의 실수와 닮았습니다.
필요한 것은 짝이 있는지 여부뿐이며, 이것을 반조인(semi-join)이라 합니다.
| 연산 | 뜻 | SQL |
|---|---|---|
| 반조인 | 오른쪽에 짝이 있는 왼쪽 행 | WHERE EXISTS |
| 반대조인 | 오른쪽에 짝이 없는 왼쪽 행 | WHERE NOT EXISTS |
반조인은 조인과 달리 왼쪽 행을 복제하지 않습니다. 짝을 하나 찾는 순간 멈추면 되므로 비용도 작습니다.
반대조인에서 159강 문제 3이 다시 나옵니다. NOT IN으로 반대조인을 쓰면 목록에 NULL이 하나만 있어도 결과가 통째로 비는데, NOT EXISTS는 그런 일이 없습니다. 의미가 같아 보이는 두 문법이 NULL 앞에서 갈라집니다.
| 질의 | 목록에 NULL이 있을 때 |
|---|---|
x NOT IN (목록) |
어떤 에 대해서도 참이 아니라 결과가 빕니다 |
NOT EXISTS (부질의) |
정상적으로 짝 없는 행을 반환합니다 |
그래서 반대조인은 NOT EXISTS로 쓰는 것이 안전합니다.
심화 3. 창 함수가 집계와 무엇이 다른지 정리하세요.
GROUP BY는 여러 행을 한 행으로 접습니다. 그래서 도시별 합계를 구하면 원래의 개별 주문은 사라집니다.
그런데 **"각 주문의 금액이 그 도시 합계의 몇 퍼센트인가"**를 물으면 곤란해집니다. 개별 행과 집계값이 동시에 필요하기 때문입니다.
창 함수는 행을 접지 않고 집계합니다.
| 방식 | 결과 행 수 | 개별 행 |
|---|---|---|
GROUP BY city |
도시 수만큼 | 사라집니다 |
SUM(amount) OVER (PARTITION BY city) |
원래 행 수 그대로 | 남습니다 |
해결책으로 자기 자신과 조인하는 방법도 있습니다. 도시별 합계를 부질의로 구해 원래 표에 다시 잇는 것인데, 문제 3의 팬아웃 위험이 그때 생깁니다. 부질의가 도시마다 정확히 한 행을 내지 않으면 원래 행이 복제됩니다.
창 함수는 그 위험을 구조적으로 없앱니다. 조인을 하지 않으므로 행 수가 변할 수 없습니다.
순위와 누적도 같은 원리입니다. 직전 행과의 차이나 누적 합처럼 행의 순서에 기대는 계산은 집계로는 표현할 수 없고 창 함수가 필요하며, 시계열 특징을 만들 때 대부분 이것을 씁니다.
심화 4. 시점 기준 조인이 왜 필요한지 정리하세요.
모델의 훈련 자료를 만들 때 가장 비싼 실수가 여기서 나옵니다.
이것을 **누출(leakage)**이라 부릅니다.
예를 봅니다. 월 일의 이탈 여부를 예측하는데, 특징으로 **"고객의 총 주문 건수"**를 넣었다고 합니다. 그 값을 오늘 기준으로 계산했다면 월 일 이후의 주문까지 포함되어 있습니다.
| 조인 방식 | 무엇을 붙이는가 |
|---|---|
| 그냥 조인 | 키가 맞는 모든 행을 붙입니다 |
| 시점 기준 조인 | 각 행의 시점 이전 값만 붙입니다 |
검증 성능이 부풀고 실제로는 작동하지 않습니다. 그리고 이 실패는 배포한 뒤에야 드러납니다. 검증 자료에도 같은 누출이 들어 있기 때문입니다.
조건은 조인 조건에 명시적으로 넣습니다.
이것이 부등호 조인이라 심화의 앞부분과 이어집니다. 해시를 쓸 수 없어 정렬 병합으로 가며, 그래서 시점 기준 조인은 느립니다. 느린 데는 이유가 있고, 빠르게 만들려고 등호로 바꾸는 순간 누출이 돌아옵니다.
심화 5. 조인 순서 최적화가 왜 어려운지 정리하세요.
표가 개면 이을 순서의 가짓수는 다음과 같습니다.
| 표의 수 | 가짓수 |
|---|---|
표 개면 백억 가지가 넘습니다. 계획 하나를 고르려고 백억 개를 평가할 수는 없습니다.
그래서 실제 최적화기는 두 가지로 줄입니다.
| 방법 | 내용 |
|---|---|
| 동적 계획법 | 부분집합마다 최선의 계획을 한 번만 구해 재사용합니다 |
| 탐색 공간 제한 | 곱집합이 되는 순서를 아예 빼고 왼쪽 깊은 나무만 봅니다 |
동적 계획법도 표 수에 대해 지수로 늘어납니다. 개의 부분집합을 다루기 때문이며, 그래서 표가 개를 넘으면 대부분의 시스템이 정확한 최적화를 포기하고 발견법으로 갈아탑니다.
여기서 실무의 결론이 나옵니다. 표를 아주 많이 잇는 질의는 최적화기가 최선을 찾아 준다고 기대하면 안 됩니다. 중간 결과를 임시 표로 끊어 두거나 질의를 나누는 것이 오히려 빠릅니다.
심화 6. 이 강의가 어디에 쓰이는지 정리하세요.
| 분야 | 쓰임 | 이어지는 강의 |
|---|---|---|
| 자료 정제 | 표를 이어 결측을 채웁니다 | 161강 |
| 변환 파이프라인 | 낟알을 맞춰 집계합니다 | 162강 |
| 기술통계 | 집단별 요약을 냅니다 | 163강 |
| 특징 공학 | 시점 기준 조인으로 누출을 막습니다 | 심화 4 |
둘째 줄이 이 강의의 실무적 핵심입니다. 파이프라인의 각 단계가 어떤 낟알의 표를 내는지 적어 두지 않으면, 두 단계를 잇는 순간 문제 3의 팬아웃이 조용히 들어옵니다.
낟알을 문서가 아니라 검사로 지킵니다. 158강 문제 5에서 스키마가 실행되는 계약이라고 한 것과 같습니다. 조인 뒤 행 수가 조인 전과 같은지 세는 한 줄이 팬아웃을 잡아내는 가장 싼 방법입니다.
WHERE를 걸면 무엇이 되는지 쓰세요.SUM(DISTINCT amount)가 팬아웃의 대책이 되지 못하는 이유를 쓰세요.COUNT(*)와 COUNT(col)의 차이를 쓰세요.WHERE와 HAVING의 차이를 검산 값과 함께 쓰세요.정답.
NULL인 행이 걸러져 안쪽 조인과 같아집니다.GROUP BY는 있는 행을 묶으므로 행이 없으면 그룹이 만들어지지 않습니다.WHERE는 묶기 전 행을 걸러 합계를 바꾸고 HAVING은 묶은 뒤 그룹을 거르며 부산의 합이 에서 으로 바뀌었습니다.| 기호 | 읽는 법 | 뜻 |
|---|---|---|
| 세타 조인 | 조건 로 두 표를 잇습니다 | |
| 자연 조인 | 같은 이름의 열로 잇습니다 | |
| 곱 | 모든 짝을 만듭니다 | |
| 감마, 집계 | 묶어서 셉니다 | |
| 안쪽 조인 | inner join | 짝 있는 행만 남깁니다 |
| 바깥쪽 조인 | outer join | 짝 없는 행도 남깁니다 |
| 팬아웃 | fan-out | 조인이 행을 복제하는 현상입니다 |
| 낟알 | granularity | 한 행이 무엇 하나를 뜻하는지입니다 |
| 반조인 | semi-join | 짝의 존재만 확인합니다 |
| 반대조인 | anti-join | 짝이 없는 행을 찾습니다 |
| 창 함수 | window function | 행을 접지 않고 집계합니다 |
| 해시 조인 | hash join | 해시표로 짝을 찾습니다 |
| 정렬 병합 조인 | sort-merge join | 정렬한 뒤 훑어 나갑니다 |
| 누출 | leakage | 예측 시점에 없던 정보가 섞입니다 |
| 기수 추정 | cardinality estimation | 결과 행 수를 미리 어림합니다 |
다음은 161강 결측값과 이상치입니다. 이 강의까지가 자료를 꺼내고 잇는 일이었고, 다음은 꺼낸 자료가 성한지 보는 일입니다.
158강 문제 4에서 결측을 어떻게 표현할지 보았고, 159강 문제 3에서 그것이 논리를 어떻게 바꾸는지 보았습니다. 다음은 그것을 어떻게 다룰지입니다. 지우는 것과 채우는 것 중 무엇이 언제 옳은지, 왜 결측을 지우는 순간 표본이 더 이상 무작위가 아닌지가 주제입니다.
그리고 문제 4의 분모 문제가 다시 나옵니다. 결측인 행을 빼고 평균을 내면 분모가 줄어드는데, 그 줄어듦이 무작위가 아니라면 평균 자체가 편향됩니다. 149강의 편향과 분산이 여기서 자료 정제의 문제로 돌아옵니다.
import numpy as np
def pw(s, n):
k = n - sum(2 if ord(c) > 0x2FFF else 1 for c in str(s))
return str(s) + " " * max(k, 0)
def rw(s, n):
k = n - sum(2 if ord(c) > 0x2FFF else 1 for c in str(s))
return " " * max(k, 0) + str(s)
users = np.array([101, 102, 103, 104, 106], dtype=np.int64)
odt = np.dtype([("id", "i8"), ("user", "i8"), ("amount", "i8")])
orders = np.array([(1, 101, 15000), (2, 102, 22000), (3, 101, 8000),
(4, 103, 31000), (5, 102, 5000), (6, 104, 12000),
(7, 101, 27000), (8, 105, 9000)], dtype=odt)
# --- 문제 1: 두 표를 어떻게 잇는가 --------------------------------------
print(" 사용자 %d 명과 주문 %d 건을 user 열로 잇습니다" % (len(users), len(orders)))
print(" 곱집합은 %d x %d = %d 행입니다" % (len(orders), len(users), len(orders) * len(users)))
pairs = [(int(o["id"]), int(u)) for o in orders for u in users if int(o["user"]) == int(u)]
print(" 그중 orders.user = users.id 를 만족하는 행은 %d 개입니다" % len(pairs))
keep = np.isin(orders["user"], users)
ij = orders[keep]
lost = orders[~keep]
print(" 안쪽 조인 결과는 %d 행이고 짝이 없어 빠진 주문은 번호 %s 입니다"
% (len(ij), ", ".join(str(int(z)) for z in lost["id"])))
print(" 조인 선택도는 %d / %d = %.4f 입니다"
% (len(ij), len(orders) * len(users), len(ij) / (len(orders) * len(users))))
print(" 조인은 곱을 만든 뒤 조건으로 거르는 것과 뜻이 같습니다")
print(" 뜻만 같습니다. 실제로 곱을 만들면 안 됩니다. 159강 문제 5 의 밀어내리기가 여기 쓰입니다")
# --- 문제 2: 짝이 없는 행은 어떻게 하는가 -------------------------------
print(" 짝이 없는 행을 어떻게 다루느냐로 조인의 종류가 갈립니다")
no_u = lost
no_o = users[~np.isin(users, orders["user"])]
n_in = len(ij)
print(" %s %s %s" % (pw("조인 종류", 16), rw("결과 행 수", 10), rw("새로 생기는 값없음", 20)))
for nm3, r, z in [("안쪽", n_in, 0),
("왼쪽 바깥쪽", n_in + len(no_u), len(no_u)),
("오른쪽 바깥쪽", n_in + len(no_o), len(no_o)),
("완전 바깥쪽", n_in + len(no_u) + len(no_o), len(no_u) + len(no_o))]:
print(" %s %10d %20d" % (pw(nm3, 16), r, z))
print(" 짝 없는 주문은 번호 %s, 주문 없는 사용자는 %s 입니다"
% (", ".join(str(int(z)) for z in no_u["id"]), ", ".join(str(int(z)) for z in no_o)))
print(" 바깥쪽 조인이 만드는 값없음은 원래 자료에 없던 것입니다")
ra = np.concatenate([ij["amount"].astype(float), np.full(len(no_o), np.nan)])
print(" 오른쪽 바깥쪽 조인 뒤 amount 열은 값 %d 개, 값없음 %d 개입니다"
% (int(np.sum(~np.isnan(ra))), int(np.sum(np.isnan(ra)))))
print(" COUNT(*) 는 %d, COUNT(amount) 는 %d, SUM 은 %d 입니다"
% (len(ra), int(np.sum(~np.isnan(ra))), int(np.nansum(ra))))
print(" AVG 는 분모를 무엇으로 두느냐에 따라 %.2f 또는 %.2f 입니다"
% (float(np.nansum(ra)) / len(ra), float(np.nanmean(ra))))
print(" 159강 문제 3 의 삼값 논리가 여기서 다시 나옵니다. 분모를 확인해야 합니다")
print(" 조건을 ON 에 두느냐 WHERE 에 두느냐로 결과가 달라집니다")
print(" 왼쪽 바깥쪽 조인 %d 행에 WHERE users.id IS NOT NULL 을 붙이면 %d 행이 됩니다"
% (n_in + len(no_u), n_in))
print(" 바깥쪽 조인을 해 놓고 오른쪽 열에 WHERE 를 걸면 안쪽 조인으로 되돌아갑니다")
# --- 문제 3: 일대다 조인이 합계를 부풀린다 ------------------------------
print(" 주문 표와 배송 표를 잇습니다. 한 주문에 배송이 여러 건 있습니다")
oid = np.array([1, 2, 3, 4], dtype=np.int64)
oamt = np.array([10000, 20000, 30000, 20000], dtype=np.int64)
ship = np.array([1, 1, 1, 2, 3, 3, 4], dtype=np.int64)
u3, c3 = np.unique(ship, return_counts=True)
print(" 주문 %d 건, 배송 %d 건, 주문 금액 합 %d 원입니다"
% (len(oid), len(ship), int(oamt.sum())))
print(" %s %s %s %s" % (rw("주문", 6), rw("금액", 9), rw("배송 건수", 12),
rw("조인 뒤 금액이 더해지는 횟수", 30)))
for a, b, c in zip(oid, oamt, c3):
print(" %6d %9d %12d %30d" % (int(a), int(b), int(c), int(c)))
joined = np.repeat(oamt, c3)
print(" 조인 결과는 %d 행이고 금액 합은 %d 원입니다" % (len(joined), int(joined.sum())))
print(" 조인 전 %d 원이 조인 뒤 %d 원이 되어 %.4f 배로 부풀었습니다"
% (int(oamt.sum()), int(joined.sum()), joined.sum() / oamt.sum()))
print(" 같은 금액이 배송 건수만큼 반복되어 더해집니다. 이것이 팬아웃입니다")
print(" 여기서 DISTINCT 로 덮으려 하면 이번에는 반대로 모자랍니다")
dsum = int(np.unique(joined).sum())
print(" SUM(DISTINCT amount) 는 %d 원입니다. 참값 %d 원보다 %d 원 적습니다"
% (dsum, int(oamt.sum()), int(oamt.sum()) - dsum))
print(" 금액이 %d 원인 주문이 %d 건인데 DISTINCT 가 그중 하나만 남기기 때문입니다"
% (20000, int((oamt == 20000).sum())))
print(" 부풀린 것을 지우려다 원래 있던 것까지 지웠습니다. 대책은 먼저 집계하는 것입니다")
print(" %s %s %s" % (rw("주문", 6), rw("금액", 9), rw("미리 센 배송 건수", 18)))
for a, b, c in zip(oid, oamt, c3):
print(" %6d %9d %18d" % (int(a), int(b), int(c)))
print(" 배송 표를 주문별로 먼저 센 뒤 이으면 주문이 %d 행 그대로라 합이 %d 원입니다"
% (len(oid), int(oamt.sum())))
print(" 조인 뒤 합계가 이상하면 먼저 행 수를 봅니다")
print(" 조인 전 %d 행이 조인 뒤 %d 행이면 일대다입니다" % (len(oid), len(joined)))
# --- 문제 4: 집계를 어떻게 하는가 ---------------------------------------
print(" 도시별로 묶어 집계합니다. 금액이 음수인 칸은 값없음을 뜻합니다")
city = np.array(["서울", "부산", "서울", "대구", "부산", "서울", "인천", "부산"], dtype="U4")
amt4 = np.array([15000, 22000, 8000, 31000, -1, 12000, 27000, 9000], dtype=np.int64)
nullm = amt4 < 0
master = ["서울", "부산", "대구", "인천", "광주"]
print(" %s %s %s %s %s" % (pw("도시", 8), rw("COUNT(*)", 8), rw("COUNT(amount)", 14),
rw("SUM", 10), rw("AVG", 11)))
for cty in master:
m = city == cty
v = amt4[m & ~nullm]
if not m.any():
continue
print(" %s %8d %14d %10d %11.2f"
% (pw(cty, 8), int(m.sum()), len(v), int(v.sum()), float(v.mean())))
print(" 부산은 행이 %d 개인데 금액이 있는 행은 %d 개입니다. 분모가 다릅니다"
% (int((city == "부산").sum()), int(((city == "부산") & ~nullm).sum())))
bs = amt4[(city == "부산") & ~nullm]
print(" 부산의 AVG 를 SUM/COUNT(*) 로 하면 %.2f, SUM/COUNT(amount) 로 하면 %.2f 입니다"
% (float(bs.sum()) / 3, float(bs.mean())))
print(" 광주는 행이 하나도 없어 결과에 아예 나오지 않습니다")
print(" 도시 목록은 %d 개인데 집계 결과에 나오는 도시는 %d 개입니다"
% (len(master), len(np.unique(city))))
print(" 없는 그룹의 SUM 은 0 이 아닙니다. 행이 없으면 그룹 자체가 없습니다")
print(" WHERE 는 묶기 전 행을 거르고 HAVING 은 묶은 뒤 그룹을 거릅니다")
print(" %s %s" % (pw("방식", 44), "남는 도시"))
w1 = [c for c in master if ((city == c) & ~nullm & (amt4 >= 10000)).any()]
h1 = [c for c in master if ((city == c) & ~nullm).any() and amt4[(city == c) & ~nullm].sum() >= 30000]
print(" %s %s" % (pw("WHERE amount >= 10000 으로 거른 뒤 묶기", 44), ", ".join(w1)))
print(" %s %s" % (pw("묶은 뒤 HAVING SUM(amount) >= 30000", 44), ", ".join(h1)))
print(" WHERE 로 거르면 부산의 합이 %d 에서 %d 으로 바뀝니다"
% (int(bs.sum()), int(amt4[(city == "부산") & ~nullm & (amt4 >= 10000)].sum())))
print(" 둘은 순서가 달라 답도 다릅니다. 무엇을 거를지 먼저 정해야 합니다")
# --- 문제 5: 조인을 어떻게 실행하는가 -----------------------------------
print(" 조인 알고리즘 넷의 연산 횟수를 견줍니다. |A| = 100 만, |B| = 10 만 입니다")
nA, nB = 1000000, 100000
algs = [("중첩 루프", nA * nB),
("색인 중첩 루프", int(nA * np.log2(nB))),
("정렬 병합", int(nA * np.log2(nA) + nB * np.log2(nB) + nA + nB)),
("해시", nA + nB)]
base = algs[0][1]
print(" %s %s %s %s" % (pw("알고리즘", 16), rw("연산 횟수", 15), rw("중첩 루프 대비", 18),
rw("중첩 루프가 느린 배수", 22)))
for nm5, v in algs:
print(" %s %15d %18.6f %22.1f" % (pw(nm5, 16), v, v / base, base / v))
print(" 등호로 잇는 조인이면 해시가 기본입니다. 양쪽을 한 번씩만 훑으면 끝납니다")
print(" 부등호로 이으면 해시를 쓸 수 없어 정렬 병합이나 중첩 루프로 갑니다")
print(" 정렬 병합 %d 회는 색인 중첩 루프 %d 회보다 많습니다. 이미 정렬되어 있으면 뒤집힙니다"
% (algs[2][1], algs[1][1]))
print(" 조인 순서도 중요합니다. 주문 A, 사용자 B, 등급 C 를 잇는 두 계획입니다")
sA, sB, sC = 1000000, 1000, 10
print(" 주문 %d 행은 사용자 하나에, 사용자 %d 행은 등급 하나에 붙습니다" % (sA, sB))
print(" %s %s %s" % (pw("계획", 20), rw("중간 결과 행 수", 18), rw("최종 행 수", 16)))
print(" %s %18d %16d" % (pw("(A 조인 B) 조인 C", 20), sA, sA))
print(" %s %18d %16d" % (pw("A 조인 (B 조인 C)", 20), sB, sA))
print(" 최종 행 수는 %d 행으로 같은데 중간 결과가 %d 배 차이 납니다" % (sA, sA // sB))
print(" 같은 답을 주는 두 계획의 비용이 %d 배 다릅니다. 실행 계획을 읽어야 합니다" % (sA // sB))
print(" 159강 문제 5 의 선택 밀어내리기가 조인에서 가장 크게 작동합니다")
# 사용자 5 명과 주문 8 건을 user 열로 잇습니다
# 곱집합은 8 x 5 = 40 행입니다
# 그중 orders.user = users.id 를 만족하는 행은 7 개입니다
# 안쪽 조인 결과는 7 행이고 짝이 없어 빠진 주문은 번호 8 입니다
# 조인 선택도는 7 / 40 = 0.1750 입니다
# 조인은 곱을 만든 뒤 조건으로 거르는 것과 뜻이 같습니다
# 뜻만 같습니다. 실제로 곱을 만들면 안 됩니다. 159강 문제 5 의 밀어내리기가 여기 쓰입니다
# 짝이 없는 행을 어떻게 다루느냐로 조인의 종류가 갈립니다
# 조인 종류 결과 행 수 새로 생기는 값없음
# 안쪽 7 0
# 왼쪽 바깥쪽 8 1
# 오른쪽 바깥쪽 8 1
# 완전 바깥쪽 9 2
# 짝 없는 주문은 번호 8, 주문 없는 사용자는 106 입니다
# 바깥쪽 조인이 만드는 값없음은 원래 자료에 없던 것입니다
# 오른쪽 바깥쪽 조인 뒤 amount 열은 값 7 개, 값없음 1 개입니다
# COUNT(*) 는 8, COUNT(amount) 는 7, SUM 은 120000 입니다
# AVG 는 분모를 무엇으로 두느냐에 따라 15000.00 또는 17142.86 입니다
# 159강 문제 3 의 삼값 논리가 여기서 다시 나옵니다. 분모를 확인해야 합니다
# 조건을 ON 에 두느냐 WHERE 에 두느냐로 결과가 달라집니다
# 왼쪽 바깥쪽 조인 8 행에 WHERE users.id IS NOT NULL 을 붙이면 7 행이 됩니다
# 바깥쪽 조인을 해 놓고 오른쪽 열에 WHERE 를 걸면 안쪽 조인으로 되돌아갑니다
# 주문 표와 배송 표를 잇습니다. 한 주문에 배송이 여러 건 있습니다
# 주문 4 건, 배송 7 건, 주문 금액 합 80000 원입니다
# 주문 금액 배송 건수 조인 뒤 금액이 더해지는 횟수
# 1 10000 3 3
# 2 20000 1 1
# 3 30000 2 2
# 4 20000 1 1
# 조인 결과는 7 행이고 금액 합은 130000 원입니다
# 조인 전 80000 원이 조인 뒤 130000 원이 되어 1.6250 배로 부풀었습니다
# 같은 금액이 배송 건수만큼 반복되어 더해집니다. 이것이 팬아웃입니다
# 여기서 DISTINCT 로 덮으려 하면 이번에는 반대로 모자랍니다
# SUM(DISTINCT amount) 는 60000 원입니다. 참값 80000 원보다 20000 원 적습니다
# 금액이 20000 원인 주문이 2 건인데 DISTINCT 가 그중 하나만 남기기 때문입니다
# 부풀린 것을 지우려다 원래 있던 것까지 지웠습니다. 대책은 먼저 집계하는 것입니다
# 주문 금액 미리 센 배송 건수
# 1 10000 3
# 2 20000 1
# 3 30000 2
# 4 20000 1
# 배송 표를 주문별로 먼저 센 뒤 이으면 주문이 4 행 그대로라 합이 80000 원입니다
# 조인 뒤 합계가 이상하면 먼저 행 수를 봅니다
# 조인 전 4 행이 조인 뒤 7 행이면 일대다입니다
# 도시별로 묶어 집계합니다. 금액이 음수인 칸은 값없음을 뜻합니다
# 도시 COUNT(*) COUNT(amount) SUM AVG
# 서울 3 3 35000 11666.67
# 부산 3 2 31000 15500.00
# 대구 1 1 31000 31000.00
# 인천 1 1 27000 27000.00
# 부산은 행이 3 개인데 금액이 있는 행은 2 개입니다. 분모가 다릅니다
# 부산의 AVG 를 SUM/COUNT(*) 로 하면 10333.33, SUM/COUNT(amount) 로 하면 15500.00 입니다
# 광주는 행이 하나도 없어 결과에 아예 나오지 않습니다
# 도시 목록은 5 개인데 집계 결과에 나오는 도시는 4 개입니다
# 없는 그룹의 SUM 은 0 이 아닙니다. 행이 없으면 그룹 자체가 없습니다
# WHERE 는 묶기 전 행을 거르고 HAVING 은 묶은 뒤 그룹을 거릅니다
# 방식 남는 도시
# WHERE amount >= 10000 으로 거른 뒤 묶기 서울, 부산, 대구, 인천
# 묶은 뒤 HAVING SUM(amount) >= 30000 서울, 부산, 대구
# WHERE 로 거르면 부산의 합이 31000 에서 22000 으로 바뀝니다
# 둘은 순서가 달라 답도 다릅니다. 무엇을 거를지 먼저 정해야 합니다
# 조인 알고리즘 넷의 연산 횟수를 견줍니다. |A| = 100 만, |B| = 10 만 입니다
# 알고리즘 연산 횟수 중첩 루프 대비 중첩 루프가 느린 배수
# 중첩 루프 100000000000 1.000000 1.0
# 색인 중첩 루프 16609640 0.000166 6020.6
# 정렬 병합 22692532 0.000227 4406.7
# 해시 1100000 0.000011 90909.1
# 등호로 잇는 조인이면 해시가 기본입니다. 양쪽을 한 번씩만 훑으면 끝납니다
# 부등호로 이으면 해시를 쓸 수 없어 정렬 병합이나 중첩 루프로 갑니다
# 정렬 병합 22692532 회는 색인 중첩 루프 16609640 회보다 많습니다. 이미 정렬되어 있으면 뒤집힙니다
# 조인 순서도 중요합니다. 주문 A, 사용자 B, 등급 C 를 잇는 두 계획입니다
# 주문 1000000 행은 사용자 하나에, 사용자 1000 행은 등급 하나에 붙습니다
# 계획 중간 결과 행 수 최종 행 수
# (A 조인 B) 조인 C 1000000 1000000
# A 조인 (B 조인 C) 1000 1000000
# 최종 행 수는 1000000 행으로 같은데 중간 결과가 1000 배 차이 납니다
# 같은 답을 주는 두 계획의 비용이 1000 배 다릅니다. 실행 계획을 읽어야 합니다
# 159강 문제 5 의 선택 밀어내리기가 조인에서 가장 크게 작동합니다