값 표현식

값 표현식 (Value Expressions)

SELECT의 대상 목록에 뭘 넣을지, INSERT나 UPDATE에서 새 컬럼 값을 뭘로 할지, 검색 조건에 뭘 쓸지 — 이 모든 곳에서 쓰이는 게 바로 값 표현식(value expression)이에요. 조금 추상적으로 들리지만, 실제로는 SQL에서 "값 하나"를 계산해내는 모든 문법을 한데 묶어 설명하는 절이라고 보면 돼요.

출처: PostgreSQL 공식 문서 — sql-expressions

값 표현식이란

값 표현식은 여러 맥락에서 사용돼요. SELECT 명령의 대상 목록(target list), INSERT 또는 UPDATE의 새 컬럼 값, 그리고 여러 명령의 검색 조건 같은 곳이죠.

값 표현식의 결과를 스칼라(scalar) 라고 부르기도 해요. 테이블 표현식(table expression)의 결과가 테이블(표)인 것과 구분하기 위해서예요. 그래서 값 표현식을 스칼라 표현식(scalar expression), 줄여서 그냥 표현식(expression) 이라고도 불러요. 표현식 문법을 쓰면 산술·논리·집합 연산 등으로 기본 조각들에서 값을 계산해낼 수 있죠.

값 표현식이 될 수 있는 건 다음과 같아요.

  • 상수 또는 리터럴 값 (constant or literal value)
  • 컬럼 참조 (column reference)
  • 함수 정의나 준비된 문장(prepared statement) 본문 안의 위치 파라미터 참조 (positional parameter reference)
  • 첨자(subscript)가 붙은 표현식
  • 필드 선택 표현식 (field selection expression)
  • 연산자 호출 (operator invocation)
  • 함수 호출 (function call)
  • 집계 표현식 (aggregate expression)
  • 윈도우 함수 호출 (window function call)
  • 형 변환 (type cast)
  • 콜레이션 표현식 (collation expression)
  • 스칼라 서브쿼리 (scalar subquery)
  • 배열 생성자 (array constructor)
  • 행 생성자 (row constructor)
  • 괄호로 묶인 또 다른 값 표현식 (하위 표현식을 묶고 우선순위를 조정하는 데 사용)

이 목록에 더해, 표현식으로 분류되지만 일반적인 문법 규칙을 따르지 않는 구성도 여럿 있어요. 이들은 대체로 함수나 연산자의 의미론을 갖고 있어서 Chapter 9에서 해당 위치에 설명돼요. 대표적인 예가 IS NULL 절이에요.

상수는 이미 4.1.2절에서 다뤘으니, 이 절에서는 나머지 항목들을 하나씩 살펴볼게요.

컬럼 참조 (Column References)

컬럼은 이런 형태로 참조할 수 있어요.

correlation.columnname

여기서 correlation은 테이블 이름(스키마 이름으로 한정될 수도 있어요)이거나, FROM 절로 정의한 테이블의 별칭(alias)이에요. 컬럼 이름이 현재 쿼리에서 사용 중인 모든 테이블에 걸쳐 유일하다면, correlation 이름과 구분 점(dot)은 생략할 수 있어요. (Chapter 7 참고.)

위치 파라미터 (Positional Parameters)

위치 파라미터 참조는 SQL 문장에 외부에서 공급되는 값을 가리켜요. 파라미터는 SQL 함수 정의와 준비된 쿼리(prepared query)에서 사용돼요. 일부 클라이언트 라이브러리는 SQL 명령 문자열과 별도로 데이터 값을 지정하는 것도 지원하는데, 이때 파라미터로 그 out-of-line 데이터 값을 참조해요. 파라미터 참조의 형태는 다음과 같아요.

$number

예를 들어 dept 함수를 이렇게 정의했다고 해볼게요.

CREATE FUNCTION dept(text) RETURNS dept
    AS $$ SELECT * FROM dept WHERE name = $1 $$
    LANGUAGE SQL;

여기서 $1은 함수가 호출될 때마다 첫 번째 함수 인자의 값을 가리켜요.

첨자 (Subscripts)

표현식이 배열 타입 값을 만들면, 배열 값의 특정 요소를 이렇게 뽑아낼 수 있어요.

expression[subscript]

또는 여러 인접 요소("배열 슬라이스")를 이렇게 뽑을 수도 있어요.

expression[lower_subscript:upper_subscript]

(여기서 대괄호 [ ]는 문자 그대로 쓰는 거예요.) 각 subscript는 그 자체로 표현식이고, 가장 가까운 정수 값으로 반올림돼요.

일반적으로 배열 expression은 괄호로 묶어야 하지만, 첨자를 붙일 표현식이 그냥 컬럼 참조나 위치 파라미터라면 괄호는 생략할 수 있어요. 또 원래 배열이 다차원이라면 여러 첨자를 연결할 수 있어요. 예를 들면:

mytable.arraycolumn[4]
mytable.two_d_column[17][34]
$1[10:42]
(arrayfunction(a,b))[42]

마지막 예시의 괄호는 반드시 필요해요. 배열에 대한 더 자세한 내용은 8.15절을 참고하세요.

필드 선택 (Field Selection)

표현식이 복합 타입(composite type, 행 타입) 값을 만들면, 행의 특정 필드를 이렇게 뽑아낼 수 있어요.

