Snowflake SQL 활용 팁 모음

여러 데이터 마트를 Snowflake에 생성했습니다. 그 과정에서 '이것은 다른 프로젝트에서도 사용할 수 있겠다'고 느낀 함수, 구조, 패턴을 비망록으로 남깁니다.

예상 독자: SELECT / JOIN / GROUP BY / CASE는 기본적인 사용법을 익힌 분들.
다루는 내용: Snowflake 고유의 문법(예: DATEADD, EQUAL_NULL, UNPIVOT)이 포함되어 있습니다.

겪었던 문제: 회계 연도가 4월 시작인 경우, 달력 연도(2024년)와 회계 연도(2023회계연도)가 어긋납니다. 1월부터 3월까지만 전년도를 적용하고 싶습니다.

YYYYMM은 6자리 문자열이므로, SUBSTRING을 사용해 '년'과 '월'을 분리한 후 CASE문으로 판별하기만 하면 됩니다.

CASE
WHEN TO_NUMBER(SUBSTRING("대상年月", 5, 2)) >= 4 -- 월이 4~12월이면
THEN TO_NUMBER(SUBSTRING("대상年月", 1, 4)) -- 그 년도가 그대로 회계연도
...

포인트:

• SUBSTRING("대상年月", 5, 2)는 '5번째 문자부터 2글자' 즉 월 부분입니다. SUBSTRING(..., 1, 4)는 년도 부분입니다.
• 마감월이 바뀌어도 조건식 >= 4만 수정하면 대응할 수 있습니다.
• BI에서 '회계연도'로 필터링하는 경우가 많으므로, 이 열을 하나 준비해 두면 나중에 편리합니다.

겪었던 문제: 거래 명세 중 '그 시점에 유효했던 회원'의 데이터만 남기고 싶습니다. 일반적인 JOIN을 사용하면 마스터 측이 1대다(one-to-many) 관계일 때 행 수가 늘어나 버립니다.

이런 식으로 '마스터 측에 조건을 만족하는 행이 있는 경우만 남기는 것'에는 JOIN 대신 EXISTS가 적합합니다. EXISTS는 '내부 조건의 행이

먼저 '모든 조합(＝뼈대)'을 만들고, 거기에 실적 데이터를 나중에 붙여 넣은 뒤, 없는 곳은 0으로 채웁니다. 모든 조합은 CROSS JOIN(전체 교차 조인)으로 만들 수 있습니다.

이 전체 조합된 뼈대에 실적이 있는 데이터를 LEFT JOIN하여 주면, 실적이 0인 행을 포함한 전체 집계가 가능해집니다.

