一、字符串函数常用字符串操作-- 字符串长度select length(hello)-- 替换select replace(hello,l,x)-- 截取从第2位开始截取3个字符select substr (hello,2,3)-- 拼接方式一concat 函数select concat(hello,hello,hello)-- 拼接方式二|| 运算符select hello||hello||hello-- 拼接表中字段select concat(id, name) from heroconcat_ws — 带分隔符的拼接concat_ws 函数可以在拼接时添加分隔符分隔符写在第一个参数位置。select concat_ws(,, id, name, id) from herocollect_set 与 collect_list聚合函数collect_set 聚合后去重collect_list 聚合后保留所有值。常用于行转列操作。select province_id,collect_set(city_name) s, -- 去重聚合collect_list(city_name) l -- 不去重聚合from citygroup by province_id字符串清理与转换select TRIM( dsdsa ) -- 去除前后空格select upper(xxx) -- 转大写select lower(XXX) -- 转小写 面试考点concat_ws 与 collect_set 配合使用可实现高效的行转列。──────────────────────────────────────────────────二、日期函数提取日期部分-- 提取年份select substr(CURRENT_TIMESTAMP, 1, 4)select year(CURRENT_TIMESTAMP)-- 提取月份select substr(CURRENT_TIMESTAMP, 6, 2)select month(CURRENT_TIMESTAMP)-- 提取日select day(CURRENT_TIMESTAMP)-- 提取时select hour(CURRENT_TIMESTAMP)-- 提取时分秒格式化方式SELECT date_format(CURRENT_TIMESTAMP, HH:mm:ss)时间戳转换将 bigint 类型的时间戳转换为可读的日期字符串select from_unixtime(1782972840, yyyy-MM-dd HH:mm:ss)日期计算-- 日期间隔天数select datediff(2020-10-01, 2026-07-02)select datediff(CURRENT_DATE, 2020-10-01)-- 间隔月数select months_between(CURRENT_DATE, 2020-10-01)-- 往前/往后 N 天select date_sub(CURRENT_DATE, 1) -- 前一天select date_add(CURRENT_DATE, 1) -- 后一天 面试考点datediff 和 date_sub/date_add 是日期计算的高频考点常用于计算留存率、连续登录天数等场景。──────────────────────────────────────────────────三、数值型函数常用数值运算-- 取模select mod(10, 3) -- 结果1-- 向上取整 / 向下取整select CEIL(3.14) -- 结果4select FLOOR(3.14) -- 结果3-- 四舍五入select round(3.14159, 2) -- 结果3.14 数值函数常用于数据处理中的精度控制、分页计算等场景。──────────────────────────────────────────────────四、转换函数NULL 值处理-- nvl将 NULL 替换为指定值常用select nvl(null, 0) -- 结果0-- COALESCE返回第一个不为 NULL 的参数推荐select COALESCE(null, 200, null, 100) -- 结果200nvl 与 COALESCE 的区别COALESCE 支持多个参数返回第一个非 NULL 值nvl 只支持两个参数。COALESCE 是 SQL 标准函数可移植性更好。多表连接中的 NULL 处理在 FULL JOIN 场景中经常需要处理关联字段为 NULL 的情况-- FULL JOIN 时处理 NULLselect nvl(a.id, b.id) from a full join b on xxxselect COALESCE(a.id, b.id, c.id) from a full join b on xxx条件判断-- IF 函数三目运算select if(12, a, b) -- 结果b-- CASE WHEN多条件判断selectcase when score 90 then 优秀when score 60 then 及格else 不及格end as gradefrom student数据类型转换 — CASTCAST 用于数据类型转换经常用于表连接的 ON 条件中当两个表的关联字段类型不一致时select 2000, cast(200 as string)-- 表连接中类型不一致时on a.id cast(b.id as int) 面试考点nvl 与 COALESCE 的区别、CAST 在 JOIN 中的使用是常考内容。──────────────────────────────────────────────────五、窗口函数⭐ 重点窗口函数语法结构函数() OVER (PARTITION BY xxx -- 分组ORDER BY xxx -- 排序ROWS BETWEEN xxx AND yyy -- 窗口范围)5.1 排名开窗函数排名开窗通常用于计算 Top-N 问题-- row_number()连续排名 1,2,3,4常用-- rank()跳跃排名 1,1,3,4-- DENSE_RANK()密集排名 1,1,2,3-- 示例每个部门薪资排名SELECTempno, ename, deptno, sal,row_number() OVER (PARTITION BY deptno ORDER BY sal DESC) rn,rank() OVER (PARTITION BY deptno ORDER BY sal DESC) rk,DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) drFROM emp5.2 ROWS BETWEEN — 窗口范围详解窗口范围的关键字• preceding向前找之前的行• following向后找之后的行• CURRENT ROW当前行• UNBOUNDED一直到窗口的边界最常用的窗口范围 — 累计计算ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW含义从窗口的第一行到当前行常用于累计求和。5.3 聚合开窗函数聚合函数配合窗口使用实现累计计算-- 累计求和SELECT dt, amt, SUM(amt) OVER (ORDER BY dt) cumulative_sumFROM test-- 累计平均SELECT dt, amt, AVG(amt) OVER (ORDER BY dt) cumulative_avgFROM test支持的聚合函数SUM、AVG、MAX、MIN、COUNT5.4 偏移开窗函数 — LAG / LEAD偏移开窗将行与行的关系转换成列与列的关系常用于计算同比、环比和连续性问题。-- LAG向上偏移看前面的行-- LEAD向下偏移看后面的行-- 语法lag/lead(要偏移的列, 偏移量, 默认值)-- 示例计算每日金额与前一天金额的差值环比SELECTdt, amt,lag(amt, 1, 0) OVER (ORDER BY dt) prev_amt,amt - lag(amt, 1, 0) OVER (ORDER BY dt) diffFROM test 面试考点LAG/LEAD 是处理连续登录天数、留存分析、同比环比计算的核心函数。5.5 切片开窗 — NTILENTILE 将数据均匀切片一般用于百分比计算如计算前 25% 的用户。-- NTILE(4)将数据均匀分成 4 片SELECT a.*,ntile(4) OVER (PARTITION BY deptno ORDER BY sal DESC) bucketFROM emp a──────────────────────────────────────────────────六、JSON 字符串处理Hive 提供 get_json_object 函数处理 JSON 格式的字符串字段。-- 示例 JSON-- {systemtype: android, education: doctor,-- marriage_status: 1, phonebrand: VIVO}-- 提取 JSON 中的字段SELECT a.*,get_json_object(extra1, $.phonebrand) phoneFROM zhiyun.user_info a 实际工作中日志数据常以 JSON 格式存储get_json_object 是解析 JSON 的必备函数。──────────────────────────────────────────────────七、多维分析函数GROUPING SETS / ROLLUP / CUBE多维分析函数用于生成多个维度组合的聚合结果减少 UNION ALL 的使用。GROUPING SETS指定需要聚合的维度组合SELECT sex, city, count(0) cFROM zhiyun.user_info aGROUP BY sex, cityGROUPING SETS(sex, city)ROLLUP从最细粒度向上逐级聚合层次聚合SELECT sex, city, count(0) cFROM zhiyun.user_info aGROUP BY sex, city WITH ROLLUPCUBE对所有维度组合进行聚合笛卡尔积SELECT sex, city, count(0) cFROM zhiyun.user_info aGROUP BY sex, city WITH CUBE 三种多维分析的对比GROUPING SETS 最灵活指定组合ROLLUP 有层级关系CUBE 是全组合。──────────────────────────────────────────────────八、侧视图函数Lateral View列转行操作侧视图函数用于将一行中的复杂字段数组、Map、分隔字符串展开为多行实现列转行。UDF / UDAF / UDTF 概念区分• UDFUser Defined Function一进一出如 length()、upper()• UDAFUser Defined Aggregation Function多进一出如 sum()、count()• UDTFUser Defined Table-Generating Function一进多出如 explode()explode 函数explode 将数组或 Map 展开为多行-- 按逗号分隔后展开select explode(split(category_detail, ,))from zhiyun.user_goods_categoryLATERAL VIEW 用法LATERAL VIEW 配合 explode 使用可以在展开的同时保留原表的其他字段SELECTa.user_name,b.plFROM zhiyun.user_goods_category aLATERAL VIEW explode(split(category_detail, ,)) b AS pl