expression.fieldname

일반적으로 행 expression은 괄호로 묶어야 하지만, 선택할 표현식이 그냥 테이블 참조나 위치 파라미터라면 괄호는 생략할 수 있어요. 예:

mytable.mycolumn
$1.somecolumn
(rowfunction(a,b)).col3

(따라서 한정된 컬럼 참조는 사실 필드 선택 문법의 특수한 경우에요.) 중요한 특수 사례는 복합 타입인 테이블 컬럼에서 필드를 뽑아내는 경우예요.

(compositecol).somefield
(mytable.compositecol).somefield

여기서 괄호가 필요한 이유는, 첫 번째 경우 compositecol이 테이블 이름이 아니라 컬럼 이름이고, 두 번째 경우 mytable이 스키마 이름이 아니라 테이블 이름임을 보여주기 위해서예요.

복합 값의 모든 필드를 요청하고 싶다면 .*를 써요.

(compositecol).*

이 표기법은 맥락에 따라 다르게 동작해요. 자세한 내용은 8.16.5절을 참고하세요.

연산자 호출 (Operator Invocations)

연산자 호출에는 두 가지 가능한 문법이 있어요.

문법 형태
expression operator expression 이항 중위(binary infix) 연산자
operator expression 단항 접두(unary prefix) 연산자

여기서 operator 토큰은 4.1.3절의 문법 규칙을 따르거나, 키워드 AND, OR, NOT 중 하나이거나, 다음과 같은 형태의 한정된 연산자 이름이에요.

OPERATOR(schema.operatorname)

어떤 연산자가 존재하고 단항인지 이항인지는 시스템이나 사용자가 정의한 연산자에 따라 달라져요. 내장 연산자는 Chapter 9에서 설명해요.

함수 호출 (Function Calls)

함수 호출 문법은 함수 이름(스키마 이름으로 한정될 수 있음) 뒤에 괄호로 감싼 인자 목록이 오는 형태예요.

function_name ([expression [, expression ... ]] )

예를 들어 다음은 2의 제곱근을 계산해요.

sqrt(2)

내장 함수 목록은 Chapter 9에 있어요. 사용자가 함수를 추가할 수도 있어요.

일부 사용자가 다른 사용자를 신뢰하지 않는 데이터베이스에서 쿼리를 실행할 때는, 함수 호출을 쓸 때 10.3절의 보안 예방 조치를 지켜야 해요.

인자에는 이름을 붙일 수도 있어요. 자세한 내용은 4.3절을 참고하세요.

참고: 복합 타입의 단일 인자를 받는 함수는 선택적으로 필드 선택 문법으로 호출할 수 있고, 반대로 필드 선택은 함수형 스타일로 작성할 수도 있어요. 즉 col(table)과 table.col은 서로 바꿔 쓸 수 있어요. 이 동작은 SQL 표준이 아니지만, 함수로 "계산된 필드(computed fields)"를 흉내 낼 수 있게 하기 위해 PostgreSQL에서 제공하는 거예요. 더 자세한 내용은 8.16.5절을 참고하세요.

집계 표현식 (Aggregate Expressions)

집계 표현식(aggregate expression) 은 쿼리가 선택한 행들에 대해 집계 함수(aggregate function)를 적용하는 것을 나타내요. 집계 함수는 여러 입력을 하나의 출력 값(예: 입력의 합이나 평균)으로 줄여주는 함수예요. 집계 표현식의 문법은 다음 중 하나예요.

aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ]

여기서 aggregate_name은 미리 정의된 집계(스키마 이름으로 한정될 수 있음)이고, expression은 그 자체로 집계 표현식이나 윈도우 함수 호출을 포함하지 않는 임의의 값 표현식이에요. 선택적인 order_by_clause와 filter_clause는 아래에서 설명할게요.

첫 번째 형태의 집계 표현식은 입력 행마다 집계를 한 번씩 호출해요. 두 번째 형태는 ALL이 기본값이므로 첫 번째와 같아요. 세 번째 형태는 입력 행에서 찾은 표현식의 각 고유 값(여러 표현식이면 고유 값 집합)마다 집계를 한 번씩 호출해요. 네 번째 형태는 입력 행마다 집계를 한 번씩 호출하는데, 특정 입력 값을 지정하지 않으므로 일반적으로 count(*) 집계 함수에만 유용해요. 마지막 형태는 아래에서 설명할 정렬 집합(ordered-set) 집계 함수와 함께 사용돼요.

대부분의 집계 함수는 null 입력을 무시해서, 표현식 중 하나 이상이 null을 만드는 행은 버려져요. 모든 내장 집계에 대해 별도 명시가 없는 한 이렇게 동작한다고 가정할 수 있어요.

예를 들어 count(*)는 전체 입력 행 수를, count(f1)은 f1이 null이 아닌 입력 행 수를 반환해요 (count가 null을 무시하므로). count(distinct f1)은 f1의 고유한 비-null 값의 수를 반환하고요.

