1
2
3
4
5
6
7
8
9

-- 加、减、乘、除、取余
SELECT 
    10 + 5 AS addition,           -- 15
    10 - 5 AS subtraction,        -- 5  
    10 * 5 AS multiplication,     -- 50
    10 / 3 AS division,           -- 3.3333
    10 % 3 AS modulus,            -- 1
    10 DIV 3 AS integer_division  -- 3 (Hive 3.0+)
FROM table_name LIMIT 1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 常用数学函数
SELECT
    ABS(-10) AS absolute,          -- 10（绝对值）
    ROUND(3.14159, 2) AS round2,   -- 3.14（四舍五入）
    CEIL(3.2) AS ceiling,          -- 4（向上取整）
    FLOOR(3.8) AS floor,           -- 3（向下取整）
    POWER(2, 3) AS power,          -- 8.0（平方）
    SQRT(16) AS sqrt,              -- 4.0（开根）
    EXP(1) AS exp,                 -- 2.71828（e的n次方）
    LN(10) AS ln,                  -- 2.30259（log2）
    LOG10(100) AS log10,           -- 2.0（log10）
    RAND() AS random,              -- 0.0~1.0随机数（随机数）
    SIGN(-5) AS sign_negative      -- -1（符号函数（取正负号））
FROM table_name LIMIT 1;

1
2
3
4
5
6
7

-- 角度弧度转换
SELECT
    SIN(RADIANS(30)) AS sin30,     -- 0.5
    COS(0) AS cos0,                -- 1.0
    TAN(PI()/4) AS tan45,          -- 1.0
    DEGREES(PI()) AS degrees       -- 180.0
FROM table_name LIMIT 1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

SELECT
    -- 数值比较
    10 > 5 AS greater_than,        -- TRUE
    10 <= 10 AS less_equal,        -- TRUE
    10 != 5 AS not_equal,          -- TRUE
    10 <> 5 AS not_equal2,         -- TRUE

    -- 字符串比较（字典序）
    'apple' < 'banana' AS str_compare,  -- TRUE

    -- BETWEEN
    15 BETWEEN 10 AND 20 AS between,   -- TRUE

    -- IN
    'red' IN ('red', 'blue', 'green') AS in_set,  -- TRUE

    -- LIKE 模糊匹配
    'hello' LIKE 'he%' AS like_match,  -- TRUE
    'data' LIKE 'd_t_' AS like_single  -- TRUE
FROM table_name LIMIT 1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT
    -- AND/OR/NOT
    (10 > 5) AND (3 < 4) AS and_result,   -- TRUE
    (10 < 5) OR (3 < 4) AS or_result,      -- TRUE
    NOT (10 < 5) AS not_result,            -- TRUE

    -- 处理 NULL
    NULL IS NULL AS is_null,               -- TRUE
    NULL IS NOT NULL AS is_not_null,       -- FALSE

    -- 特殊NULL处理函数
    COALESCE(NULL, 'default') AS coalesce,  -- 'default'
    NVL(NULL, 'backup') AS nvl_result,      -- 'backup'
    NULLIF(10, 10) AS nullif_same,          -- NULL
    NULLIF(10, 5) AS nullif_diff            -- 10
FROM table_name LIMIT 1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 简单CASE
SELECT 
    user_id,
    CASE age
        WHEN age < 18 THEN '未成年'
        WHEN age BETWEEN 18 AND 35 THEN '青年'
        WHEN age BETWEEN 36 AND 60 THEN '中年'
        ELSE '老年'
    END AS age_group
FROM users;

-- 搜索CASE
SELECT
    order_id,
    amount,
    CASE
        WHEN amount > 1000 THEN '大额'
        WHEN amount > 500 THEN '中额'
        WHEN amount > 0 THEN '小额'
        ELSE '异常'
    END AS amount_level
FROM orders;

1
2
3
4
5
6
7

