企业官网建设流程全解析

做数据开发这几年，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'); -- 5

substring 和 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、regexp

Hive 里正则相关的三个函数，强烈建议一次学透。这类函数的场景太常见了：从日志里提取 IP、从 URL 里提取参数、清洗脏数据。

regexp_extract(str, pattern, idx) 的作用是按正则提取内容，第三个参数是指定返回第几个括号分组的值：

-- 从 URL 中提取 id 参数的值 SELECT regexp_extract('http://example.com?page=2&id=10086', '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 相除结果还是 int，5 / 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'); -- 20250101

date_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 表示未知，你想统计平均值时忽略 -1：

SELECT 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)。前两种结果一样，都是统计行数，不会忽略 NULL；count(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,order03

collect_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 会折叠行数，窗口函数不会。

排名三兄弟是窗口函数里最经典的一组。它们的区别值得用一张表说透：

一个直观的例子：

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;

如果部门里有两个人的工资都是 30000：

• row_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 问题速查表：一句话定位 + 解决方案

把常见问题整理成速查表放在这里，方便直接对照排查。这些问题全部来自我过往的真实排障过程，每一行都值得收藏。

字符串与正则类

问题1：substring 从 1 开始，取不到第一个字符？ 解决：substring('abc', 1, 1) 返回 a，不是从 0 开始。想取第 2 个字符用 substring('abc', 2, 1)。 问题2：concat 拼接字段有 NULL，整个结果为 NULL？ 解决：改用 concat_ws(','， col1, col2)，它会跳过 NULL。 问题3：正则里写 \d 匹配不到数字？ 解决：Hive 字符串里反斜杠需要写成 \\d，例如 regexp_extract(str, '(\\d+)', 1)。 问题4：用 like 校验特定字符串结尾？ 解决：LIKE '%目标串'，例如 URL LIKE '%.html'。

数值与日期类

问题5：round 浮点精度不准，0.145 被舍成 0.14？ 解决：先 cast 成 decimal 再 round，例如 round(cast(0.145 as decimal(10,3)), 2)。 问题6：两个整数相除得到整数？ 解决：除数和被除数至少一个转成小数，salary / 10000.0 或者使用 cast。 问题7：date_format 格式串大小写混用导致解析错误？ 解决：年份 yyyy，月份 MM，分钟 mm，24 小时制 HH，12 小时制 hh。 问题8：datediff 带时间戳的字段误差？ 解决：先 to_date 或 date_format 只保留日期部分，再做差值。

空值与聚合类

问题9：count(distinct 大字段) 跑得慢甚至挂了？ 解决：改用先 group by 再 count 外层查询。 问题10：聚合时 NULL 不参与计算，统计结果和预期不一致？ 解决：使用 nvl 或 coalesce 显式指定默认值，确认 NULL 是否要被统计。 问题11：if 除零保护没生效，还是报错？ 解决：优先用 case when，不依赖短路求值，先判断再计算。

行转列和窗口类

问题12：collect_list 拼接的元素顺序不对？ 解决：Hive 不保证 collect 顺序，先 sort_array 或接受无顺序结果。 问题13：explode 空数组把整行过滤了？ 解决：使用 LEFT OUTER JOIN LATERAL VIEW explode(...) t AS col。 问题14：row_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 + 子查询完成分组 TopN，collect_set + concat_ws 完成行转列。这些组合不是死记硬背出来的，而是多写多踩坑之后形成的条件反射。

最后说一句我在带新人时经常强调的话：Hive SQL 看着简单，但每个函数背后都有一套分布式执行的逻辑。踩过的坑记下来，写 SQL 之前先想清楚数据类型和 null 语义，你的代码质量和执行效率都会上一个台阶。