보통 입력 행은 지정되지 않은 순서로 집계 함수에 전달돼요. 많은 경우 이는 문제가 되지 않아요. 예를 들어 min은 입력을 어떤 순서로 받든 같은 결과를 내죠. 그런데 array_agg나 string_agg 같은 일부 집계 함수는 입력 행의 순서에 따라 달라지는 결과를 만들어요. 이런 집계를 쓸 때는 선택적인 order_by_clause로 원하는 순서를 지정할 수 있어요. order_by_clause는 7.5절에서 설명하는 쿼리 수준 ORDER BY 절과 같은 문법이지만, 그 표현식은 항상 단순 표현식이고 출력 컬럼 이름이나 숫자일 수는 없어요. 예:

WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
SELECT array_agg(v ORDER BY v DESC) FROM vals;
  array_agg
-------------
 {4,3,3,2,1}

jsonb는 마지막으로 일치하는 키만 유지하므로, 키의 순서가 중요할 수 있어요.

WITH vals (k, v) AS ( VALUES ('key0','1'), ('key1','3'), ('key1','2') )
SELECT jsonb_object_agg(k, v ORDER BY v) FROM vals;
      jsonb_object_agg
----------------------------
 {"key0": "1", "key1": "3"}

여러 인자를 받는 집계 함수를 다룰 때는 ORDER BY 절이 모든 집계 인자 뒤에 와야 한다는 점을 기억하세요. 예를 들어 이렇게 쓰고:

SELECT string_agg(a, ',' ORDER BY a) FROM table;

이렇게는 쓰지 마세요.

SELECT string_agg(a ORDER BY a, ',') FROM table;  -- incorrect

후자는 문법적으로는 유효하지만, ORDER BY 키가 두 개인 단일 인자 집계 함수 호출을 나타내요 (두 번째 키는 상수라서 거의 쓸모없구요).

DISTINCT를 order_by_clause와 함께 지정하면, ORDER BY 표현식은 DISTINCT 목록의 컬럼만 참조할 수 있어요. 예:

WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
SELECT array_agg(DISTINCT v ORDER BY v DESC) FROM vals;
 array_agg
-----------
 {4,3,2,1}

지금까지 설명한 대로 집계의 일반 인자 목록 안에 ORDER BY를 두는 것은, 일반용·통계 집계에서 입력 행의 순서를 정할 때 사용되며 이때 정렬은 선택적이에요. 그런데 정렬 집합(ordered-set) 집계라는 하위 분류가 있는데, 여기서는 order_by_clause가 필수예요. 보통 그 집계의 계산이 입력 행의 특정 순서에 대해서만 의미 있기 때문이죠. 정렬 집합 집계의 전형적인 예는 rank(순위)와 percentile(백분위) 계산이에요. 정렬 집합 집계에서 order_by_clause는 위 마지막 문법 대안에 보이듯 WITHIN GROUP (...) 안에 써요. order_by_clause의 표현식은 일반 집계 인자처럼 입력 행마다 한 번씩 평가되고, order_by_clause의 요구에 따라 정렬된 뒤 입력 인자로 집계 함수에 전달돼요. (이것은 집계 함수의 인자로 취급되지 않는, WITHIN GROUP이 붙지 않은 order_by_clause의 경우와는 달라요.) WITHIN GROUP 앞에 오는 인자 표현식(있으면)을 직접 인자(direct arguments) 라고 부르는데, order_by_clause에 나열된 집계 인자(aggregated arguments) 와 구분하기 위해서예요. 일반 집계 인자와 달리 직접 인자는 입력 행마다가 아니라 집계 호출당 한 번만 평가돼요. 따라서 직접 인자는 GROUP BY로 그룹화된 변수만 포함할 수 있어요. 이 제한은 직접 인자가 집계 표현식 안에 있지 않은 것과 같은 규칙이에요. 직접 인자는 보통 percentile 분수 같은 것에 쓰이는데, 이는 집계 계산 하나당 단일 값으로만 의미가 있어요. 직접 인자 목록은 비어 있을 수 있는데, 이때는 (*)가 아니라 그냥 ()로 써요. (PostgreSQL은 실제로 두 표기 모두 받아들이지만, SQL 표준에 맞는 것은 첫 번째뿐이에요.)

정렬 집합 집계 호출의 예:

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households;
 percentile_cont
-----------------
           50489

이 쿼리는 households 테이블의 income 컬럼에서 50번째 백분위, 즉 중앙값(median)을 구해요. 여기서 0.5는 직접 인자예요. 백분위 분수가 행마다 달라지는 값이라면 말이 안 되죠.

FILTER를 지정하면 filter_clause가 참으로 평가되는 입력 행만 집계 함수에 전달되고 나머지 행은 버려져요. 예:

SELECT
    count(*) AS unfiltered,
    count(*) FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i);
 unfiltered | filtered
------------+----------
         10 |        4
(1 row)

사전 정의된 집계 함수는 9.21절에서 설명해요. 사용자가 집계 함수를 추가할 수도 있어요.

집계 표현식은 SELECT 명령의 결과 목록이나 HAVING 절에만 나타날 수 있어요. WHERE 같은 다른 절에서는 금지되는데, 그런 절은 집계 결과가 만들어지기 전에 논리적으로 평가되기 때문이에요.