SELECT
    CAST('123' AS INT) AS cast_int,        -- 123
    CAST(123.456 AS DECIMAL(5,2)) AS cast_decimal,  -- 123.46
    CONVERT('123.45', DOUBLE) AS convert_double,    -- 123.45
    -- 隐式转换（自动）
    10 + '5' AS implicit_cast              -- 15.0
FROM table_name LIMIT 1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

-- 基本聚合
SELECT
    COUNT(*) AS total_count,
    COUNT(DISTINCT user_id) AS distinct_users,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount,
    MIN(amount) AS min_amount,
    MAX(amount) AS max_amount,
    VARIANCE(amount) AS variance_amount,
    STDDEV(amount) AS stddev_amount
FROM transactions
WHERE dt = '2023-10-01';

-- 分组聚合
SELECT
    department,
    AVG(salary) AS avg_salary,
    PERCENTILE_APPROX(salary, 0.5) AS median_salary  -- 近似中位数
FROM employees
GROUP BY department;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 窗口函数（分析函数）
SELECT
    user_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY user_id 
        ORDER BY order_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    AVG(amount) OVER (PARTITION BY user_id) AS user_avg,
    RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS sales_rank
FROM orders;

1
2
3
4

SELECT IF(score >= 60, '及格', '不及格') AS result FROM students;
-- 注意：valueTrue 和 valueFalse 必须类型一致或可隐式转换

SELECT IF(1=1, 'a', 100); -- 会报错或隐式转 string

1
2
3
4

SELECT COALESCE(phone, email, '未知联系方式') FROM user_info;
-- 先判断 phone，若非 NULL 则返回 phone；
-- 若 phone为 NULL，则判断 email，若非 NULL 则返回 email；
-- 若 phone和 email都为 NULL，则返回字符串 '未知联系方式'。

1
2
3
4

SELECT NVL(salary, 0) FROM emp;
-- 等价于 COALESCE(salary, 0)
-- 如果 salary字段不为 NULL，就返回 salary的值；
-- 如果 salary为 NULL，就返回 0

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- 写法一：搜索式（条件可以是任意表达式）
SELECT
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 60 THEN 'C'
ELSE 'D'
END AS grade
FROM students;

-- 写法二：简单式（只能做等值判断）
SELECT
CASE gender
WHEN 'M' THEN '男'
WHEN 'F' THEN '女'
ELSE '未知'
END
FROM user_info;

1
2
3

SELECT NULLIF(a, 0); -- 当 a=0 时返回 NULL，避免除零

SELECT amount / NULLIF(qty, 0) FROM orders; -- 经典防除零写法

1
2
3
4
5

SELECT * FROM t WHERE ISNULL(col);
SELECT * FROM employee WHERE col IS NULL;

SELECT * FROM t WHERE ISNOTNULL(col); -- 等价于 col IS NOT NULL
SELECT * FROM employee WHERE col IS NOT NULL;

1
2
3
4
5

SELECT current_date(); -- 2026-05-25

SELECT current_timestamp(); -- 2026-05-25 21:42:41.123

SELECT unix_timestamp(); -- 1748180561（秒）

1
2
3
4
5
6
7

-- 字符串 → Unix 秒
SELECT unix_timestamp('2026-05-25 21:42:41'); -- 默认格式
SELECT unix_timestamp('2026/05/25', 'yyyy/MM/dd'); -- 自定义格式

-- Unix 秒 → 字符串
SELECT from_unixtime(1748180561); -- '2026-05-25 21:42:41'
SELECT from_unixtime(1748180561, 'yyyy-MM-dd'); -- '2026-05-25'

1
2
3
4
5

SELECT year('2026-05-25'), -- 2026
month('2026-05-25'), -- 5
day('2026-05-25'), -- 25
weekofyear('2026-05-25'), -- 21
quarter('2026-05-25'); -- 2

1
2
3
4
5
6
7
8
9

SELECT date_add('2026-05-25', 7); -- 2026-06-01

