做数据开发这几年Hive 基本是绕不过去的坎。我印象最深的一次是接手一个数仓任务上游表的字段拼接乱了业务方要按逗号拆开重新组合还要校验某个字段是不是以特定字符结尾再来一轮去重统计。当时我翻了半天手册把字符串、正则、聚合、窗口函数挨个试了一遍才把这条 SQL 拼利索。那次之后我就意识到Hive 常用基础函数看着简单真正用起来全是细节坑也都埋在这些细节里。这篇文章我想把 Hive 里最常用、也最容易踩坑的一批函数系统梳理一遍配合完整的 SQL 示例和实战场景来讲。不是为了罗列 API而是告诉你每个函数在什么场景下用、为什么这么用、有什么版本和数据类型上的坑。适合刚接触 Hive SQL 的新人也适合写过一段时间但总在细节上翻车的数据开发。你能从中拿到一套可以直接抄作业的函数组合方案也能搞明白行转列、列转行、窗口函数这类高频考点背后的执行逻辑。1. Hive 函数体系与学习地图1.1 先搞懂 Hive 的本质Hive 的本质很多人一句话就能说出来“SQL 转 MapReduce 的引擎”。这个说法没错但有点过时。现在 Hive 底层跑的不只是 MapReduce还有 Tez、Spark 这些执行引擎SQL 解析成执行计划之后提交给对应的引擎去调度。HDFS 上的文件才是数据本体Hive 的 MetaStore 只负责记录“表结构、分区、字段类型”这些元数据。所以你在 Hive 里写的每一条 SQL最终都会拆成一个个算子在集群上执行。函数也一样——你以为它只是一个简单的表达式实际在分布式环境下字符串处理、空值判断、聚合去重都会被分散到多个节点上并行计算。理解这个本质有什么好处好处是你在写函数时能下意识去考虑“这段逻辑放到分布式环境会怎么跑”。举个最简单的例子count(distinct user_id) 在数据量小的时候没问题数据量一大就慢得离谱因为它需要把所有去重后的 user_id 拉到同一个节点上做精确去重。如果你理解了 Hive 的执行本质就会知道还能通过 size(collect_set(user_id)) 之类的替代方案或者用多个 MapReduce 阶段的 distinct group by 来改善。这种思维不是背函数背出来的是理解了执行方式之后自然形成的。安装配置这块我不展开讲网上教程很多。但对刚接触 Hive 的人我建议至少先弄清楚三件事一是你连的是 HiveServer2 还是直连 metastore这决定了你用什么方式提交 SQL二是你的执行引擎是 MapReduce、Tez 还是 Spark这直接影响函数性能三是你的 Hive 版本Hive 1.x、2.x、3.x 在函数细节上有差异后面我会提到具体差异点。1.2 内置函数全家福与查看技巧Hive 内置函数非常多不用全部记住但要有一个系统分类的框架。按官方文档可以分为数学函数、字符串函数、日期函数、条件函数、聚合函数、窗口函数、集合函数、数据掩码函数等。实际开发中用得最多的是字符串、日期、条件、聚合、窗口这五类加起来覆盖 80% 以上的日常场景。想查看当前环境里有哪些函数直接在命令行或者 beeline 里执行-- 列出所有内置函数 SHOW FUNCTIONS; -- 查看某个函数的具体用法和示例 DESC FUNCTION extended; DESC FUNCTION extended row_number;这条命令我觉得比手册还好用它能显示函数的签名、参数说明、返回类型还有官方给的示例。比如你忘了 get_json_object 的语法执行一下 desc function extended get_json_object;里面的示例会直接告诉你 JSON 路径怎么写。我平时写不熟悉的函数第一件事不是翻网页而是敲这条命令。还有一个技巧show functions 支持模糊匹配例如 show functions like json; 可以直接把带 json 关键字的函数全部列出来省得自己猜函数名。2. 字符串函数坑最多也最常用的一块2.1 最常用的一套截取、拼接、替换、大小写字符串函数是 Hive 里使用频率最高的一类但同时也是最容易出细节问题的一类。先看最基础的一组几乎每条 SQL 里都可能出现-- 字符串截取下标从 1 开始这一点和 Java 完全不同 SELECT substring(helloworld, 1, 5); -- hello SELECT substr(helloworld, 6); -- world -- 字符串拼接 SELECT concat(hello, -, world); -- hello-world SELECT concat_ws(,, a, b, c); -- a,b,c -- 字符串替换 SELECT replace(hello world, world, hive); -- hello hive -- 大小写转换 SELECT upper(hello), lower(WORLD); -- HELLO world -- 字符串长度 SELECT length(hello); -- 5substring 和 substr 是同一个函数起点都是 1 而不是 0这是新手最容易踩的第一个坑。还有一个细节如果 substring 的第二个参数是负数比如 substring(abcde, -2)返回的是 de从右往左截取。这个语义在一些其他数据库里并不完全一致团队协作时容易产生分歧。concat 和 concat_ws 的区别不只是“有没有分隔符”。concat 遇到任何一个参数为 NULL整个结果就是 NULL而 concat_ws 会跳过 NULL。这一点在拼接多字段时特别关键。比如你要拼一个完整的用户地址省、市、区、详细地址其中某个字段可能为空。用 concat 会把整个地址拼成 NULL用 concat_ws 则能把非空的字段完整拼出来。我去年排查过一个数据异常跑出来的结果大面积是 NULL最后定位就是 concat 遇到空值导致的。trim、ltrim、rtrim 用来去除空格但要注意它们只处理空格不处理制表符和换行符。如果你要清洗的数据里有换行符 \n 或者制表符 \t得先用 regexp_replace。例如清理一个字段里的所有空白字符可以用 regexp_replace(column, [\s], )注意 Hive 字符串里反斜杠要转义。2.2 校验开头结尾like、rlike、instr、locate 的组合方案热搜词里有一条特别具体“hive 校验以某些值结尾的函数”。很多人在查这个因为业务场景里太常见了判断一个订单号是不是以某个渠道号结尾判断一个文件路径是不是以 .tmp 结尾判断一个手机号是不是以 10086 结尾。这类需求在 Hive 里有好几种实现方式我一次说清楚。第一种like 通配符。like 里 % 代表任意多个字符_ 代表一个字符。校验结尾用 %目标串-- 校验 url 是否以 .html 结尾 SELECT url FROM access_log WHERE url LIKE %.html; -- 校验 name 是否以 beijing 结尾 SELECT name FROM user_info WHERE name LIKE %beijing;这种写法最简单直观也是我在生产环境里用得最多的方式。like 走的是标准 SQL 语义最容易读懂。要校验开头就把 % 放在右边比如 LIKE beijing%。要校验包含两边都加 %LIKE %beijing%。第二种rlike 正则。rlike 右边接的是 Java 正则表达式校验以某些值结尾用的是 $ 锚点-- 校验 order_no 是否以 A123 结尾 SELECT order_no FROM orders WHERE order_no RLIKE A123$;还有一种新写法 regexp实际效果和 rlike 一样。rlike 的灵活度比 like 高很多但性能上通常更慢因为正则需要编译。简单场景我不建议一上来就用 rlike能用 like 解决的问题没必要引入正则。第三种instr 或 locate 判断位置。instr(str, substr) 返回子串在原串中的位置如果找不到返回 0。校验结尾可以结合 length 使用-- 校验 str 是否以 target 结尾 SELECT if(instr(hello-target, target) length(hello-target) - length(target) 1, yes, no);这个写法比较绕但有一个场景它会派上用场如果你想在一个 SQL 里同时校验多个结尾关键词比如以 .html 或 .htm 结尾instr 或 locate 的写法可以配合 OR而 like 也能做就是啰嗦一些。实际情况下我基本用 like 和 rlike 就够了instr 这种写法更多是面试题里出现工程上不适合为了炫技牺牲可读性。2.3 正则函数三兄弟regexp_extract、regexp_replace、regexpHive 里正则相关的三个函数强烈建议一次学透。这类函数的场景太常见了从日志里提取 IP、从 URL 里提取参数、清洗脏数据。regexp_extract(str, pattern, idx) 的作用是按正则提取内容第三个参数是指定返回第几个括号分组的值-- 从 URL 中提取 id 参数的值 SELECT regexp_extract(http://example.com?page2id10086, id(\\d), 1); -- 返回 10086 -- 提取手机号 SELECT regexp_extract(contact: 13800138000, (1[3-9]\\d{9}), 1);注意 Hive 字符串里反斜杠要写成 \d。这是我最常看到新手翻车的地方SQL 里写的是 \d执行直接报错或者匹配不到。regexp_replace(str, pattern, replacement) 是按正则替换前面提到的清洗空白字符就用它-- 把多个连续空格替换成单个空格 SELECT regexp_replace(hive sql is fun, \\s, ); -- 把中文括号替换成英文括号 SELECT regexp_replace(数据开发岗位, [], ();不建议用 regexp_replace 做太复杂的替换逻辑比如嵌套多个正则才处理完的场景不如拆分到多个 SQL 节点。原因很简单复杂正则在分布式环境下出了问题排渣成本高维护的人也容易看懵。rlike 和 regexp 的作用类似都是判断字符串是否匹配正则返回布尔值。它们可以配合 case when 做多分支判断SELECT CASE WHEN url RLIKE \\.html$ THEN 静态页面 WHEN url RLIKE \\.php$ THEN 动态页面 ELSE 其他 END AS page_type FROM access_log;这一套三兄弟记牢日志清洗、文本解析类的需求基本都能扛下来。3. 数值与日期函数别在这两个坑里翻车3.1 数值函数四舍五入千万别用 round 一把梭数值函数看起来简单round、floor、ceil、abs、rand每个都认识。但真在数仓里算金额、算转化率的时候坑就来了。最常见的坑是 round 函数在 Hive 里的精度问题。先看表现SELECT round(0.145, 2); -- 期望 0.15实际可能返回 0.14原因是 0.145 在 double 类型里存的是近似值底层二进制表示可能比 0.145 略小round 之后就掉到 0.14 了。我用 Hive 跑过很多次金额数据对这种“四舍五入结果差一分”的案例记忆深刻。解决方案是把 double 先转成 decimal 再计算SELECT round(cast(0.145 as decimal(10, 3)), 2); -- 返回 0.15或者在源数据写入时就保证使用 decimal 类型而不是把金额全部塞进 double。数仓设计规范里金额字段一律用 decimal不只是因为精度更因为下游报表、结算系统对金额的准确性要求极高。再来看数值三兄弟的使用场合SELECT floor(3.7); -- 3向下取整 SELECT ceil(3.2); -- 4向上取整 SELECT round(3.5); -- 4四舍五入 SELECT abs(-5); -- 5绝对值floor 和 ceil 常用于分页、分桶、分组计算。比如把用户按年龄分成 5 岁一个区间可以写成 cast(floor(age / 5) * 5 as int)。这种写法比 case when 逐段枚举高效得多代码也简洁。rand() 函数返回 0 到 1 之间的随机数。常用于随机抽样-- 随机抽取 1% 的数据 SELECT * FROM ods_table WHERE rand() 0.01;还有一个容易忽略的点rand(seed) 带种子时每次生成相同序列这在数据复现场景里非常有用。比如做 AB 实验时希望每次跑分桶逻辑结果一致就给 rand 指定一个固定种子。其他常用数值函数还包括 pow、sqrt、取整类 cast。注意 Hive 的整数除法两个 int 相除结果还是 int5 / 2 返回 2不是 2.5。需要小数结果时要写成 5.0 / 2或者用 cast 转换。这一点和很多编程语言一样但 SQL 写多了反而容易忘。3.2 日期函数最常用的日期处理组合拳日期函数是数仓 SQL 里另一大高频门派。Hive 里没有传统数据库那种丰富的日期类型语义它更多是基于字符串和时间戳的函数处理。先看最基础的-- 当前时间 SELECT current_date(); -- 2025-01-01 SELECT current_timestamp(); -- 2025-01-01 12:00:00 -- 时间戳和日期的互转 SELECT unix_timestamp(2025-01-01 10:00:00); -- 返回秒级时间戳 SELECT from_unixtime(1704067200, yyyy-MM-dd HH:mm:ss); -- 转格式化字符串unix_timestamp 有两个重载一个是默认按当前时区解析另一个可以显式指定格式。日常做日志分析时经常出现日期字符串格式不统一的情况有的字段是 yyyy-MM-dd HH:mm:ss有的是 yyyy/MM/dd还有的是纯时间戳。清洗阶段我习惯全部拉到一个统一格式再说。日期计算组合是实战里最常用的-- 加一天、减一天 SELECT date_add(2025-01-01, 1); -- 2025-01-02 SELECT date_sub(2025-01-01, 1); -- 2024-12-31 -- 两个日期相差天数 SELECT datediff(2025-01-10, 2025-01-01); -- 9 -- 取月份最后一天 SELECT last_day(2025-02-01); -- 2025-02-28 -- 日期格式化统一 SELECT date_format(2025-01-01 10:30:00, yyyy-MM-dd); -- 2025-01-01 SELECT date_format(2025-01-01, yyyyMMdd); -- 20250101date_add / date_sub 在处理滚动窗口时非常实用。比如统计最近 7 天的数据分区条件可以写成 partition_date date_add(current_date(), -6)。这种写法的好处是 SQL 每次执行自动计算时间范围不需要手动改日期。datediff 只按日期部分计算不关心时间。如果两个字段带时间戳最好先 date_format 或 to_date 之后再算避免边界问题。months_between 计算月份差注意它的语义是完整的月个数的差值比较适合算工龄、账龄SELECT months_between(2025-06-01, 2024-01-15); -- 返回 16.5 左右next_day 可以取下一个指定星期几的日期比如下一个周一SELECT next_day(2025-01-01, Monday);这种函数处理“每周一跑批”的调度场景很合适比手动写一堆 case when 判断星期几干净多了。日期函数里最容易踩的坑是格式字符串的大小写。Hive 的 yyyy 是四位年而 M 和 m 分别代表月份和分钟。date_format(2025-01-01 10:30:00, yyyy-MM-dd HH:mm:ss) 里小时用 HH 表示 24 小时制用 hh 表示 12 小时制。我已经见过不止一个同事把月份和分钟混用结果日期解析直接出错或者返回 NULL。4. 条件与空值处理写 SQL 的保命技能4.1 case when 与 if 的取舍条件函数是 SQL 表达能力的重要来源。Hive 里的条件函数主要有 if、case when、coalesce、nullif还有一个 nvl。if(condition, true_value, false_value) 是最简单的二分支SELECT if(salary 10000, 高薪, 普通) AS salary_level FROM emp;case when 是多分支判断的主力SELECT CASE WHEN salary 30000 THEN S WHEN salary 20000 THEN A WHEN salary 10000 THEN B ELSE C END AS salary_level FROM emp;if 和 case when 怎么选我个人的原则是只有两个分支用 if三个分支以上用 case when。这不是性能问题而是可读性问题。嵌套多层 if 的 SQL过两周自己回来看都头疼更别说让别人接手维护。这里有一个容易被忽略的坑Hive 的 if 函数在部分版本里并不保证短路求值。也就是说if(a 0, b / a, 0) 在 a 0 时理论上应该返回 0不会触发除零错误但如果优化器把 b / a 提前求值了就可能报错。我在 Hive 2.3 版本上确实遇到过类似诡异的问题。所以涉及除法的安全保护我更推荐在 case when 里先判断或者把可能出错的表达式包在嵌套子查询里提前过滤。case when 本身也不建议在 when 条件里写太复杂的计算逻辑每次判断都可能触发一次表达式求值条件多了整体执行开销会上去。能提前用 where 过滤的数据别全堆在 case when 里做。4.2 nvl、coalesce、nullif空值处理三板斧空值处理是数据开发日常里最琐碎也最重要的环节。数仓里面 NULL 的含义很多可能是数据确实没有、可能是清洗时转换失败、可能是 join 没匹配上。不处理 NULL后面统计结果很可能跟你预期差得十万八千里。nvl(value, default_value) 是 Hive 里最直白的空值替换函数SELECT nvl(age, 0) FROM user_info;coalesce(value1, value2, ..., valueN) 返回第一个非 NULL 的值。它的妙处是可以串一长串备选值SELECT coalesce(province, city, county, 未知) FROM user_address;这个逻辑用 case when 写会很啰嗦coalesce 一行搞定。注意它的执行顺序是从左到右取第一个非 NULL所以优先级高的字段放在最前面。nullif(a, b) 如果 a 等于 b 则返回 NULL否则返回 a。它常用于把某个特殊值转成 NULL方便后续 aggregate 函数忽略。比如某个字段用 -1 表示未知你想统计平均值时忽略 -1SELECT avg(nullif(salary, -1)) FROM emp;这比先 where salary ! -1 再求平均更简洁而且保留了其他行的参与。空值处理还有一个非常隐蔽的坑在聚合函数里NULL 值会被忽略count(column) 不会统计 NULL 的行sum(column) 会跳过 NULL。如果你想把 NULL 统计进数量里得用 count(*) 减去 count(column)或者先把 NULL 转成 0 再 count。理解这个特性在写报表 SQL 时能少走很多弯路。5. 聚合函数与行转列、列转行从分组到炸裂5.1 聚合函数与去重计数的性能细节聚合函数是 Hive SQL 的灵魂group by 配合 sum、avg、max、min、count 这“五大金刚”解决了绝大多数统计需求。但里面有几个细节值得单独拎出来说。count 的三种形态count()、count(1)、count(column)。前两种结果一样都是统计行数不会忽略 NULLcount(column) 只统计该列非 NULL 的行数。很多人以为 count(1) 比 count() 快在 Hive 里它们执行计划基本一致不必纠结这种微优化。最值得关注的是 count(distinct column) 的性能问题。当去重基数特别大比如上亿用户的 UV 统计count(distinct user_id) 会产生严重的数据倾斜因为所有去重值都要汇聚到少量 reduce 节点上比对。我亲身经历过一个凌晨跑批任务用了 count(distinct user_id) 之后从 20 分钟涨到 3 小时最后卡到内存溢出的情况。替代方案之一是先 group by 再 count-- 低效写法 SELECT count(distinct user_id) FROM logs; -- 改进写法 SELECT count(1) FROM ( SELECT user_id FROM logs GROUP BY user_id ) t;这样会把去重压力分散到多个节点整体效率会好很多。还有一个思路是先用 size(collect_set(user_id))但这种方案在基数极大时可能撑爆内存不如 group by 稳妥。聚合函数配合 case when 可以实现“条件聚合”相当于一次 group by 统计多个指标SELECT dept, count(1) AS total_cnt, sum(CASE WHEN salary 15000 THEN 1 ELSE 0 END) AS high_salary_cnt FROM emp GROUP BY dept;这种写法比多个 SQL 分别统计再 join 要高效得多也是日常报表开发的标准姿势。能在一个 group by 里解决的事不要拆成两个 SQL 再关联。5.2 行转列实战collect_list / collect_set 组合方案行转列在 Hive 里最经典的实现就是 collect_list 或 collect_set 配合 concat_ws。collect_list 把多行数据聚合成一个数组保留重复值collect_set 会去重返回一个不重复的集合。先看场景用户表里有多个订单每个用户一行一个订单现在要把一个用户的所有订单号拼接成一列展示SELECT user_id, concat_ws(,, collect_list(order_id)) AS order_list FROM orders GROUP BY user_id;结果类似于user_001 order01,order02,order03collect_list 的坑在于它不保证顺序。即使你在子查询里有序排列collect_list 聚合后数组内元素的顺序也可能被打乱。如果想控制拼接顺序可以先对子查询排序但不同版本下行为不完全一致。要精确控制顺序最好在数组生成后配合后续处理或者用 sort_array。如果订单里存在大量重复订单号希望拼接后不重复用 collect_set 更合适SELECT user_id, concat_ws(,, collect_set(category)) AS category_list FROM user_behavior GROUP BY user_id;collect_set 底层是 Set 语义天然去重但同样不保证顺序。行转列还有一个优化点当 group by 后的分组非常多、每个分组收集的元素很多时collect_list 会产生大量序列化数据增加网络传输和内存开销。如果只是为了拼接字符串做下游展示建议尽量把粒度缩小比如先过滤掉不必要的行或者只收集需要的字段不要在 collect 里面套复杂表达式。5.3 列转行实战lateral view explode 完全解读列转行是 Hive 里另一个高频考点核心就是 explode 函数配合 lateral view 语法。explode 可以把一个数组或 map 炸开成多行lateral view 则把炸开后的结果和原始行关联起来。最常见的场景一张表里某个字段存的是逗号分隔的多个标签要拆成一行一个标签去统计。假设表结构如下user_id tags u001 sports,music,movie u002 food,travel要拆解并统计标签频次SELECT user_id, tag FROM user_table LATERAL VIEW explode(split(tags, ,)) t AS tag;输出结果u001 sports u001 music u001 movie u002 food u002 travel然后对这个结果继续 group by tag 就能做标签频次统计。explode 抽取出来的字段别忘记取别名否则后面没法引用。lateral view 的语义可以理解为对每一行数据对某个字段调用 explode 函数得到多行输出这些输出行和原始行的其他字段组成新的多行结果。理解了这个语义就不难理解为什么 explode 不能在 select 子句里单独用必须要配合 lateral view。再看一个 map 类型的拆解SELECT user_id, info_key, info_value FROM user_info LATERAL VIEW explode(property_map) t AS info_key, info_value;explode 一个 map 会输出两列分别是 key 和 value。这在处理 kv 结构的数据时非常实用。explode 的使用有几个常见限制。第一explode 函数的参数只能是 array 或 map 类型不能直接传字符串所以要先用 split 把字符串转成数组。第二如果 explode 的数组为空这一行会被过滤掉不会保留原始行。如果希望空数组也保留原始行其他字段为 NULL可以考虑用 left outer join lateral view 的写法SELECT user_id, tag FROM user_table LEFT OUTER JOIN LATERAL VIEW explode(split(tags, ,)) t AS tag;这种细节在数据要求完整保留的场景下特别重要我印象里有一次统计用户标签覆盖率就是因为 explode 自动过滤了空数组导致覆盖率多算了几个百分点排查了半天。6. 窗口函数基础函数里最容易用错的进阶点6.1 排名三件套row_number、rank、dense_rank窗口函数在 Hive 里的地位非常高它解决的是“分组内排序”和“分组内计算”的问题标准 SQL 里叫分析函数。很多人把它和 group by 混淆其实它们最大区别是group by 会折叠行数窗口函数不会。排名三兄弟是窗口函数里最经典的一组。它们的区别值得用一张表说透函数相同值处理序号跳跃典型场景row_number()相同值分配不同序号无跳跃按行编号取每组前 N 条rank()相同值分配相同序号有跳跃并列名次统计dense_rank()相同值分配相同序号无跳跃连续名次统计一个直观的例子SELECT name, dept, salary, row_number() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn, rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk, dense_rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr FROM emp;如果部门里有两个人的工资都是 30000row_number 会给它们分配 1 和 2 两个不同序号不管两个人是否并列。rank 会给它们都分配 1下一个人是 3中间空出 2。dense_rank 会给它们都分配 1下一个人是 2不空号。实际开发中取“每个部门工资最高的员工”这种需求用 row_number 加外查询过滤 rn 1 是最常用的方案。因为 row_number 结果唯一不会因为并列而多出纪录。如果业务上允许并列比如“找出每个部门前三名销售”那就用 rank 或 dense_rank看你是想跳过还是不想跳过名次。窗口函数的 PARTITION BY 字段决定了分组边界ORDER BY 字段决定了窗口内排序。这两个参数极其重要没有 PARTITION BY 时整个表作为一个窗口排名会在全表范围内进行。我遇过不止一次因为漏写 PARTITION BY排名全部错乱的案例。6.2 累计求和与移动平均sum over、avg over 的窗口帧除了排名窗口函数还常用于累计、移动平均、占比等场景。这一块的核心概念是“窗口帧”——在分组排序的基础上可以进一步定义每行计算时使用哪些相邻行。累计求和的经典写法SELECT order_date, sales_amount, sum(sales_amount) OVER (ORDER BY order_date) AS cumulative_sales FROM sales_daily;这个 SQL 按日期排序后每一行累计值等于当日及之前所有日的销售总和这就是一个从窗口起始到当前行的累计。更精细的控制方式是用 ROWS BETWEEN 和 RANGE BETWEEN 指定窗口帧-- 最近 3 天的移动平均包含当天、前一天、前两天 SELECT order_date, sales_amount, avg(sales_amount) OVER ( ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg FROM sales_daily;ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 的意思是“从当前行往前推 2 行一直到当前行”相当于一个宽度固定的滑动窗口。想算 7 日移动平均就把数字改成 6。在实际工作中我发现很多人不会主动用窗口帧遇到需要移动平均的时候就先自己写复杂的自关联或者子查询。其实 row_number 加多个聚合子查询也能实现但代码长度会翻好几倍执行效率也差。窗口帧是标准能力建议一次学透。窗口函数的性能也需要注意。大表上做窗口排序尤其是没有合理裁剪分区会产生全量排序的负担。使用前尽量通过 WHERE 条件缩小数据范围PARTITION BY 的分区粒度越小计算开销分散得越好。7. 常见问题速查表与实操心得7.1 问题速查表一句话定位 解决方案把常见问题整理成速查表放在这里方便直接对照排查。这些问题全部来自我过往的真实排障过程每一行都值得收藏。字符串与正则类问题1substring 从 1 开始取不到第一个字符 解决substring(abc, 1, 1) 返回 a不是从 0 开始。想取第 2 个字符用 substring(abc, 2, 1)。 问题2concat 拼接字段有 NULL整个结果为 NULL 解决改用 concat_ws(, col1, col2)它会跳过 NULL。 问题3正则里写 \d 匹配不到数字 解决Hive 字符串里反斜杠需要写成 \\d例如 regexp_extract(str, (\\d), 1)。 问题4用 like 校验特定字符串结尾 解决LIKE %目标串例如 URL LIKE %.html。数值与日期类问题5round 浮点精度不准0.145 被舍成 0.14 解决先 cast 成 decimal 再 round例如 round(cast(0.145 as decimal(10,3)), 2)。 问题6两个整数相除得到整数 解决除数和被除数至少一个转成小数salary / 10000.0 或者使用 cast。 问题7date_format 格式串大小写混用导致解析错误 解决年份 yyyy月份 MM分钟 mm24 小时制 HH12 小时制 hh。 问题8datediff 带时间戳的字段误差 解决先 to_date 或 date_format 只保留日期部分再做差值。空值与聚合类问题9count(distinct 大字段) 跑得慢甚至挂了 解决改用先 group by 再 count 外层查询。 问题10聚合时 NULL 不参与计算统计结果和预期不一致 解决使用 nvl 或 coalesce 显式指定默认值确认 NULL 是否要被统计。 问题11if 除零保护没生效还是报错 解决优先用 case when不依赖短路求值先判断再计算。行转列和窗口类问题12collect_list 拼接的元素顺序不对 解决Hive 不保证 collect 顺序先 sort_array 或接受无顺序结果。 问题13explode 空数组把整行过滤了 解决使用 LEFT OUTER JOIN LATERAL VIEW explode(...) t AS col。 问题14row_number 分组错乱排到了全表 解决检查 PARTITION BY 是否写全漏掉分区时窗口会覆盖整个数据集。 问题15窗口函数大面积排序很慢 解决提前 WHERE 裁剪数据缩小 PARTITION BY 分区粒度避免全量排序。7.2 实操心得版本差异、类型选择、性能意识最后聊几个我在实际项目里的习惯不一定写在官方文档里但对提升 SQL 质量和排障效率非常有帮助。第一Hive 版本差异真心存在。比如 Hive 3.0 开始不再支持某些 MapReduce 相关参数部分函数默认行为也有调整。写函数之前最好确认当前集群的 Hive 版本别把网上搜到的示例直接往生产环境搬。通用的办法是在测试环境执行 desc function extended 确认行为再看看执行计划。第二字段类型选择影响函数语义。金额用 decimal 不要用 double百分比和比率直接保留原始值在下游报表层再格式化时间字段能统一成 yyyy-MM-dd HH:mm:ss 就统一不要在 SQL 里反复做格式转换。数据模型层做好类型规范SQL 层会省掉大量隐式转换的麻烦。第三写函数前先想性能。不是所有函数都适合大数据量。正则表达式函数、collect_set、count(distinct) 在大表上都有各自的坑。能用简单函数解决的不要为了炫技用复杂方案。我之前参与过一次大表优化把正则校验那段替换成 like执行时间直接砍掉 60%。SQL 的每一步运算都会在分布式环境被放大简单即高效。第四多练组合用法。单个函数很多人都认识真正拉开差距的是“组合能力”。比如 split explode lateral view 组合完成列转行row_number 子查询完成分组 TopNcollect_set concat_ws 完成行转列。这些组合不是死记硬背出来的而是多写多踩坑之后形成的条件反射。最后说一句我在带新人时经常强调的话Hive SQL 看着简单但每个函数背后都有一套分布式执行的逻辑。踩过的坑记下来写 SQL 之前先想清楚数据类型和 null 语义你的代码质量和执行效率都会上一个台阶。