강수 여부와 지역별 비교

전체 차이를 지역 안의 차이로 나눕니다

강수 전체 평균110대와 비강수 전체 평균40대를 구한 뒤 팀원이 모든 지역에서 차이가 같은지 물었습니다. 전체 평균은 관측이 모인 구성의 결과입니다. 지역별 기본 통행량이 다르면 관측 지역의 비율만 바뀌어도 전체 평균이 달라집니다. 지역·강수 여부를 동시에 묶어 n과 통행량 통계를 반환하면 차이가 어느 지역과 어느 관측에서 나왔는지 확인할 수 있습니다.

앞 단계 정제 표의 완전 사례는 A 강수100대, A 비강수80대, B 강수120대, B 비강수0대입니다. 지역 안 차이는 A20대, B120대이지만 각 셀의 n은1입니다. 이는 지역별 관찰값의 차이이지 반복해서 확인된 효과가 아닙니다. 전체110대와40대만 보고 같은 증가 폭이 모든 지역에서 나타났다고 설명하면 표가 제공하는 정보를 넘어섭니다.

유효한 짝을 먼저 선택합니다

실습 테이블 clean은 region TEXT, date TEXT, total_vehicles INTEGER, rain_mm REAL 네 열을 가집니다. 브라우저에서는 초기화 SQL이 각 테스트 전에 테이블을 준비합니다. 미션 CSV의 다른 감사 열은 생략한 동일 관측 단위의 표입니다. date는 YYYY-MM-DD이고 이미 검증된 날짜입니다. 지역과 날짜 조합은 유일하며 결측은 SQL NULL입니다. 문자열 NA나 빈 문자열을 NULL 대신 넣지 않습니다.

WHERE에서 기간의 양 끝을 포함하고 total_vehicles IS NOT NULL과 rain_mm IS NOT NULL을 확인합니다. total_vehicles가0인 행은 이 조건을 통과합니다. 강수 기준은 rain_mm이1 이상이면 rain, 그렇지 않으면 dry라는 CASE 표현식입니다. NULL을 먼저 제외하지 않고 CASE의 ELSE에 맡기면 관측되지 않은 강수가 비강수로 분류되므로 행 선택과 그룹 분류를 분리합니다.

GROUP BY의 키를 질문에 맞춥니다

지역별·강수별 통계는 region과 rain_group 조합으로 묶습니다. GROUP BY rain_group만 쓰면 지역을 합친 표이고 GROUP BY region만 쓰면 날씨를 합친 표입니다. SELECT에 그룹 키와 집계값만 남기면 각 열의 의미가 명확합니다. 임의의 date를 같이 선택하여 특정 날짜처럼 보이게 하지 않습니다. 한 그룹에는 여러 날짜가 있을 수 있어 날짜가 필요하면 기간이나 별도의 날짜별 표를 제공합니다.

출력 순서는 region, rain_group으로 ORDER BY합니다. dry 다음 rain이라는 문자열 정렬 순서를 사용합니다. 결과 행 순서를 고정해야 학습자가 같은 표를 보고 테스트가 값과 행을 안정적으로 비교할 수 있습니다. 평균·최소·최대가 같아도 n이 다르면 근거량은 다릅니다. 숫자가 큰 순서만으로 정렬하면 n=1의 값이 가장 위에 올라 중요한 결과처럼 보일 수 있어 비교 목적에 맞춰 정렬을 정합니다.

없는 그룹을 의도적으로 남깁니다

유효한 행만 GROUP BY하면 그 지역에 강수 관측이 없는 경우 rain 행이 아예 나오지 않습니다. 이 실습은 기간 안에 존재하는 지역 목록과 dry/rain 두 범주를 먼저 CROSS JOIN하여 틀을 만듭니다. 그 틀에 eligible을 LEFT JOIN하면 관측 없는 셀도 결과에 남습니다. 지역 자체가 기간 안에 없으면 틀에서 만들지 않습니다. 따라서 빈 입력과 특정 지역의 빈 그룹은 다른 결과입니다.