집계 표현식이 서브쿼리(4.2.11절과 9.24절 참고)에 나타나면, 집계는 보통 서브쿼리의 행들에 대해 평가돼요. 그런데 집계의 인자(및 있으면 filter_clause)가 외부 수준 변수만 포함하는 경우는 예외예요. 이 경우 집계는 가장 가까운 그런 외부 수준에 속하고, 그 쿼리의 행들에 대해 평가돼요. 그러면 집계 표현식 전체는 그것이 나타나는 서브쿼리에 대한 외부 참조가 되어, 해당 서브쿼리의 어느 한 평가 동안에는 상수로 작동해요. 결과 목록이나 HAVING 절에만 나타나야 한다는 제한은 집계가 속하는 쿼리 수준에 대해 적용돼요.

윈도우 함수 호출 (Window Function Calls)

윈도우 함수 호출(window function call) 은 쿼리가 선택한 행의 일부에 대해 집계 같은 함수를 적용하는 것을 나타내요. 비-윈도우 집계 호출과 달리, 선택된 행을 하나의 출력 행으로 그룹화하는 데 묶이지 않아요 — 각 행은 쿼리 출력에서 개별 행으로 남아요. 그러나 윈도우 함수는 윈도우 함수 호출의 그룹 지정(PARTITION BY 목록)에 따라 현재 행의 그룹에 속하게 될 모든 행에 접근할 수 있어요. 윈도우 함수 호출의 문법은 다음 중 하나예요.

function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )

여기서 window_definition은 다음과 같은 문법을 가져요.

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

선택적인 frame_clause는 다음 중 하나예요.

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]

여기서 frame_start와 frame_end는 다음 중 하나일 수 있어요.

UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING

그리고 frame_exclusion은 다음 중 하나예요.

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

여기서 expression은 그 자체로 윈도우 함수 호출을 포함하지 않는 임의의 값 표현식을 나타내요.

window_name은 쿼리의 WINDOW 절에 정의된 이름 있는 윈도우 지정을 가리켜요. 또는 WINDOW 절에서 이름 있는 윈도우를 정의할 때와 같은 문법으로 괄호 안에 전체 window_definition을 줄 수 있어요. 자세한 내용은 SELECT 참조 페이지를 참고하세요. OVER wname이 OVER (wname ...)과 정확히 같지는 않다는 점을 짚어둘게요. 후자는 윈도우 정의를 복사·수정하는 것을 의미하며, 참조된 윈도우 지정이 frame 절을 포함하면 거부돼요.

PARTITION BY 절은 쿼리의 행을 파티션(partition) 으로 그룹화하고, 각 파티션은 윈도우 함수가 별도로 처리해요. PARTITION BY는 쿼리 수준 GROUP BY 절과 비슷하게 동작하지만, 그 표현식은 항상 단순 표현식이고 출력 컬럼 이름이나 숫자일 수 없어요. PARTITION BY가 없으면 쿼리가 만든 모든 행이 하나의 파티션으로 취급돼요. ORDER BY 절은 파티션의 행이 윈도우 함수에 의해 처리되는 순서를 결정해요. 이것도 쿼리 수준 ORDER BY 절과 비슷하게 동작하지만 역시 출력 컬럼 이름이나 숫자를 쓸 수 없어요. ORDER BY가 없으면 행은 지정되지 않은 순서로 처리돼요.

frame_clause는 윈도우 프레임(window frame) 을 구성하는 행 집합을 지정해요. 프레임은 현재 파티션의 부분집합이며, 프레임이 아닌 파티션 전체에 대해 작동하는 윈도우 함수를 위한 거예요. 프레임의 행 집합은 현재 행이 무엇이냐에 따라 달라질 수 있어요. 프레임은 RANGE, ROWS, GROUPS 모드로 지정할 수 있고, 각 경우 frame_start부터 frame_end까지 이어져요. frame_end를 생략하면 끝은 기본적으로 CURRENT ROW가 돼요.

frame_start가 UNBOUNDED PRECEDING이면 프레임이 파티션의 첫 행에서 시작한다는 뜻이고, 비슷하게 frame_end가 UNBOUNDED FOLLOWING이면 프레임이 파티션의 마지막 행에서 끝난다는 뜻이에요.

RANGE 또는 GROUPS 모드에서 frame_start가 CURRENT ROW면 프레임이 현재 행의 첫 번째 동료(peer) 행 (윈도우의 ORDER BY 절이 현재 행과 동등하다고 정렬하는 행)에서 시작한다는 뜻이고, frame_end가 CURRENT ROW면 현재 행의 마지막 동료 행에서 끝난다는 뜻이에요. ROWS 모드에서 CURRENT ROW는 단순히 현재 행을 의미해요.