all_data AS (
-- 모든 월 × 모든 지점 × 모든 카테고리를 전부 만듭니다
SELECT A."대상年月", B."지점코드", C."카테고리"
...

포인트

• 뼈대(모집합)는 DISTINCT로 추출한 월이나, 마스터 데이터, VALUES를 사용해 준비합니다. - 실적은 LEFT JOIN하여 COALESCE(..., 0)으로 0 채우기 합니다. '행이 사라지는' 문제가 없어집니다.

'가로형(横持ち)' = 항목이 열 방향으로 배열된 형태, '세로형(縦持ち)' = 항목명과 값이 행 방향으로 배열된 형태를 말합니다.

어려움 (가로 → 세로): 항목1, 항목2, 항목3…처럼 열이 가로로 나열되어 있어 대응표와 결합하기 어렵습니다.

UNPIVOT을 사용하면 가로로 배열된 열을 '항목명'과 '값'의 두 열로 펼칠 수 있습니다. 빈 값도 남기고 싶으므로 INCLUDE NULLS를 붙입니다.

SELECT "대상일", "지점코드", "항목", "결과"
FROM 원데이터
UNPIVOT INCLUDE NULLS (
...

어려움 (세로 → 가로): 반대로, 결제 구분별 수량을 BI용으로 가로로 배열하고 싶습니다.

정해진 수의 구분이라면 CASE + SUM으로 직접 만들 수 있습니다(수동 피벗).

SUM(CASE WHEN "결제구분" = '01' THEN "수량" ELSE 0 END) AS "현금",
SUM(CASE WHEN "결제구분" = '02' THEN "수량" ELSE 0 END) AS "전자머니"

포인트

• UNPIVOT INCLUDE NULLS를 붙이면, 값이 비어있는 항목도 행으로 남길 수 있습니다. - PIVOT 구문도 있지만, CASE + SUM이 조건을 더 세밀하게 작성할 수 있어 유연한 경우가 많습니다.

JOIN의 키에 NULL이 섞이면, NULL = NULL은 참이 아니므로 결합이 끊어집니다. 나눗셈 역시 분모가 0일 때 오류가 납니다. Snowflake의 안전 함수로 회피합니다.

-- NULL끼리도 일치로 간주하여 결합
ON EQUAL_NULL(A."구분A", B."구분A")
AND EQUAL_NULL(A."카테고리", B."카테고리")
...

포인트

• EQUAL_NULL(a, b)는 'a = b 이지만, 양쪽 모두 NULL일 때도 일치로 간주'합니다. NULL을 포함하는 키로 결합할 때 누락을 방지할 수 있습니다. - 비율은 a / b가 아니라 DIV0(a, b)를 사용합니다. 분모 0 때문에 전체 쿼리가 실패하는 사고를 막을 수 있습니다.

어려움: '기준별로 상위 25%의 지점만 집계하고 싶다'. GROUP BY를 사용하면 집계되어 순위로 필터링할 수 없습니다.

윈도우 함수는, GROUP BY와 달리 행을 줄이지 않고, 행마다 순위나 건수를 계산할 수 있는 함수입니다. 순위(ROW_NUMBER)와 모수(COUNT(*) OVER)를 구해서 비율로 기준치를 자릅니다.

rank_cnt AS (
SELECT
...,
...

포인트

• PARTITION BY는 '이 묶음별로 계산을 리셋'하는 지정입니다. GROUP BY의 집계 버전이라고 생각하면 이해하기 쉽습니다. - 상위 N%는 TRUNC(총건수 * 비율)로 기준치를 만듭니다. 단순히 '각 그룹 1위'라면 ROW_NUMBER() = 1 (Tip 4 참조)를 사용합니다.

• TABLE과 VIEW의 사용 구분: 무거운 집계나, 일별로 쌓이는 이력 데이터는 CREATE OR REPLACE TABLE ... AS로 실체화하여 빠르게 합니다. BI 표시용 최종 가공은 CREATE OR REPLACE VIEW로 하여 항상 최신 데이터를 반환하도록 분담하는 것이 편리합니다. - 표시용 변환은 마지막에: 결과 코드 CASE OK/NG/NA → ○/×/‐

필드 구분 코드(区分コード)를 라벨 등 시각적인 형태로 변환하는 것은 뷰의 최종 SELECT에서 한 번에 처리하면 관리가 편리합니다. - 하지만, SELECT 문의 열 순서를 바꾸면 깨질 수 있습니다. 긴 GROUP BY에서는 열 이름으로 작성하는 것이 더 안전할 때도 있습니다. GROUP BY 1, 2, 3

순서 지정(序数指定)을 사용하는 방법입니다.
연월을 숫자로 비교하고 싶을 때: TO_NUMBER(TO_CHAR(d, 'YYYYMM'))

또는 TRUNC("YYYYMMDD" / 100)를 사용하면 YYYYMM에 해당하는 숫자를 만들 수 있습니다. 날짜가 숫자형으로 입력된 마스터 데이터와 대조할 때 편리합니다.

Snowflake SQL을 접하면서 기초적인 부분부터 독창적인 작성 방식까지 폭넓게 배울 수 있었습니다.

또한, 포맷이나 템플릿이 없는 상태에서 '어떤 함수를 사용해야 할까', '더 효율화할 수는 없을까'라고 시행착오를 거치며 진행할 수 있었던 것은 저에게 매우 귀중한 경험이었습니다.

본 기사가 여러분의 학습에 조금이라도 도움이 되기를 바랍니다.