LEFT JOIN 뒤 COUNT(*)를 사용하면 매칭 없는 셀도 바깥쪽 행 하나 때문에1로 셉니다. COUNT(e.total_vehicles)는 매칭 없는 NULL을 건너뛰므로 n=0이 됩니다. eligible에서 교통 결측을 제외했기 때문에 유효 관측0대도 이 COUNT에 포함됩니다. COUNT의 기준 열은 지금 어떤 집합을 세는지에 따라 결정하며 모든 쿼리에 같은 COUNT 표현식을 습관적으로 적용하지 않습니다.

빈 그룹의 합계는 COALESCE(SUM(e.total_vehicles),0)으로0을 표시합니다. 평균은 n=0이면NA, 아니면 printf의 소수 한 자리 문자열로 표시합니다. 최소와 최대는 빈 그룹에서NULL입니다. 합계0과 평균NA가 함께 나온 이유는 더할 관측이 없고 나눌 분모도 없기 때문입니다. 실제0대 한 행이면 n=1, 평균0.0, 최소0, 최대0입니다.

집계의 분모를 대조합니다

이번 표의 지역·날씨 네 셀 n을 더하면4이고 완전 사례 WHERE로 센 전체 n도4입니다. 이 두 수가 다르면 조인이 관측을 늘렸거나 분류에서 누락한 행이 있는지 확인합니다. 틀 테이블이 지역·범주마다 정확히 한 행인지 확인하는 것도 필요합니다. 관측을 반복하는 join 조건을 쓰면 합계와 n이 함께 늘어 평균만 보고는 중복을 못 찾을 수 있습니다.

지역별 평균을 단순히 평균내는 값과 전체 관측 평균은 표본 수가 다를 때 다릅니다. A에100대 한 행, B에0대 세 행이면 지역평균의 단순 평균은50대이고 전체 관측 평균은25대입니다. 전체 관측 평균은 지역별 합계를 모두 더해 n 합으로 나눕니다. 지역을 같은 비중으로 비교하려는 별도 질문이라면50대도 의미가 있지만 그 가중 방식과 목표를 문서에 써야 합니다.

행 조건과 그룹 조건을 분리합니다

기간과 결측은 집계 전에 행을 골라야 하므로 WHERE에 둡니다. 유효 관측이2개 이상인 그룹만 별도로 보고 싶다면 집계 후 HAVING COUNT가 적절합니다. 이번 실습은 n=0도 보고하므로 그런 HAVING을 넣지 않습니다. 작은 표본을 숨겨 표가 깔끔해지는 것과 근거를 정직하게 드러내는 것은 다릅니다. 그룹 수와 제외 조건을 먼저 공개하고 비교의 한계를 설명합니다.

OperationalError에 no such column이 나오면 테이블 열 이름과 CTE 별칭을 확인합니다. misuse of aggregate function이 나오면 WHERE에서 AVG나 COUNT를 호출했는지 봅니다. 값이 두 배라면 집계식보다 JOIN의 키와 틀의 중복부터 조사합니다. 오류 없이 실행되어도 질문이 틀릴 수 있으므로 예상하는 네 셀을 종이에 적고 각 셀의 원본 관측과 대조합니다.

제출 표를 읽는 기준을 정합니다

브라우저 실습은 region, rain_group, n, total, mean, min, max를 이 순서로 출력합니다. 평균은 소수 한 자리 문자열이며 n=0이면NA입니다. 빈 그룹의 최소·최대는NULL로 채점합니다. 전체 입력이 비었으면 출력 행이 없습니다. 경계1mm, 기간 밖의 행, 결측만 있는 지역, 한 행0대를 모두 다루어 그룹 틀과 유효 관측 선택이 별개로 동작하는지 확인합니다.

이 결과를 미션의 Python 표와 대조할 때 같은 기간과 같은 강수 기준을 사용합니다. 미션은 중앙값과 민감도 열도 추가하지만 n과 합계·평균은 이 SQL 결과와 같아야 합니다. 중앙값 문법은 데이터베이스마다 다르므로 이번 SQL 문제에서 억지로 다른 엔진의 함수를 호출하지 않습니다. 그룹 선택과 결측 집계 규칙의 상세 내용은 SQL 집계 장을 더 읽고, 여기서는 지역 구성의 영향을 실제 표로 확인합니다.