标量函数 · R2 SQL docs

标量函数转换单个值，可用于 SELECT、WHERE、GROUP BY、HAVING 和 ORDER BY 子句中。

核心函数

arrow_cast

按字符串名称将表达式强制转换为特定的 Arrow 数据类型。

SELECT arrow_cast(total_amount, 'Float32') AS amount_f32
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 1

arrow_typeof

返回表达式的 Arrow 数据类型名称。

SELECT arrow_typeof(total_amount) AS amount_type,
       arrow_typeof(customer_id) AS id_type
FROM my_namespace.sales_data
LIMIT 1

coalesce

返回第一个非 NULL 参数。

SELECT coalesce(department, region, 'unknown') AS first_val
FROM my_namespace.sales_data
LIMIT 5

get_field

按名称从结构体中提取字段。

SELECT get_field(named_struct('customer', customer_id, 'amount', total_amount), 'amount') AS amt
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 1

greatest

返回参数列表中的最大值。

SELECT greatest(total_amount, unit_price, quantity) AS max_val
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

least

返回参数列表中的最小值。

SELECT least(total_amount, unit_price, quantity) AS min_val
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

named_struct

从键值对创建一个包含命名字段的结构体。

SELECT named_struct('customer', customer_id, 'amount', total_amount) AS info
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 1

nullif

如果两个参数相等，则返回 NULL，否则返回第一个参数。

SELECT nullif(department, 'Unknown') AS dept
FROM my_namespace.sales_data
LIMIT 5

nvl

如果第一个参数为 NULL，则返回第二个参数。别名：ifnull。

SELECT nvl(department, 'N/A') AS dept
FROM my_namespace.sales_data
LIMIT 5

nvl2

如果第一个参数不为 NULL，则返回第二个参数，否则返回第三个参数。

SELECT nvl2(department, 'has_dept', 'no_dept') AS dept_status
FROM my_namespace.sales_data
LIMIT 5

overlay

在给定位置替换子字符串。

SELECT customer_id,
       overlay(customer_id PLACING 'XX' FROM 1 FOR 2) AS masked
FROM my_namespace.sales_data
LIMIT 3

struct

创建一个具有位置字段的结构体。别名：row。

SELECT struct(customer_id, total_amount, region) AS info
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 1

加密函数

digest

使用指定的算法返回字符串的哈希值。支持的算法：md5、sha224、sha256、sha384、sha512。

SELECT customer_id, digest(customer_id, 'sha256') AS hash
FROM my_namespace.sales_data
LIMIT 1

md5

返回字符串的 MD5 哈希值。

SELECT customer_id, md5(customer_id) AS hash
FROM my_namespace.sales_data
LIMIT 1

sha224

返回字符串的 SHA-224 哈希值。

SELECT sha224(customer_id) AS hash
FROM my_namespace.sales_data
LIMIT 1

sha256

返回字符串的 SHA-256 哈希值。

SELECT customer_id, sha256(customer_id) AS hash
FROM my_namespace.sales_data
LIMIT 1

sha384

返回字符串的 SHA-384 哈希值。

SELECT sha384(customer_id) AS hash
FROM my_namespace.sales_data
LIMIT 1

sha512

返回字符串的 SHA-512 哈希值。

SELECT sha512(customer_id) AS hash
FROM my_namespace.sales_data
LIMIT 1

日期时间函数

current_date

返回今天的日期。别名：today。

SELECT current_date() AS today_date
FROM my_namespace.sales_data
LIMIT 1

current_time

返回当前时间。精度量化到 10 毫秒边界。

SELECT current_time() AS now_time
FROM my_namespace.sales_data
LIMIT 1

date_bin

将时间戳分组到对齐到原点的固定大小间隔中。

SELECT date_bin(INTERVAL '1 hour', timestamp, '2025-01-01T00:00:00Z') AS hour_bin,
       COUNT(*) AS cnt
