합계가 부풀어 오르는 이유

짝의 개수만큼 교통값이 반복됩니다

A의 하루 통행량이 100대이고 날씨 관측소 두 곳의 강수량이 2mm와 4mm라고 가정합니다. 지역·날짜로 직접 조인하면 교통 100대가 두 번 표시됩니다. 두 결과 행의 SUM은 200대지만 차량이 늘어난 것은 아닙니다. 관측 단위가 다른 표를 붙여 수량이 반복된 것입니다. 먼저 COUNT(*)와 교통 SUM을 조인 전후에 비교하면 증상을 확인할 수 있습니다.

한 키에 왼쪽 두 행, 오른쪽 세 행이 있다면 그 키에서는 여섯 조합이 생깁니다. daily의 키가 유일하면 이번 프로젝트의 직접 결합은 일대다입니다. 교통 원본까지 일별로 묶지 않으면 다대다 문제가 됩니다. JOIN 종류를 바꾸는 것만으로 짝의 개수는 줄지 않습니다. LEFT JOIN도 오른쪽에 여러 짝이 있으면 왼쪽 값을 반복합니다.

날씨를 목표 단위로 먼저 요약합니다

WITH weather_daily AS (...)는 이번 쿼리 안에서 쓸 집계 결과에 이름을 붙입니다. weather를 weather_region,date로 GROUP BY하고 AVG(rain_mm)으로 유효 관측소 강수량의 산술평균을 계산합니다. 이후 교통에 weather_daily를 붙이면 한 교통 키에 날씨 요약 한 행만 대응합니다. CTE는 입력을 삭제하거나 원본 표를 바꾸는 명령이 아닙니다.

이번 평균은 교육용 집계 규칙입니다. 관측소마다 하루 누적량을 한 번 기록했다고 가정하며 같은 가중치로 평균냅니다. 2와 4의 평균은 3mm입니다. 지역 전체의 총강수량이나 면적 가중 강수량이라고 부르지 않습니다. 실제 자료에서 지점 대표성·유효 관측 시간·측정 방식이 다르면 같은 평균을 곧바로 적용하기 어렵습니다. 집계 규칙과 한계를 출력 사전에 기록합니다.

평균 옆에 유효 관측 수를 붙입니다

AVG는 NULL 값을 제외합니다. NULL과 0이 있으면 평균은 0이며 전체 관측소 행은 2, 유효 관측소는 1입니다. 모두 NULL이면 평균도 NULL이고 유효 관측소는 0입니다. COUNT(*) AS station_rows,COUNT(rain_mm) AS valid_stations를 함께 출력하면 이 차이를 알 수 있습니다. 관측소를 합쳤다는 이유로 결측을 무강수로 바꾸지 않습니다.

같은 관측소·날짜가 두 번 적재되면 평균의 가중치가 달라집니다. 2,2,4의 평균은 2와 4의 평균과 다릅니다. 원본 키 weather_region,date,station을 유일하게 검사하고 중복을 거부한 뒤 집계합니다. 관측소 코드가 다른 두 행은 정상 반복일 수 있으므로 지역·날짜만 보고 한 행을 삭제하지 않습니다. 결합 단위와 원본 키의 역할을 분리해야 하는 이유입니다.

숫자를 억지로 맞추는 방법을 피합니다

SUM(DISTINCT total_vehicles)는 같은 수치의 서로 다른 교통 관측을 하나로 줄입니다. 서로 다른 두 날이 100대라면 정상 합계200을 100으로 만듭니다. 조인 후 DISTINCT 전체 행도 관측소 값이 다르면 반복된 교통값을 없애지 못합니다. 최종 합계를 관측소 수로 나누는 방법은 날짜마다 관측소 수가 다를 때 성립하지 않습니다. 원인을 해결하는 방법은 조인 전 집계 단위를 맞추는 것입니다.

직접 결합과 선집계 결과를 대조합니다

