BigQuery Notes

JOIN vs UNION

• JOIN = horizontal enrich (열 추가): bring columns from another table.
• UNION = vertical append (행 추가): stack same-schema rows.

NOAA Weather dataset 구조

• Daily temps: 일별 관측값이 여러 테이블(연도별 등) 로 분리.
• Stations lookup: 위경도/주/스테이션명 등 정적 메타데이터 한 테이블.
• Daily에는 Country/State 없음 → JOIN으로 lookup에서 보강.

Unique key 찾기

• 단일 키 후보: usaf (not unique) → 재사용/장비 교체 등으로 중복.
• 해결: composite key CONCAT(usaf, '-', wban) 사용 → unique (~30,016 stations)

-- 중복 usaf 찾기 (왜 단일키로 부족한지 확인)
WITH s AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY usaf ORDER BY wban) AS rn
  FROM `bigquery-public-data.noaa_gsod.stations`
)
SELECT * FROM s WHERE rn > 1 ORDER BY usaf, rn;

-- 조인용 복합키 예시
SELECT CONCAT(usaf, '-', wban) AS station_key, *
FROM `bigquery-public-data.noaa_gsod.stations`;

JOIN: 기본과 주의점

Basic JOINs (빨리 보는 표)

• INNER JOIN: match 있는 행만.
• LEFT JOIN: 왼쪽 전부 + 매칭 오른쪽.
• RIGHT JOIN: 오른쪽 전부 + 매칭 왼쪽.
• FULL OUTER: 둘 다 전부, 미매칭은 NULL.
• CROSS JOIN: 카티전 곱(조심).

-- temps + station info enrich (N:1 의도)
WITH temps AS (SELECT * FROM `...temps_2020`),
     st AS (SELECT CONCAT(usaf, '-', wban) AS k, * FROM `...stations`)
SELECT t.*, s.state, s.latitude, s.longitude
FROM temps t
JOIN st s
  ON CONCAT(t.usaf, '-', t.wban) = s.k;