FROM my_namespace.sales_data
GROUP BY date_bin(INTERVAL '1 hour', timestamp, '2025-01-01T00:00:00Z')
ORDER BY hour_bin
LIMIT 5

date_part

从时间戳中提取一个分量。别名：datepart。

支持的字段：year、month、day、hour、minute、second、millisecond、microsecond、week、dow、doy、quarter、epoch。

SELECT date_part('hour', timestamp) AS hr,
       date_part('minute', timestamp) AS mn
FROM my_namespace.sales_data
LIMIT 1

date_trunc

将时间戳截断为指定的单位。别名：datetrunc。

支持的单位：year、month、week、day、hour、minute、second。

SELECT date_trunc('day', timestamp) AS day_trunc, COUNT(*) AS cnt
FROM my_namespace.sales_data
GROUP BY date_trunc('day', timestamp)
ORDER BY day_trunc
LIMIT 5

from_unixtime

将 Unix 纪元时间（秒）转换为时间戳。

SELECT from_unixtime(1770000000) AS ts
FROM my_namespace.sales_data
LIMIT 1

make_date

从年、月、日分量构造日期。

SELECT make_date(2026, 3, 1) AS d
FROM my_namespace.sales_data
LIMIT 1

make_time

从小时、分钟和秒分量构造时间。

SELECT make_time(14, 30, 0) AS t
FROM my_namespace.sales_data
LIMIT 1

now

返回当前时间戳。别名：current_timestamp。

精度量化到 10 毫秒边界。

SELECT now() AS current_ts
FROM my_namespace.sales_data
LIMIT 1

to_char

使用 strftime 格式将时间戳格式化为字符串。别名：date_format。

SELECT to_char(timestamp, '%Y-%m-%d %H:%M') AS formatted
FROM my_namespace.sales_data
LIMIT 1

to_date

使用格式模式从字符串解析日期。

SELECT to_date('2026-03-01', '%Y-%m-%d') AS d
FROM my_namespace.sales_data
LIMIT 1

to_local_time

从时间戳中剥离时区信息。

SELECT to_local_time(timestamp) AS local_ts
FROM my_namespace.sales_data
LIMIT 1

to_time

使用格式模式从字符串解析时间。

SELECT to_time('14:30:00', '%H:%M:%S') AS t
FROM my_namespace.sales_data
LIMIT 1

to_timestamp

使用格式模式从字符串解析时间戳。

SELECT to_timestamp('2026-03-01 12:00:00', '%Y-%m-%d %H:%M:%S') AS ts
FROM my_namespace.sales_data
LIMIT 1

to_timestamp_micros

将自 Unix 纪元起的微秒数转换为时间戳。

SELECT to_timestamp_micros(1770000000000000) AS ts
FROM my_namespace.sales_data
LIMIT 1

to_timestamp_millis

将自 Unix 纪元起的毫秒数转换为时间戳。

SELECT to_timestamp_millis(1770000000000) AS ts
FROM my_namespace.sales_data
LIMIT 1

to_timestamp_nanos

将自 Unix 纪元起的纳秒数转换为时间戳。大的值可能会溢出。

SELECT to_timestamp_nanos(1770000000000000000) AS ts
FROM my_namespace.sales_data
LIMIT 1

to_timestamp_seconds

将自 Unix 纪元起的秒数转换为时间戳。

SELECT to_timestamp_seconds(1770000000) AS ts
FROM my_namespace.sales_data
LIMIT 1

to_unixtime

将时间戳转换为 Unix 纪元时间（秒）。

SELECT to_unixtime(timestamp) AS epoch
FROM my_namespace.sales_data
LIMIT 1

编码函数

decode

将字符串解码为二进制数据。支持的编码：base64。

SELECT decode('aGVsbG8=', 'base64') AS raw
FROM my_namespace.sales_data
LIMIT 1

encode

将二进制数据编码为字符串。支持的编码：base64。

