스타 표현식
스타 표현식
* 표현식은 SELECT 문에서 FROM 절에 투영된 모든 컬럼을 선택할 때 사용해요. 여기에 여러 변형을 붙여 컬럼을 제외·교체·이름 변경하거나, 패턴으로 필터링하는 방법까지 함께 알아볼게요.
출처: 문서
본문
문법
* 표현식은 SELECT 문에서 FROM 절에 투영된 모든 컬럼을 선택하는 데 쓰여요.
SELECT *
FROM tbl;
TABLE.*와 STRUCT.*
* 표현식 앞에 테이블 이름을 붙이면 그 테이블의 컬럼만 선택할 수 있어요.
SELECT tbl.*
FROM tbl
JOIN other_tbl USING (id);
비슷하게, * 표현식은 struct의 모든 키를 별도의 컬럼으로 꺼내는 데도 쓸 수 있어요. 이전 연산이 모양을 알 수 없는 struct를 만들거나, 쿼리가 어떤 잠재적 struct 키든 처리해야 할 때 특히 유용하답니다. struct 작업에 대한 자세한 내용은 STRUCT 데이터 타입과 STRUCT 함수 페이지를 참고해 주세요.
예를 들어 볼게요.
SELECT st.* FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS st);
| x | y | z |
|---|---|---|
| 1 | 2 | 3 |
EXCLUDE 절
EXCLUDE는 * 표현식에서 특정 컬럼을 제외할 수 있게 해줘요.
SELECT * EXCLUDE (col)
FROM tbl;
REPLACE 절
REPLACE는 특정 컬럼을 대체 표현식으로 교체할 수 있게 해줘요.
SELECT * REPLACE (col1 / 1_000 AS col1, col2 / 1_000 AS col2)
FROM tbl;
RENAME 절
RENAME은 특정 컬럼의 이름을 교체할 수 있게 해줘요.
SELECT * RENAME (col1 AS height, col2 AS width)
FROM tbl;
패턴 매칭 연산자를 통한 컬럼 필터링
패턴 매칭 연산자 LIKE, GLOB, SIMILAR TO와 그 변형들은 컬럼 이름을 패턴과 매칭해 컬럼을 선택할 수 있게 해줘요.
SELECT * LIKE 'col%'
FROM tbl;
SELECT * GLOB 'col*'
FROM tbl;
SELECT * SIMILAR TO 'col.'
FROM tbl;
이 연산자들의 NOT 변형도 지원돼요. 패턴과 일치하는 컬럼을 제외하는 데 쓰면 됩니다.
SELECT * NOT SIMILAR TO 'col.'
FROM tbl;
COLUMNS 표현식
COLUMNS 표현식은 일반 스타 표현식과 비슷하지만, 결과 컬럼들에 같은 표현식을 실행할 수 있게 해줘요.
CREATE TABLE numbers (id INTEGER, number INTEGER);
INSERT INTO numbers VALUES (1, 10), (2, 20), (3, NULL);
SELECT min(COLUMNS(*)), count(COLUMNS(*)) FROM numbers;
| id | number | id | number |
|---|---|---|---|
| 1 | 10 | 3 | 2 |
SELECT
min(COLUMNS(* REPLACE (number + id AS number))),
count(COLUMNS(* EXCLUDE (number)))
FROM numbers;
| id | min(number := (number + id)) | id |
|---|---|---|
| 1 | 11 | 3 |
COLUMNS 표현식은 같은 스타 표현식을 담고 있으면 결합할 수도 있어요.
SELECT COLUMNS(*) + COLUMNS(*) FROM numbers;
| id | number |
|---|---|
| 2 | 20 |
| 4 | 40 |
| 6 | NULL |
WHERE 절 안의 COLUMNS 표현식
COLUMNS 표현식은 WHERE 절에서도 쓸 수 있어요. 조건은 모든 컬럼에 적용되며 논리 AND 연산자로 결합됩니다.
SELECT *
FROM (
SELECT 'a', 'a'
UNION ALL
SELECT 'a', 'b'
UNION ALL
SELECT 'b', 'b'
) _(x, y)
WHERE COLUMNS(*) = 'a'; -- x = 'a' AND y = 'a'와 동일
| x | y |
|---|---|
| a | a |
논리 OR 연산자로 조건을 결합하려면, COLUMNS 표현식을 가변 인자 greatest 함수로 UNPACK하면 돼요.
SELECT *
FROM (
SELECT 'a', 'a'
UNION ALL
SELECT 'a', 'b'
UNION ALL
SELECT 'b', 'b'
) _(x, y)
WHERE greatest(UNPACK(COLUMNS(*) = 'a')); -- x = 'a' OR y = 'a'와 동일
| x | y |
|---|---|
| a | a |
| a | b |
DISTINCT ON 안의 COLUMNS 표현식
COLUMNS 표현식은 DISTINCT ON 절에서 패턴으로 distinct 컬럼을 지정하는 데 쓸 수 있어요.
SELECT DISTINCT ON (COLUMNS('x|y')) *
FROM (VALUES (1, 2, 'a'), (1, 2, 'b'), (3, 4, 'c')) t(x, y, z);
| x | y | z |
|---|---|---|
| 1 | 2 | a |
| 3 | 4 | c |
COLUMNS 표현식 안의 정규식
COLUMNS 표현식은 현재 패턴 매칭 연산자를 지원하지 않지만, 스타 대신 문자열 상수를 전달함으로써 정규식 매칭을 지원해요.
SELECT COLUMNS('(id|numbers?)') FROM numbers;
| id | number |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | NULL |
COLUMNS 표현식 안의 정규식으로 컬럼 이름 변경
정규식의 캡처 그룹 매칭으로 일치하는 컬럼의 이름을 바꿀 수 있어요. 캡처 그룹은 1 기반이며, \0은 원래 컬럼 이름이에요.
예를 들어 컬럼 이름의 처음 세 글자를 선택하려면 다음을 실행해요.
SELECT COLUMNS('(\\w{3}).*') AS '\\1' FROM numbers;
| id | num |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | NULL |
컬럼 이름 중간의 콜론(:) 문자를 제거하려면 다음을 실행해요.
CREATE TABLE tbl ("Foo:Bar" INTEGER, "Foo:Baz" INTEGER, "Foo:Qux" INTEGER);
SELECT COLUMNS('(\\w*):(\\w*)') AS '\\1\\2' FROM tbl;
원래 컬럼 이름을 표현식 별칭에 추가하려면 다음을 실행해요.
SELECT min(COLUMNS(*)) AS "min_\\0" FROM numbers;
| min_id | min_number |
|---|---|
| 1 | 10 |
COLUMNS 람다 함수
COLUMNS는 람다 함수를 전달하는 것도 지원해요. 람다 함수는 FROM 절에 있는 모든 컬럼에 대해 평가됩니다. 람다 함수가 TRUE로 평가되는 컬럼만 유지되고, 그 외에는 버려져요.
COLUMNS-절 람다는 컬럼 이름을 받는 필수 파라미터가 하나 있어요. 선택적으로 두 번째 파라미터를 선언해 (1 기반) 컬럼 인덱스를 받을 수 있습니다. COLUMNS-절 람다는 컬럼을 선택하고 이름을 바꾸기 위해 임의 표현식을 실행할 수 있게 해줘요.
SELECT COLUMNS(lambda c: c LIKE '%num%') FROM numbers;
| number |
|---|
| 10 |
| 20 |
| NULL |
COLUMNS 목록
COLUMNS는 컬럼 이름 목록을 전달하는 것도 지원해요.
SELECT COLUMNS(['id', 'num']) FROM numbers;
| id | num |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | NULL |
COLUMNS 표현식 언패킹
COLUMNS 표현식을 UNPACK로 감싸면 컬럼들이 부모 표현식으로 확장돼요. Python의 iterable 언패킹 동작과 비슷하답니다.
UNPACK 없이 COLUMNS 표현식에 대한 연산은 각 컬럼에 개별적으로 적용돼요.
SELECT coalesce(COLUMNS(['a', 'b', 'c'])) AS result
FROM (SELECT NULL a, 42 b, true c);
| result | result | result |
|---|---|---|
| NULL | 42 | true |
UNPACK을 쓰면 COLUMNS 표현식이 위 예시의 coalesce 같은 부모 표현식으로 확장되어 단일 컬럼이 돼요.
SELECT coalesce(UNPACK(COLUMNS(['a', 'b', 'c']))) AS result
FROM (SELECT NULL AS a, 42 AS b, true AS c);
| result |
|---|
| 42 |
중간 연산 없이 COLUMNS 표현식에 직접 적용할 때는, UNPACK 키워드를 *로 바꿀 수 있어요 (Python 문법과 일치).
SELECT coalesce(*COLUMNS(*)) AS result
FROM (SELECT NULL a, 42 AS b, true AS c);
| result |
|---|
| 42 |
경고 — 다음 예시에서
UNPACK를*로 바꾸면 문법 오류가 발생해요.SELECT greatest(UNPACK(COLUMNS(*) + 1)) AS result FROM (SELECT 1 AS a, 2 AS b, 3 AS c);
result 4
STRUCT.*
* 표현식은 struct의 모든 키를 별도의 컬럼으로 꺼내는 데도 쓸 수 있어요. 이전 연산이 모양을 알 수 없는 struct를 만들거나, 쿼리가 어떤 잠재적 struct 키든 처리해야 할 때 특히 유용하답니다. struct 작업의 자세한 내용은 STRUCT 데이터 타입과 STRUCT 함수 페이지를 참고해 주세요.
예를 들어 볼게요.
SELECT st.* FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS st);
| x | y | z |
|---|---|---|
| 1 | 2 | 3 |
더 알아보기 (Learn more)
- struct 데이터 타입·함수는
sql/data_types/struct,sql/functions/struct문서를 참고해 주세요.