Flink SQL 函数完整清单与测试指南 (第1部分)

参考文档: Apache Flink System Functions

📖 说明

文档共分为5个部分：

  • ** 1**: 比较函数、逻辑函数、算术函数
  • ** 2**: 字符串函数
  • ** 3**: 时间函数、条件函数
  • ** 4**: 类型转换、集合函数、JSON函数
  • ** 5**: 聚合函数、窗口函数、表值函数

目录

1. 比较函数 (Comparison Functions)

1.1 函数清单

函数名称中文说明语法示例返回类型
=等于比较value1 = value2BOOLEAN
<>, !=不等于比较value1 <> value2BOOLEAN
>大于比较value1 > value2BOOLEAN
>=大于等于value1 >= value2BOOLEAN
<小于比较value1 < value2BOOLEAN
<=小于等于value1 <= value2BOOLEAN
IS NULL判断是否为NULLvalue IS NULLBOOLEAN
IS NOT NULL判断是否不为NULLvalue IS NOT NULLBOOLEAN
IS DISTINCT FROMNULL安全的不等于value1 IS DISTINCT FROM value2BOOLEAN
IS NOT DISTINCT FROMNULL安全的等于value1 IS NOT DISTINCT FROM value2BOOLEAN
BETWEEN范围判断value BETWEEN min AND maxBOOLEAN
NOT BETWEEN范围外判断value NOT BETWEEN min AND maxBOOLEAN
LIKE模式匹配string LIKE pattern [ESCAPE char]BOOLEAN
NOT LIKE模式不匹配string NOT LIKE patternBOOLEAN
SIMILAR TOSQL正则匹配string SIMILAR TO patternBOOLEAN
IN值在列表中value IN (v1, v2, ...)BOOLEAN
NOT IN值不在列表中value NOT IN (v1, v2, ...)BOOLEAN

1.2 详细测试示例

测试 1.1: 基础比较操作

测试目的: 验证等于、不等于、大于、小于等基础比较操作

测试SQL:

SELECT 
    -- 等于比较
    5 = 5 as eq_true,
    5 = 3 as eq_false,
    NULL = NULL as eq_null,
    
    -- 不等于比较
    5 <> 3 as neq_true,
    5 != 5 as neq_false,
    
    -- 大小比较
    10 > 5 as gt_true,
    10 >= 10 as gte_true,
    3 < 5 as lt_true,
    5 <= 5 as lte_true,
    
    -- 字符串比较（字典序）
    'apple' < 'banana' as str_lt,
    'Flink' = 'Flink' as str_eq
FROM (VALUES (1)) AS T(x)

执行结果:

+----------+-----------+----------+----------+-----------+----------+-----------+----------+-----------+---------+---------+
| eq_true  | eq_false  | eq_null  | neq_true | neq_false | gt_true  | gte_true  | lt_true  | lte_true  | str_lt  | str_eq  |
+----------+-----------+----------+----------+-----------+----------+-----------+----------+-----------+---------+---------+
| TRUE     | FALSE     | NULL     | TRUE     | FALSE     | TRUE     | TRUE      | TRUE     | TRUE      | TRUE    | TRUE    |
+----------+-----------+----------+----------+-----------+----------+-----------+----------+-----------+---------+---------+

结果说明:

  • ✅ 所有基础比较操作正常
  • ⚠️ NULL = NULL 返回 NULL (UNKNOWN)，这是SQL的三值逻辑
  • ✅ 字符串按字典序比较，区分大小写

实际应用场景:

-- 场景1: 查询指定价格范围的商品
SELECT product_id, name, price
FROM products
WHERE price >= 100 AND price <= 500
ORDER BY price DESC

-- 场景2: 查询特定状态的订单
SELECT order_id, status, create_time
FROM orders
WHERE status = 'paid' OR status = 'shipped'

-- 场景3: 查询评分高于平均分的学生
SELECT student_id, name, score
FROM students
WHERE score > (SELECT AVG(score) FROM students)
测试 1.2: IS DISTINCT FROM (NULL安全比较)

测试目的: 验证NULL安全的相等和不等比较

测试SQL:

SELECT 
    -- 普通等于（NULL不安全）
    1 = NULL as normal_eq,
    NULL = NULL as null_eq,
    
    -- IS DISTINCT FROM（NULL安全的不等于）
    1 IS DISTINCT FROM NULL as distinct_1_null,
    NULL IS DISTINCT FROM NULL as distinct_null_null,
    5 IS DISTINCT FROM 5 as distinct_5_5,
    5 IS DISTINCT FROM 3 as distinct_5_3,
    
    -- IS NOT DISTINCT FROM（NULL安全的等于）
    1 IS NOT DISTINCT FROM NULL as not_distinct_1_null,
    NULL IS NOT DISTINCT FROM NULL as not_distinct_nn,
    5 IS NOT DISTINCT FROM 5 as not_distinct_5_5
FROM (VALUES (1)) AS T(x)

执行结果:

+------------+----------+------------------+---------------------+---------------+---------------+---------------------+------------------+-------------------+
| normal_eq  | null_eq  | distinct_1_null  | distinct_null_null  | distinct_5_5  | distinct_5_3  | not_distinct_1_null | not_distinct_nn  | not_distinct_5_5  |
+------------+----------+------------------+---------------------+---------------+---------------+---------------------+------------------+-------------------+
| NULL       | NULL     | TRUE             | FALSE               | FALSE         | TRUE          | FALSE               | TRUE             | TRUE              |
+------------+----------+------------------+---------------------+---------------+---------------+---------------------+------------------+-------------------+

结果说明:

  • ✅ IS DISTINCT FROM 将NULL视为普通值，两个NULL相等
  • ✅ 用于JOIN条件时，能正确匹配NULL值
  • ⚠️ 标准=操作符遇到NULL返回NULL，可能导致WHERE子句过滤错误

实际应用场景:

-- 场景1: 查找两表中不同的记录（包括NULL值比较）
SELECT a.id, a.name, a.email, b.email
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id
WHERE a.email IS DISTINCT FROM b.email

-- 场景2: 数据变更检测（包括NULL变化）
SELECT 
    old.id,
    old.status as old_status,
    new.status as new_status
FROM old_data old
JOIN new_data new ON old.id = new.id
WHERE old.status IS DISTINCT FROM new.status

-- 场景3: 去重（NULL也视为重复）
SELECT DISTINCT ON (
    name IS NOT DISTINCT FROM NULL
) * 
FROM users
测试 1.3: BETWEEN范围查询

测试目的: 验证BETWEEN和NOT BETWEEN的各种用法

测试SQL:

