집계 한 줄로 안 되는 질문들 — 창, 피벗, 시각 조인

한 줄 요약

"매장별 누적", "월을 열로", "그때 환율로" 는 GROUP BY 로 풀리지 않는다. 윈도 함수·QUALIFY·PIVOT·ASOF JOIN 이 각각 그 자리를 맡고, EXPLAIN ANALYZE 가 그 질의가 실제로 무엇을 했는지 보여 준다.

왜 이게 필요했나

"매장별로 가장 많이 팔린 상품 3개" 를 뽑는다. group by store_id, product_id 까지는 되는데 거기서 매장마다 3개만 남길 방법이 없다. 서브쿼리로 순위를 매기고 바깥에서 거르는 것이 보통의 답이고, 그 질의는 읽기 어렵다. "월을 열로" 는 sum(case when month = …) 을 여섯 번 적게 만들고 다음 달이 되면 SQL 을 고쳐야 한다.

"원화 매출" 은 겉보기에 쉽다. 판매 표에 환율 표를 조인하면 된다. 그런데 환율은 매일 있지 않다. 판매일에 환율 행이 없으면 보통 조인은 그 판매를 버리거나, >= 로 느슨하게 걸면 과거의 모든 환율이 다 붙어 행이 몇 배로 불어난다.

어떻게 동작하나

윈도 함수는 행을 합치지 않고 행마다 "이웃을 본 값" 을 덧붙인다. sum(qty) over (partition by store_id order by month) 는 매장마다 창을 따로 열고 월 순서로 지금 행까지의 합을 낸다. WHERE 는 윈도 함수보다 먼저 평가되므로 그 결과로 거를 수 없다. DuckDB 의 QUALIFY 가 그 자리다 — qualify rnk <= 3 한 줄이면 서브쿼리가 사라진다.

PIVOT t ON month USING sum(qty) GROUP BY country 는 어떤 월이 있는지 미리 알 필요 없이 값을 열로 펼친다. UNPIVOT 은 ON COLUMNS(* EXCLUDE (country)) INTO NAME month VALUE qty 로 열 이름을 다시 값으로 되돌린다.

ASOF JOIN 은 "그 시점에 유효했던 한 행" 을 붙인다. sales s asof join fx_rates f on s.currency = f.currency and s.sold_at >= f.valid_from 은 통화가 같은 행 중에서 판매 시각보다 앞선 valid_from 가운데 가장 늦은 하나만 붙인다. 행이 불어나지 않고, 판매일에 환율이 없어도 직전 값이 온다. 픽스처의 36만 행이 그대로 36만 행으로 나온다.

값 하나에 리스트와 구조체를 담을 수 있다. JSON 을 read_json 으로 읽으면 tags 는 VARCHAR[], spec 은 STRUCT 가 되고, unnest(tags) 가 리스트를 행으로 펼치며 spec.weight_g 로 필드를 꺼낸다.

EXPLAIN ANALYZE 는 실제로 실행하며 연산자마다 행 수와 시간을 찍는다(EXPLAIN 만 하면 돌리지 않는다). 아래에서 읽어 올라오면 TABLE_SCAN 이 두 열만 읽었고 HASH_GROUP_BY 가 6행을 냈다는 것이 보인다.

HASH_GROUP_BY      6 rowsTABLE_SCAN   Projections: sold_at, qty   367,299 rows

SET threads 와 SET memory_limit 은 엔진이 쓸 자원의 상한이다. 기본값은 코어 수와 RAM 의 80% 인데 파드 상한이 그보다 작으면 엔진은 그것을 모르고 죽는다. 2GiB 파드에서는 memory_limit 을 그 아래로 내려 둔다. 상한을 넘는 정렬·조인은 영속 DB 라면 디스크로 흘려 완주하고, 메모리 DB 는 temp_directory 가 없으면 실패한다.

실무에서 진짜 중요한 것

첫째, 시각 순서 표에는 ASOF 를 먼저 떠올린다. 환율·가격 이력·설정 변경 이력 전부 같은 모양이다. 보통 조인으로 맞추면 행이 불어나거나 빠지는데 합계가 그럴듯해서 눈에 안 띈다.

둘째, EXPLAIN ANALYZE 의 행 수를 본다. 조인 뒤 행 수가 입력보다 커졌다면 조인 조건이 잘못된 것이다. 시간보다 행 수가 먼저다.

셋째, SUMMARIZE 표 를 습관으로 한다. 열마다 최소·최대·NULL 비율을 한 번에 내준다. 분석 전에 이상한 열을 찾는 가장 싼 방법이고, 다음 실습의 마지막 단계다.