SELECT encode(CAST('hello' AS BYTEA), 'base64') AS b64
FROM my_namespace.sales_data
LIMIT 1

JSON 函数

json_as_text

以无引号文本形式返回任何 JSON 值。

SELECT json_as_text(doc, 'description') AS description
FROM my_namespace.sales_data
LIMIT 5

json_contains

如果 JSON 中存在指定的键路径，则返回真。

SELECT customer_id, json_contains(doc, 'email') AS has_email
FROM my_namespace.sales_data
LIMIT 5

json_get

通过键路径提取值。返回联合类型 — 使用带类型的变体（json_get_str、json_get_int 等）以获得可预测的结果。

SELECT json_get(doc, 'name') AS name
FROM my_namespace.sales_data
LIMIT 5

json_get_array

以字符串列表形式返回 JSON 数组。

SELECT json_get_array(doc, 'tags') AS tags
FROM my_namespace.sales_data
LIMIT 5

json_get_bool

通过键路径从 JSON 列返回布尔值。

SELECT json_get_bool(doc, 'active') AS is_active
FROM my_namespace.sales_data
LIMIT 5

json_get_float

通过键路径从 JSON 列返回浮点值。

SELECT json_get_float(doc, 'price') AS price
FROM my_namespace.sales_data
LIMIT 5

json_get_int

通过键路径从 JSON 列返回整数值。

SELECT json_get_int(doc, 'age') AS age
FROM my_namespace.sales_data
LIMIT 5

json_get_json

以原始 JSON 字符串形式返回嵌套的 JSON。

SELECT json_get_json(doc, 'metadata') AS metadata
FROM my_namespace.sales_data
LIMIT 5

json_get_str

通过键路径从 JSON 列返回字符串值。

SELECT json_get_str(doc, 'name') AS name
FROM my_namespace.sales_data
LIMIT 5

json_length

返回 JSON 数组或对象的长度。

SELECT json_length(doc, 'items') AS item_count
FROM my_namespace.sales_data
LIMIT 5

数学函数

abs

返回数字的绝对值。

SELECT abs(total_amount - 500) AS distance_from_500
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

cbrt

返回数字的立方根。

SELECT cbrt(CAST(quantity AS DOUBLE)) AS cbrt_qty
FROM my_namespace.sales_data
WHERE quantity IS NOT NULL
LIMIT 5

ceil

返回大于或等于某个数字的最小整数。

SELECT ceil(total_amount) AS rounded_up
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

degrees

将弧度转换为度。

SELECT degrees(pi()) AS full_circle
FROM my_namespace.sales_data
LIMIT 1

exp

返回 e 的指定次幂。

SELECT exp(total_amount / 1000.0) AS exp_val
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

factorial

返回非负整数的阶乘。

SELECT factorial(5) AS fact5
FROM my_namespace.sales_data
LIMIT 1

floor

返回小于或等于某个数字的最大整数。

SELECT floor(total_amount) AS rounded_down
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

gcd

返回两个整数的最大公约数。

SELECT gcd(12, 8) AS gcd_val
FROM my_namespace.sales_data
LIMIT 1

双曲函数 (Hyperbolic functions)

sinh, cosh, tanh, asinh, acosh, atanh

SELECT sinh(1.0) AS sh, cosh(1.0) AS ch, tanh(1.0) AS th
FROM my_namespace.sales_data
LIMIT 1

isnan

如果值是 NaN（非数字），则返回真。

SELECT isnan(0.0 / 0.0) AS is_nan
FROM my_namespace.sales_data
LIMIT 1

iszero

如果值为零，则返回真。

SELECT iszero(0.0) AS is_zero
FROM my_namespace.sales_data
LIMIT 1

lcm

返回两个整数的最小公倍数。

SELECT lcm(4, 6) AS lcm_val
FROM my_namespace.sales_data
LIMIT 1

ln

返回自然对数。