offset PRECEDING과 offset FOLLOWING 프레임 옵션에서 offset은 변수·집계 함수·윈도우 함수를 포함하지 않는 표현식이어야 해요. offset의 의미는 프레임 모드에 따라 달라져요.

  • ROWS 모드에서 offset은 null이 아니고 음이 아닌 정수를 내야 하며, 프레임이 현재 행보다 지정된 수만큼 앞이나 뒤의 행에서 시작하거나 끝난다는 뜻이에요.
  • GROUPS 모드에서도 offset은 null이 아니고 음이 아닌 정수를 내야 하며, 프레임이 현재 행의 동료 그룹보다 지정된 수만큼 앞이나 뒤의 동료 그룹(peer group) 에서 시작하거나 끝난다는 뜻이에요 (동료 그룹은 ORDER BY 정렬에서 동등한 행들의 집합이에요). GROUPS 모드를 쓰려면 윈도우 정의에 ORDER BY 절이 있어야 해요.
  • RANGE 모드에서 이 옵션들은 ORDER BY 절이 정확히 하나의 컬럼을 지정하도록 요구해요. offset은 현재 행의 해당 컬럼 값과 프레임의 앞·뒤 행 값 사이의 최대 차이를 지정해요. offset 표현식의 데이터 타입은 정렬 컬럼의 데이터 타입에 따라 달라져요. 숫자 정렬 컬럼이면 보통 정렬 컬럼과 같은 타입이지만, 날짜/시간 정렬 컬럼이면 interval이에요. 예를 들어 정렬 컬럼이 date나 timestamp 타입이면 RANGE BETWEEN '1 day' PRECEDING AND '10 days' FOLLOWING처럼 쓸 수 있어요. offset은 여전히 null이 아니고 음이 아니어야 하는데, "음이 아님"의 의미는 데이터 타입에 따라 달라져요.

어떤 경우든 프레임 끝까지의 거리는 파티션 끝까지의 거리에 의해 제한돼요. 그래서 파티션 끝 근처의 행에서는 프레임에 다른 곳보다 적은 행이 포함될 수 있어요.

ROWS와 GROUPS 모드 모두에서 0 PRECEDING과 0 FOLLOWING은 CURRENT ROW와 같다는 점을 주목하세요. RANGE 모드에서도 데이터 타입별 "영(zero)"의 의미에 따라 일반적으로 이게 성립해요.

frame_exclusion 옵션을 쓰면 frame 시작·끝 옵션에 따라 포함될 행이라도 현재 행 주변의 행을 프레임에서 제외할 수 있어요. EXCLUDE CURRENT ROW는 현재 행을 프레임에서 제외하고, EXCLUDE GROUP은 현재 행과 그 정렬 동료들을 제외하며, EXCLUDE TIES는 현재 행의 동료들을 제외하되 현재 행 자신은 제외하지 않아요. EXCLUDE NO OTHERS는 현재 행이나 동료를 제외하지 않는 기본 동작을 명시적으로 지정할 뿐이에요.

기본 프레임 옵션은 RANGE UNBOUNDED PRECEDING인데, 이는 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW와 같아요. ORDER BY가 있으면 프레임이 파티션 시작부터 현재 행의 마지막 ORDER BY 동료까지의 모든 행으로 설정돼요. ORDER BY가 없으면 파티션의 모든 행이 프레임에 포함되는데, 모든 행이 현재 행의 동료가 되기 때문이에요.

제한 사항으로는 frame_start는 UNBOUNDED FOLLOWING일 수 없고, frame_end는 UNBOUNDED PRECEDING일 수 없으며, frame_end 선택은 위 frame_start/frame_end 옵션 목록에서 frame_start 선택보다 앞에 올 수 없어요 — 예를 들어 RANGE BETWEEN CURRENT ROW AND offset PRECEDING은 허용되지 않아요. 하지만 예를 들어 ROWS BETWEEN 7 PRECEDING AND 8 PRECEDING은 어떤 행도 선택하지 않더라도 허용돼요.

FILTER를 지정하면 filter_clause가 참으로 평가되는 입력 행만 윈도우 함수에 전달되고 나머지 행은 버려져요. FILTER 절을 받아들이는 윈도우 함수는 집계인 것들뿐이에요.

내장 윈도우 함수는 Table 9.67에서 설명해요. 사용자가 윈도우 함수를 추가할 수도 있어요. 또 내장 또는 사용자 정의 일반용·통계 집계를 윈도우 함수로 쓸 수 있어요. (정렬 집합과 가상 집합(hypothetical-set) 집계는 현재 윈도우 함수로 쓸 수 없어요.)

*를 쓰는 문법은 인자 없는 집계 함수를 윈도우 함수로 호출할 때 사용돼요. 예를 들어 count(*) OVER (PARTITION BY x ORDER BY y)처럼요. 별표(*)는 보통 윈도우 전용 함수에는 쓰지 않아요. 윈도우 전용 함수는 함수 인자 목록 안에서 DISTINCT나 ORDER BY를 허용하지 않아요.

윈도우 함수 호출은 쿼리의 SELECT 목록과 ORDER BY 절에서만 허용돼요.

윈도우 함수에 대한 더 자세한 내용은 3.5절, 9.22절, 7.2.5절에서 확인할 수 있어요.

형 변환 (Type Casts)

형 변환(type cast)은 한 데이터 타입에서 다른 데이터 타입으로의 변환을 지정해요. PostgreSQL은 형 변환에 두 가지 동등한 문법을 받아들여요.

CAST ( expression AS type )
expression::type

CAST 문법은 SQL 표준을 따르고, :: 문법은 역사적인 PostgreSQL 관용구예요.

알려진 타입의 값 표현식에 캐스트가 적용되면 런타임 타입 변환을 나타내요. 적절한 타입 변환 연산이 정의된 경우에만 캐스트가 성공해요. 이것은 4.1.2.7절에서 보여주는 상수와 캐스트 사용과는 미묘하게 다르다는 점을 주목하세요. 꾸밈 없는 문자열 리터럴에 적용된 캐스트는 리터럴 상수 값에 타입을 처음 할당하는 것을 나타내므로, 타입에 대해 (문자열 리터럴 내용이 그 데이터 타입의 허용 가능한 입력 문법이라면) 어떤 타입이든 성공해요.