SELECT 
    -- ASYMMETRIC（默认）
    12 BETWEEN 10 AND 15 as between_normal,
    12 BETWEEN 15 AND 10 as between_reversed,
    
    -- SYMMETRIC（顺序无关）
    12 BETWEEN SYMMETRIC 10 AND 15 as sym_normal,
    12 BETWEEN SYMMETRIC 15 AND 10 as sym_reversed,
    
    -- NULL处理
    12 BETWEEN 10 AND NULL as between_null_upper,
    12 BETWEEN NULL AND 15 as between_null_lower,
    12 BETWEEN SYMMETRIC NULL AND 12 as sym_null,
    
    -- NOT BETWEEN
    20 NOT BETWEEN 10 AND 15 as not_between_out,
    12 NOT BETWEEN 10 AND 15 as not_between_in,
    
    -- 日期范围
    DATE '2024-05-15' BETWEEN DATE '2024-01-01' AND DATE '2024-12-31' as date_between,
    
    -- 字符串范围
    'M' BETWEEN 'A' AND 'Z' as char_between
FROM (VALUES (1)) AS T(x)

执行结果:

+-----------------+-------------------+-------------+---------------+---------------------+--------------------+-----------+-------------------+-----------------+---------------+---------------+
| between_normal  | between_reversed  | sym_normal  | sym_reversed  | between_null_upper  | between_null_lower | sym_null  | not_between_out   | not_between_in  | date_between  | char_between  |
+-----------------+-------------------+-------------+---------------+---------------------+--------------------+-----------+-------------------+-----------------+---------------+---------------+
| TRUE            | FALSE             | TRUE        | TRUE          | NULL                | FALSE              | NULL      | TRUE              | FALSE           | TRUE          | TRUE          |
+-----------------+-------------------+-------------+---------------+---------------------+--------------------+-----------+-------------------+-----------------+---------------+---------------+

结果说明:

  • ✅ BETWEEN 等价于 value >= min AND value <= max
  • ✅ SYMMETRIC 自动处理min/max顺序
  • ⚠️ NULL边界导致UNKNOWN或FALSE结果
  • ✅ 支持数值、日期、字符串类型

实际应用场景:

-- 场景1: 时间范围查询
SELECT order_id, order_time, amount
FROM orders
WHERE order_time BETWEEN TIMESTAMP '2024-01-01 00:00:00' 
                    AND TIMESTAMP '2024-01-31 23:59:59'

-- 场景2: 价格区间筛选
SELECT product_id, name, price
FROM products
WHERE price BETWEEN 100.0 AND 500.0
ORDER BY price

-- 场景3: 年龄段统计
SELECT 
    COUNT(*) as count,
    CASE 
        WHEN age BETWEEN 0 AND 17 THEN 'Minor'
        WHEN age BETWEEN 18 AND 35 THEN 'Young'
        WHEN age BETWEEN 36 AND 60 THEN 'Middle'
        ELSE 'Senior'
    END as age_group
FROM users
GROUP BY age_group

-- 场景4: IP地址范围（转换为数值）
SELECT ip_address
FROM access_logs
WHERE ip_to_int(ip_address) BETWEEN ip_to_int('192.168.1.0') 
                                AND ip_to_int('192.168.1.255')
测试 1.4: LIKE模式匹配

测试目的: 验证LIKE和NOT LIKE的模式匹配功能

测试SQL:

SELECT 
    -- 基础LIKE
    'Hello World' LIKE 'Hello%' as like_prefix,
    'Hello World' LIKE '%World' as like_suffix,
    'Hello World' LIKE '%o W%' as like_contains,
    'Hello World' LIKE 'Hello_World' as like_single_char,
    'Hello World' LIKE 'Hello%World' as like_both,
    
    -- NOT LIKE
    'Hello World' NOT LIKE 'Goodbye%' as not_like,
    
    -- ESCAPE字符
    '100% discount' LIKE '100!%%' ESCAPE '!' as like_escape,
    'C:\path\file' LIKE '%!_path!_%' ESCAPE '!' as like_escape_underscore,
    
    -- 实际案例
    'user@example.com' LIKE '%@%.com' as email_match,
    'ORDER-2024-001' LIKE 'ORDER-____-___' as order_pattern,
    '+86-138-1234-5678' LIKE '+__-___-____-____' as phone_pattern,
    
    -- 大小写敏感
    'Hello' LIKE 'hello' as case_sensitive,
    'Hello' LIKE 'Hello' as case_match
FROM (VALUES (1)) AS T(x)

执行结果:

+--------------+--------------+----------------+------------------+------------+-----------+--------------+-------------------------+--------------+----------------+---------------+-----------------+------------+
| like_prefix  | like_suffix  | like_contains  | like_single_char | like_both  | not_like  | like_escape  | like_escape_underscore  | email_match  | order_pattern  | phone_pattern | case_sensitive  | case_match |
+--------------+--------------+----------------+------------------+------------+-----------+--------------+-------------------------+--------------+----------------+---------------+-----------------+------------+
| TRUE         | TRUE         | TRUE           | TRUE             | TRUE       | TRUE      | TRUE         | TRUE                    | TRUE         | TRUE           | TRUE          | FALSE           | TRUE       |
+--------------+--------------+----------------+------------------+------------+-----------+--------------+-------------------------+--------------+----------------+---------------+-----------------+------------+

结果说明:

  • ✅ % 匹配任意数量字符（包括0个）
  • ✅ _ 匹配恰好1个字符
  • ✅ ESCAPE 用于转义特殊字符
  • ⚠️ 默认大小写敏感

实际应用场景:

-- 场景1: 模糊搜索用户名
SELECT user_id, username, email
FROM users
WHERE username LIKE '%zhang%' OR email LIKE '%zhang%'

-- 场景2: 搜索特定域名的邮箱
SELECT user_id, email
FROM users
WHERE email LIKE '%@gmail.com' OR email LIKE '%@163.com'

-- 场景3: 搜索特定前缀的订单
SELECT order_id, create_time, amount
FROM orders
WHERE order_id LIKE 'VIP-%'

-- 场景4: 文件名匹配
SELECT file_id, filename
FROM files
WHERE filename LIKE '%.pdf' 
   OR filename LIKE '%.docx'
   OR filename LIKE '%.xlsx'

-- 场景5: 电话号码格式验证
SELECT phone
FROM contacts
WHERE phone NOT LIKE '+__-___-____-____'  -- 格式不正确的号码
测试 1.5: IN操作符

测试目的: 验证IN和NOT IN的各种情况，特别是NULL处理

测试SQL:

SELECT 
    -- 基础IN
    4 IN (1, 2, 3, 4, 5) as in_exists,
    6 IN (1, 2, 3, 4, 5) as in_not_exists,
    'apple' IN ('apple', 'banana', 'orange') as in_string,
    
    -- NULL处理（重要！）
    1 IN (1, 2, NULL) as in_null_match,
    4 IN (1, 2, NULL) as in_null_no_match,
    NULL IN (1, 2, 3) as null_in_list,
    NULL IN (1, 2, NULL) as null_in_list_with_null,
    
    -- NOT IN
    6 NOT IN (1, 2, 3, 4, 5) as not_in_true,
    1 NOT IN (1, 2, NULL) as not_in_null_match,
    4 NOT IN (1, 2, NULL) as not_in_null_no_match,
    
    -- 子查询（示例）
    10 IN (SELECT unnest(ARRAY[5, 10, 15])) as in_subquery
FROM (VALUES (1)) AS T(x)

执行结果:

+------------+----------------+------------+----------------+---------------------+--------------+--------------------------+-------------+---------------------+-----------------------+--------------+
| in_exists  | in_not_exists  | in_string  | in_null_match  | in_null_no_match    | null_in_list | null_in_list_with_null   | not_in_true | not_in_null_match   | not_in_null_no_match  | in_subquery  |
+------------+----------------+------------+----------------+---------------------+--------------+--------------------------+-------------+---------------------+-----------------------+--------------+
| TRUE       | FALSE          | TRUE       | TRUE           | NULL                | NULL         | NULL                     | TRUE        | FALSE               | NULL                  | TRUE         |
+------------+----------------+------------+----------------+---------------------+--------------+--------------------------+-------------+---------------------+-----------------------+--------------+

结果说明:

  • ✅ IN 检查值是否在列表中
  • ⚠️ 列表含NULL且未找到匹配：返回NULL（UNKNOWN）
  • ⚠️ 列表含NULL且找到匹配：返回TRUE
  • ⚠️ NOT IN 遇到NULL时结果可能不符合直觉
  • ✅ 支持子查询作为值列表

实际应用场景:

-- 场景1: 多状态查询
SELECT order_id, status, amount
FROM orders
WHERE status IN ('paid', 'shipped', 'delivered')

-- 场景2: 黑名单过滤
SELECT user_id, username
FROM users
WHERE user_id NOT IN (
    SELECT user_id FROM blacklist
)

-- 场景3: 多值精确匹配
SELECT product_id, category, price
FROM products
WHERE category IN ('Electronics', 'Books', 'Clothing')
  AND brand IN ('Apple', 'Samsung', 'Huawei')

-- 场景4: VIP用户查询
SELECT *
FROM orders
WHERE user_id IN (SELECT user_id FROM vip_users)

-- 场景5: 排除测试数据
SELECT * 
FROM transactions
WHERE account_id NOT IN (1, 2, 3, 999, 1000)  -- 测试账号

2. 逻辑函数 (Logical Functions)

2.1 函数清单

函数名称中文说明语法示例返回类型
AND逻辑与boolean1 AND boolean2BOOLEAN
OR逻辑或boolean1 OR boolean2BOOLEAN
NOT逻辑非NOT booleanBOOLEAN
IS FALSE严格FALSE检查boolean IS FALSEBOOLEAN
IS NOT FALSE非FALSE检查boolean IS NOT FALSEBOOLEAN
IS TRUE严格TRUE检查boolean IS TRUEBOOLEAN
IS NOT TRUE非TRUE检查boolean IS NOT TRUEBOOLEAN
IS UNKNOWNUNKNOWN检查boolean IS UNKNOWNBOOLEAN
IS NOT UNKNOWN非UNKNOWN检查boolean IS NOT UNKNOWNBOOLEAN

2.2 详细测试示例

测试 2.1: AND/OR/NOT三值逻辑

测试目的: 验证SQL的三值逻辑（TRUE, FALSE, UNKNOWN）

测试SQL:

SELECT 
    -- AND真值表
    TRUE AND TRUE as and_tt,
    TRUE AND FALSE as and_tf,
    TRUE AND NULL as and_tn,
    FALSE AND TRUE as and_ft,
    FALSE AND FALSE as and_ff,
    FALSE AND NULL as and_fn,
    NULL AND TRUE as and_nt,
    NULL AND FALSE as and_nf,
    NULL AND NULL as and_nn,
    
    -- OR真值表
    TRUE OR TRUE as or_tt,
    TRUE OR FALSE as or_tf,
    TRUE OR NULL as or_tn,
    FALSE OR FALSE as or_ff,
    FALSE OR NULL as or_fn,
    NULL OR NULL as or_nn,
    
    -- NOT真值表
    NOT TRUE as not_t,
    NOT FALSE as not_f,
    NOT NULL as not_n
FROM (VALUES (1)) AS T(x)

执行结果:

+--------+--------+--------+--------+--------+--------+--------+--------+--------+-------+-------+-------+-------+-------+-------+--------+--------+--------+
| and_tt | and_tf | and_tn | and_ft | and_ff | and_fn | and_nt | and_nf | and_nn | or_tt | or_tf | or_tn | or_ff | or_fn | or_nn | not_t  | not_f  | not_n  |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+-------+-------+-------+-------+-------+-------+--------+--------+--------+
| TRUE   | FALSE  | NULL   | FALSE  | FALSE  | FALSE  | NULL   | FALSE  | NULL   | TRUE  | TRUE  | TRUE  | FALSE | NULL  | NULL  | FALSE  | TRUE   | NULL   |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+-------+-------+-------+-------+-------+-------+--------+--------+--------+

三值逻辑真值表:

AND操作:

ABA AND B
TRUETRUETRUE
TRUEFALSEFALSE
TRUENULLNULL
FALSETRUEFALSE
FALSEFALSEFALSE
FALSENULLFALSE
NULLTRUENULL
NULLFALSEFALSE
NULLNULLNULL

OR操作:

ABA OR B
TRUETRUETRUE
TRUEFALSETRUE
TRUENULLTRUE
FALSEFALSEFALSE
FALSENULLNULL
NULLNULLNULL

NOT操作:

ANOT A
TRUEFALSE
FALSETRUE
NULLNULL

结果说明:

  • ✅ FALSE AND anything = FALSE（短路）
  • ✅ TRUE OR anything = TRUE（短路）
  • ⚠️ NULL参与运算通常导致NULL结果
  • ✅ 遵循SQL三值逻辑标准

实际应用场景:

-- 场景1: 复合条件过滤
SELECT * FROM orders
WHERE (status = 'paid' OR status = 'shipped')
  AND amount > 100
  AND (discount IS NULL OR discount < 0.3)

-- 场景2: 多条件搜索
SELECT * FROM products
WHERE (category = 'Electronics' AND brand = 'Apple')
   OR (category = 'Books' AND price < 50)

-- 场景3: 排除特定组合
SELECT * FROM users
WHERE NOT (is_blocked AND is_deleted)