SELECT ln(total_amount) AS ln_val
FROM my_namespace.sales_data
WHERE total_amount > 0
LIMIT 5

log

返回给定底数的值的对数。

SELECT log(10.0, total_amount) AS log10_val
FROM my_namespace.sales_data
WHERE total_amount > 0
LIMIT 5

log2

返回以 2 为底的对数。

SELECT log2(total_amount) AS log2_val
FROM my_namespace.sales_data
WHERE total_amount > 0
LIMIT 5

log10

返回以 10 为底的对数。

SELECT log10(total_amount) AS log10_val
FROM my_namespace.sales_data
WHERE total_amount > 0
LIMIT 5

nanvl

如果第一个参数不是 NaN，则返回第一个参数，否则返回第二个参数。

SELECT nanvl(0.0 / 0.0, -1.0) AS safe_val
FROM my_namespace.sales_data
LIMIT 1

pi

返回 pi 的值。

SELECT pi() AS pi_val
FROM my_namespace.sales_data
LIMIT 1

power

计算数字的幂。别名：pow。

SELECT power(total_amount, 2.0) AS amount_squared
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

radians

将度转换为弧度。

SELECT radians(180.0) AS pi_val
FROM my_namespace.sales_data
LIMIT 1

random

返回 0 到 1 之间的随机浮点数。

SELECT random() AS rnd
FROM my_namespace.sales_data
LIMIT 1

round

将数字四舍五入到指定的小数位数。

SELECT round(total_amount, 2) AS rounded
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

signum

返回数字的符号：-1、0 或 1。

SELECT signum(total_amount - 500) AS sign_val
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

sqrt

返回数字的平方根。

SELECT sqrt(CAST(quantity AS DOUBLE)) AS sqrt_qty
FROM my_namespace.sales_data
WHERE quantity IS NOT NULL
LIMIT 5

三角函数 (Trigonometric functions)

sin, cos, tan, asin, acos, atan, atan2, cot

SELECT sin(1.0) AS s, cos(1.0) AS c, tan(1.0) AS t,
       asin(0.5) AS as_val, acos(0.5) AS ac_val, atan(1.0) AS at_val
FROM my_namespace.sales_data
LIMIT 1

trunc

将数字截断为指定的小数位数。

SELECT trunc(total_amount, 0) AS truncated
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

正则表达式函数 (Regex functions)

regexp_count

返回模式在字符串中匹配的次数。

SELECT department, regexp_count(department, '[aeiou]') AS vowels
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

regexp_instr

返回首次匹配模式的位置。

SELECT department, regexp_instr(department, '[0-9]') AS digit_pos
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

regexp_like

如果字符串匹配正则表达式模式，则返回真。

SELECT department, regexp_like(department, '^[A-Z]{2}') AS starts_two_caps
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

regexp_match

以数组形式返回模式的首次匹配项。

SELECT department, regexp_match(department, '([A-Z][a-z]+)') AS first_word
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 3

regexp_replace

使用替换字符串替换匹配模式的项。

SELECT department, regexp_replace(department, '[0-9]', '#') AS no_digits
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

字符串函数

ascii

返回第一个字符的 ASCII 码。

SELECT customer_id, ascii(customer_id) AS first_code
FROM my_namespace.sales_data
LIMIT 3

bit_length

返回字符串的位数。

SELECT customer_id, bit_length(customer_id) AS bits
FROM my_namespace.sales_data
LIMIT 3

btrim

删除字符串两端的字符。别名：trim。

SELECT btrim('  hello  ') AS trimmed
FROM my_namespace.sales_data
LIMIT 1

chr

返回给定 ASCII 码对应的字符。

SELECT chr(65) AS letter
FROM my_namespace.sales_data
LIMIT 1

concat

连接两个或多个字符串。

SELECT concat(department, ' - ', region) AS label
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

concat_ws

使用分隔符连接字符串。