값 표현식이 만들어야 하는 타입이 모호하지 않다면(예를 들어 테이블 컬럼에 할당될 때) 명시적 형 변환은 보통 생략할 수 있어요. 그런 경우 시스템이 자동으로 형 변환을 적용해요. 단, 자동 캐스팅은 시스템 카탈로그에서 "암묵적으로 적용해도 OK"로 표시된 캐스팅에만 적용돼요. 다른 캐스팅은 명시적 캐스트 문법으로 호출해야 해요. 이 제한은 예상치 못한 변환이 조용히 일어나지 않게 막기 위한 거예요.

함수 같은 문법으로 형 변환을 지정할 수도 있어요.

typename ( expression )

하지만 이것은 이름이 함수 이름으로도 유효한 타입에만 적용돼요. 예를 들어 double precision은 이런 식으로 쓸 수 없지만 동등한 float8은 쓸 수 있어요. 또 interval, time, timestamp 이름은 문법 충돌 때문에 큰따옴표로 감싼 경우에만 이런 방식으로 쓸 수 있어요. 그래서 함수 같은 캐스트 문법은 불일치를 만들 수 있으니 피하는 게 좋아요.

참고: 함수 같은 문법은 사실 그냥 함수 호출이에요. 두 표준 캐스트 문법 중 하나로 런타임 변환을 수행하면 내부적으로 등록된 함수를 호출해 변환을 수행해요. 관례상 이 변환 함수들은 출력 타입과 같은 이름을 갖고, 그래서 "함수 같은 문법"은 근본 변환 함수를 직접 호출하는 것에 지나지 않아요. 물론 이식 가능한 애플리케이션이 이것에 의존해서는 안 돼요. 자세한 내용은 CREATE CAST를 참고하세요.

콜레이션 표현식 (Collation Expressions)

COLLATE 절은 표현식의 콜레이션(collation, 문자열 정렬 규칙)을 재정의해요. 적용되는 표현식 뒤에 붙여요.

expr COLLATE collation

여기서 collation은 스키마로 한정될 수 있는 식별자예요. COLLATE 절은 연산자보다 더 강하게 묶이므로, 필요하면 괄호를 쓸 수 있어요.

콜레이션이 명시적으로 지정되지 않으면, 데이터베이스 시스템은 표현식에 관련된 컬럼들에서 콜레이션을 유도하거나, 표현식에 컬럼이 없으면 데이터베이스의 기본 콜레이션을 사용해요.

COLLATE 절의 일반적인 용도 두 가지는, ORDER BY 절에서 정렬 순서를 재정의하는 것과, 예를 들면:

SELECT a, b, c FROM tbl WHERE ... ORDER BY a COLLATE "C";

로케일 민감한 결과를 내는 함수나 연산자 호출의 콜레이션을 재정의하는 것이에요. 예:

SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";

후자의 경우 COLLATE 절이 우리가 영향 주려는 연산자의 입력 인자 하나에 붙는 점을 주목하세요. 연산자나 함수 호출의 어느 인자에 COLLATE 절이 붙든 상관없어요. 연산자나 함수가 적용하는 콜레이션은 모든 인자를 고려해 유도되고, 명시적 COLLATE 절이 다른 모든 인자의 콜레이션을 덮어쓰기 때문이에요. (단, 일치하지 않는 COLLATE 절을 여러 인자에 붙이는 것은 오류예요. 자세한 내용은 23.2절 참고.) 그래서 다음도 이전 예와 같은 결과를 내요.

SELECT * FROM tbl WHERE a COLLATE "C" > 'foo';

하지만 이것은 오류예요.

SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C";

> 연산자의 결과인 boolean(콜레이션 불가 데이터 타입)에 콜레이션을 적용하려 하기 때문이에요.

스칼라 서브쿼리 (Scalar Subqueries)

스칼라 서브쿼리(scalar subquery)는 정확히 한 행과 한 컬럼을 반환하는, 괄호 안의 일반 SELECT 쿼리예요. (쿼리 작성에 대한 정보는 Chapter 7 참고.) SELECT 쿼리가 실행되고 반환된 단일 값이 주변 값 표현식에서 사용돼요. 한 행 이상이나 한 컬럼 이상을 반환하는 쿼리를 스칼라 서브쿼리로 쓰는 것은 오류예요. (단, 특정 실행 중 서브쿼리가 아무 행도 반환하지 않으면 오류가 아니라 스칼라 결과가 null로 취급돼요.) 서브쿼리는 주변 쿼리의 변수를 참조할 수 있는데, 이 변수들은 서브쿼리를 한 번 평가하는 동안에는 상수로 작동해요. 서브쿼리를 포함하는 다른 표현식은 9.24절도 참고하세요.

예를 들어 다음은 각 주(state)에서 가장 큰 도시 인구를 찾아요.

SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
    FROM states;

배열 생성자 (Array Constructors)