-- 场景4: 可选过滤条件
SELECT * FROM logs
WHERE level = 'ERROR'
  AND (user_id IS NULL OR user_id = 12345)  -- user_id可选
测试 2.2: IS TRUE/FALSE/UNKNOWN

测试目的: 验证严格的布尔值检查

测试SQL:

SELECT 
    -- IS TRUE（仅TRUE返回TRUE）
    TRUE IS TRUE as is_true_t,
    FALSE IS TRUE as is_true_f,
    NULL IS TRUE as is_true_n,
    (5 > 3) IS TRUE as is_true_expr,
    
    -- IS NOT TRUE（FALSE或NULL返回TRUE）
    TRUE IS NOT TRUE as not_true_t,
    FALSE IS NOT TRUE as not_true_f,
    NULL IS NOT TRUE as not_true_n,
    
    -- IS FALSE
    FALSE IS FALSE as is_false_f,
    TRUE IS FALSE as is_false_t,
    NULL IS FALSE as is_false_n,
    
    -- IS NOT FALSE
    FALSE IS NOT FALSE as not_false_f,
    TRUE IS NOT FALSE as not_false_t,
    NULL IS NOT FALSE as not_false_n,
    
    -- IS UNKNOWN
    NULL IS UNKNOWN as is_unknown_n,
    TRUE IS UNKNOWN as is_unknown_t,
    (1 = NULL) IS UNKNOWN as is_unknown_expr,
    
    -- IS NOT UNKNOWN
    NULL IS NOT UNKNOWN as not_unknown_n,
    TRUE IS NOT UNKNOWN as not_unknown_t
FROM (VALUES (1)) AS T(x)

执行结果:

+------------+------------+------------+--------------+--------------+--------------+--------------+-------------+-------------+-------------+--------------+--------------+--------------+---------------+---------------+------------------+----------------+----------------+
| is_true_t  | is_true_f  | is_true_n  | is_true_expr | not_true_t   | not_true_f   | not_true_n   | is_false_f  | is_false_t  | is_false_n  | not_false_f  | not_false_t  | not_false_n  | is_unknown_n  | is_unknown_t  | is_unknown_expr  | not_unknown_n  | not_unknown_t  |
+------------+------------+------------+--------------+--------------+--------------+--------------+-------------+-------------+-------------+--------------+--------------+--------------+---------------+---------------+------------------+----------------+----------------+
| TRUE       | FALSE      | FALSE      | TRUE         | FALSE        | TRUE         | TRUE         | TRUE        | FALSE       | FALSE       | FALSE        | TRUE         | TRUE         | TRUE          | FALSE         | TRUE             | FALSE          | TRUE           |
+------------+------------+------------+--------------+--------------+--------------+--------------+-------------+-------------+-------------+--------------+--------------+--------------+---------------+---------------+------------------+----------------+----------------+

结果说明:

  • ✅ IS TRUE: 仅当值为TRUE时返回TRUE
  • ✅ IS FALSE: 仅当值为FALSE时返回TRUE
  • ✅ IS UNKNOWN: 仅当值为NULL时返回TRUE
  • ✅ IS NOT TRUE = IS FALSE OR IS UNKNOWN
  • ✅ IS NOT FALSE = IS TRUE OR IS UNKNOWN

实际应用场景:

-- 场景1: 严格的布尔检查（排除NULL）
SELECT * FROM products
WHERE (is_active IS TRUE) AND (is_deleted IS FALSE)

-- 场景2: 处理三值逻辑的CASE
SELECT user_id,
       CASE 
           WHEN age > 18 IS TRUE THEN 'Adult'
           WHEN age > 18 IS FALSE THEN 'Minor'
           WHEN age > 18 IS UNKNOWN THEN 'Unknown'
       END as age_group
FROM users

-- 场景3: 查找缺失的布尔值
SELECT * FROM config
WHERE enabled IS UNKNOWN  -- 找出NULL值

-- 场景4: 数据质量检查
SELECT 
    COUNT(*) as total,
    COUNT(*) FILTER (WHERE is_valid IS TRUE) as valid_count,
    COUNT(*) FILTER (WHERE is_valid IS FALSE) as invalid_count,
    COUNT(*) FILTER (WHERE is_valid IS UNKNOWN) as unknown_count
FROM data_records

3. 算术函数 (Arithmetic Functions)

3.1 函数清单

函数名称中文说明语法示例返回类型
+加法n1 + n2NUMERIC
-减法/取负n1 - n2, -nNUMERIC
*乘法n1 * n2NUMERIC
/除法n1 / n2NUMERIC
%, MOD取模n1 % n2, MOD(n1, n2)NUMERIC
POWER幂运算POWER(base, exp)DOUBLE
ABS绝对值ABS(n)NUMERIC
SQRT平方根SQRT(n)DOUBLE
LN自然对数LN(n)DOUBLE
LOG10常用对数LOG10(n)DOUBLE
LOG2二进制对数LOG2(n)DOUBLE
LOG对数LOG([base,] n)DOUBLE
EXPe的指数EXP(n)DOUBLE
CEIL, CEILING向上取整CEIL(n)INTEGER
FLOOR向下取整FLOOR(n)INTEGER
ROUND四舍五入ROUND(n [, scale])NUMERIC
TRUNCATE, TRUNC截断TRUNCATE(n [, scale])NUMERIC
SIN正弦SIN(radian)DOUBLE
COS余弦COS(radian)DOUBLE
TAN正切TAN(radian)DOUBLE
COT余切COT(radian)DOUBLE
ASIN反正弦ASIN(n)DOUBLE
ACOS反余弦ACOS(n)DOUBLE
ATAN反正切ATAN(n)DOUBLE
ATAN2两参数反正切ATAN2(y, x)DOUBLE
SINH双曲正弦SINH(n)DOUBLE
COSH双曲余弦COSH(n)DOUBLE
TANH双曲正切TANH(n)DOUBLE
DEGREES弧度转角度DEGREES(radian)DOUBLE
RADIANS角度转弧度RADIANS(degree)DOUBLE
SIGN符号函数SIGN(n)INTEGER
PI圆周率PI()DOUBLE
E自然常数E()DOUBLE
RAND随机数RAND([seed])DOUBLE
RAND_INTEGER随机整数RAND_INTEGER(bound)INTEGER

3.2 详细测试示例

测试 3.1: 基础算术运算

测试SQL:

SELECT 
    -- 四则运算
    10 + 5 as addition,
    10 - 3 as subtraction,
    6 * 7 as multiplication,
    20 / 4 as integer_division,
    20.0 / 3.0 as decimal_division,
    
    -- 取模
    17 % 5 as modulo,
    MOD(17, 5) as modulo_function,
    -17 % 5 as negative_modulo,
    17.5 % 5.2 as decimal_modulo,
    
    -- 取负
    -100 as negation,
    -(5 + 3) as expression_negation,
    
    -- 运算符优先级
    10 + 5 * 2 as precedence_test,
    (10 + 5) * 2 as parentheses_test,
    
    -- 类型转换
    CAST(10 AS DOUBLE) / 3 as force_decimal,
    10 / CAST(3 AS DOUBLE) as force_decimal2
FROM (VALUES (1)) AS T(x)

执行结果:

+-----------+--------------+-----------------+-------------------+-------------------+---------+------------------+------------------+-----------------+-----------+----------------------+-----------------+--------------------+----------------+-----------------+
| addition  | subtraction  | multiplication  | integer_division  | decimal_division  | modulo  | modulo_function  | negative_modulo  | decimal_modulo  | negation  | expression_negation  | precedence_test | parentheses_test   | force_decimal  | force_decimal2  |
+-----------+--------------+-----------------+-------------------+-------------------+---------+------------------+------------------+-----------------+-----------+----------------------+-----------------+--------------------+----------------+-----------------+
| 15        | 7            | 42              | 5                 | 6.6666666667      | 2       | 2                | -2               | 2.1             | -100      | -8                   | 20              | 30                 | 3.3333333333   | 3.3333333333    |
+-----------+--------------+-----------------+-------------------+-------------------+---------+------------------+------------------+-----------------+-----------+----------------------+-----------------+--------------------+----------------+-----------------+

实际应用场景:

-- 场景1: 订单金额计算
SELECT order_id,
       quantity,
       unit_price,
       quantity * unit_price as subtotal,
       quantity * unit_price * 1.13 as total_with_tax,
       quantity * unit_price * (1 - discount_rate) as discounted_amount
FROM order_items

-- 场景2: 分页计算
SELECT 
    page_number,
    page_size,
    (page_number - 1) * page_size as offset_value,
    page_size as limit_value
FROM pagination_config

-- 场景3: 百分比计算
SELECT 
    category,
    COUNT(*) as count,
    CAST(COUNT(*) AS DOUBLE) / (SELECT COUNT(*) FROM products) * 100 as percentage
FROM products
GROUP BY category

Flink SQL 函数完整清单与测试指南

  • 算术函数高级用法和字符串函数详解

目录

3.3 算术函数高级测试

测试 3.2: 幂运算和根运算

测试SQL:

SELECT 
    -- 幂运算
    POWER(2, 10) as power_1024,
    POWER(5, 3) as power_125,
    POWER(10, -2) as power_negative_exp,
    POWER(4, 0.5) as power_sqrt,
    POWER(27, 1.0/3.0) as power_cube_root,
    
    -- 平方根
    SQRT(16) as sqrt_16,
    SQRT(2) as sqrt_2,
    SQRT(100) as sqrt_100,
    SQRT(0.25) as sqrt_decimal,
    
    -- 验证关系
    POWER(SQRT(16), 2) as verify_sqrt,
    SQRT(POWER(5, 2)) as verify_power
FROM (VALUES (1)) AS T(x)

执行结果:

+-------------+------------+----------------------+-------------+-------------------+---------+-----------+----------+--------------+--------------+--------------+
| power_1024  | power_125  | power_negative_exp   | power_sqrt  | power_cube_root   | sqrt_16 | sqrt_2    | sqrt_100 | sqrt_decimal | verify_sqrt  | verify_power |
+-------------+------------+----------------------+-------------+-------------------+---------+-----------+----------+--------------+--------------+--------------+
| 1024.0      | 125.0      | 0.01                 | 2.0         | 3.0               | 4.0     | 1.414214  | 10.0     | 0.5          | 16.0         | 5.0          |
+-------------+------------+----------------------+-------------+-------------------+---------+-----------+----------+--------------+--------------+--------------+

实际应用场景:

-- 场景1: 复利计算
SELECT 
    initial_amount,
    annual_rate,
    years,
    initial_amount * POWER(1 + annual_rate, years) as final_amount,
    initial_amount * POWER(1 + annual_rate, years) - initial_amount as interest_earned
FROM investments

-- 场景2: 欧几里得距离
SELECT 
    p1_id, p2_id,
    SQRT(POWER(x2 - x1, 2) + POWER(y2 - y1, 2)) as distance_2d,
    SQRT(POWER(x2 - x1, 2) + POWER(y2 - y1, 2) + POWER(z2 - z1, 2)) as distance_3d
FROM point_pairs

-- 场景3: 标准差计算（手动）
SELECT 
    category,
    SQRT(AVG(POWER(value - avg_value, 2))) as std_deviation
FROM (
    SELECT category, value, AVG(value) OVER (PARTITION BY category) as avg_value
    FROM measurements
) T
GROUP BY category
测试 3.3: 对数和指数函数

测试SQL:

SELECT 
    -- 自然对数（ln）
    LN(1) as ln_1,
    LN(E()) as ln_e,
    LN(10) as ln_10,
    LN(EXP(1)) as ln_exp_1,
    
    -- 常用对数（log10）
    LOG10(1) as log10_1,
    LOG10(10) as log10_10,
    LOG10(100) as log10_100,
    LOG10(1000) as log10_1000,
    
    -- 二进制对数（log2）
    LOG2(1) as log2_1,
    LOG2(2) as log2_2,
    LOG2(8) as log2_8,
    LOG2(1024) as log2_1024,
    
    -- 自定义底数
    LOG(2, 8) as log_2_8,
    LOG(3, 27) as log_3_27,
    LOG(10, 100) as log_10_100,
    
    -- 指数函数
    EXP(0) as exp_0,
    EXP(1) as exp_1,
    EXP(2) as exp_2,
    EXP(-1) as exp_neg_1,
    
    -- 常量
    PI() as pi_value,
    E() as e_value
FROM (VALUES (1)) AS T(x)

执行结果:

+-------+-------+-----------+-----------+-----------+-----------+------------+-------------+---------+---------+---------+------------+----------+-----------+-------------+-------+-----------+-----------+------------+-----------+-----------+
| ln_1  | ln_e  | ln_10     | ln_exp_1  | log10_1   | log10_10  | log10_100  | log10_1000  | log2_1  | log2_2  | log2_8  | log2_1024  | log_2_8  | log_3_27  | log_10_100  | exp_0 | exp_1     | exp_2     | exp_neg_1  | pi_value  | e_value   |
+-------+-------+-----------+-----------+-----------+-----------+------------+-------------+---------+---------+---------+------------+----------+-----------+-------------+-------+-----------+-----------+------------+-----------+-----------+
| 0.0   | 1.0   | 2.302585  | 1.0       | 0.0       | 1.0       | 2.0        | 3.0         | 0.0     | 1.0     | 3.0     | 10.0       | 3.0      | 3.0       | 2.0         | 1.0   | 2.718282  | 7.389056  | 0.367879   | 3.141593  | 2.718282  |
+-------+-------+-----------+-----------+-----------+-----------+------------+-------------+---------+---------+---------+------------+----------+-----------+-------------+-------+-----------+-----------+------------+-----------+-----------+