따라하기 표의 교통 합계는 100, 80, 120, 0을 더한 300대입니다. 관측소 원본에 직접 LEFT JOIN하면 A의 첫 날짜가 두 행으로 늘어나 교통 합계는 400대가 됩니다. A의 둘째 날짜와 B의 첫 날짜에는 각각 관측소 한 행이 있고 B의 둘째 날짜에는 짝이 없습니다. 미매칭 한 행은 보존되므로 전체 결과는 다섯 행입니다. 합계 증가분 100대가 어떤 키에서 생겼는지 키별 결과로 확인하면 계산 과정을 설명할 수 있습니다.

날씨를 먼저 집계하면 WA의 첫 날짜는 평균 3, 전체 관측소 2, 유효 관측소 2인 한 행이 됩니다. 이 요약을 붙인 결과는 교통 네 행과 300대를 유지합니다. WB의 첫 날짜는 관측소가 하나 있지만 값이 결측이므로 평균 NULL, 전체 1, 유효 0입니다. WB의 둘째 날짜는 요약 행이 없어 세 날씨 열이 모두 NULL입니다. 평균만 표시하면 이 두 상태를 구별할 수 없으므로 수량 열까지 읽습니다.

집계 경계를 쿼리에 드러냅니다

GROUP BY에 station까지 넣으면 관측소별 한 행이라는 원래 단위가 유지됩니다. 지역·날짜별 한 행을 만들려는 목적에 맞지 않아 이후 조인에서 교통값이 다시 반복됩니다. 반대로 date를 빼면 같은 지역의 여러 날 강수량이 하나의 평균으로 섞입니다. 목표 키를 먼저 종이에 적고 GROUP BY의 열 목록과 대조합니다. 출력이 그럴듯한 평균이라고 해서 집계 기간과 지역 범위가 맞다는 뜻은 아닙니다.

평균과 수량은 같은 그룹 안에서 동시에 계산합니다. AVG만 별도 조회하고 관측소 수를 다른 날짜 범위로 세면 숫자가 같은 모집단을 설명하지 못합니다. COUNT(rain_mm)은 0도 유효값으로 셉니다. 강수량 양수만 세는 조건은 비가 관측된 관측소 수이며 유효 관측소 수와 다릅니다. 열 이름과 계산식이 같은 의미인지 확인하고, 누락을 다루기 위해 AVG 안에 COALESCE를 넣어 결측을 0으로 대체하지 않습니다.

중복과 정상 반복을 나누어 검사합니다

관측소 s1과 s2가 같은 지역·날짜에 있어도 서로 다른 측정 지점이므로 두 행을 평균에 사용합니다. s1이 같은 날짜에 다시 나타나면 원본 키 중복입니다. 같은 값의 재적재뿐 아니라 값이 달라진 수정 기록도 이 실습에서는 거부합니다. 수정 이력을 처리하는 별도 정책 없이 마지막 행만 선택하면 파일 순서가 평균을 바꿉니다. 행을 지우기 전에는 관측소 식별자와 날짜를 함께 비교합니다.

여러 날짜를 묶어 평가할 때 날짜별 평균을 다시 단순 평균내는 것과 모든 관측소 값을 한 번에 평균내는 것은 다릅니다. 첫날 관측소 두 곳이 2와 4, 둘째 날 한 곳이 0이면 날짜 평균들의 평균은 1.5이고 세 측정값 평균은 2입니다. 어떤 날짜에 더 큰 가중치를 줄지 목적에 따라 결정합니다. 이번 출력은 일별 지역 평균까지만 계산하며 기간 평균이나 비와 교통의 인과관계를 추론하지 않습니다.

브라우저 과제에서는 대응표와 교통 키가 이미 유일하다는 전제를 사용합니다. 날씨 CTE의 키를 지역·날짜로 맞추고 두 LEFT JOIN을 유지하는 것이 핵심입니다. 미매칭의 station_rows와 valid_stations를 0으로 채우지 않습니다. 0은 존재하는 그룹에서 유효값이 없다는 정보와 연결되지만 NULL은 연결된 요약이 없다는 표시입니다. 정답 결과의 마지막 세 열을 비교하면 집계 규칙과 누락 보존을 한 번에 확인할 수 있습니다.