SELECT concat_ws('/', region, department) AS path
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

contains

如果字符串包含子字符串，则返回真。

SELECT customer_id, contains(department, 'Sales') AS is_sales
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

ends_with

如果字符串以指定的后缀结尾，则返回真。

SELECT customer_id, ends_with(department, 'ing') AS ends_ing
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

levenshtein

返回两个字符串之间的 Levenshtein 编辑距离。

SELECT department, levenshtein(department, 'Engineering') AS dist
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

lower

将字符串转换为小写。

SELECT lower(department) AS dept_lower
FROM my_namespace.sales_data
LIMIT 5

ltrim

删除字符串左侧的字符。

SELECT ltrim('  hello') AS trimmed
FROM my_namespace.sales_data
LIMIT 1

octet_length

返回字符串的字节数。

SELECT customer_id, octet_length(customer_id) AS bytes
FROM my_namespace.sales_data
LIMIT 3

repeat

将字符串重复指定的次数。

SELECT repeat(region, 2) AS doubled
FROM my_namespace.sales_data
LIMIT 3

replace

替换所有出现的子字符串。

SELECT department, replace(department, ' ', '_') AS underscored
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

rtrim

删除字符串右侧的字符。

SELECT rtrim('hello  ') AS trimmed
FROM my_namespace.sales_data
LIMIT 1

split_part

通过分隔符拆分字符串并返回指定的部分（从 1 开始的索引）。

SELECT customer_id, split_part(customer_id, '-', 1) AS first_part
FROM my_namespace.sales_data
WHERE customer_id IS NOT NULL
LIMIT 5

starts_with

如果字符串以指定的前缀开头，则返回真。

SELECT customer_id, starts_with(department, 'Eng') AS is_eng
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

to_hex

将整数转换为十六进制字符串。

SELECT to_hex(255) AS hex_ff
FROM my_namespace.sales_data
LIMIT 1

upper

将字符串转换为大写。

SELECT upper(region) AS region_upper
FROM my_namespace.sales_data
LIMIT 5

uuid

生成随机的 UUID。

SELECT uuid() AS new_id
FROM my_namespace.sales_data
LIMIT 1

Unicode 函数

character_length

返回字符串的字符数。别名：length、char_length。

SELECT department, character_length(department) AS len
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

find_in_set

返回字符串在以逗号分隔的列表中的位置。

SELECT find_in_set('North', 'South,North,East,West') AS pos
FROM my_namespace.sales_data
LIMIT 1

initcap

将每个单词的第一个字母大写。

SELECT initcap('hello world') AS capped
FROM my_namespace.sales_data
LIMIT 1

left

返回字符串中最左边的 n 个字符。

SELECT department, left(department, 5) AS prefix
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

lpad

用字符在字符串左侧填充至指定长度。

SELECT region, lpad(region, 15, '.') AS padded
FROM my_namespace.sales_data
LIMIT 5

reverse

反转字符串。

SELECT department, reverse(department) AS rev
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

right

返回字符串中最右边的 n 个字符。

SELECT department, right(department, 3) AS suffix
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

rpad

用字符在字符串右侧填充至指定长度。

SELECT region, rpad(region, 15, '.') AS padded
FROM my_namespace.sales_data
LIMIT 5

strpos

返回子字符串的位置（从 1 开始）。别名：instr、position。

SELECT department, strpos(department, 'a') AS a_pos
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

substr

返回从某个位置开始且具有给定长度的子字符串。别名：substring。

SELECT department, substr(department, 1, 8) AS first_eight
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

substr_index

返回第 n 次出现的分隔符之前的子字符串。别名：substring_index。

SELECT customer_id, substr_index(customer_id, '-', 1) AS first_segment
FROM my_namespace.sales_data
WHERE customer_id IS NOT NULL
LIMIT 5

translate

根据映射替换字符串中的字符。

SELECT department, translate(department, 'aeiou', '12345') AS coded
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5