实际应用场景:

-- 场景1: 增长率计算（对数变换）
SELECT 
    date,
    value,
    LOG10(value) as log_value,
    LN(value / LAG(value) OVER (ORDER BY date)) as growth_rate
FROM time_series

-- 场景2: 信息熵计算
SELECT 
    category,
    -SUM(probability * LOG2(probability)) as entropy
FROM probability_distribution
GROUP BY category

-- 场景3: pH值计算
SELECT 
    sample_id,
    hydrogen_concentration,
    -LOG10(hydrogen_concentration) as ph_value
FROM chemical_samples

-- 场景4: 指数衰减模型
SELECT 
    time_elapsed,
    initial_value * EXP(-decay_rate * time_elapsed) as current_value
FROM decay_process
测试 3.4: 取整函数详解

测试SQL:

SELECT 
    -- CEIL/CEILING（向上取整）
    CEIL(3.1) as ceil_31,
    CEIL(3.9) as ceil_39,
    CEIL(-3.1) as ceil_neg31,
    CEIL(-3.9) as ceil_neg39,
    CEILING(3.5) as ceiling_35,
    
    -- FLOOR（向下取整）
    FLOOR(3.1) as floor_31,
    FLOOR(3.9) as floor_39,
    FLOOR(-3.1) as floor_neg31,
    FLOOR(-3.9) as floor_neg39,
    
    -- ROUND（四舍五入）
    ROUND(3.14159) as round_pi,
    ROUND(3.14159, 2) as round_pi_2,
    ROUND(3.14159, 4) as round_pi_4,
    ROUND(3.5) as round_35,
    ROUND(4.5) as round_45,
    ROUND(1234.5678, -2) as round_neg2,
    ROUND(-3.5) as round_neg35,
    
    -- TRUNCATE（截断）
    TRUNCATE(3.14159) as trunc_pi,
    TRUNCATE(3.14159, 2) as trunc_pi_2,
    TRUNCATE(3.14159, 4) as trunc_pi_4,
    TRUNCATE(-3.7) as trunc_neg37,
    TRUNCATE(1234.5678, -2) as trunc_neg2
FROM (VALUES (1)) AS T(x)

执行结果:

+----------+----------+-------------+-------------+-------------+-----------+-----------+-------------+-------------+----------+-------------+--------------+-----------+-----------+-------------+-------------+-----------+-----------+--------------+--------------+-----------+-------------+
| ceil_31  | ceil_39  | ceil_neg31  | ceil_neg39  | ceiling_35  | floor_31  | floor_39  | floor_neg31 | floor_neg39 | round_pi | round_pi_2  | round_pi_4   | round_35  | round_45  | round_neg2  | round_neg35 | trunc_pi  | trunc_pi_2 | trunc_pi_4   | trunc_neg37  | trunc_neg2 |
+----------+----------+-------------+-------------+-------------+-----------+-----------+-------------+-------------+----------+-------------+--------------+-----------+-----------+-------------+-------------+-----------+-----------+--------------+--------------+------------+
| 4        | 4        | -3          | -3          | 4           | 3         | 3         | -4          | -4          | 3        | 3.14        | 3.1416       | 4         | 5         | 1200        | -4          | 3         | 3.14      | 3.1415       | -3           | 1200       |
+----------+----------+-------------+-------------+-------------+-----------+-----------+-------------+-------------+----------+-------------+--------------+-----------+-----------+-------------+-------------+-----------+-----------+--------------+--------------+------------+

说明:

  • CEIL: 向正无穷方向取整（向上）
  • FLOOR: 向负无穷方向取整（向下）
  • ROUND: 四舍五入，可指定小数位数（负数表示整数部分）
  • TRUNCATE: 直接截断，不进行四舍五入

实际应用场景:

-- 场景1: 价格向上取整
SELECT 
    product_id,
    calculated_price,
    CEIL(calculated_price) as display_price
FROM product_prices

-- 场景2: 计算页数
SELECT 
    CEIL(CAST(total_records AS DOUBLE) / page_size) as total_pages
FROM (
    SELECT COUNT(*) as total_records, 20 as page_size
    FROM large_table
) T

-- 场景3: 金额格式化（保留两位小数）
SELECT 
    order_id,
    ROUND(subtotal, 2) as subtotal,
    ROUND(tax, 2) as tax,
    ROUND(subtotal + tax, 2) as total
FROM orders

-- 场景4: 时间槽（整点分组）
SELECT 
    FLOOR(HOUR(event_time)) as hour_slot,
    COUNT(*) as event_count
FROM events
GROUP BY hour_slot
测试 3.5: 三角函数和反三角函数

测试SQL:

SELECT 
    -- 常量
    PI() as pi,
    E() as e,
    
    -- 基础三角函数（输入弧度）
    SIN(0) as sin_0,
    SIN(PI()/6) as sin_30deg,
    SIN(PI()/4) as sin_45deg,
    SIN(PI()/2) as sin_90deg,
    
    COS(0) as cos_0,
    COS(PI()/3) as cos_60deg,
    COS(PI()/2) as cos_90deg,
    
    TAN(0) as tan_0,
    TAN(PI()/4) as tan_45deg,
    
    COT(PI()/4) as cot_45deg,
    COT(PI()/2) as cot_90deg,
    
    -- 反三角函数（输出弧度）
    ASIN(0) as asin_0,
    ASIN(0.5) as asin_05,
    ASIN(1) as asin_1,
    
    ACOS(1) as acos_1,
    ACOS(0.5) as acos_05,
    ACOS(0) as acos_0,
    
    ATAN(0) as atan_0,
    ATAN(1) as atan_1,
    
    ATAN2(1, 1) as atan2_1_1,
    ATAN2(1, 0) as atan2_1_0,
    ATAN2(0, 1) as atan2_0_1,
    
    -- 角度弧度转换
    DEGREES(PI()) as deg_180,
    DEGREES(PI()/2) as deg_90,
    RADIANS(180) as rad_180,
    RADIANS(90) as rad_90,
    
    -- 双曲函数
    SINH(0) as sinh_0,
    SINH(1) as sinh_1,
    COSH(0) as cosh_0,
    TANH(0) as tanh_0
FROM (VALUES (1)) AS T(x)

执行结果:

+----------+----------+--------+-----------+-----------+-----------+--------+-----------+-----------+--------+-----------+-----------+-----------+----------+-----------+----------+----------+-----------+----------+----------+----------+-----------+-----------+-----------+----------+---------+-----------+--------+---------+-----------+----------+-------+
| pi       | e        | sin_0  | sin_30deg | sin_45deg | sin_90deg | cos_0  | cos_60deg | cos_90deg | tan_0  | tan_45deg | cot_45deg | cot_90deg | asin_0   | asin_05   | asin_1   | acos_1   | acos_05   | acos_0   | atan_0   | atan_1   | atan2_1_1 | atan2_1_0 | atan2_0_1 | deg_180  | deg_90  | rad_180   | rad_90 | sinh_0  | sinh_1    | cosh_0  | tanh_0  |
+----------+----------+--------+-----------+-----------+-----------+--------+-----------+-----------+--------+-----------+-----------+-----------+----------+-----------+----------+----------+-----------+----------+----------+----------+-----------+-----------+-----------+----------+---------+-----------+--------+---------+-----------+---------+---------+
| 3.141593 | 2.718282 | 0.0    | 0.5       | 0.707107  | 1.0       | 1.0    | 0.5       | 0.0       | 0.0    | 1.0       | 1.0       | 0.0       | 0.0      | 0.523599  | 1.570796 | 0.0      | 1.047198  | 1.570796 | 0.0      | 0.785398 | 0.785398  | 1.570796  | 0.0       | 180.0    | 90.0    | 3.141593  | 1.5708 | 0.0     | 1.175201  | 1.0     | 0.0     |
+----------+----------+--------+-----------+-----------+-----------+--------+-----------+-----------+--------+-----------+-----------+-----------+----------+-----------+----------+----------+-----------+----------+----------+----------+-----------+-----------+-----------+----------+---------+-----------+--------+---------+-----------+---------+---------+

实际应用场景:

-- 场景1: 地理坐标距离计算（Haversine公式）
SELECT 
    p1.id as point1,
    p2.id as point2,
    6371 * ACOS(
        SIN(RADIANS(p1.latitude)) * SIN(RADIANS(p2.latitude)) +
        COS(RADIANS(p1.latitude)) * COS(RADIANS(p2.latitude)) *
        COS(RADIANS(p2.longitude - p1.longitude))
    ) as distance_km
FROM geo_points p1
CROSS JOIN geo_points p2
WHERE p1.id < p2.id

-- 场景2: 方位角计算
SELECT 
    start_point,
    end_point,
    DEGREES(ATAN2(
        SIN(RADIANS(end_lon - start_lon)) * COS(RADIANS(end_lat)),
        COS(RADIANS(start_lat)) * SIN(RADIANS(end_lat)) -
        SIN(RADIANS(start_lat)) * COS(RADIANS(end_lat)) * COS(RADIANS(end_lon - start_lon))
    )) as bearing
FROM routes

-- 场景3: 周期性数据建模
SELECT 
    time_hour,
    temperature,
    20 + 10 * SIN(RADIANS(time_hour * 15 - 90)) as expected_temp
FROM hourly_weather
测试 3.6: 其他数学函数

测试SQL:

SELECT 
    -- 绝对值
    ABS(10) as abs_pos,
    ABS(-10) as abs_neg,
    ABS(-3.14) as abs_decimal,
    
    -- 符号函数
    SIGN(10) as sign_pos,
    SIGN(-10) as sign_neg,
    SIGN(0) as sign_zero,
    SIGN(0.0001) as sign_small_pos,
    SIGN(-0.0001) as sign_small_neg,
    
    -- MOD函数
    MOD(17, 5) as mod_17_5,
    MOD(-17, 5) as mod_neg17_5,
    MOD(17, -5) as mod_17_neg5,
    MOD(-17, -5) as mod_neg17_neg5,
    17 % 5 as operator_mod,
    
    -- 随机数（每次执行结果不同）
    RAND() as random_float,
    RAND(42) as random_seeded,
    RAND_INTEGER(100) as random_int_100,
    RAND_INTEGER(10, 20) as random_int_range
FROM (VALUES (1)) AS T(x)

执行结果（随机数每次不同）:

+----------+----------+-------------+-----------+-----------+------------+----------------+------------------+-----------+--------------+-------------+------------------+---------------+-----------------+----------------+---------------+-------------------+
| abs_pos  | abs_neg  | abs_decimal | sign_pos  | sign_neg  | sign_zero  | sign_small_pos | sign_small_neg   | mod_17_5  | mod_neg17_5  | mod_17_neg5 | mod_neg17_neg5   | operator_mod  | random_float    | random_seeded  | random_int_100 | random_int_range  |
+----------+----------+-------------+-----------+-----------+------------+----------------+------------------+-----------+--------------+-------------+------------------+---------------+-----------------+----------------+---------------+-------------------+
| 10       | 10       | 3.14        | 1         | -1        | 0          | 1              | -1               | 2         | -2           | 2           | -2               | 2             | 0.634258        | 0.834729       | 57            | 14                |
+----------+----------+-------------+-----------+-----------+------------+----------------+------------------+-----------+--------------+-------------+------------------+---------------+-----------------+----------------+---------------+-------------------+

实际应用场景:

-- 场景1: 价格差异计算
SELECT 
    product_id,
    current_price,
    standard_price,
    ABS(current_price - standard_price) as price_difference,
    SIGN(current_price - standard_price) as price_trend  -- 1=上涨, -1=下跌, 0=不变
FROM product_prices

-- 场景2: 随机抽样
SELECT * FROM large_dataset
WHERE RAND() < 0.1  -- 随机抽取约10%的数据

-- 场景3: 生成随机测试数据
SELECT 
    RAND_INTEGER(1, 1000000) as user_id,
    CONCAT('user', CAST(RAND_INTEGER(1000) AS STRING)) as username,
    RAND() * 1000 as random_score
FROM UNNEST(SEQUENCE(1, 100)) as T(i)

-- 场景4: 计算奇偶性
SELECT 
    number,
    MOD(number, 2) as remainder,
    CASE WHEN MOD(number, 2) = 0 THEN 'Even' ELSE 'Odd' END as parity
FROM numbers_table

-- 场景5: 周期性任务（每3行取一行）
SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER () as rn
    FROM source_table
) T
WHERE MOD(rn, 3) = 1

4. 字符串函数 (String Functions)

4.1 函数清单

