158강이 자료를 표에 담았습니다. 이제 그 표에서 원하는 것을 꺼냅니다.
선택, 사영, 조인 세 연산이 질의의 전부입니다. 나머지는 이 셋의 조합이거나 집계이며, 조인은 160강에서 따로 다룹니다.
그리고 그 논리가 앞에서 이미 배운 것입니다.
| 질의의 것 | 어디서 배웠는가 |
|---|---|
조건을 AND, OR, NOT으로 잇기 |
22강 명제와 논리 연산 |
| 드모르간 법칙 | 22강 |
| 합집합, 교집합, 차집합 | 26강 집합의 연산 |
| 포함배제 | 122강 확률의 공리와 셈 |
| 조건을 술어로 적기 | 22강 술어 논리 |
새로 배울 것은 하나입니다. 158강 문제 4에서 본 결측이 질의에서는 참도 거짓도 아닌 세 번째 값이 되며, 그것을 모르면 조건이 조용히 행을 빠뜨립니다.
이 강의의 절반이 그 세 번째 값을 다룹니다. 실무에서 가장 많은 질의 오류가 여기서 나옵니다.
NULL이 만드는 함정을 판정할 수 있습니다.문제. 주문 표 행에서 기본 연산을 해 봅니다.
(1) 선택과 사영을 각각 적용하세요.
(2) 둘을 함께 적용하면 어떻게 되는지 보세요.
(3) 집합 연산을 적용하세요.
생각의 실마리. 표는 행과 열의 격자입니다. 꺼내는 방법도 두 방향뿐이며, 행을 고르는 것과 열을 고르는 것입니다.
풀이. (1)(2) 검산 결과입니다.
| 연산 | 결과 |
|---|---|
선택 amount >= 10000 |
행이 남습니다 |
사영 city, 중복 제거 |
개 (대구, 부산, 서울, 인천) |
선택 amount >= 20000 뒤 사영 |
개 (대구, 부산, 인천) |
중복 제거 전 city 열 |
개 |
선택을 강하게 걸면 사영 결과도 줄어듭니다. 만 이상만 남기면 서울이 사라집니다.
(3) 집합 연산입니다.
| 대상 | 결과 |
|---|---|
| 만 이상 주문한 사용자 | |
| 서울에서 주문한 사용자 | |
이 문제에서 배우는 것: 세 가지 기본 연산.
선택 . 조건 를 만족하는 행만 남깁니다.
사영 . 지정한 열만 남깁니다.
집합 연산. 두 표의 행을 합집합, 교집합, 차집합으로 묶습니다. 두 표의 열 구조가 같아야 합니다.
| 연산 | 바꾸는 것 | SQL |
|---|---|---|
| 선택 | 행의 개수 | WHERE |
| 사영 | 열의 개수 | SELECT |
| 이름바꾸기 | 열의 이름 | AS |
| 합집합 | 행을 더합니다 | UNION |
| 차집합 | 행을 뺍니다 | EXCEPT |
| 곱 | 모든 짝 | CROSS JOIN |
26강의 집합 연산이 그대로 쓰입니다. 다만 표는 집합이 아니라 다중집합이라 중복이 남으며, 그 차이가 문제 4의 주제입니다.
사영은 중복을 만듭니다. 열을 버리면 서로 달랐던 행이 같아질 수 있습니다.
검산에서 행이 개로 줄었습니다. 원래 행은 모두 달랐는데 city만 남기니 중복이 생겼습니다.
바로 확인 1.
확인 1-1. 선택과 사영이 각각 무엇을 바꾸는지 쓰세요.
답. 선택은 행의 개수를, 사영은 열의 개수를 바꿉니다.
확인 1-2. 사영이 중복을 만드는 이유를 쓰세요.
답. 열을 버리면 서로 달랐던 행이 같아지기 때문입니다.
확인 1-3. 집합 연산에 필요한 조건을 쓰세요.
답. 두 표의 열 구조가 같아야 합니다.
문제. 두 조건을 논리 연산으로 잇습니다.
(1) 논리곱, 논리합, 부정의 행 수를 세세요.
(2) 드모르간 법칙을 확인하세요.
(3) 조건의 순서를 바꿔 보세요.
생각의 실마리. 조건은 각 행에 대해 참이나 거짓을 주는 함수입니다. 22강의 명제 연산이 행마다 적용되는 것뿐입니다.
풀이. (1) 조건 는 만 이상, 는 서울입니다.
| 조건 | 참인 행 수 |
|---|---|
포함배제가 맞습니다. 이며, 122강의 셈이 그대로입니다.
(2) 드모르간을 확인합니다.
| 등식 | 모든 행에서 같은가 |
|---|---|
| 참 | |
| 참 |
(3) 순서를 바꿉니다.
| 순서 | 결과 |
|---|---|
| 금액 먼저 거른 뒤 도시 | , |
| 도시 먼저 거른 뒤 금액 | , |
| 두 결과가 같은가 | 참 |
결과는 같은데 중간 크기가 다릅니다. 금액 먼저면 중간이 행이고 도시 먼저면 행입니다.
이 문제에서 배우는 것: 선택은 교환되고 그것이 최적화의 여지입니다.
선택의 교환법칙.
어느 쪽을 먼저 해도 답이 같습니다. 그러면 중간 결과가 작아지는 쪽을 먼저 하는 것이 이득이며, 문제 5의 밀어내리기가 이 원리입니다.
| 법칙 | 식 |
|---|---|
| 드모르간 | |
| 분배 | |
| 흡수 | |
| 선택의 교환 | \sigma_{p}\sigma_{q}=\sigma_{q}\sigma_ |
| 선택의 결합 | \sigma_{p}\sigma_{q}=\sigma_ |
드모르간이 실무에서 자주 쓰입니다. "서울이 아니고 부산도 아닌" 조건을 NOT (city='서울' OR city='부산')으로 쓸지 city<>'서울' AND city<>'부산'으로 쓸지가 같은 뜻이며, 가독성이나 색인 사용에 따라 고릅니다.
그런데 값이 없으면 드모르간이 깨질 수 있습니다. 문제 3에서 확인합니다.
바로 확인 2.
확인 2-1. 드모르간 법칙 두 개를 쓰세요.
답. 이고 입니다.
확인 2-2. 선택의 교환법칙을 쓰세요.
답. 입니다.
확인 2-3. 순서를 바꿀 때 무엇이 달라지는지 쓰세요.
답. 결과는 같고 중간 결과의 크기가 달라집니다.
NULL이 논리를 어떻게 바꾸는가문제. 값이 없는 칸을 넣고 논리를 다시 봅니다.
(1) 삼값 논리의 진리표를 만드세요.
(2) 조건과 그 부정을 합치면 전체가 되는지 판정하세요.
(3)NOT IN의 동작을 확인하세요.
생각의 실마리. 금액을 모르는 주문에 대해 "만 이상인가"를 물으면 답할 수 없습니다. 참도 거짓도 아닌 세 번째 답이 필요합니다.
풀이. (1) 순서를 거짓 모름 참으로 두면 AND는 최솟값, OR는 최댓값입니다.
| AND | OR | ||
|---|---|---|---|
| 거짓 | 거짓 | 거짓 | 거짓 |
| 거짓 | 모름 | 거짓 | 모름 |
| 거짓 | 참 | 거짓 | 참 |
| 모름 | 거짓 | 거짓 | 모름 |
| 모름 | 모름 | 모름 | 모름 |
| 모름 | 참 | 모름 | 참 |
| 참 | 거짓 | 거짓 | 참 |
| 참 | 모름 | 모름 | 참 |
| 참 | 참 | 참 | 참 |
NOT은 참과 거짓을 뒤집고 모름은 그대로 둡니다.
둘째 줄과 일곱째 줄이 중요합니다. 거짓 AND 모름이 거짓이고 참 OR 모름이 참입니다. 하나만으로 답이 정해지면 나머지를 몰라도 됩니다.
(2) 금액 칸 중 칸이 값 없음입니다.
| 항목 | 값 |
|---|---|
조건 amount >= 10000이 참 |
행 |
| 거짓 | 행 |
| 모름 | 행 |
| 조건이 참인 행 부정이 참인 행 | |
| 전체 |
합쳐도 전체가 되지 않습니다. WHERE는 참인 행만 통과시키므로 모름인 행이 양쪽에서 모두 빠집니다.
이것이 가장 흔한 질의 오류입니다. 조건으로 나눠 집계한 두 값을 더하면 전체와 맞지 않는데, 원인을 찾기 어렵습니다.
(3) NOT IN을 봅니다. 목록이 입니다.
| 결과 | |
|---|---|
| 거짓 | |
| 모름 | |
| 모름 |
어떤 에 대해서도 참이 되지 않습니다. 이고 까지는 참인데, 이 모름이라 AND의 최솟값이 모름이 됩니다.
이 문제에서 배우는 것: 삼값 논리.
NULL은 값이 아니라 값이 없다는 표시입니다. 그래서NULL = NULL도 참이 아니라 모름입니다.
| 표현 | 결과 |
|---|---|
x = NULL |
모름 |
NULL = NULL |
모름 |
x IS NULL |
참 또는 거짓 |
NULL AND FALSE |
거짓 |
NULL OR TRUE |
참 |
COUNT(col) |
NULL을 세지 않습니다 |
COUNT(*) |
모든 행을 셉니다 |
셋째 줄만이 판정할 수 있는 방법입니다. 등호로는 영원히 찾을 수 없습니다.
여섯째와 일곱째 줄이 집계에서 갈립니다. AVG도 NULL을 빼고 계산하므로, 분모가 몇인지 확인하지 않으면 다른 열과 비교할 수 없습니다.
실무의 규칙 셋입니다.
| 규칙 | 이유 |
|---|---|
NULL 판정은 IS NULL로 |
등호가 통하지 않습니다 |
NOT IN에 부질의를 쓸 때 NULL 확인 |
결과가 통째로 빕니다 |
| 조건으로 나눴으면 합이 전체인지 검사 | 빠진 행이 있는지 드러납니다 |
둘째 줄이 조용히 틀립니다. WHERE id NOT IN (SELECT parent_id FROM t)에서 parent_id에 NULL이 하나만 있어도 결과가 빈 표가 되며, 오류 없이 그렇게 됩니다.
158강 문제 4의 결측이 여기서 논리의 문제가 됩니다. 저장에서는 표현의 문제였는데 질의에서는 참과 거짓 사이의 제삼의 값이 됩니다.
바로 확인 3.
확인 3-1. 삼값 논리에서 AND와 OR를 순서로 설명하세요.
답. 거짓 모름 참일 때 AND는 최솟값이고 OR는 최댓값입니다.
확인 3-2. 조건과 그 부정을 합쳐도 전체가 안 되는 이유를 쓰세요.
답. WHERE가 참인 행만 통과시켜 모름인 행이 양쪽에서 빠지기 때문입니다.
확인 3-3. NOT IN 목록에 NULL이 있으면 어떻게 되는지 쓰세요.
답. 어떤 값에 대해서도 참이 되지 않아 결과가 빕니다.
문제. 표가 집합인지 다중집합인지 확인합니다.
(1) 사영 결과의 중복을 세세요.
(2)UNION과UNION ALL을 견주세요.
(3) 중복 제거를 언제 할지 판정하세요.
생각의 실마리. 수학의 집합은 중복을 허용하지 않습니다. 그런데 표는 같은 행이 두 번 있어도 됩니다.
풀이. (1) 도시별 행 수입니다.
| 도시 | 행 수 |
|---|---|
| 대구 | |
| 부산 | |
| 서울 | |
| 인천 |
사영만 하면 행이고 중복을 지우면 행입니다.
(2) 다른 표의 도시 목록(서울, 부산, 광주)과 합칩니다.
| 연산 | 행 수 |
|---|---|
UNION ALL |
|
UNION |
차이 행이 양쪽에 다 있던 서울과 부산입니다.
(3) 개수를 세는 방식이 갈립니다.
| 세는 방법 | 결과 |
|---|---|
| 중복 포함 | |
| 중복 제거 |
이 문제에서 배우는 것: 다중집합 의미론.
관계형 표는 다중집합입니다. 같은 행이 여러 번 있을 수 있고, 연산이 중복을 보존합니다.
| 연산 | 중복 |
|---|---|
SELECT |
보존합니다 |
SELECT DISTINCT |
제거합니다 |
UNION |
제거합니다 |
UNION ALL |
보존합니다 |
COUNT(*) |
중복 포함 |
COUNT(DISTINCT c) |
중복 제거 |
넷째 줄이 기본값과 반대라 헷갈립니다. SELECT는 중복을 남기는데 UNION은 지웁니다.
중복 제거는 공짜가 아닙니다. 정렬이나 해시가 필요하므로 큰 표에서 비쌉니다.
| 상황 | 판단 |
|---|---|
| 결과에 중복이 없음이 보장됨 | DISTINCT 불필요 |
| 개수를 세는 목적 | 무엇을 셀지 먼저 정합니다 |
| 여러 표를 이어 붙임 | 중복이 뜻을 갖는지 봅니다 |
| 조인 뒤 행이 늘어남 | 160강의 팽창 문제 |
둘째 줄이 자주 틀립니다. "주문 수"인지 "주문한 사용자 수"인지에 따라 COUNT(*)와 COUNT(DISTINCT user)가 갈리며, 질문을 정확히 하지 않으면 어느 쪽이 맞는지 알 수 없습니다.
넷째 줄이 다음 강의의 주제입니다. 조인이 행을 늘리면 합계가 부풀며, 그때 DISTINCT로 덮으면 다른 곳이 틀립니다.
바로 확인 4.
확인 4-1. 관계형 표가 집합인지 쓰세요.
답. 집합이 아니라 다중집합입니다.
확인 4-2. UNION과 UNION ALL의 차이를 쓰세요.
답. UNION은 중복을 제거하고 UNION ALL은 보존합니다.
확인 4-3. 중복 제거가 공짜가 아닌 이유를 쓰세요.
답. 정렬이나 해시가 필요하기 때문입니다.
문제. 질의의 실행을 조사합니다.
(1) 논리적 실행 순서를 쓰세요.
(2)WHERE와HAVING의 차이를 쓰세요.
(3) 선택 밀어내리기의 이득을 계산하세요.
생각의 실마리. 적는 순서와 실행 순서가 다릅니다. SELECT를 맨 앞에 적지만 거의 마지막에 실행됩니다.
풀이. (1) 논리적 실행 순서입니다.
| 순서 | 절 |
|---|---|
FROM |
|
WHERE |
|
GROUP BY |
|
HAVING |
|
SELECT |
|
ORDER BY |
|
LIMIT |
SELECT가 다섯 번째라 WHERE에서는 SELECT의 별명을 쓸 수 없습니다. 아직 만들어지지 않았기 때문입니다.
(2) WHERE는 묶기 전 행을, HAVING은 묶은 뒤 그룹을 거릅니다. 그래서 HAVING에는 집계 함수를 쓸 수 있고 WHERE에는 쓸 수 없습니다.
(3) 표 가 만 행, 가 행이고 의 선택도가 입니다.
| 방식 | 중간 결과 행 수 | 비 |
|---|---|---|
| 곱한 뒤 거르기 | ||
| 거른 뒤 곱하기 |
같은 답을 주는 두 계획의 중간 결과가 배 다릅니다.
이 문제에서 배우는 것: 선언형 질의와 최적화.
선택 밀어내리기. 선택을 곱이나 조인보다 아래로 밀어 중간 결과를 줄입니다.
| 최적화 | 내용 |
|---|---|
| 선택 밀어내리기 | 거르기를 먼저 합니다 |
| 사영 밀어내리기 | 필요 없는 열을 일찍 버립니다 |
| 조인 순서 변경 | 작은 결과부터 잇습니다 |
| 색인 사용 | 전체를 훑지 않습니다 |
| 조건 단순화 | 항상 참인 조건을 지웁니다 |
셋째 줄이 조인이 셋 이상일 때 결정적입니다. 순서에 따라 중간 결과가 수천 배 달라지며, 160강에서 다시 다룹니다.
질의는 선언형입니다. 무엇을 원하는지만 적고 어떻게 할지는 최적화기가 정합니다.
22강의 술어 논리로 조건을 적는 것이 그 뜻입니다. 절차를 적지 않으므로 최적화기가 자유롭게 계획을 바꿀 수 있습니다.
그래서 실행 계획을 읽을 줄 알아야 합니다. 같은 질의가 자료 크기나 통계에 따라 다른 계획으로 실행되며, 느릴 때 원인은 대개 계획에 있습니다.
바로 확인 5.
확인 5-1. 논리적 실행 순서를 쓰세요.
답. FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT 순입니다.
확인 5-2. WHERE와 HAVING의 차이를 쓰세요.
답. WHERE는 묶기 전 행을, HAVING은 묶은 뒤 그룹을 거릅니다.
확인 5-3. 선택 밀어내리기의 이득을 검산 수치로 쓰세요.
답. 선택도 에서 중간 결과가 배 줄었습니다.
| 연산 | 기호 | SQL |
|---|---|---|
| 선택 | \sigma_ | WHERE |
| 사영 | \pi_ | SELECT |
| 이름바꾸기 | AS |
|
| 곱 | CROSS JOIN |
|
| 조인 | JOIN |
|
| 합집합 | UNION |
|
| 차집합 | EXCEPT |
| 삼값 논리 | 규칙 |
|---|---|
| 순서 | 거짓 모름 참 |
AND |
최솟값 |
OR |
최댓값 |
NOT |
참과 거짓을 뒤집고 모름은 그대로 |
NULL 판정 |
IS NULL만 가능 |
| 중복 | 동작 |
|---|---|
SELECT |
보존 |
SELECT DISTINCT |
제거 |
UNION |
제거 |
UNION ALL |
보존 |
| 실행 순서 | 절 |
|---|---|
| ~ | FROM, WHERE, GROUP BY |
| ~ | HAVING, SELECT |
| ~ | ORDER BY, LIMIT |
| 자주 하는 실수 | 바로잡기 |
|---|---|
x = NULL로 결측을 찾습니다 |
언제나 모름이라 IS NULL을 씁니다 |
| 조건과 부정을 합쳐 전체라 봅니다 | 모름인 행이 양쪽에서 빠집니다 |
NOT IN 목록에 NULL을 둡니다 |
결과가 통째로 빕니다 |
SELECT 별명을 WHERE에서 씁니다 |
아직 만들어지지 않았습니다 |
WHERE에 집계 함수를 씁니다 |
HAVING에 씁니다 |
습관적으로 DISTINCT를 붙입니다 |
비용만 늘고 원인을 숨깁니다 |
COUNT(*)와 COUNT(col)을 혼용합니다 |
후자는 NULL을 세지 않습니다 |
문제 6. 선택과 사영이 각각 무엇을 바꾸는지 쓰세요.
답. 선택은 행의 개수를, 사영은 열의 개수를 바꿉니다.
문제 7. 사영이 중복을 만드는 이유를 쓰세요.
답. 열을 버리면 서로 달랐던 행이 같아지기 때문입니다.
문제 8. 드모르간 법칙 두 개를 쓰세요.
답. 이고 입니다.
문제 9. 선택의 교환법칙과 그것이 여는 최적화를 쓰세요.
답. 순서를 바꿔도 결과가 같으므로 중간 결과가 작아지는 쪽을 먼저 합니다.
문제 10. 삼값 논리에서
AND와OR를 순서로 설명하세요.
답. 거짓 모름 참일 때 AND는 최솟값이고 OR는 최댓값입니다.
문제 11.
NULL = NULL의 결과를 쓰세요.
답. 참이 아니라 모름입니다.
문제 12. 조건과 그 부정을 합쳐도 전체가 안 되는 이유를 쓰세요.
답. WHERE가 참인 행만 통과시켜 모름인 행이 양쪽에서 빠지기 때문입니다.
문제 13. 검산에서 참, 거짓, 모름의 행 수를 쓰세요.
답. , , 이며 합이 입니다.
문제 14.
NOT IN목록에NULL이 있으면 어떻게 되는지 쓰세요.
답. 어떤 값에 대해서도 참이 되지 않아 결과가 빕니다.
문제 15.
UNION과UNION ALL의 차이를 쓰세요.
답. UNION은 중복을 제거하고 UNION ALL은 보존합니다.
문제 16.
COUNT(*)와COUNT(col)의 차이를 쓰세요.
답. 앞은 모든 행을 세고 뒤는 NULL을 세지 않습니다.
문제 17. 논리적 실행 순서와
WHERE,HAVING의 차이를 쓰세요.
답. FROM부터 LIMIT까지 일곱 단계이며 WHERE는 행을, HAVING은 그룹을 거릅니다.
문제 18. 선택 밀어내리기의 식과 검산의 이득을 쓰세요.
답. 이며 중간 결과가 배 줄었습니다.
심화 1. 관계대수와 관계해석의 관계를 정리하세요.
질의를 적는 방법이 둘입니다.
| 방식 | 성격 | 예 |
|---|---|---|
| 관계대수 | 연산을 이어 붙입니다 | |
| 관계해석 | 조건을 논리식으로 적습니다 | \ |
코드의 정리. 두 표현력이 같습니다. 관계대수로 쓸 수 있는 질의는 관계해석으로도 쓸 수 있고 그 반대도 성립합니다.
SQL은 관계해석의 모습을 하고 관계대수로 실행됩니다. WHERE에 조건을 적는 것은 해석의 방식이고, 최적화기가 그것을 대수 연산의 나무로 바꿉니다.
표현할 수 없는 것도 있습니다. 관계대수는 재귀를 담지 못하므로 조직도의 모든 하위 부서 같은 질의를 쓸 수 없으며, 재귀 공통 표현식이 그 확장입니다.
심화 2. 집계와 묶기를 정리하세요.
| 함수 | NULL 처리 |
빈 그룹 |
|---|---|---|
COUNT(*) |
셉니다 | |
COUNT(col) |
세지 않습니다 | |
SUM |
무시합니다 | NULL |
AVG |
무시합니다 | NULL |
MAX, MIN |
무시합니다 | NULL |
셋째 줄이 뜻밖입니다. 빈 집합의 합은 수학적으로 인데 SQL은 NULL을 줍니다. "더할 것이 없다"와 "합이 이다"를 구별하려는 선택입니다.
AVG가 NULL을 빼고 계산하므로 분모가 달라집니다. 두 열의 평균을 비교할 때 결측 개수가 다르면 같은 모집단을 보고 있지 않습니다.
묶기는 사영과 다릅니다. GROUP BY 뒤에는 묶은 열과 집계 값만 쓸 수 있으며, 묶지 않은 열을 쓰면 여러 값 중 무엇을 낼지 정해지지 않습니다.
심화 3. 부질의와 상관 부질의를 정리하세요.
| 종류 | 성격 | 비용 |
|---|---|---|
| 비상관 부질의 | 한 번만 계산합니다 | 낮습니다 |
| 상관 부질의 | 바깥 행마다 계산합니다 | 높습니다 |
EXISTS |
하나 찾으면 멈춥니다 | 대개 낫습니다 |
IN |
목록을 만듭니다 | NULL 주의 |
| 파생 표 | FROM에 놓습니다 |
한 번 계산 |
셋째와 넷째 줄이 NULL에서 갈립니다. NOT EXISTS는 문제 3의 함정이 없는데 NOT IN은 있습니다. 바꿔 쓸 수 있으면 NOT EXISTS가 안전합니다.
둘째 줄이 성능 문제의 단골입니다. 바깥 행이 만이면 부질의를 만 번 실행하며, 최적화기가 조인으로 바꾸지 못하면 그대로 느립니다.
심화 4. 창 함수를 정리하세요.
집계는 행을 줄이는데 창 함수는 행을 유지하면서 집계를 붙입니다.
| 용도 | 예 |
|---|---|
| 순위 | ROW_NUMBER, RANK, DENSE_RANK |
| 누적 | SUM OVER, 이동 평균 |
| 이웃 참조 | LAG, LEAD |
| 그룹 내 비율 | 값 나누기 그룹 합 |
| 첫 값, 마지막 값 | FIRST_VALUE, LAST_VALUE |
세 번째 줄이 시계열에서 필수입니다. 전날 대비 변화율을 구하려면 이전 행을 봐야 하는데, 집계로는 할 수 없고 자기 조인은 비쌉니다.
실행 순서에서 창 함수는 SELECT 단계입니다. 그래서 WHERE에서 쓸 수 없고, 순위로 거르려면 한 겹 감싸야 합니다.
심화 5. 질의에서 흔히 저지르는 실수를 정리하세요.
| 실수 | 결과 |
|---|---|
NULL을 등호로 비교합니다 |
언제나 빈 결과 |
NOT IN에 NULL이 섞입니다 |
결과가 통째로 빕니다 |
| 조건으로 나눈 합이 전체가 아님을 못 봅니다 | 수치가 조용히 어긋납니다 |
WHERE와 HAVING을 바꿔 씁니다 |
오류이거나 다른 답 |
묶지 않은 열을 SELECT에 씁니다 |
값이 정해지지 않습니다 |
LIMIT을 ORDER BY 없이 씁니다 |
매번 다른 행이 나옵니다 |
| 문자열 비교에 대소문자와 공백을 무시합니다 | 그룹이 갈라집니다 |
| 부동소수점을 등호로 비교합니다 | 158강 문제 2 |
여섯째 줄이 재현성을 깨뜨립니다. 정렬 없이 상위 개를 뽑으면 실행할 때마다 다를 수 있으며, 계획이 바뀌면 결과도 바뀝니다.
일곱째 줄이 158강 심화 3과 이어집니다. 앞뒤 공백이나 유니코드 정규화 차이로 같은 값이 다른 그룹이 되며, 집계가 조용히 갈라집니다.
심화 6. 데이터 분석과 기계학습에서 질의가 쓰이는 자리를 정리하세요.
| 자리 | 무엇을 하는가 | 관련 강의 |
|---|---|---|
| 특징 만들기 | 집계와 창 함수로 파생 열 | 216강 |
| 훈련 자료 추출 | 시점 기준 필터링 | 212강 |
| 누출 방지 | 미래 정보 배제 | 212강 |
| 표본 추출 | 층화와 가중치 | 175강 |
| 자료 검증 | 제약 위반 탐지 | 162강 |
| 실험 분석 | 집단별 집계 | 178강 |
| 특징 저장소 | 시점 정확 조인 | 160강 |
셋째 줄이 가장 비싼 실수를 막습니다. 훈련 자료를 만들 때 예측 시점 이후의 정보가 섞이면 검증 성능이 부풀고 실제로는 작동하지 않습니다. 시점 조건을 질의에 명시적으로 넣어야 하며, 160강의 시점 기준 조인이 그 도구입니다.
첫째 줄이 실무 시간의 대부분을 씁니다. 모델보다 특징이 성능을 좌우하는 경우가 많고, 그 특징의 대부분이 집계와 창 함수로 만들어집니다.
AND와 OR를 순서로 설명하세요.NOT IN 목록에 NULL이 있으면 어떻게 되는지 쓰세요.UNION과 UNION ALL의 차이를 쓰세요.정답.
AND는 최솟값이고 OR는 최댓값입니다.WHERE가 참인 행만 통과시켜 모름인 행이 양쪽에서 빠지기 때문입니다.UNION은 중복을 제거하고 UNION ALL은 보존합니다.FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT 순입니다.| 기호 | 읽는 법 | 뜻 |
|---|---|---|
| \sigma_ | 시그마, 선택 | 조건을 만족하는 행만 남깁니다 |
| \pi_ | 파이, 사영 | 지정한 열만 남깁니다 |
| 로, 이름바꾸기 | 열이나 표의 이름을 바꿉니다 | |
| 조인 | 두 표를 조건으로 잇습니다 | |
| 삼값 논리 | three-valued logic | 참, 거짓, 모름 |
NULL |
널 | 값이 없다는 표시입니다 |
IS NULL |
널 판정 | 결측을 찾는 유일한 방법입니다 |
| 다중집합 | multiset, bag | 중복을 허용하는 모음입니다 |
| 선택도 | selectivity | 조건을 통과하는 비율입니다 |
| 밀어내리기 | pushdown | 연산을 아래로 옮겨 중간을 줄입니다 |
| 선언형 | declarative | 무엇을 원하는지만 적습니다 |
| 실행 계획 | execution plan | 최적화기가 정한 절차입니다 |
| 창 함수 | window function | 행을 유지하며 집계합니다 |
| 상관 부질의 | correlated subquery | 바깥 행마다 계산합니다 |
다음은 160강 조인과 집계입니다. 이 강의는 표 하나를 다뤘고, 다음은 표 여럿을 잇습니다.
조인은 곱을 만든 뒤 조건으로 거르는 것이며, 문제 5의 밀어내리기가 그 자리에서 가장 크게 작동합니다. 안쪽 조인과 바깥쪽 조인이 무엇을 남기고 무엇을 버리는지, 일대다 조인이 왜 합계를 부풀리는지가 다음 주제입니다.
그리고 문제 3의 NULL이 조인에서 다시 나옵니다. 바깥쪽 조인이 만들어 내는 NULL은 원래 자료에 없던 것이라, 그 뒤의 집계가 무엇을 세는지 달라집니다.
import numpy as np
rng = np.random.default_rng(20260827)
dt = np.dtype([("id", "i8"), ("user", "i8"), ("amount", "i8"), ("city", "U8")])
rows = [(1, 101, 15000, "서울"), (2, 102, 22000, "부산"), (3, 101, 8000, "서울"),
(4, 103, 31000, "대구"), (5, 102, 5000, "부산"), (6, 104, 12000, "서울"),
(7, 101, 27000, "인천"), (8, 105, 9000, "부산")]
T = np.array(rows, dtype=dt)
# --- 문제 1: 기본 연산 세 가지 ------------------------------------------
print(" 주문 표 %d 행에서 세 가지 기본 연산을 해 봅니다" % len(T))
sel = T[T["amount"] >= 10000]
print(" 선택 amount >= 10000 : %d 행이 남습니다" % len(sel))
proj = np.unique(T["city"])
print(" 사영 city 만 남기고 중복 제거 : %d 개 (%s)" % (len(proj), ", ".join(proj)))
big = T[T["amount"] >= 20000]
print(" 선택 amount >= 20000 뒤 사영하면 %d 개 (%s)"
% (len(np.unique(big["city"])), ", ".join(np.unique(big["city"]))))
A = set(int(z) for z in T[T["amount"] >= 10000]["user"])
B = set(int(z) for z in T[T["city"] == "서울"]["user"])
print(" 1 만 이상 주문한 사용자 %s" % sorted(A))
print(" 서울에서 주문한 사용자 %s" % sorted(B))
print(" 합집합 %s, 교집합 %s, 차집합 %s"
% (sorted(A | B), sorted(A & B), sorted(A - B)))
print(" 선택은 행을 고르고 사영은 열을 고릅니다. 26강 집합 연산이 그대로 쓰입니다")
print(" 사영은 중복을 만듭니다. 집합으로 보려면 중복을 지워야 합니다")
print(" 중복을 지우기 전 city 열 %d 개, 지운 뒤 %d 개" % (len(T), len(proj)))
# --- 문제 2: 조건을 어떻게 조합하는가 -----------------------------------
print(" 두 조건을 논리곱과 논리합으로 잇고 드모르간을 확인합니다")
p = T["amount"] >= 10000
q = T["city"] == "서울"
print(" 조건 p (1 만 이상) 참인 행 %d, 조건 q (서울) 참인 행 %d" % (p.sum(), q.sum()))
print(" p AND q : %d, p OR q : %d, NOT p : %d" % ((p & q).sum(), (p | q).sum(), (~p).sum()))
print(" NOT(p AND q) 와 (NOT p) OR (NOT q) 가 모든 행에서 같은가: %s"
% bool(np.all(~(p & q) == ((~p) | (~q)))))
print(" NOT(p OR q) 와 (NOT p) AND (NOT q) 가 모든 행에서 같은가: %s"
% bool(np.all(~(p | q) == ((~p) & (~q)))))
print(" 포함배제로 센 합집합 %d = %d + %d - %d"
% (p.sum() + q.sum() - (p & q).sum(), p.sum(), q.sum(), (p & q).sum()))
print(" 조건의 순서를 바꿔도 결과가 같습니다. 선택은 교환됩니다")
r1 = T[p][T[p]["city"] == "서울"]
r2 = T[q][T[q]["amount"] >= 10000]
print(" 먼저 금액으로 거른 뒤 도시 : %s" % ", ".join(str(int(z)) for z in r1["id"]))
print(" 먼저 도시로 거른 뒤 금액 : %s" % ", ".join(str(int(z)) for z in r2["id"]))
print(" 두 결과가 같은가: %s" % bool(np.array_equal(np.sort(r1["id"]), np.sort(r2["id"]))))
print(" 같은 결과인데 중간 크기가 다릅니다. 이것이 문제 5 의 최적화 여지입니다")
print(" 금액 먼저면 중간 %d 행, 도시 먼저면 중간 %d 행" % (p.sum(), q.sum()))
# --- 문제 3: NULL 이 논리를 어떻게 바꾸는가 -----------------------------
print(" 값이 없는 칸을 넣고 논리가 어떻게 바뀌는지 봅니다")
F, U, Tr = 0, 1, 2
nm = {F: "거짓", U: "모름", Tr: "참"}
print(" 삼값 논리의 진리표입니다. 순서는 거짓 < 모름 < 참 입니다")
print(" p q p AND q p OR q")
for a in [F, U, Tr]:
for b in [F, U, Tr]:
print(" %-6s %-6s %-10s %s" % (nm[a], nm[b], nm[min(a, b)], nm[max(a, b)]))
print(" NOT: 참 -> 거짓, 거짓 -> 참, 모름 -> %s" % nm[U])
amt = np.array([15000, 22000, -1, 31000, -1, 12000, 27000, 9000], dtype=np.int64)
null = amt < 0
print(" 금액 8 칸 중 %d 칸이 값 없음입니다" % int(null.sum()))
cond = np.where(null, U, np.where(amt >= 10000, Tr, F))
print(" 조건 amount >= 10000 의 삼값 결과: 참 %d, 거짓 %d, 모름 %d"
% (int((cond == Tr).sum()), int((cond == F).sum()), int((cond == U).sum())))
ncond = np.where(cond == U, U, np.where(cond == Tr, F, Tr))
print(" WHERE 는 참인 행만 통과시킵니다")
print(" 조건이 참인 행 %d + 부정이 참인 행 %d = %d 인데 전체는 %d 입니다"
% (int((cond == Tr).sum()), int((ncond == Tr).sum()),
int((cond == Tr).sum()) + int((ncond == Tr).sum()), len(amt)))
print(" 조건과 그 부정을 합쳐도 전체가 되지 않습니다. 모름인 행이 양쪽에서 빠집니다")
print(" 값 없음끼리 비교도 모름입니다. NULL = NULL 은 %s 입니다" % nm[U])
lst = [1, 2, -1]
print(" NOT IN 의 함정을 봅니다. 목록 (1, 2, 값없음) 에 대해 x NOT IN 목록 입니다")
print(" x 결과")
for x in [1, 3, 5]:
res = Tr
for v in lst:
eq = U if v < 0 else (Tr if x == v else F)
neq = U if eq == U else (F if eq == Tr else Tr)
res = min(res, neq)
print(" %6d %s" % (x, nm[res]))
print(" 목록에 값 없음이 하나만 있어도 NOT IN 은 참이 되지 못합니다. 결과가 빕니다")
print(" 값 없음은 IS NULL 로만 판정합니다. 등호로는 영원히 찾을 수 없습니다")
# --- 문제 4: 중복을 어떻게 다루는가 -------------------------------------
print(" 관계형 표는 집합이 아니라 다중집합입니다")
c = T["city"]
u, k = np.unique(c, return_counts=True)
print(" 도시 행 수")
for a, b in zip(u, k):
print(" %-8s %6d" % (a, int(b)))
print(" 사영만 하면 %d 행, 중복을 지우면 %d 행" % (len(c), len(u)))
c2 = np.array(["서울", "부산", "광주"], dtype="U8")
allu = np.concatenate([np.unique(c), c2])
print(" UNION ALL 은 %d 행, UNION 은 %d 행" % (len(allu), len(np.unique(allu))))
print(" 차이 %d 행이 양쪽에 다 있던 도시입니다: %s"
% (len(allu) - len(np.unique(allu)),
", ".join(sorted(set(np.unique(c)) & set(c2)))))
print(" 중복 제거는 공짜가 아닙니다. 정렬이나 해시가 필요합니다")
print(" 필요 없는데 DISTINCT 를 붙이면 비용만 늘고 결과는 같습니다")
print(" 반대로 필요한데 빠뜨리면 개수를 세는 순간 틀립니다")
print(" 도시 개수를 중복 포함으로 세면 %d, 지우고 세면 %d 입니다" % (len(c), len(u)))
# --- 문제 5: 질의를 어떻게 읽고 최적화하는가 ----------------------------
print(" 질의의 논리적 실행 순서를 확인합니다")
order = ["FROM", "WHERE", "GROUP BY", "HAVING", "SELECT", "ORDER BY", "LIMIT"]
for i, s in enumerate(order):
print(" %d. %s" % (i + 1, s))
print(" SELECT 가 다섯 번째라 WHERE 에서는 SELECT 의 별명을 쓸 수 없습니다")
print(" WHERE 는 묶기 전 행을, HAVING 은 묶은 뒤 그룹을 거릅니다")
print(" 선택을 곱 앞으로 밀면 중간 크기가 얼마나 줄어드는지 봅니다")
nA, nB = 100000, 200
selA = 0.01
print(" 방식 중간 결과 행 수 비")
naive = nA * nB
push = int(nA * selA) * nB
print(" %-32s %18d %8.1f" % ("곱한 뒤 거르기", naive, naive / push))
print(" %-32s %18d %8.1f" % ("거른 뒤 곱하기", push, 1.0))
print(" 선택도 %.2f 이면 중간 결과가 %d 배 줄어듭니다" % (selA, naive // push))
print(" 같은 답을 주는 두 계획의 비용이 100 배 다릅니다")
print(" 그래서 질의 최적화기가 선택을 아래로 밀고 사영을 먼저 합니다")
print(" 질의는 무엇을 원하는지만 적고 어떻게 할지는 최적화기가 정합니다")
print(" 선언형이라 부르며 22강 술어 논리로 조건을 적는 것이 그 뜻입니다")
# 주문 표 8 행에서 세 가지 기본 연산을 해 봅니다
# 선택 amount >= 10000 : 5 행이 남습니다
# 사영 city 만 남기고 중복 제거 : 4 개 (대구, 부산, 서울, 인천)
# 선택 amount >= 20000 뒤 사영하면 3 개 (대구, 부산, 인천)
# 1 만 이상 주문한 사용자 [101, 102, 103, 104]
# 서울에서 주문한 사용자 [101, 104]
# 합집합 [101, 102, 103, 104], 교집합 [101, 104], 차집합 [102, 103]
# 선택은 행을 고르고 사영은 열을 고릅니다. 26강 집합 연산이 그대로 쓰입니다
# 사영은 중복을 만듭니다. 집합으로 보려면 중복을 지워야 합니다
# 중복을 지우기 전 city 열 8 개, 지운 뒤 4 개
# 두 조건을 논리곱과 논리합으로 잇고 드모르간을 확인합니다
# 조건 p (1 만 이상) 참인 행 5, 조건 q (서울) 참인 행 3
# p AND q : 2, p OR q : 6, NOT p : 3
# NOT(p AND q) 와 (NOT p) OR (NOT q) 가 모든 행에서 같은가: True
# NOT(p OR q) 와 (NOT p) AND (NOT q) 가 모든 행에서 같은가: True
# 포함배제로 센 합집합 6 = 5 + 3 - 2
# 조건의 순서를 바꿔도 결과가 같습니다. 선택은 교환됩니다
# 먼저 금액으로 거른 뒤 도시 : 1, 6
# 먼저 도시로 거른 뒤 금액 : 1, 6
# 두 결과가 같은가: True
# 같은 결과인데 중간 크기가 다릅니다. 이것이 문제 5 의 최적화 여지입니다
# 금액 먼저면 중간 5 행, 도시 먼저면 중간 3 행
# 값이 없는 칸을 넣고 논리가 어떻게 바뀌는지 봅니다
# 삼값 논리의 진리표입니다. 순서는 거짓 < 모름 < 참 입니다
# p q p AND q p OR q
# 거짓 거짓 거짓 거짓
# 거짓 모름 거짓 모름
# 거짓 참 거짓 참
# 모름 거짓 거짓 모름
# 모름 모름 모름 모름
# 모름 참 모름 참
# 참 거짓 거짓 참
# 참 모름 모름 참
# 참 참 참 참
# NOT: 참 -> 거짓, 거짓 -> 참, 모름 -> 모름
# 금액 8 칸 중 2 칸이 값 없음입니다
# 조건 amount >= 10000 의 삼값 결과: 참 5, 거짓 1, 모름 2
# WHERE 는 참인 행만 통과시킵니다
# 조건이 참인 행 5 + 부정이 참인 행 1 = 6 인데 전체는 8 입니다
# 조건과 그 부정을 합쳐도 전체가 되지 않습니다. 모름인 행이 양쪽에서 빠집니다
# 값 없음끼리 비교도 모름입니다. NULL = NULL 은 모름 입니다
# NOT IN 의 함정을 봅니다. 목록 (1, 2, 값없음) 에 대해 x NOT IN 목록 입니다
# x 결과
# 1 거짓
# 3 모름
# 5 모름
# 목록에 값 없음이 하나만 있어도 NOT IN 은 참이 되지 못합니다. 결과가 빕니다
# 값 없음은 IS NULL 로만 판정합니다. 등호로는 영원히 찾을 수 없습니다
# 관계형 표는 집합이 아니라 다중집합입니다
# 도시 행 수
# 대구 1
# 부산 3
# 서울 3
# 인천 1
# 사영만 하면 8 행, 중복을 지우면 4 행
# UNION ALL 은 7 행, UNION 은 5 행
# 차이 2 행이 양쪽에 다 있던 도시입니다: 부산, 서울
# 중복 제거는 공짜가 아닙니다. 정렬이나 해시가 필요합니다
# 필요 없는데 DISTINCT 를 붙이면 비용만 늘고 결과는 같습니다
# 반대로 필요한데 빠뜨리면 개수를 세는 순간 틀립니다
# 도시 개수를 중복 포함으로 세면 8, 지우고 세면 4 입니다
# 질의의 논리적 실행 순서를 확인합니다
# 1. FROM
# 2. WHERE
# 3. GROUP BY
# 4. HAVING
# 5. SELECT
# 6. ORDER BY
# 7. LIMIT
# SELECT 가 다섯 번째라 WHERE 에서는 SELECT 의 별명을 쓸 수 없습니다
# WHERE 는 묶기 전 행을, HAVING 은 묶은 뒤 그룹을 거릅니다
# 선택을 곱 앞으로 밀면 중간 크기가 얼마나 줄어드는지 봅니다
# 방식 중간 결과 행 수 비
# 곱한 뒤 거르기 20000000 100.0
# 거른 뒤 곱하기 200000 1.0
# 선택도 0.01 이면 중간 결과가 100 배 줄어듭니다
# 같은 답을 주는 두 계획의 비용이 100 배 다릅니다
# 그래서 질의 최적화기가 선택을 아래로 밀고 사영을 먼저 합니다
# 질의는 무엇을 원하는지만 적고 어떻게 할지는 최적화기가 정합니다
# 선언형이라 부르며 22강 술어 논리로 조건을 적는 것이 그 뜻입니다