FILTER
FILTER (배열 필터)
람다 표현식의 로직에 따라 배열을 필터링해요.
참조: Use lambda functions on data with Snowflake higher-order functions
본문
구문
FILTER( <array>, <lambda_expression> )
인자
array
필터링할 요소를 포함하는 배열이에요. 배열은 반구조적(semi-structured)이거나 구조적(structured)일 수 있어요.
lambda_expression
각 배열 요소에 대한 필터 조건을 정의하는 람다 표현식이에요.
람다 표현식은 다음 구문으로 지정된 하나의 인자만 가져야 해요:
<arg> [ <datatype> ] -> <expr>
반환 값
이 함수의 반환 타입은 입력 배열과 같은 타입의 배열이에요. 반환된 배열은 필터 조건이 TRUE를 반환하는 요소를 포함해요.
어느 인자든 NULL이면 함수는 오류를 보고하지 않고 NULL을 반환해요.
사용상 주의사항
- 람다 인자의 데이터 타입이 명시적으로 지정되면, 배열 요소는 람다 호출 전에 지정된 타입으로 강제 변환(coerced)돼요. 강제 변환 정보는 Data type conversion을 참고해요.
- 필터 조건이 NULL로 평가되면 해당 배열 요소는 필터링되어 제거돼요.
예시
다음 예시들은 FILTER 함수를 사용해요.
값보다 큰 배열 요소 필터링
FILTER 함수를 사용해 값이 50 이상인 배열의 객체를 반환해요:
SELECT FILTER([
{'name':'Pat', 'value': 50},
{'name':'Terry', 'value': 75},
{'name':'Dana', 'value': 25}
], a -> a:value >= 50) AS "Filter >= 50";
+----------------------+
| Filter >= 50 |
|----------------------|
| [ |
| { |
| "name": "Pat", |
| "value": 50 |
| }, |
| { |
| "name": "Terry", |
| "value": 75 |
| } |
| ] |
+----------------------+
NULL이 아닌 배열 요소 필터링
FILTER 함수를 사용해 NULL이 아닌 배열 요소를 반환해요:
SELECT FILTER([1, NULL, 3, 5, NULL], a -> a IS NOT NULL) AS "Not NULL Elements";
+-------------------+
| Not NULL Elements |
|-------------------|
| [ |
| 1, |
| 3, |
| 5 |
| ] |
+-------------------+
테이블에서 값보다 크거나 같은 배열 요소 필터링
order_id, order_date, order_detail 열이 있는 orders라는 테이블이 있다고 가정해요. order_detail 열은 품목, 구매 수량, 소계의 배열이에요. 테이블은 두 행의 데이터를 포함해요. 다음 SQL 문은 이 테이블을 만들고 행을 삽입해요:
CREATE OR REPLACE TABLE orders AS
SELECT 1 AS order_id, '2024-01-01' AS order_date, [
{'item':'UHD Monitor','quantity':3,'subtotal':1500},
{'item':'Business Printer','quantity':1,'subtotal':1200}
] AS order_detail
UNION
SELECT 2 AS order_id, '2024-01-02' AS order_date, [
{'item':'Laptop','quantity':5,'subtotal':7500},
{'item':'Noise-canceling Headphones','quantity':5,'subtotal':1000}
] AS order_detail;
SELECT * FROM orders;
+----------+------------+-------------------------------------------+
| ORDER_ID | ORDER_DATE | ORDER_DETAIL |
|----------+------------+-------------------------------------------|
| 1 | 2024-01-01 | [ |
| | | { |
| | | "item": "UHD Monitor", |
| | | "quantity": 3, |
| | | "subtotal": 1500 |
| | | }, |
| | | { |
| | | "item": "Business Printer", |
| | | "quantity": 1, |
| | | "subtotal": 1200 |
| | | } |
| | | ] |
| 2 | 2024-01-02 | [ |
| | | { |
| | | "item": "Laptop", |
| | | "quantity": 5, |
| | | "subtotal": 7500 |
| | | }, |
| | | { |
| | | "item": "Noise-canceling Headphones", |
| | | "quantity": 5, |
| | | "subtotal": 1000 |
| | | } |
| | | ] |
+----------+------------+-------------------------------------------+
FILTER 함수를 사용해 소계가 1500 이상인 주문을 반환해요:
SELECT order_id,
order_date,
FILTER(o.order_detail, i -> i:subtotal >= 1500) AS order_detail_gt_equal_1500
FROM orders o;
+----------+------------+----------------------------+
| ORDER_ID | ORDER_DATE | ORDER_DETAIL_GT_EQUAL_1500 |
|----------+------------+----------------------------|
| 1 | 2024-01-01 | [ |
| | | { |
| | | "item": "UHD Monitor", |
| | | "quantity": 3, |
| | | "subtotal": 1500 |
| | | } |
| | | ] |
| 2 | 2024-01-02 | [ |
| | | { |
| | | "item": "Laptop", |
| | | "quantity": 5, |
| | | "subtotal": 7500 |
| | | } |
| | | ] |
+----------+------------+----------------------------+
람다 표현식에서 테이블 열을 참조해 테이블 데이터의 배열 요소 필터링
ARRAY 타입 열 하나와 INT 타입 열 하나가 있는 테이블 생성:
CREATE OR REPLACE TABLE filter_column_ref_demo AS
SELECT [10, 15, 20] AS col1, 18 AS col2
UNION
SELECT [30, 50, 70] AS col1, 40 AS col2;
SELECT * FROM filter_column_ref_demo;
+-------+------+
| COL1 | COL2 |
|-------+------|
| [ | 18 |
| 10, | |
| 15, | |
| 20 | |
| ] | |
| [ | 40 |
| 30, | |
| 50, | |
| 70 | |
| ] | |
+-------+------+
FILTER 함수를 사용해 각 행의 col2 값보다 낮은 배열 요소 값을 반환해요:
SELECT FILTER(col1, v -> v < col2) AS filter_col_ref
FROM filter_column_ref_demo;
+----------------+
| FILTER_COL_REF |
|----------------|
| [ |
| 10, |
| 15 |
| ] |
| [ |
| 30 |
| ] |
+----------------+
더 알아보기
- Semi-structured and structured data functions (Higher-order) — 고차 함수 모음
- TRANSFORM — 배열 변환
- REDUCE — 배열 축소