배열 생성자(array constructor)는 멤버 요소의 값들을 사용해 배열 값을 만드는 표현식이에요. 간단한 배열 생성자는 키워드 ARRAY, 왼쪽 대괄호 [, 배열 요소 값들에 대한 쉼표로 구분된 표현식 목록, 마지막으로 오른쪽 대괄호 ]로 구성돼요. 예:

SELECT ARRAY[1,2,3+4];
  array
---------
 {1,2,7}
(1 row)

기본적으로 배열 요소 타입은 멤버 표현식들의 공통 타입인데, UNION이나 CASE 구성과 같은 규칙으로 결정돼요 (10.5절 참고). 배열 생성자를 원하는 타입으로 명시적 캐스팅해서 재정의할 수 있어요. 예:

SELECT ARRAY[1,2,22.7]::integer[];
  array
----------
 {1,2,23}
(1 row)

이것은 각 표현식을 배열 요소 타입으로 개별 캐스팅하는 것과 같은 효과예요. 캐스팅에 대한 자세한 내용은 4.2.9절을 참고하세요.

다차원 배열 값은 배열 생성자를 중첩해 만들 수 있어요. 안쪽 생성자에서는 키워드 ARRAY를 생략할 수 있어요. 예를 들어 다음 두 쿼리는 같은 결과를 내요.

SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];
     array
---------------
 {{1,2},{3,4}}
(1 row)

SELECT ARRAY[[1,2],[3,4]];
     array
---------------
 {{1,2},{3,4}}
(1 row)

다차원 배열은 직사각형이어야 하므로, 같은 수준의 안쪽 생성자는 동일한 차원의 부분 배열을 만들어야 해요. 바깥 ARRAY 생성자에 적용된 캐스트는 자동으로 모든 안쪽 생성자로 전파돼요.

다차원 배열 생성자 요소는 하위 ARRAY 구성뿐 아니라 적절한 종류의 배열을 만드는 무엇이든 될 수 있어요. 예:

CREATE TABLE arr(f1 int[], f2 int[]);

INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]);

SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM arr;
                     array
------------------------------------------------
 {{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}
(1 row)

빈 배열을 만들 수도 있지만, 타입이 없는 배열은 불가능하므로 빈 배열은 원하는 타입으로 명시적으로 캐스팅해야 해요. 예:

SELECT ARRAY[]::integer[];
 array
-------
 {}
(1 row)

서브쿼리 결과에서 배열을 만드는 것도 가능해요. 이 형태에서 배열 생성자는 키워드 ARRAY 뒤에 괄호로 묶인 (대괄호가 아닌) 서브쿼리로 써요. 예:

SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
                              array
------------------------------------------------------------------
 {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31,2412}
(1 row)

SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i));
              array
----------------------------------
 {{1,2},{2,4},{3,6},{4,8},{5,10}}
(1 row)

서브쿼리는 단일 컬럼을 반환해야 해요. 서브쿼리의 출력 컬럼이 배열 타입이 아니면, 결과 1차원 배열은 서브쿼리 결과의 각 행에 대해 요소가 하나씩 생기고 요소 타입은 서브쿼리 출력 컬럼과 일치해요. 서브쿼리 출력 컬럼이 배열 타입이면 결과는 같은 타입이되 차원이 하나 더 높은 배열이에요. 이 경우 모든 서브쿼리 행이 동일한 차원의 배열을 만들어야 하고, 그렇지 않으면 결과가 직사각형이 아니게 돼요.

ARRAY로 만든 배열 값의 첨자는 항상 1부터 시작해요. 배열에 대한 더 자세한 내용은 8.15절을 참고하세요.

행 생성자 (Row Constructors)

행 생성자(row constructor)는 멤버 필드의 값들을 사용해 행 값(복합 값이라고도 함)을 만드는 표현식이에요. 행 생성자는 키워드 ROW, 왼쪽 괄호, 행 필드 값들에 대한 0개 이상의 쉼표로 구분된 표현식, 마지막으로 오른쪽 괄호로 구성돼요. 예:

SELECT ROW(1,2.5,'this is a test');

키워드 ROW는 목록에 표현식이 두 개 이상이면 선택적이에요.

행 생성자는 rowvalue.* 문법을 포함할 수 있는데, 이는 SELECT 목록의 최상위 수준에서 .* 문법을 쓸 때와 마찬가지로 행 값의 요소 목록으로 확장돼요 (8.16.5절 참고). 예를 들어 테이블 t에 컬럼 f1과 f2가 있다면 다음 둘은 같아요.

SELECT ROW(t.*, 42) FROM t;
SELECT ROW(t.f1, t.f2, 42) FROM t;

참고: PostgreSQL 8.2 이전에는 .* 문법이 행 생성자에서 확장되지 않아서, ROW(t.*, 42)를 쓰면 첫 번째 필드가 또 다른 행 값인 두 필드 행을 만들었어요. 새 동작이 보통 더 유용해요. 중첩 행 값의 옛 동작이 필요하면 안쪽 행 값을 .* 없이 써요. 예를 들어 ROW(t, 42)처럼요.

기본적으로 ROW 표현식이 만드는 값은 익명의 record 타입이에요. 필요하면 이름 있는 복합 타입으로 캐스팅할 수 있는데 — 테이블의 행 타입이거나 CREATE TYPE AS로 만든 복합 타입이에요. 모호함을 피하려면 명시적 캐스트가 필요할 수 있어요. 예:

CREATE TABLE mytable(f1 int, f2 float, f3 text);

CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;

-- No cast needed since only one getf1() exists
SELECT getf1(ROW(1,2.5,'this is a test'));
 getf1
-------
     1
(1 row)

CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);

CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;

-- Now we need a cast to indicate which function to call:
SELECT getf1(ROW(1,2.5,'this is a test'));
ERROR:  function getf1(record) is not unique

SELECT getf1(ROW(1,2.5,'this is a test')::mytable);
 getf1
-------
     1
(1 row)

SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));
 getf1
-------
    11
(1 row)

행 생성자는 복합 타입 테이블 컬럼에 저장할 복합 값을 만들거나, 복합 파라미터를 받는 함수에 전달하는 데 쓸 수 있어요. 또 9.2절에서 설명하는 표준 비교 연산자로 행을 검사하거나, 9.25절에서 설명하는 대로 행을 서로 비교하고, 9.24절에서 논의하는 대로 서브쿼리와 함께 사용할 수도 있어요.

표현식 평가 규칙 (Expression Evaluation Rules)

하위 표현식의 평가 순서는 정의되어 있지 않아요. 특히 연산자나 함수의 입력이 반드시 왼쪽에서 오른쪽으로 또는 어떤 고정된 순서로 평가되지는 않아요.

또한 표현식의 결과가 그 일부만 평가해서 결정될 수 있다면, 다른 하위 표현식은 전혀 평가되지 않을 수도 있어요. 예를 들어 이렇게 썼다면:

SELECT true OR somefunc();

somefunc()는 (아마도) 전혀 호출되지 않아요. 이렇게 써도 마찬가지예요.

SELECT somefunc() OR true;

이것은 일부 프로그래밍 언어에서 볼 수 있는 불리언 연산자의 왼쪽에서 오른쪽 "단락 평가(short-circuiting)"와는 다르다는 점을 주목하세요.

결과적으로 부작용(side effects)이 있는 함수를 복잡한 표현식의 일부로 쓰는 것은 현명하지 않아요. 특히 WHERE와 HAVING 절에서 부작용이나 평가 순서에 의존하는 것은 아주 위험해요. 그 절들은 실행 계획을 세우는 과정에서 광범위하게 재처리되기 때문이죠. 그 절의 불리언 표현식(AND/OR/NOT 조합)은 불리언 대수 법칙이 허용하는 어떤 방식으로든 재구성될 수 있어요.

평가 순서를 강제하는 것이 필수일 때는 CASE 구성(9.18절 참고)을 쓸 수 있어요. 예를 들어 이것은 WHERE 절에서 0으로 나누기를 피하려는 신뢰할 수 없는 방법이에요.

SELECT ... WHERE x > 0 AND y/x > 1.5;

하지만 이것은 안전해요.

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

이런 방식으로 쓴 CASE 구성은 최적화 시도를 무력화하므로, 필요할 때만 해야 해요. (이 특정 예에서는 y > 1.5*x라고 쓰는 편이 문제를 우회하는 더 나은 방법이에요.)

그러나 CASE가 이런 문제의 만병통치약은 아니에요. 위에서 보여준 기법의 한계 중 하나는 상수 하위 표현식의 조기 평가를 막지 못한다는 거예요. 36.7절에서 설명하듯 IMMUTABLE로 표시된 함수와 연산자는 실행 때가 아니라 쿼리 계획 때 평가될 수 있어요. 그래서 예를 들어:

SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab;

테이블의 모든 행에서 x > 0이라서 런타임에 ELSE 분기가 절대 들어가지 않더라도, 플래너가 상수 하위 표현식을 단순화하려 하기 때문에 0으로 나누기 오류가 날 가능성이 높아요.

그 특정 예가 우스워 보일 수 있지만, 함수 안에서 실행되는 쿼리에서는 상수와 분명히 관련 없는 것처럼 보이는 유사한 경우가 생길 수 있어요. 함수 인자와 지역 변수의 값이 계획 목적으로 상수로 쿼리에 삽입될 수 있기 때문이죠. 예를 들어 PL/pgSQL 함수 안에서는 위험한 계산을 보호하기 위해 CASE 표현식에 중첩하는 것보다 IF-THEN-ELSE 문장을 쓰는 편이 훨씬 안전해요.

같은 종류의 또 다른 한계는, CASE가 그 안에 포함된 집계 표현식의 평가를 막지 못한다는 거예요. 집계 표현식은 SELECT 목록이나 HAVING 절의 다른 표현식보다 먼저 계산되기 때문이죠. 예를 들어 다음 쿼리는 겉보기엔 보호한 것처럼 보여도 0으로 나누기 오류를 일으킬 수 있어요.

SELECT CASE WHEN min(employees) > 0
            THEN avg(expenses / employees)
       END
    FROM departments;

min()과 avg() 집계는 모든 입력 행에 대해 동시에 계산돼요. 그래서 어떤 행이 employees가 0이면, min()의 결과를 검사할 기회도 전에 0으로 나누기 오류가 발생해요. 대신 WHERE나 FILTER 절을 써서 문제가 되는 입력 행이 애초에 집계 함수에 도달하지 않게 막아야 해요.

더 알아보기 (Learn more)