SQL function reference

This reference comes from the executable compiler registry. Argument domains describe SQL constraints; the shared semantic binder resolves coercions and result types from declared input and parameter contracts. Contexts describe supported syntax, not executor eligibility. An external executor must admit the complete plan and its operation requirements. Constant arguments may be prepared parameters fixed for one request. Exact bounded fallbacks do not promise probabilistic execution. Explicit evidence overloads return accuracy evidence through the ordinary semantic contract.

Scalar functions

abs

abs(number)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

acos

acos(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

acosh

acosh(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

add_months

add_months(date, integer)

Arguments	Domains in order	Contexts	Evaluation
2	date, integer	scalar	pure

array

array(value, ...)

Arguments	Domains in order	Contexts	Evaluation
0–4096	any	scalar	pure

array_concat

array_concat(array, array, ...)

Aliases: array_cat.

Arguments	Domains in order	Contexts	Evaluation
2–64	array, array	scalar	pure

array_contains

array_contains(array, value)

Arguments	Domains in order	Contexts	Evaluation
2	array, any	scalar	pure

array_distinct

array_distinct(array)

Arguments	Domains in order	Contexts	Evaluation
1	array	scalar	pure

array_except

array_except(array, array)

Arguments	Domains in order	Contexts	Evaluation
2	array, array	scalar	pure

array_intersect

array_intersect(array, array)

Arguments	Domains in order	Contexts	Evaluation
2	array, array	scalar	pure

array_join

array_join(array, separator [, null_replacement])

Arguments	Domains in order	Contexts	Evaluation
2	array, text	scalar	pure
3	array, text, text	scalar	pure

array_max

array_max(array)

Arguments	Domains in order	Contexts	Evaluation
1	array	scalar	pure

array_min

array_min(array)

Arguments	Domains in order	Contexts	Evaluation
1	array	scalar	pure

array_position

array_position(array, value)

Arguments	Domains in order	Contexts	Evaluation
2	array, any	scalar	pure

array_remove

array_remove(array, value)

Arguments	Domains in order	Contexts	Evaluation
2	array, any	scalar	pure

array_repeat

array_repeat(value, integer_count)

Arguments	Domains in order	Contexts	Evaluation
2	any, integer	scalar	pure

array_reverse

array_reverse(array)

Arguments	Domains in order	Contexts	Evaluation
1	array	scalar	pure

array_sort

array_sort(array)

Arguments	Domains in order	Contexts	Evaluation
1	array	scalar	pure

array_union

array_union(array, array)

Arguments	Domains in order	Contexts	Evaluation
2	array, array	scalar	pure

arrays_overlap

arrays_overlap(array, array)

Arguments	Domains in order	Contexts	Evaluation
2	array, array	scalar	pure

asin

asin(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

asinh

asinh(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

atan

atan(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

atan2

atan2(double_y, double_x)

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric	scalar	pure

atanh

atanh(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

base64

base64(text_or_binary); unchunked standard alphabet

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure
1	binary	scalar	pure

bround

bround(number [, integer_scale])

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure
2	numeric, integer	scalar	pure

cardinality

cardinality(array)

Aliases: array_length, array_size, size.

Arguments	Domains in order	Contexts	Evaluation
1	array	scalar	pure

cbrt

cbrt(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

ceil

ceil(number)

Aliases: ceiling.

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

coalesce

coalesce(value, value, ...)

Aliases: ifnull, nvl.

Arguments	Domains in order	Contexts	Evaluation
2–64	any, any	scalar	pure

concat

concat(text, text, ...)

Arguments	Domains in order	Contexts	Evaluation
2–64	text, text	scalar	pure

concat_ws

concat_ws(separator, text, ...)

Arguments	Domains in order	Contexts	Evaluation
2–64	text, text	scalar	pure

contains

contains(text, substring)

Arguments	Domains in order	Contexts	Evaluation
2	text, text	scalar	pure

cos

cos(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

cosh

cosh(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

crc32

crc32(text_or_bytes)

Arguments	Domains in order	Contexts	Evaluation
1	text_or_binary	scalar	pure

current_date

current_date()

Arguments	Domains in order	Contexts	Evaluation
0	none	scalar	captured_context

current_timestamp

current_timestamp()

Aliases: now.

Arguments	Domains in order	Contexts	Evaluation
0	none	scalar	captured_context

date_add

date_add(date, integer)

Arguments	Domains in order	Contexts	Evaluation
2	date, integer	scalar	pure

date_format

date_format(date_or_timestamp, format [, constant_timezone])

Arguments	Domains in order	Contexts	Evaluation
2	temporal, text	scalar	pure
3	temporal, text, text (constant)	scalar	pure

date_sub

date_sub(date, integer)

Arguments	Domains in order	Contexts	Evaluation
2	date, integer	scalar	pure

date_trunc

date_trunc(constant_unit, timestamp [, constant_timezone])

Arguments	Domains in order	Contexts	Evaluation
2	text (constant), timestamp	scalar	pure
3	text (constant), timestamp, text (constant)	scalar	pure

datediff

datediff(end_date, start_date)

Aliases: date_diff.

Arguments	Domains in order	Contexts	Evaluation
2	text, text	scalar	pure

day

day(date)

Aliases: dayofmonth.

Arguments	Domains in order	Contexts	Evaluation
1	date	scalar	pure

dayofweek

dayofweek(date)

Arguments	Domains in order	Contexts	Evaluation
1	date	scalar	pure

dayofyear

dayofyear(date)

Arguments	Domains in order	Contexts	Evaluation
1	date	scalar	pure

degrees

degrees(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

e()

Arguments	Domains in order	Contexts	Evaluation
0	none	scalar	pure

ends_with

ends_with(text, suffix)

Aliases: endswith.

Arguments	Domains in order	Contexts	Evaluation
2	text, text	scalar	pure

exp

exp(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

expm1

expm1(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

flatten

flatten(array_of_arrays)

Arguments	Domains in order	Contexts	Evaluation
1	array	scalar	pure

floor

floor(number)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

format_timestamp

format_timestamp(timestamp, constant_format [, constant_timezone])

Arguments	Domains in order	Contexts	Evaluation
2	timestamp, text (constant)	scalar	pure
3	timestamp, text (constant), text (constant)	scalar	pure

from_json

from_json(text, constant_sql_container_schema)

Arguments	Domains in order	Contexts	Evaluation
2	text, text (constant)	scalar	pure

get

get(array, zero_based_index)

Arguments	Domains in order	Contexts	Evaluation
2	array, integer	scalar	pure

get_json_object

get_json_object(text, constant_json_path)

Arguments	Domains in order	Contexts	Evaluation
2	text, text (constant)	scalar	pure

greatest

greatest(value, value, ...)

Arguments	Domains in order	Contexts	Evaluation
2–16	any, any	scalar	pure

hex

hex(text_or_binary_or_integral); uppercase UTF-8 bytes or Int64 two's complement

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure
1	binary	scalar	pure
1	integer	scalar	pure

hour

hour(timestamp)

Arguments	Domains in order	Contexts	Evaluation
1	timestamp	scalar	pure

if(boolean, then_value, else_value)

Arguments	Domains in order	Contexts	Evaluation
3	boolean, any, any	scalar	pure

initcap

initcap(text)

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure

instr

instr(text, substring)

Arguments	Domains in order	Contexts	Evaluation
2	text, text	scalar	pure

json_array_length

json_array_length(json)

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure

json_at

json_at(json, constant_pointer)

Arguments	Domains in order	Contexts	Evaluation
2	text, text (constant)	scalar	pure

json_extract_path_text

json_extract_path_text(json, key, ...)

Arguments	Domains in order	Contexts	Evaluation
2–33	text, any	scalar	pure

json_object_keys

json_object_keys(json)

Aliases: json_keys.

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure

last_day

last_day(date)

Arguments	Domains in order	Contexts	Evaluation
1	date	scalar	pure

least

least(value, value, ...)

Arguments	Domains in order	Contexts	Evaluation
2–16	any, any	scalar	pure

left

left(text, length)

Arguments	Domains in order	Contexts	Evaluation
2	text, integer	scalar	pure

length

length(text)

Aliases: char_length, character_length, len.

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure

ln(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

locate

locate(substring, text [, start])

Arguments	Domains in order	Contexts	Evaluation
2	text, text	scalar	pure
3	text, text, integer	scalar	pure

log

log(double) or log(double_base, double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure
2	numeric, numeric	scalar	pure

log10

log10(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

log1p

log1p(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

log2

log2(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

lower

lower(text)

Aliases: lcase.

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure

lpad

lpad(text_or_bytes, integer_length [, same_domain_pad])

Arguments	Domains in order	Contexts	Evaluation
2	text_or_binary, integer	scalar	pure
3	text_or_binary, integer, same_as_first	scalar	pure

ltrim

ltrim(text)

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure

map

map(text_key, value, ...)

Arguments	Domains in order	Contexts	Evaluation
2–128, step 2	text, any	scalar	pure

map_at

map_at(map, text_key)

Arguments	Domains in order	Contexts	Evaluation
2	map, text	scalar	pure

map_from_arrays

map_from_arrays(keys, values)

Arguments	Domains in order	Contexts	Evaluation
2	array, array	scalar	pure

map_keys

map_keys(map)

Arguments	Domains in order	Contexts	Evaluation
1	map	scalar	pure

map_values

map_values(map)

Arguments	Domains in order	Contexts	Evaluation
1	map	scalar	pure

md5

md5(text_or_bytes)

Arguments	Domains in order	Contexts	Evaluation
1	text_or_binary	scalar	pure

minute

minute(timestamp)

Arguments	Domains in order	Contexts	Evaluation
1	timestamp	scalar	pure

mod

mod(number, number)

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric	scalar	pure

month

month(date)

Arguments	Domains in order	Contexts	Evaluation
1	date	scalar	pure

months_between

months_between(end_date_or_timestamp, start_date_or_timestamp [, constant_round_off [, constant_timezone]])

Arguments	Domains in order	Contexts	Evaluation
2	temporal, temporal	scalar	pure
3	temporal, temporal, boolean (constant)	scalar	pure
4	temporal, temporal, boolean (constant), text (constant)	scalar	pure

next_day

next_day(date, constant_weekday)

Arguments	Domains in order	Contexts	Evaluation
2	date, text (constant)	scalar	pure

nullif

nullif(value, value)

Arguments	Domains in order	Contexts	Evaluation
2	any, any	scalar	pure

nvl2

nvl2(value, present_value, null_value)

Arguments	Domains in order	Contexts	Evaluation
3	any, any, any	scalar	pure

parse_timestamp

parse_timestamp(text [, constant_format [, constant_timezone]])

Aliases: to_timestamp.

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure
2	text, text (constant)	scalar	pure
3	text, text (constant), text (constant)	scalar	pure

pi()

Arguments	Domains in order	Contexts	Evaluation
0	none	scalar	pure

position

position(substring IN text)

Arguments	Domains in order	Contexts	Evaluation
2	text, text	scalar	pure

power

power(number, exponent)

Aliases: pow.

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric	scalar	pure

quarter

quarter(date)

Arguments	Domains in order	Contexts	Evaluation
1	date	scalar	pure

radians

radians(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

regexp_count

regexp_count(text, pattern)

Arguments	Domains in order	Contexts	Evaluation
2	text, text	scalar	pure

regexp_extract

regexp_extract(text, pattern [, group])

Arguments	Domains in order	Contexts	Evaluation
2	text, text	scalar	pure
3	text, text, integer	scalar	pure

regexp_extract_all

regexp_extract_all(text, pattern [, group])

Arguments	Domains in order	Contexts	Evaluation
2	text, text	scalar	pure
3	text, text, integer	scalar	pure

regexp_like

regexp_like(text, pattern)

Aliases: regexp, rlike.

Arguments	Domains in order	Contexts	Evaluation
2	text, text	scalar	pure

regexp_replace

regexp_replace(text, pattern, replacement)

Arguments	Domains in order	Contexts	Evaluation
3	text, text, text	scalar	pure

repeat

repeat(text, integer_count)

Arguments	Domains in order	Contexts	Evaluation
2	text, integer	scalar	pure

replace

replace(text, search, replacement)

Arguments	Domains in order	Contexts	Evaluation
3	text, text, text	scalar	pure

reverse

reverse(text)

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure

right

right(text, length)

Arguments	Domains in order	Contexts	Evaluation
2	text, integer	scalar	pure

round

round(number [, integer_scale])

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure
2	numeric, integer	scalar	pure

rpad

rpad(text_or_bytes, integer_length [, same_domain_pad])

Arguments	Domains in order	Contexts	Evaluation
2	text_or_binary, integer	scalar	pure
3	text_or_binary, integer, same_as_first	scalar	pure

rtrim

rtrim(text)

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure

second

second(timestamp)

Arguments	Domains in order	Contexts	Evaluation
1	timestamp	scalar	pure

sequence

sequence(integer_start, integer_end [, integer_step])

Arguments	Domains in order	Contexts	Evaluation
2	integer, integer	scalar	pure
3	integer, integer, integer	scalar	pure

sha1

sha1(text_or_bytes)

Aliases: sha.

Arguments	Domains in order	Contexts	Evaluation
1	text_or_binary	scalar	pure

sha2

sha2(text_or_bytes, constant_bits_0_224_256_384_512)

Arguments	Domains in order	Contexts	Evaluation
2	text_or_binary, integer (constant)	scalar	pure

sha224

sha224(text_or_bytes)

Arguments	Domains in order	Contexts	Evaluation
1	text_or_binary	scalar	pure

sha256

sha256(text_or_bytes)

Arguments	Domains in order	Contexts	Evaluation
1	text_or_binary	scalar	pure

sha384

sha384(text_or_bytes)

Arguments	Domains in order	Contexts	Evaluation
1	text_or_binary	scalar	pure

sha512

sha512(text_or_bytes)

Arguments	Domains in order	Contexts	Evaluation
1	text_or_binary	scalar	pure

sign

sign(number)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

sin

sin(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

sinh

sinh(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

slice

slice(array, one_based_start, length)

Arguments	Domains in order	Contexts	Evaluation
3	array, integer, integer	scalar	pure

sort_array

sort_array(array [, boolean_ascending])

Arguments	Domains in order	Contexts	Evaluation
1	array	scalar	pure
2	array, boolean	scalar	pure

split

split(text, constant_separator)

Arguments	Domains in order	Contexts	Evaluation
2	text, text (constant)	scalar	pure

split_part

split_part(text, constant_separator, nonzero_position)

Arguments	Domains in order	Contexts	Evaluation
3	text, text (constant), integer	scalar	pure

sqrt

sqrt(number)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

st_area

st_area(geometry)

Arguments	Domains in order	Contexts	Evaluation
1	geometry	scalar	pure

st_boundary

st_boundary(geometry)

Arguments	Domains in order	Contexts	Evaluation
1	geometry	scalar	pure

st_buffer

st_buffer(geometry, metres)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, numeric	scalar	pure

st_centroid

st_centroid(geometry)

Arguments	Domains in order	Contexts	Evaluation
1	geometry	scalar	pure

st_contains

st_contains(geometry, geometry)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, geometry	scalar	pure

st_convexhull

st_convexhull(geometry)

Arguments	Domains in order	Contexts	Evaluation
1	geometry	scalar	pure

st_coveredby

st_coveredby(geometry, geometry)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, geometry	scalar	pure

st_covers

st_covers(geometry, geometry)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, geometry	scalar	pure

st_crosses

st_crosses(geometry, geometry)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, geometry	scalar	pure

st_difference

st_difference(geometry, geometry)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, geometry	scalar	pure

st_disjoint

st_disjoint(geometry, geometry)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, geometry	scalar	pure

st_distance

st_distance(geometry, geometry)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, geometry	scalar	pure

st_equals

st_equals(geometry, geometry)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, geometry	scalar	pure

st_intersection

st_intersection(geometry, geometry)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, geometry	scalar	pure

st_intersects

st_intersects(geometry, geometry)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, geometry	scalar	pure

st_length

st_length(geometry)

Arguments	Domains in order	Contexts	Evaluation
1	geometry	scalar	pure

st_overlaps

st_overlaps(geometry, geometry)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, geometry	scalar	pure

st_perimeter

st_perimeter(geometry)

Arguments	Domains in order	Contexts	Evaluation
1	geometry	scalar	pure

st_simplify

st_simplify(geometry, metres)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, numeric	scalar	pure

st_symdifference

st_symdifference(geometry, geometry)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, geometry	scalar	pure

st_touches

st_touches(geometry, geometry)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, geometry	scalar	pure

st_union

st_union(geometry, geometry)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, geometry	scalar	pure

st_within

st_within(geometry, geometry)

Arguments	Domains in order	Contexts	Evaluation
2	geometry, geometry	scalar	pure

starts_with

starts_with(text, prefix)

Aliases: startswith.

Arguments	Domains in order	Contexts	Evaluation
2	text, text	scalar	pure

struct

struct(value, ...)

Arguments	Domains in order	Contexts	Evaluation
1–64	any	scalar	pure

substring

substring(text, start [, length])

Aliases: substr.

Arguments	Domains in order	Contexts	Evaluation
2	text, integer	scalar	pure
3	text, integer, integer	scalar	pure

tan

tan(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

tanh

tanh(double)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	scalar	pure

timestamp_micros

timestamp_micros(integer)

Arguments	Domains in order	Contexts	Evaluation
1	integer	scalar	pure

timestamp_millis

timestamp_millis(integer)

Arguments	Domains in order	Contexts	Evaluation
1	integer	scalar	pure

timestamp_seconds

timestamp_seconds(integer)

Arguments	Domains in order	Contexts	Evaluation
1	integer	scalar	pure

to_date

to_date(value [, constant_timezone])

Arguments	Domains in order	Contexts	Evaluation
1	any	scalar	pure
2	any, text (constant)	scalar	pure

to_json

to_json(struct_array_or_map)

Arguments	Domains in order	Contexts	Evaluation
1	container	scalar	pure

trim

trim(text)

Aliases: btrim.

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure

try_json_at

try_json_at(json, constant_pointer)

Arguments	Domains in order	Contexts	Evaluation
2	text, text (constant)	scalar	pure

try_parse_timestamp

try_parse_timestamp(text [, constant_format [, constant_timezone]])

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure
2	text, text (constant)	scalar	pure
3	text, text (constant), text (constant)	scalar	pure

unbase64

unbase64(text); Java MIME alphabet and padding rules

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure

unhex

unhex(text); odd lengths receive a leading zero nibble, malformed hex returns null

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure

unix_micros

unix_micros(timestamp)

Arguments	Domains in order	Contexts	Evaluation
1	timestamp	scalar	pure

unix_millis

unix_millis(timestamp)

Arguments	Domains in order	Contexts	Evaluation
1	timestamp	scalar	pure

unix_seconds

unix_seconds(timestamp)

Arguments	Domains in order	Contexts	Evaluation
1	timestamp	scalar	pure

upper

upper(text)

Aliases: ucase.

Arguments	Domains in order	Contexts	Evaluation
1	text	scalar	pure

weekday

weekday(date)

Arguments	Domains in order	Contexts	Evaluation
1	date	scalar	pure

weekofyear

weekofyear(date)

Arguments	Domains in order	Contexts	Evaluation
1	date	scalar	pure

xxhash64

xxhash64(typed_scalar_or_array [, typed_scalar_or_array, ...]); seed 42, nulls skipped

Arguments	Domains in order	Contexts	Evaluation
1–64	array, array, text	scalar	pure

year

year(date)

Arguments	Domains in order	Contexts	Evaluation
1	date	scalar	pure

Aggregate functions

approx_count_distinct

approx_count_distinct(value [, constant_rsd=0.05]); exact bounded count, error 0; or approx_count_distinct(value, relative_error, confidence) returning estimate and accuracy evidence

Arguments	Domains in order	Contexts	Evaluation
1	any	group, window	exact_bounded_fallback
2	any, numeric (constant)	group, window	exact_bounded_fallback
3	any, numeric (constant), numeric (constant)	group	explicit_evidence

approx_percentile

approx_percentile(value, constant_fraction_or_array [, constant_accuracy=10000]); exact bounded nearest-rank fallback; or approx_percentile(number, fraction, rank_error, confidence) returning estimate and accuracy evidence

Arguments	Domains in order	Contexts	Evaluation
2	any, numeric_or_array (constant)	group, window	exact_bounded_fallback
3	any, numeric_or_array (constant), integer (constant)	group, window	exact_bounded_fallback
4	numeric, numeric (constant), numeric (constant), numeric (constant)	group	explicit_evidence

array_agg

array_agg([DISTINCT] value [ORDER BY expression])

Arguments	Domains in order	Contexts	Evaluation
1	any	group, window	pure

avg

avg([DISTINCT] number)

Aliases: mean.

Arguments	Domains in order	Contexts	Evaluation
1	numeric	group, window	pure

bit_and

bit_and([DISTINCT] integral) [FILTER (WHERE predicate)] [OVER window]

Arguments	Domains in order	Contexts	Evaluation
1	integer	group, window	pure

bit_or

bit_or([DISTINCT] integral) [FILTER (WHERE predicate)] [OVER window]

Arguments	Domains in order	Contexts	Evaluation
1	integer	group, window	pure

bit_xor

bit_xor([DISTINCT] integral) [FILTER (WHERE predicate)] [OVER window]

Arguments	Domains in order	Contexts	Evaluation
1	integer	group, window	pure

bool_and

bool_and(boolean)

Aliases: every.

Arguments	Domains in order	Contexts	Evaluation
1	boolean	group	pure

bool_or

bool_or(boolean)

Arguments	Domains in order	Contexts	Evaluation
1	boolean	group	pure

collect_list

collect_list(value)

Arguments	Domains in order	Contexts	Evaluation
1	any	group, window	pure

collect_set

collect_set(value)

Arguments	Domains in order	Contexts	Evaluation
1	any	group, window	pure

corr

corr([DISTINCT] number, number)

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric	group, window	pure

count

count() or count(*) or count([DISTINCT] value [, value, ...])

Arguments	Domains in order	Contexts	Evaluation
0–64	any	group, window	pure

count_if

count_if(boolean)

Arguments	Domains in order	Contexts	Evaluation
1	boolean	group, window	pure

covar_pop

covar_pop([DISTINCT] number, number)

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric	group, window	pure

covar_samp

covar_samp([DISTINCT] number, number)

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric	group, window	pure

first

first(value [, nonnull_boolean_ignore_nulls] [ORDER BY keys]) [FILTER (WHERE predicate)] [OVER window]

Arguments	Domains in order	Contexts	Evaluation
1–2	any, boolean	group, window	pure

geometry_union

geometry_union(geometry)

Arguments	Domains in order	Contexts	Evaluation
1	geometry	group	pure

grouping

grouping(grouping_expression, ...)

Aliases: grouping_id.

Arguments	Domains in order	Contexts	Evaluation
1–64	text	grouping	pure

last

last(value [, nonnull_boolean_ignore_nulls] [ORDER BY keys]) [FILTER (WHERE predicate)] [OVER window]

Arguments	Domains in order	Contexts	Evaluation
1–2	any, boolean	group, window	pure

max

max(value)

Arguments	Domains in order	Contexts	Evaluation
1	any	group, window	pure

max_by

max_by(value, key)

Arguments	Domains in order	Contexts	Evaluation
2	any, any	group	pure

min

min(value)

Arguments	Domains in order	Contexts	Evaluation
1	any	group, window	pure

min_by

min_by(value, key)

Arguments	Domains in order	Contexts	Evaluation
2	any, any	group	pure

percentile

percentile([DISTINCT] number, constant_fraction_or_array [, integral_frequency=1]) [FILTER (WHERE predicate)] [OVER window]

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric_or_array (constant)	group, window	pure
3	numeric, numeric_or_array (constant), integer	group, window	pure

percentile_approx

percentile_approx(value, constant_fraction_or_array [, constant_accuracy=10000]); exact bounded nearest-rank fallback, rank error 0

Arguments	Domains in order	Contexts	Evaluation
2	any, numeric_or_array (constant)	group, window	exact_bounded_fallback
3	any, numeric_or_array (constant), integer (constant)	group, window	exact_bounded_fallback

percentile_cont

percentile_cont(fraction) WITHIN GROUP (ORDER BY number)

Arguments	Domains in order	Contexts	Evaluation
1	numeric (constant)	group	pure

percentile_disc

percentile_disc(fraction) WITHIN GROUP (ORDER BY value)

Arguments	Domains in order	Contexts	Evaluation
1	numeric (constant)	group	pure

regr_avgx

regr_avgx([DISTINCT] dependent_y, independent_x)

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric	group, window	pure

regr_avgy

regr_avgy([DISTINCT] dependent_y, independent_x)

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric	group, window	pure

regr_count

regr_count([DISTINCT] dependent_y, independent_x)

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric	group, window	pure

regr_intercept

regr_intercept([DISTINCT] dependent_y, independent_x)

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric	group, window	pure

regr_r2

regr_r2([DISTINCT] dependent_y, independent_x)

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric	group, window	pure

regr_slope

regr_slope([DISTINCT] dependent_y, independent_x)

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric	group, window	pure

regr_sxx

regr_sxx([DISTINCT] dependent_y, independent_x)

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric	group, window	pure

regr_sxy

regr_sxy([DISTINCT] dependent_y, independent_x)

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric	group, window	pure

regr_syy

regr_syy([DISTINCT] dependent_y, independent_x)

Arguments	Domains in order	Contexts	Evaluation
2	numeric, numeric	group, window	pure

skewness

skewness([DISTINCT] number) [FILTER (WHERE predicate)] [OVER window]

Arguments	Domains in order	Contexts	Evaluation
1	numeric	group, window	pure

stddev_pop

stddev_pop([DISTINCT] number)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	group, window	pure

stddev_samp

stddev_samp([DISTINCT] number)

Aliases: stddev.

Arguments	Domains in order	Contexts	Evaluation
1	numeric	group, window	pure

sum

sum([DISTINCT] number)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	group, window	pure

var_pop

var_pop([DISTINCT] number)

Arguments	Domains in order	Contexts	Evaluation
1	numeric	group, window	pure

var_samp

var_samp([DISTINCT] number)

Aliases: variance.

Arguments	Domains in order	Contexts	Evaluation
1	numeric	group, window	pure

Window functions

cume_dist

cume_dist() OVER window

Arguments	Domains in order	Contexts	Evaluation
0	none	window	pure

dense_rank

dense_rank() OVER window

Arguments	Domains in order	Contexts	Evaluation
0	none	window	pure

first_value

first_value(value) OVER window

Arguments	Domains in order	Contexts	Evaluation
1	any	window	pure

lag

lag(value [, integer_offset [, default]]) OVER window

Arguments	Domains in order	Contexts	Evaluation
1	any	window	pure
2	any, integer	window	pure
3	any, integer, any	window	pure

last_value

last_value(value) OVER window

Arguments	Domains in order	Contexts	Evaluation
1	any	window	pure

lead

lead(value [, integer_offset [, default]]) OVER window

Arguments	Domains in order	Contexts	Evaluation
1	any	window	pure
2	any, integer	window	pure
3	any, integer, any	window	pure

nth_value

nth_value(value, integer_position) OVER window

Arguments	Domains in order	Contexts	Evaluation
2	any, integer	window	pure

ntile

ntile(integer_buckets) OVER window

Arguments	Domains in order	Contexts	Evaluation
1	integer	window	pure

percent_rank

percent_rank() OVER window

Arguments	Domains in order	Contexts	Evaluation
0	none	window	pure

rank

rank() OVER window

Arguments	Domains in order	Contexts	Evaluation
0	none	window	pure

row_number

row_number() OVER window

Arguments	Domains in order	Contexts	Evaluation
0	none	window	pure

Table functions

generate_series

generate_series(integer_start, integer_stop [, integer_step])

Arguments	Domains in order	Contexts	Evaluation
2	integer, integer	table	pure
3	integer, integer, integer	table	pure

range

range([integer_start,] integer_stop [, integer_step])

Arguments	Domains in order	Contexts	Evaluation
1	integer	table	pure
2	integer, integer	table	pure
3	integer, integer, integer	table	pure

Generator functions

explode

explode(array_or_map)

Arguments	Domains in order	Contexts	Evaluation
1	collection	table, select_generator	pure

explode_outer

explode_outer(array_or_map)

Arguments	Domains in order	Contexts	Evaluation
1	collection	table, select_generator	pure

posexplode

posexplode(array_or_map) -> pos, col | pos, key, value

Arguments	Domains in order	Contexts	Evaluation
1	collection	table, select_generator	pure

posexplode_outer

posexplode_outer(array_or_map) -> pos, col | pos, key, value

Arguments	Domains in order	Contexts	Evaluation
1	collection	table, select_generator	pure

Get Started

Use-case atlas

Platform

Data and Operations

Guides

Client Libraries

Tools

API Reference

SQL function reference

Scalar functions

Aggregate functions

Window functions

Table functions

Generator functions

​Scalar functions

​Aggregate functions

​Window functions

​Table functions

​Generator functions

Scalar functions

Aggregate functions

Window functions

Table functions

Generator functions