函数名称中文说明语法示例返回类型
CONCAT字符串连接CONCAT(str1, str2, ...)STRING
CONCAT_WS使用分隔符连接CONCAT_WS(sep, str1, ...)STRING
SUBSTRING/SUBSTR提取子串SUBSTRING(str, start[, len])STRING
UPPER转大写UPPER(str)STRING
LOWER转小写LOWER(str)STRING
INITCAP首字母大写INITCAP(str)STRING
TRIM去除首尾字符TRIM([BOTH|LEADING|TRAILING] str1 FROM str2)STRING
LTRIM去除左侧空格LTRIM(str)STRING
RTRIM去除右侧空格RTRIM(str)STRING
LENGTH/CHAR_LENGTH字符串长度LENGTH(str)INTEGER
POSITION查找子串位置POSITION(substr IN str)INTEGER
LOCATE查找子串位置LOCATE(substr, str[, start])INTEGER
INSTR查找子串位置INSTR(str, substr)INTEGER
REPLACE替换字符串REPLACE(str, search, repl)STRING
REGEXP_REPLACE正则替换REGEXP_REPLACE(str, pattern, repl)STRING
REGEXP_EXTRACT正则提取REGEXP_EXTRACT(str, pattern[, group])STRING
OVERLAY覆盖字符串OVERLAY(str PLACING new FROM start [FOR len])STRING
LPAD左填充LPAD(str, length, pad)STRING
RPAD右填充RPAD(str, length, pad)STRING
REPEAT重复字符串REPEAT(str, times)STRING
REVERSE反转字符串REVERSE(str)STRING
SPLIT_INDEX分割取元素SPLIT_INDEX(str, sep, index)STRING
CHRASCII转字符CHR(ascii_code)STRING
ASCII字符转ASCIIASCII(str)INTEGER
ENCODE字符串编码ENCODE(str, charset)BINARY
DECODE字符串解码DECODE(binary, charset)STRING
TO_BASE64Base64编码TO_BASE64(binary)STRING
FROM_BASE64Base64解码FROM_BASE64(str)BINARY
MD5MD5哈希MD5(str)STRING
SHA1SHA1哈希SHA1(str)STRING
SHA224/SHA256/SHA384/SHA512SHA系列哈希SHA256(str)STRING
UUID生成UUIDUUID()STRING

4.2 详细测试示例

测试 4.1: 字符串连接

测试SQL:

SELECT 
    -- CONCAT：直接连接
    CONCAT('Hello', ' ', 'World') as concat_basic,
    CONCAT('User', '123') as concat_two,
    CONCAT('A', NULL, 'B') as concat_with_null,
    CONCAT(CAST(123 AS STRING), '-', CAST(456 AS STRING)) as concat_numbers,
    
    -- CONCAT_WS：使用分隔符
    CONCAT_WS('-', '2024', '10', '18') as concat_ws_date,
    CONCAT_WS(',', 'A', 'B', 'C', 'D') as concat_ws_list,
    CONCAT_WS(',', 'A', NULL, 'C') as concat_ws_skip_null,
    CONCAT_WS('|', 'Name', 'Age', 'City') as concat_ws_pipe,
    CONCAT_WS(' ', 'John', 'M.', 'Doe') as concat_ws_name,
    
    -- 实际应用：构建完整信息
    CONCAT_WS(' - ', brand, model, color) as product_display,
    CONCAT('https://', domain, '/', path) as full_url
FROM (VALUES 
    ('Apple', 'iPhone 15', 'Black', 'example.com', 'products/123')
) AS T(brand, model, color, domain, path)

执行结果:

+---------------+------------+-------------------+------------------+------------------+-----------------+----------------------+-----------------+----------------+-------------------------+------------------+
| concat_basic  | concat_two | concat_with_null  | concat_numbers   | concat_ws_date   | concat_ws_list  | concat_ws_skip_null  | concat_ws_pipe  | concat_ws_name | product_display         | full_url         |
+---------------+------------+-------------------+------------------+------------------+-----------------+----------------------+-----------------+----------------+-------------------------+------------------+
| Hello World   | User123    | NULL              | 123-456          | 2024-10-18       | A,B,C,D         | A,C                  | Name\|Age\|City | John M. Doe    | Apple - iPhone 15 - Black | https://example.com/products/123 |
+---------------+------------+-------------------+------------------+------------------+-----------------+----------------------+-----------------+----------------+-------------------------+------------------+

实际应用场景:

-- 场景1: 生成完整地址
SELECT 
    user_id,
    CONCAT_WS(', ', country, province, city, district, street, building) as full_address
FROM addresses

-- 场景2: 生成显示名称
SELECT 
    product_id,
    CONCAT(brand, ' ', model, ' (', color, ', ', size, ')') as display_name
FROM products

-- 场景3: 生成CSV行
SELECT 
    CONCAT_WS(',', 
        CAST(id AS STRING),
        CONCAT('"', name, '"'),  -- 字符串加引号
        email,
        phone
    ) as csv_line
FROM users

-- 场景4: 构建SQL语句
SELECT 
    CONCAT('UPDATE users SET status = ''active'' WHERE id = ', CAST(user_id AS STRING)) as update_sql
FROM pending_activations
# flink # sql # 大数据
Logo
亚马逊云科技技术品牌专区

更多推荐

open-code-review Codex 插件教程：对话式调用评审技能指南

本文是一份面向新手的 **open-code-review Codex 插件教程**，教你在 Codex 中通过对话式方式调用 open-code-review 的 AI 代码评审技能。open-code-review 是阿里巴巴开源的 AI 代码评审工具，采用"确定性流水线 + LLM Agent"混合架构，内置多语言评审规则（NPE、线程安全、XSS、SQL 注入等），可在 Codex 里用一

avatar 亚马逊云科技技术品牌专区

OBS Studio 完整指南：免费开源录屏与直播软件从零上手

想做一场像模像样的直播，或者把屏幕内容录成教程视频，但一想到商用软件的订阅费就有些犹豫？其实你不需要花一分钱。OBS Studio 是一款免费开源的直播与录屏软件，支持 Windows、macOS 和 Linux，采集、混音、编码、推流、录制，一条流水线全包了。## 先搞清楚 OBS Studio 是什么OBS Studio 是一个"场景化"的采集合成工具：你把摄像头、窗口、浏览器、图片、

avatar 亚马逊云科技技术品牌专区

如何用MediaCrawler爬取小红书抖音数据？7大平台爬虫从安装到实战完整指南

MediaCrawler 是一个支持小红书、抖音、快手、B站、微博、百度贴吧、知乎的开源自媒体数据采集工具。它能按关键词、按帖子、按创作者主页三种方式抓取笔记/视频内容、评论甚至二级评论，并把结果存成 JSON、CSV、Excel 或数据库。适合做市场调研、竞品监控、内容选题的运营人员，以及想要现成爬虫框架而不想从零写 JS 逆向的开发者。## 从一次"手动抄数据"说起想象这个场景：你负责

avatar 亚马逊云科技技术品牌专区