SELECT date_sub('2026-05-25', 7); -- 2026-05-18

SELECT add_months('2026-05-25', 1); -- 2026-06-25

SELECT datediff('2026-05-25', '2026-05-01'); -- 24

SELECT months_between('2026-05-25', '2026-01-01'); -- 4.77...

1
2
3
4
5
6
7
8
9

SELECT trunc('2026-05-25', 'MM'); -- 2026-05-01（月初）

SELECT trunc('2026-05-25', 'YY'); -- 2026-01-01（年初）

SELECT last_day('2026-05-25'); -- 2026-05-31

SELECT date_format('2026-05-25', 'yyyyMMdd'); -- '20260525'

SELECT date_format('2026-05-25 21:42:41', 'yyyy-MM-dd HH:00:00'); -- 小时桶

1
2
3
4
5
6

-- from_utc_timestamp / to_utc_timestamp
SELECT from_utc_timestamp('2026-05-25 13:42:41', 'Asia/Shanghai');
-- → '2026-05-25 21:42:41'（UTC + 8h）

SELECT to_utc_timestamp('2026-05-25 21:42:41', 'Asia/Shanghai');
-- → '2026-05-25 13:42:41'

1
2
3
4

SELECT length('hello世界'); -- 7（按字符算）

SELECT upper('abc'), lower('ABC'), initcap('hello world');
-- 'ABC', 'abc', 'Hello World'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 注意：Hive substr 索引从 1 开始
SELECT substr('abcdef', 2, 3); -- 'bcd'

SELECT substr('abcdef', -3); -- 'def'（负数从右数）

SELECT concat('a', 'b', 'c'); -- 'abc'

SELECT concat('a', NULL, 'c'); -- NULL ⚠️

SELECT concat_ws('-', 'a', NULL, 'c'); -- 'a-c'（自动忽略 NULL）

SELECT concat_ws(',', array('a', 'b', 'c')); -- 'a,b,c'（拼数组）

1
2
3
4
5
6
7
8
9

SELECT trim(' hello '); -- 'hello'

SELECT lpad('5', 3, '0'); -- '005'（左补 0）

SELECT rpad('abc', 6, '*'); -- 'abc***'

SELECT repeat('ab', 3); -- 'ababab'

SELECT reverse('hello'); -- 'olleh'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 从头开始查找字符o的出现位置(索引从1开始)
SELECT instr('hello world', 'o'); -- 5

-- 指定起始位置为6，开始搜索字符o
SELECT locate('o', 'hello world', 6); -- 8

-- 将字符串中-替换为_
SELECT replace('a-b-c', '-', '_'); -- 'a_b_c'

-- 正则替换（脱敏经典场景）
SELECT regexp_replace('13812345678', '(\\d{3})\\d{4}(\\d{4})', '$1****$2');
-- '138****5678'

-- 正则提取
SELECT regexp_extract('order_20260525_001', '(\\d{8})', 1); -- '20260525'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT split('a,b,c', ','); -- ['a','b','c']

SELECT split('a,b,c', ',')[1]; -- 'b'

-- JSON 提取
SELECT get_json_object('{"name":"Tom","age":18}', '$.name'); -- 'Tom'

-- 一次提多个字段（推荐，性能好）
SELECT b.name, b.age
FROM (SELECT '{"name":"Tom","age":18}' AS json) t
LATERAL VIEW json_tuple(t.json, 'name', 'age') b AS name, age;

1
2
3
4
5

SELECT parse_url('https://www.bing.com/search?q=hive', 'HOST'); -- 'www.bing.com'

SELECT parse_url('https://www.bing.com/search?q=hive', 'QUERY', 'q'); -- 'hive'SELECT parse_url('https://www.bing.com/search?q=hive', 'HOST'); -- 'www.bing.com'

SELECT parse_url('https://www.bing.com/search?q=hive', 'QUERY', 'q'); -- 'hive'