NULL, 컬렉션과 분석 window 함수
SQL의 NULL은 값이 없음을 나타내며 빈 문자열이나 길이 0의 배열과 다릅니다. 조건이나 집계에 적용하는 규칙을 구분합니다.
NULL 함수
| 함수와 인자 | 의미 |
|---|---|
COALESCE(expr1, expr2, ...) | 순서대로 첫 비NULL 값. 모두 NULL이면 NULL |
ISNULL(expr) | 값이 NULL일 때 True |
NULLIF(left, right) | 두 값이 같으면 NULL, 아니면 첫 값 |
COALESCE의 인자들은 공통 결과 자료형으로 해석할 수 있어야 합니다. 문자열과 숫자를 섞으면 ANSI와 자료형 우선순위에 따라 의도하지 않은 cast나 오류가 생길 수 있습니다.
SELECT
COALESCE(CAST(NULL AS STRING), 'fallback') AS chosen_value,
ISNULL(CAST(NULL AS STRING)) AS missing_value,
NULLIF('same', 'same') AS equal_values
컬렉션 생성과 검사
| 함수와 인자 | 결과 |
|---|---|
ARRAY(expr1, expr2, ...) | 배열 |
ARRAY_CONTAINS(array, value) | 배열에 지정 값이 있는지 검사 |
MAP(key1, value1, key2, value2, ...) | 키/값 쌍의 map |
SIZE(collection) | 배열 또는 map의 길이 |
SORT_ARRAY(array[, ascending]) | 자연 순서로 원소 정렬. 기본 True, False이면 내림차순 |
STRUCT(expr1, expr2, ...) | 여러 필드를 가진 struct |
MAP은 키와 값을 번갈아 전달합니다. STRUCT는 필드가 있는 하나의 값을 만들며 배열처럼 각 항목을 행으로 펼치는 대상과 구분합니다.
SELECT
ARRAY(3, 1, 2) AS numbers,
ARRAY_CONTAINS(ARRAY(3, 1, 2), 1) AS contains_one,
SIZE(ARRAY(3, 1, 2)) AS number_count,
SORT_ARRAY(ARRAY(3, 1, 2), false) AS descending_numbers,
MAP('left', 1, 'right', 2) AS coordinates,
STRUCT('Sara' AS name, 31 AS age) AS person
SORT_ARRAY는 오름차순에서 NULL 원소를 앞에, 내림차순에서 뒤에 놓습니다. 부동소수점 NaN은 일반 숫자보다 크게 취급합니다.
EXPLODE와 POSEXPLODE
EXPLODE(expr)는 배열 원소마다 한 행을, map에서는 각 키/값마다 한 행을 만듭니다. POSEXPLODE(expr)는 그 위치도 함께 반환합니다.
SELECT EXPLODE(ARRAY('a', 'b')) AS value
SELECT POSEXPLODE(ARRAY('a', 'b')) AS (position, value)
SELECT EXPLODE(MAP('left', 1, 'right', 2)) AS (map_key, map_value)
배열의 위치는 posexplode에서 0부터 시작합니다. 입력이 빈 배열이나 NULL인 경우 원래 행을 그대로 유지하는 기능은 아니므로 원행 보존 요구사항을 확인합니다.
LAG와 LEAD
분석 window의 순서 안에서 이전/다음 행의 값을 읽습니다.
| 함수와 인자 | 의미 |
|---|---|
LAG(expr[, offset[, defaultValue]]) | offset만큼 이전 행. 기본 offset 1, defaultValue NULL |
LEAD(expr[, offset[, defaultValue]]) | offset만큼 다음 행. 기본 offset 1, defaultValue NULL |
대상 행 자체가 없으면 defaultValue를 사용합니다. 대상 행은 있지만 해당 값이 NULL이면 그 NULL을 반환하며 defaultValue로 치환하지 않습니다.
SELECT
customer,
event_id,
amount,
LAG(amount, 1, NULL) OVER (
PARTITION BY customer ORDER BY event_id
) AS previous_amount,
LEAD(amount, 1, NULL) OVER (
PARTITION BY customer ORDER BY event_id
) AS next_amount
FROM VALUES ('A', 1, 10), ('A', 2, 20), ('A', 3, 15) AS samples(customer, event_id, amount)
예제의 event_id는 고객 안에서 유일하여 순서가 정해집니다. 실제 데이터에서도 원하는 업무 순서와 동률 처리 기준을 지정합니다. Timestamp를 일정한 구간으로 묶는 WINDOW 함수는 시간 구간 생성에서 별도로 설명합니다.