1. 开头为什么你需要这份Hive函数清单做了这么多年数据开发我见过太多人卡在Hive函数上——不是不会用而是不知道有哪些函数可用等到需要的时候才去翻文档一趟一趟查得头疼。Hive内置了几百个函数覆盖字符串处理、数值计算、日期操作、聚合统计、复杂类型解析等场景谁能把这些函数梳理成一套体系谁写SQL的速度就能快一倍。这篇文章就是基于我自己的实战经验把Hive里最常用、最容易被遗漏、最容易踩坑的函数按类别整理成一份可查可用的“大全”。不论你是刚接触Hive的新人还是写过千行SQL的老手都能从这里找到直接能抄的用法、参数说明和避坑要点。我会把每个函数的核心逻辑讲透附上可复现的示例重点讲清楚那些文档里不写但实际一定会遇到的坑。先说清楚一点本文基于Hive 3.x版本函数清单以官方内置函数为准。不同发行版比如HDP、CDH、开源社区版可能有个别差异但通用核心函数完全一致。MySQL、PostgreSQL里的一些思路可以借鉴但Hive的语法和函数行为明显不同刚从关系型数据库转过来的人尤其要注意。2. Hive函数的整体架构与核心分类2.1 从UDF到内置函数Hive函数体系怎么构成的Hive的函数体系分为三层最底层的是内置函数Built-in Functions开箱即用覆盖了绝大多数日常需求中间层是用户自定义函数UDF、UDAF、UDTF用来处理内置函数搞不定的个性化逻辑最上层是函数注册与调用机制包括临时函数和永久函数。日常开发中内置函数的使用频率占到了90%以上。但很多人对内置函数的理解停留在“用的时候查一下”的层面没有建立分类意识导致同一个功能绕远路实现。比如判断字符串是否为空有人非要用length(str)0其实用trim配合if就能优雅解决。从执行引擎的角度来看Hive函数最终会翻译成底层计算框架MapReduce、Tez或Spark的算子操作。理解了这一点你就明白为什么某些函数在where条件里使用会拖慢性能——因为函数无法下推导致全表扫描。这个后面会专门讲。2.2 为什么按类别掌握函数比逐个死记硬背更高效我面试过不少候选人问“字符串拼接怎么实现”有人只知道concat不知道concat_ws可以指定分隔符问“取当前时间”有人只记得sysdate()却不清楚Hive里稳定的是current_timestamp。这些问题本质上不是记性差而是没有建立函数分类体系。把函数按用途分成字符串、数值、日期、条件、聚合、集合、窗口、特殊这八类之后你脑子里的索引就建立起来了。写SQL的时候先判断“我要解决什么问题”再去对应类别里挑选函数命中率会高很多。这也是本篇文章的编排逻辑。2.3 函数分类一览一张表看全最常用函数先列一个我平时最常用的函数速查表下面每个类别我都会展开细讲。类别高频函数字符串concat, concat_ws, substr, substring_index, split, regexp_replace, regexp_extract, lower, upper, trim, lpad, rpad, repeat, reverse, instr, length, replace数值round, floor, ceil, rand, abs, pow, sqrt, greatest, least, pmod, sin, cos日期current_date, current_timestamp, from_unixtime, unix_timestamp, to_date, year, month, day, datediff, date_add, date_sub, date_format, last_day, next_day, months_between条件if, case when, coalesce, nvl, nullif, assert_true聚合count, sum, avg, min, max, collect_list, collect_set, percentile_approx, histogram_numeric集合size, array, map, explode, posexplode, json_tuple, str_to_map, map_keys, map_values, array_contains, sort_array窗口row_number, rank, dense_rank, sum over, lag, lead, first_value, last_value, ntile特殊uuid, current_database, sha2, md5, aes_encrypt, reflect这张表不用背你就做一件事把它收藏起来写SQL卡住的时候回来翻。翻的次数多了自然而然就记住了。3. 字符串函数最常用也最容易写错的细节3.1 字符串长度与截取substr的边界问题length(string str)返回字符数而不是字节数对中文特别友好。比如length(你好)返回2而不是6。如果你需要字节数做存储判断可以用length(cast(str as binary))。这个坑我在做编码转换时踩过——用Java的getBytes思路去套Hive结果长度对不上后来才意识到Hive默认按字符计算。substr(string str, int start, int len)是截取函数start从1开始不是0。substr(abcde, 1, 3)结果是abcsubstr(abcde, 3, 2)结果是cd。start支持负数表示从尾部倒着数substr(abcde, -2, 2)结果是de。如果你写substr(abcde, 0, 3)Hive不会报错但行为等价于从第0位开始截取很多版本会返回空字符串或报错写的时候务必从1开始。substring_index(string str, string delim, int count)是另一个宝藏。它是按分隔符拆分的“第N段”函数substring_index(a,b,c,d, ,, 2)返回a,bsubstring_index(a,b,c,d, ,, -2)返回c,d。注意count为负数时是取倒数第N个分隔符右边的部分。3.2 拼接与替换concat_ws和regexp_replace的组合拳concat(string a, string b, ...)直接拼接不做任何空值处理。任何一个参数为NULL结果就是NULL。这个行为很坑——你明明拼接了三个字段有一个为空整个结果就没了。解决办法是用nvl提前转换或者直接用concat_ws。concat_ws(string sep, string a, string b, ...)以指定分隔符拼接最大的优点是会忽略NULL值不忽略空字符串。比如concat_ws(-, a, null, c)返回a-c。如果你要拼接数组里的元素concat_ws还支持传入arraystringconcat_ws(,, array(a,b))返回a,b。这个特性在列转行拼接时非常实用。regexp_replace(string str, string pattern, string replace)用正则替换子串。注意口诀前面的参数是“要处理的字符串”第二个是“正则规则”第三个是“替换成什么”。replace(str, old, new)则是简单的字符串替换不解析正则。区分这两个函数是入门课。实战中经常遇到需求把手机号中间四位打码。可以这么写SELECT regexp_replace(13812345678, (\\d{3})\\d{4}(\\d{4}), $1****$2); -- 输出 138****5678注意Hive字符串常量里的反斜杠要转义正则里\d要写成\\d很多人就卡在这里。3.3 提取与清洗regexp_extract的捕获组用法regexp_extract(string str, string pattern, int index)从字符串中按正则提取子串第三个参数指定返回第几个捕获组。比如从日志中提取IP和时间SELECT regexp_extract(192.168.1.1 - - [10/Oct/2023:14:23:01], (\\d\\.\\d\\.\\d\\.\\d), 1);多捕获组时index0返回整个匹配项index1返回第一个括号组依次类推。如果匹配不到返回空字符串不是NULL。trim(string str)去除两端空格ltrim和rtrim分别处理左右。注意trim只能去空格不能去换行符\n和制表符\t。需要去全角空格时要先用regexp_replace(str, [\\u00a0\\u3000], )处理这是从外部数据清洗时踩过的坑。字符串函数里还有一个高频需求判断是否包含某个子串。instr(string str, string substr)返回子串第一次出现的位置从1开始找不到返回0。所以判断“是否包含”经常写成instr(str, key) 0。有的版本还支持locate用法和instr一致只是参数顺序不同不建议混用。3.4 左补右补与大小写容易被忽略的格式化场景lpad(string str, int len, string pad)左补齐rpad右补齐。比如生成固定长度的订单号lpad(123, 6, 0)返回000123。如果原字符串长度超过len则截断到len长度lpad(1234567, 3, 0)返回123。这个截断行为在生成批次号时很容易出问题——长度不够补零没问题但长度超出时直接截断导致数据错误。我建议在补位前先用length做一次判断。lower和upper的大小写转换没什么黑科技但要注意initcap函数把每个单词首字母大写对英文姓名清洗很好用。reverse(string str)反转字符串适合做回文判断也可以配合split做字符串倒序。重复生成字符串用repeat(string str, int n)比如生成连续逗号分隔的N个占位符repeat(?,, 3)配合split可以生成临时序列。4. 数值与日期函数计算准确性与时区陷阱4.1 四舍五入与取整round不是你想的那样round(double d, int n)四舍五入保留n位小数n可以省略默认取整。一个经典坑round(2.5)在Hive里返回3四舍五入但round(3.5)也返回4没有MySQL里的“四舍六入五成双”规则。如果要银行家舍入得自己用floor和ceil绕。还有round的参数如果是decimal类型精度按decimal处理如果是float/double会有二进制浮点的误差。比如round(1.005, 2)可能得到1.00而不是1.01。解决办法是先把double转成decimal或者用cast提高精度。floor向下取整ceil向上取整参数为负数时也要小心floor(-1.2)返回-2ceil(-1.2)返回-1。这个方向性符合数学定义但业务里经常用错。pmod(int a, int b)返回a对b取模的正余数与%运算符不同。pmod(-1, 10)返回9而-1 % 10在Hive里返回-1。做分页、分桶时推荐用pmod避免负数索引。4.2 随机数与最值rand的种子参数rand(int seed)返回0到1之间的随机数。不传种子时每次执行结果不同传入固定种子时同一查询内随机序列可复现。这个特性在抽样和数据脱敏时非常有用——比如要给同一批用户每次生成一致的随机分组就写rand(user_id)保证同一个user_id分到同一组。greatest(col1, col2, ...)返回参数列表的最大值least返回最小值。参数类型必须一致否则隐式转换可能导致精度损失。这个函数适合做多列比较比如取三个报价字段的最高值比嵌套case when简洁得多。4.3 日期获取unix_timestamp的前世今生unix_timestamp()返回当前时间的Unix秒级时间戳current_timestamp返回带时区的timestamp类型。注意两者返回类型不同不能直接相加。from_unixtime(bigint seconds)将时间戳转为日期字符串默认格式yyyy-MM-dd HH:mm:ss。如果要指定格式比如只取年月日from_unixtime(ts, yyyy-MM-dd)。时区问题是日期函数最大的坑。Hive的current_timestamp跟随集群的时区配置如果你的任务跑在UTC时区的集群上而你期望北京时间直接取current_timestamp就会差8小时。正确处理方式是SELECT from_utc_timestamp(current_timestamp, Asia/Shanghai);from_utc_timestamp和to_utc_timestamp是Hive特有的时区转换函数比手动加减8小时可靠得多。手动加减在夏令时地区还会出错这个函数能正确处理时区规则。4.4 日期计算datediff与date_add的组合场景datediff(string enddate, string startdate)返回两个日期相差的天数。注意参数顺序前大后小为正前小后大为负。date_add(string startdate, int days)加天数date_sub减天数。这两个函数接受字符串日期也接受date类型但传timestamp类型时有的版本会隐式转成字符串导致格式不对。实际场景求上月第一天、上月末日。可以用trunc(month)实现。trunc(string date, string format)按format截断日期支持YEAR、MONTH、QUARTER等。trunc(2023-10-15, MONTH)返回2023-10-01trunc(2023-10-15, YEAR)返回2023-01-01。这个函数比date_sub拼接更干净。months_between(string date1, string date2)返回两个日期之间的月数结果是浮点数。比如months_between(2023-10-31, 2023-01-31)返回9.0如果日不对齐会返回带小数的月数比如months_between(2023-10-15, 2023-01-31)返回8.45...。计算年龄时如果希望按月份整算可以用floor(months_between(...))但注意边界条件。last_day(string date)返回当月最后一天。next_day(string date, string weekday)返回指定日期之后的下一个周几weekday为MO、TU等在Hive里不区分大小写。注意是“下一个”不是“本周的”。如果要找本周一需要配合date_sub处理。date_format是格式化的万能函数date_format(2023-10-15 10:30:00, yyyy-MM-dd HH:mm:ss)按模板输出。特别注意Hive的格式符yyyy是四位年MM是两位月dd是两位日HH是24小时制hh是12小时制。写YYYY大写代表“周基准年”和yyyy含义完全不同很多人写错导致跨年日期偏移。5. 条件判断与聚合分析从if到窗口函数的进阶之路5.1 条件函数if、case when、coalesce、nvl的区别与选择if(boolean test, T trueValue, T falseValue)是Hive里最基础的条件分支。case when支持多条件适合复杂逻辑。两者可以互换但性能差异几乎可以忽略主要看可读性。coalesce(T a, T b, ...)返回第一个非NULL值所有参数都为NULL时才返回NULL。这个函数在做多字段兜底时非常实用比如取用户手机号优先取主叫字段为空则取备用字段coalesce(mobile, mobile2, mobile3)。注意coalesce不忽略空字符串空字符串不是NULL所以如果手机号字段是空字符串而不是NULLcoalesce不会帮你兜底需要配合trim和if处理。nvl(T a, T b)是coalesce的简化版只有两个参数功能一模一样。很多从Oracle转过来的人习惯用nvl而Hive也原生支持所以尽管用。nullif(T a, T b)返回NULL当a等于b否则返回a。这个函数在做除法避免除零时有用nullif(cnt, 0)可以和coalesce嵌套实现“除数为0则返回默认值”的效果。5.2 聚合函数count的特殊形式与collect_set的去重能力count(1)、count(*)、count(col)这三个要分清。count(*)统计行数count(1)等价于count(*)count(col)只统计该列非NULL的行数。如果想统计非NULL且去重的数量用count(distinct col)。注意count(distinct col1, col2)是多列去重统计语法上没问题但性能通常很差大数据量下尽量避免。collect_list分组内收集列表不去重、有序collect_set收集去重列表。两者都是UDAF配合concat_ws可以把分组内的多个值拼成一个字符串。比如统计每个用户的所有角色SELECT user_id, concat_ws(,, collect_list(role)) AS roles FROM user_role_table GROUP BY user_id;collect_list不保证顺序如果要按某个字段排序后再收集得用sort_array或在子查询里先order by。一个常用技巧是concat_ws(,, sort_array(collect_list(role)))来得到有序去重列表。percentile_approx是分位数计算的利器。percentile_approx(col, 0.5)计算中位数percentile_approx(col, array(0.25, 0.5, 0.75))同时计算多个分位数。它基于近似算法在大数据量下比percentile快很多而且percentile只支持整型percentile_approx支持double。在做数据质量监控时用分位数比平均值更靠谱——平均值容易被极端值带偏但中位数和四分位数能反映真实分布。histogram_numeric是数值分布直方图返回一个包含histogram的数组一般配合自定义代码使用。5.3 窗口函数row_number必会lag/lead必用Hive对窗口函数的支持已经很完善。row_number() over (partition by col order by col)是最经典的行号函数常用于取每个分组的前N条。rank允许并列且会跳号1,1,3dense_rank允许并列但不跳号1,1,2。这三个的区别是面试高频题实际使用时也很关键。取每个用户最近一条订单通常用row_number而不是rank因为业务上不存在并列第一的情况。lag(col, offset)取分组排序后的上一行值lead取下下一行值。这个函数在做环比、会话间隔分析时能救命的。比如求每个用户相邻两次登录的时间间隔SELECT user_id, login_time, lag(login_time) over (partition by user_id order by login_time) AS prev_login FROM login_log;然后在外层做unix_timestamp差值。注意lag的默认值参数lag(col, 1, 0)表示如果上一行为NULL则返回0。不写默认值结果就是NULL不影响业务但容易在计算里带出NULL。窗口函数的框架定义rows between unbounded preceding and current row常用作累加。默认的窗口范围是从分区开始到当前行但如果order by字段有重复值默认会包含所有重复值的行导致累加结果偏大。要精确控制用rows between显式指定。这个细节我踩过必须用rows between unbounded preceding and current row强制行为。5.4 增强型聚合grouping sets与cube基础group by之外Hive支持grouping sets、cube、rollup。比如一次SQL同时统计总订单量、按地区统计、按品类统计SELECT region, category, count(*) FROM orders GROUP BY region, category GROUPING SETS ((region), (category), ());rollup是grouping sets的子集生成上卷聚合cube生成所有维度组合的笛卡尔积。这些函数能减少多次扫描但结果里会出现NULL占位使用时需要配合grouping__id计算字段来区分纬度。我自己更常用grouping sets因为cube生成的组合过多业务上往往用不到。6. 复杂类型与特殊函数解析JSON、MAP和ARRAY的实战招法6.1 集合函数size、sort_array、array_containssize(Map)返回map的键值对数量size(Array)返回数组长度。判断数组是否为空用size(arr) 0。array_contains(arrayT, T value)判断数组中是否包含某值常用于筛选标签数据where array_contains(tags, vip)。sort_array(ArrayT)对数组排序默认升序sort_array(arr, false)降序。注意sort_array只支持基本类型数组不支持复杂结构体数组。如果要按元素的某个字段排序得先用explode再排序聚合操作比较绕。map_keys(Map)和map_values(Map)提取键列表和值列表。两者的返回类型是数组可以继续和size、array_contains嵌套使用。str_to_map(string str, string pairDelim, string kvDelim)将形如k1v1k2v2的字符串解析为Map在解析URL参数时非常常用。默认pairDelim是逗号kvDelim是冒号但据我所知很多版本默认值不同显式传参最稳妥。6.2 explode与posexplode列转行的核心工具explode是UDTF表生成函数把一行的array或map展开成多行。展开Arrayselect explode(array(a,b))会产生两行。展开Map产生两列一列是key一列是value。posexplode额外返回数组下标下标从0开始在做行列转置时经常需要。实战经典场景标签表里每个用户有多个标签存在一个array字段里要展开成用户-标签明细表SELECT user_id, tag FROM user_tag_table LATERAL VIEW explode(tags) t AS tag;注意lateral view是UDTF的标准搭配把explode生成的虚拟表和原表行关联。一个常见错误是忘写as别名导致后续字段无法引用。另外explode在MapReduce里会产生数据膨胀如果数组极大比如上万元素要评估下游数据量防止OOM。6.3 json_tuple与get_json_objectJSON解析的两个流派JSON解析是数据分析里最常遇到的需求。get_json_object(string json, string path)用JSONPath语法提取值get_json_object({name:张三,age:20}, $.name)返回张三。$.是根路径也可以用$[0]访问数组元素。注意path里不要有空格否则匹配失败。json_tuple(jsonStr, k1, k2, ...)一次提取多个字段返回一个虚拟表配合lateral view使用SELECT t.name, t.age FROM json_table LATERAL VIEW json_tuple(json_str, name, age) t AS name, age;json_tuple比多个get_json_object性能好因为只需解析一次JSON。如果JSON里字段缺失对应位置返回NULL不会报错。但如果JSON本身解析失败不是合法JSONjson_tuple和get_json_object都会返回NULL不会抛异常所以排查时要先确认数据格式。处理嵌套JSON时我的习惯是先get_json_object取到子对象再继续解析两层套用。6.4 反射函数与加密函数reflect、md5、sha2reflect(class, method, arg1, arg2, ...)可以调用Java静态方法。这是一个很暴力的扩展点比如调用java.lang.Math.powreflect(java.lang.Math, pow, 2, 3)返回8.0。在需要一些Hive没有包装好的小功能时非常方便但也要注意性能损耗不要在高频路径里随便用。加密函数中md5(string)返回32位MD5十六进制字符串sha2(string, int)支持SHA-224/256/384/512。做脱敏和特征值关联时很常用。注意md5对NULL返回NULL所以生成ID时要先处理NULL。Hive还提供aes_encrypt和aes_decrypt但要注意密钥类型是二进制使用前最好测试。uuid()返回随机UUID字符串适合生成唯一任务ID。但注意uuid()每次调用都生成新值在MapReduce中如果并行度高生成的UUID无序而且性能开销不小。批量生成唯一ID时我更推荐用concat(date_format(current_date,yyMMdd), lpad(abs(hash(rand())) % 1000000, 6, 0))等业务型方案。7. 窗口函数深度排名、累计、跨行引用的完整攻略7.1 排名三兄弟row_number、rank、dense_rank的细节差异三者语法一致row_number() over (partition by ... order by ...)。row_number生成连续递增的行号不处理并列rank遇到相同的排序值会并列下一个名次跳号dense_rank并列后不跳号。举例说明SELECT id, score, row_number() over (order by score desc) AS rn, rank() over (order by score desc) AS rk, dense_rank() over (order by score desc) AS dr FROM scores;假设score数据为90, 90, 80则rn为1,2,3rk为1,1,3dr为1,1,2。业务上“取前10名”应该用row_number还是rank如果需求是“不管有没有并列只要10条”用row_number如果需求是“有并列的都要”用rank配合where rk 10。具体场景具体分析没有万能答案但默认推荐row_number。7.2 累加与移动窗口rows between的正确姿势求和累加最经典sum(sales) over (partition by region order by month rows between unbounded preceding and current row)。如果不加rows between默认的窗口是range between unbounded preceding and current row也就是包括所有排序字段值相同的行。如果month有重复值累加结果就会出现跳跃——比如两个月的数据相同先出现的那个会把两个月的值都算进去导致第一个月累加值偏大。这是窗口函数里最隐蔽的坑之一。移动平均的例子avg(sales) over (partition by region order by month rows between 3 preceding and current row)表示计算当月和前三个月的平均值。如果窗口边界超出分区会自动截断。rows between current row and unbounded following则从当前行到分区末尾常用于占比计算。7.3 lag/lead跨行引用的正确打开方式lag和lead在会话分析、涨跌判断里必不可少。举个真实场景判断用户每一步操作是否属于同一次会话可以取当前时间与上一条记录时间的差值超过30分钟就视为新会话。SELECT user_id, op_time, unix_timestamp(op_time) - unix_timestamp(lag(op_time) over (partition by user_id order by op_time)) AS gap_seconds FROM user_ops;注意lag的第二个参数offset必须非负lead的offset同样非负。如果想取上和下的多行可以分别用lag(col, 2)。first_value与last_value用来取窗口内首尾值但last_value有个大坑——默认窗口是到当前行所以如果不用rows between unbounded preceding and unbounded followinglast_value拿到的不是全部分区的最后一个值而是当前行附近的最后一个值。很多人在这里出错要显式声明窗口。7.4 ntile分箱均匀分组的利器ntile(n)把分区内数据按顺序分成n桶返回桶编号从1开始。这个函数常用来做“随机分组”或“均匀抽样”。比如把用户按消费金额分成5档SELECT user_id, amount, ntile(5) over (order by amount desc) AS bucket FROM user_orders;注意ntile要求每个分区的行数至少等于n否则部分桶没有数据。它按行数均分不是按金额均分所以分箱的阈值和金额边界没有直接关系。要按金额区间分桶得自己用case when或自定义区间映射。8. 常见问题与排查技巧这些坑我替你踩过了8.1 类型隐式转换带来的结果不一致Hive的类型转换规则很宽松但也容易出意外。12会被当作整数加法返回3但要小心字符串无法转换时报错或返回NULL。concat(1, 2)返回12还是NULL这取决于版本。在Hive 3.x里concat(1,2)会将数字转成字符串然后拼接返回12。但旧版本可能对INT直接拼接行为不同。稳妥做法是显式castconcat(cast(1 as string), cast(2 as string))。另一个常见问题是cast(2023-10-15 as date)正常但cast(20231015 as date)会失败需先转标准格式。日期比较时不要把字符串日期直接和date类型比较尽量统一类型。cast(2023-10-15 as timestamp)会得到2023-10-15 00:00:00转成字符串时也可能出现格式变化。8.2 正则表达式转义与中文匹配Hive正则默认基于Java正则反斜杠需要双重转义。匹配中文时Java正则支持[\u4e00-\u9fa5]但Hive SQL里要写成[\\u4e00-\\u9fa5]直接写\u会被解析成普通字符。很多人在清洗中文文本时踩这个坑。另外正则函数匹配不到时regexp_extract返回空字符串regexp_replace返回原字符串这不是bug是设计。8.3 大表join时的函数下推真相当你在where条件里写substr(id, 1, 3) abcHive会把整个表读出来对每一行应用函数再过滤无法利用分区裁剪或索引。而如果写成id like abc%某些执行引擎可以优化为范围扫描性能差距可能达到几十倍。所以在分区表上尽量让过滤条件保持原始列不要包一层函数。实在需要函数考虑提前把函数结果写成新列或用子查询先过滤再处理。8.4 聚合结果中NULL的处理sum(col)当所有行都为NULL时返回NULL而不是0。业务上往往期望0所以习惯用sum(nvl(col, 0))。但注意count不受影响count(col)统计非NULL行数count(*)统计总行数。avg同理如果所有行NULL则返回NULL。用coalesce(sum(col), 0)兜底更简洁但要注意如果col本身就包含NULLcoalesce也会把总和里的NULL分段抵消结果不会自动处理应该在聚合前先用nvl清洗。8.5 日期函数的时区偏移之前提过集群时区问题这里再强调current_timestamp取到的是session时区时间如果任务运行在UTC显示时间比北京时间晚8小时。处理方式是统一在ETL抽取时做转换不在下游开发时做。可以用from_utc_timestamp(current_timestamp, Asia/Shanghai)固定转换到东八区。同时unix_timestamp(2023-10-15 00:00:00)的解析也受时区影响不同时区得到不同的秒数这会导致跨时区计算时间差时出现偏差。做全球业务时一定不能用本地时区硬编码。8.6 函数不可变与UDTF的局限性Hive内置函数大多是确定性的deterministic但像rand()、uuid()等每次调用结果不同属于非确定性函数。这类函数在查询重试、物化视图计算时可能结果不一致。另外explode等UDTF只能在select表达式里使用不能直接在where或group by里用必须先lateral view展开成虚拟表再过滤。如果要开发自定义函数必须考虑序列化问题和类加载顺序。永久函数注册到系统库后如果类路径没有更新会使用旧版本。改了JAR后要drop function再create function或者加版本号。临时函数只对当前会话有效多个任务并行时要各自注册。这些细节文档里提得少但生产环境必踩。9. 实用速查一行一条写出健壮Hive SQL的核心建议最后分享几条我从实际项目里提炼的写函数相关SQL的硬性建议。第一写函数前先确认参数类型的定义。比如date_add接受字符串yyyy-MM-dd如果你传2023/10/15有的版本会报错或返回NULL。尽量统一日期格式。用date_format做格式化是最可靠的方式。第二聚合函数里混合使用count(distinct ...)时要谨慎。Hive对不同列的count(distinct col1, col2)支持有限某些版本会退化为Map端聚合性能极差。能用近似函数approx_count_distinct的就尽量用在超大基数下误差可控但速度提升巨大。第三多字段拼接用concat_ws而不是concat。concat遇到NULL直接返回NULL而concat_ws跳过NULL让结果里保留分隔符。在线日志清洗时一行日志少一个字段很常见用concat_ws能避免整行丢失。第四使用窗口函数时如果order by后面跟的是多字段要注意desc只作用于最后一个字段其他字段仍默认升序。想要多个字段都倒序需要分别写desc。这个细节在排序里特别容易错。第五重视函数的执行效率。避免在where里用regexp_*这种高开销函数优先使用like、instr等简单匹配。like本身也有陷阱比如like %a%在全表上会全扫描如果数据量大且匹配频率低建议先做关键词字典表再join。第六在复杂ETL里宁可多用子查询分层也不要把十几个函数嵌套在一行。嵌套层数越多调试越痛苦而且执行计划难以优化。把中间结果落成临时表或者直接拆成CTE代码可读性和运行效率往往更好。10. 个人体会函数只是一个工具组合设计才是关键写了这多年的Hive SQL我最大的体会是单个函数再熟练也不会让数据仓库自动产出高质量结果。函数的价值在于组合、在于场景匹配。同样是截取字符串substr和regexp_extract各有适用场合同样是聚合collect_set和count(distinct)在不同数据量下结论完全不同。你需要根据数据特征和业务目标去选择函数而不是记住所有函数。我也建议你在自己的笔记里整理一份“函数-场景”映射表比如“手机号脱敏”对应regexp_replace“取上一条记录”对应lag“分组内去重拼接”对应collect_set concat_ws。这张表比我给你的任何清单都珍贵因为它是从你自己的数据里长出来的。最后一个小技巧出现“函数不存在”报错时不要只怀疑拼写。检查一下当前Hive版本是否支持检查函数名大小写是否敏感Hive函数名不区分大小写但某些第三方驱动会转换检查是否是Calcite解析器下海豚调度里被语法限制。先show functions看一下名字再describe function function_name看参数说明这比翻网络文档快得多。希望这份Hive函数大全能成为你日常工作里的“字典”。下次遇到函数问题不用慌先分类再对表最后写出来跑一遍你就能比大多数人更快更准地完成任务。
1. 开头为什么你需要这份Hive函数清单做了这么多年数据开发我见过太多人卡在Hive函数上——不是不会用而是不知道有哪些函数可用等到需要的时候才去翻文档一趟一趟查得头疼。Hive内置了几百个函数覆盖字符串处理、数值计算、日期操作、聚合统计、复杂类型解析等场景谁能把这些函数梳理成一套体系谁写SQL的速度就能快一倍。这篇文章就是基于我自己的实战经验把Hive里最常用、最容易被遗漏、最容易踩坑的函数按类别整理成一份可查可用的“大全”。不论你是刚接触Hive的新人还是写过千行SQL的老手都能从这里找到直接能抄的用法、参数说明和避坑要点。我会把每个函数的核心逻辑讲透附上可复现的示例重点讲清楚那些文档里不写但实际一定会遇到的坑。先说清楚一点本文基于Hive 3.x版本函数清单以官方内置函数为准。不同发行版比如HDP、CDH、开源社区版可能有个别差异但通用核心函数完全一致。MySQL、PostgreSQL里的一些思路可以借鉴但Hive的语法和函数行为明显不同刚从关系型数据库转过来的人尤其要注意。2. Hive函数的整体架构与核心分类2.1 从UDF到内置函数Hive函数体系怎么构成的Hive的函数体系分为三层最底层的是内置函数Built-in Functions开箱即用覆盖了绝大多数日常需求中间层是用户自定义函数UDF、UDAF、UDTF用来处理内置函数搞不定的个性化逻辑最上层是函数注册与调用机制包括临时函数和永久函数。日常开发中内置函数的使用频率占到了90%以上。但很多人对内置函数的理解停留在“用的时候查一下”的层面没有建立分类意识导致同一个功能绕远路实现。比如判断字符串是否为空有人非要用length(str)0其实用trim配合if就能优雅解决。从执行引擎的角度来看Hive函数最终会翻译成底层计算框架MapReduce、Tez或Spark的算子操作。理解了这一点你就明白为什么某些函数在where条件里使用会拖慢性能——因为函数无法下推导致全表扫描。这个后面会专门讲。2.2 为什么按类别掌握函数比逐个死记硬背更高效我面试过不少候选人问“字符串拼接怎么实现”有人只知道concat不知道concat_ws可以指定分隔符问“取当前时间”有人只记得sysdate()却不清楚Hive里稳定的是current_timestamp。这些问题本质上不是记性差而是没有建立函数分类体系。把函数按用途分成字符串、数值、日期、条件、聚合、集合、窗口、特殊这八类之后你脑子里的索引就建立起来了。写SQL的时候先判断“我要解决什么问题”再去对应类别里挑选函数命中率会高很多。这也是本篇文章的编排逻辑。2.3 函数分类一览一张表看全最常用函数先列一个我平时最常用的函数速查表下面每个类别我都会展开细讲。类别高频函数字符串concat, concat_ws, substr, substring_index, split, regexp_replace, regexp_extract, lower, upper, trim, lpad, rpad, repeat, reverse, instr, length, replace数值round, floor, ceil, rand, abs, pow, sqrt, greatest, least, pmod, sin, cos日期current_date, current_timestamp, from_unixtime, unix_timestamp, to_date, year, month, day, datediff, date_add, date_sub, date_format, last_day, next_day, months_between条件if, case when, coalesce, nvl, nullif, assert_true聚合count, sum, avg, min, max, collect_list, collect_set, percentile_approx, histogram_numeric集合size, array, map, explode, posexplode, json_tuple, str_to_map, map_keys, map_values, array_contains, sort_array窗口row_number, rank, dense_rank, sum over, lag, lead, first_value, last_value, ntile特殊uuid, current_database, sha2, md5, aes_encrypt, reflect这张表不用背你就做一件事把它收藏起来写SQL卡住的时候回来翻。翻的次数多了自然而然就记住了。3. 字符串函数最常用也最容易写错的细节3.1 字符串长度与截取substr的边界问题length(string str)返回字符数而不是字节数对中文特别友好。比如length(你好)返回2而不是6。如果你需要字节数做存储判断可以用length(cast(str as binary))。这个坑我在做编码转换时踩过——用Java的getBytes思路去套Hive结果长度对不上后来才意识到Hive默认按字符计算。substr(string str, int start, int len)是截取函数start从1开始不是0。substr(abcde, 1, 3)结果是abcsubstr(abcde, 3, 2)结果是cd。start支持负数表示从尾部倒着数substr(abcde, -2, 2)结果是de。如果你写substr(abcde, 0, 3)Hive不会报错但行为等价于从第0位开始截取很多版本会返回空字符串或报错写的时候务必从1开始。substring_index(string str, string delim, int count)是另一个宝藏。它是按分隔符拆分的“第N段”函数substring_index(a,b,c,d, ,, 2)返回a,bsubstring_index(a,b,c,d, ,, -2)返回c,d。注意count为负数时是取倒数第N个分隔符右边的部分。3.2 拼接与替换concat_ws和regexp_replace的组合拳concat(string a, string b, ...)直接拼接不做任何空值处理。任何一个参数为NULL结果就是NULL。这个行为很坑——你明明拼接了三个字段有一个为空整个结果就没了。解决办法是用nvl提前转换或者直接用concat_ws。concat_ws(string sep, string a, string b, ...)以指定分隔符拼接最大的优点是会忽略NULL值不忽略空字符串。比如concat_ws(-, a, null, c)返回a-c。如果你要拼接数组里的元素concat_ws还支持传入arraystringconcat_ws(,, array(a,b))返回a,b。这个特性在列转行拼接时非常实用。regexp_replace(string str, string pattern, string replace)用正则替换子串。注意口诀前面的参数是“要处理的字符串”第二个是“正则规则”第三个是“替换成什么”。replace(str, old, new)则是简单的字符串替换不解析正则。区分这两个函数是入门课。实战中经常遇到需求把手机号中间四位打码。可以这么写SELECT regexp_replace(13812345678, (\\d{3})\\d{4}(\\d{4}), $1****$2); -- 输出 138****5678注意Hive字符串常量里的反斜杠要转义正则里\d要写成\\d很多人就卡在这里。3.3 提取与清洗regexp_extract的捕获组用法regexp_extract(string str, string pattern, int index)从字符串中按正则提取子串第三个参数指定返回第几个捕获组。比如从日志中提取IP和时间SELECT regexp_extract(192.168.1.1 - - [10/Oct/2023:14:23:01], (\\d\\.\\d\\.\\d\\.\\d), 1);多捕获组时index0返回整个匹配项index1返回第一个括号组依次类推。如果匹配不到返回空字符串不是NULL。trim(string str)去除两端空格ltrim和rtrim分别处理左右。注意trim只能去空格不能去换行符\n和制表符\t。需要去全角空格时要先用regexp_replace(str, [\\u00a0\\u3000], )处理这是从外部数据清洗时踩过的坑。字符串函数里还有一个高频需求判断是否包含某个子串。instr(string str, string substr)返回子串第一次出现的位置从1开始找不到返回0。所以判断“是否包含”经常写成instr(str, key) 0。有的版本还支持locate用法和instr一致只是参数顺序不同不建议混用。3.4 左补右补与大小写容易被忽略的格式化场景lpad(string str, int len, string pad)左补齐rpad右补齐。比如生成固定长度的订单号lpad(123, 6, 0)返回000123。如果原字符串长度超过len则截断到len长度lpad(1234567, 3, 0)返回123。这个截断行为在生成批次号时很容易出问题——长度不够补零没问题但长度超出时直接截断导致数据错误。我建议在补位前先用length做一次判断。lower和upper的大小写转换没什么黑科技但要注意initcap函数把每个单词首字母大写对英文姓名清洗很好用。reverse(string str)反转字符串适合做回文判断也可以配合split做字符串倒序。重复生成字符串用repeat(string str, int n)比如生成连续逗号分隔的N个占位符repeat(?,, 3)配合split可以生成临时序列。4. 数值与日期函数计算准确性与时区陷阱4.1 四舍五入与取整round不是你想的那样round(double d, int n)四舍五入保留n位小数n可以省略默认取整。一个经典坑round(2.5)在Hive里返回3四舍五入但round(3.5)也返回4没有MySQL里的“四舍六入五成双”规则。如果要银行家舍入得自己用floor和ceil绕。还有round的参数如果是decimal类型精度按decimal处理如果是float/double会有二进制浮点的误差。比如round(1.005, 2)可能得到1.00而不是1.01。解决办法是先把double转成decimal或者用cast提高精度。floor向下取整ceil向上取整参数为负数时也要小心floor(-1.2)返回-2ceil(-1.2)返回-1。这个方向性符合数学定义但业务里经常用错。pmod(int a, int b)返回a对b取模的正余数与%运算符不同。pmod(-1, 10)返回9而-1 % 10在Hive里返回-1。做分页、分桶时推荐用pmod避免负数索引。4.2 随机数与最值rand的种子参数rand(int seed)返回0到1之间的随机数。不传种子时每次执行结果不同传入固定种子时同一查询内随机序列可复现。这个特性在抽样和数据脱敏时非常有用——比如要给同一批用户每次生成一致的随机分组就写rand(user_id)保证同一个user_id分到同一组。greatest(col1, col2, ...)返回参数列表的最大值least返回最小值。参数类型必须一致否则隐式转换可能导致精度损失。这个函数适合做多列比较比如取三个报价字段的最高值比嵌套case when简洁得多。4.3 日期获取unix_timestamp的前世今生unix_timestamp()返回当前时间的Unix秒级时间戳current_timestamp返回带时区的timestamp类型。注意两者返回类型不同不能直接相加。from_unixtime(bigint seconds)将时间戳转为日期字符串默认格式yyyy-MM-dd HH:mm:ss。如果要指定格式比如只取年月日from_unixtime(ts, yyyy-MM-dd)。时区问题是日期函数最大的坑。Hive的current_timestamp跟随集群的时区配置如果你的任务跑在UTC时区的集群上而你期望北京时间直接取current_timestamp就会差8小时。正确处理方式是SELECT from_utc_timestamp(current_timestamp, Asia/Shanghai);from_utc_timestamp和to_utc_timestamp是Hive特有的时区转换函数比手动加减8小时可靠得多。手动加减在夏令时地区还会出错这个函数能正确处理时区规则。4.4 日期计算datediff与date_add的组合场景datediff(string enddate, string startdate)返回两个日期相差的天数。注意参数顺序前大后小为正前小后大为负。date_add(string startdate, int days)加天数date_sub减天数。这两个函数接受字符串日期也接受date类型但传timestamp类型时有的版本会隐式转成字符串导致格式不对。实际场景求上月第一天、上月末日。可以用trunc(month)实现。trunc(string date, string format)按format截断日期支持YEAR、MONTH、QUARTER等。trunc(2023-10-15, MONTH)返回2023-10-01trunc(2023-10-15, YEAR)返回2023-01-01。这个函数比date_sub拼接更干净。months_between(string date1, string date2)返回两个日期之间的月数结果是浮点数。比如months_between(2023-10-31, 2023-01-31)返回9.0如果日不对齐会返回带小数的月数比如months_between(2023-10-15, 2023-01-31)返回8.45...。计算年龄时如果希望按月份整算可以用floor(months_between(...))但注意边界条件。last_day(string date)返回当月最后一天。next_day(string date, string weekday)返回指定日期之后的下一个周几weekday为MO、TU等在Hive里不区分大小写。注意是“下一个”不是“本周的”。如果要找本周一需要配合date_sub处理。date_format是格式化的万能函数date_format(2023-10-15 10:30:00, yyyy-MM-dd HH:mm:ss)按模板输出。特别注意Hive的格式符yyyy是四位年MM是两位月dd是两位日HH是24小时制hh是12小时制。写YYYY大写代表“周基准年”和yyyy含义完全不同很多人写错导致跨年日期偏移。5. 条件判断与聚合分析从if到窗口函数的进阶之路5.1 条件函数if、case when、coalesce、nvl的区别与选择if(boolean test, T trueValue, T falseValue)是Hive里最基础的条件分支。case when支持多条件适合复杂逻辑。两者可以互换但性能差异几乎可以忽略主要看可读性。coalesce(T a, T b, ...)返回第一个非NULL值所有参数都为NULL时才返回NULL。这个函数在做多字段兜底时非常实用比如取用户手机号优先取主叫字段为空则取备用字段coalesce(mobile, mobile2, mobile3)。注意coalesce不忽略空字符串空字符串不是NULL所以如果手机号字段是空字符串而不是NULLcoalesce不会帮你兜底需要配合trim和if处理。nvl(T a, T b)是coalesce的简化版只有两个参数功能一模一样。很多从Oracle转过来的人习惯用nvl而Hive也原生支持所以尽管用。nullif(T a, T b)返回NULL当a等于b否则返回a。这个函数在做除法避免除零时有用nullif(cnt, 0)可以和coalesce嵌套实现“除数为0则返回默认值”的效果。5.2 聚合函数count的特殊形式与collect_set的去重能力count(1)、count(*)、count(col)这三个要分清。count(*)统计行数count(1)等价于count(*)count(col)只统计该列非NULL的行数。如果想统计非NULL且去重的数量用count(distinct col)。注意count(distinct col1, col2)是多列去重统计语法上没问题但性能通常很差大数据量下尽量避免。collect_list分组内收集列表不去重、有序collect_set收集去重列表。两者都是UDAF配合concat_ws可以把分组内的多个值拼成一个字符串。比如统计每个用户的所有角色SELECT user_id, concat_ws(,, collect_list(role)) AS roles FROM user_role_table GROUP BY user_id;collect_list不保证顺序如果要按某个字段排序后再收集得用sort_array或在子查询里先order by。一个常用技巧是concat_ws(,, sort_array(collect_list(role)))来得到有序去重列表。percentile_approx是分位数计算的利器。percentile_approx(col, 0.5)计算中位数percentile_approx(col, array(0.25, 0.5, 0.75))同时计算多个分位数。它基于近似算法在大数据量下比percentile快很多而且percentile只支持整型percentile_approx支持double。在做数据质量监控时用分位数比平均值更靠谱——平均值容易被极端值带偏但中位数和四分位数能反映真实分布。histogram_numeric是数值分布直方图返回一个包含histogram的数组一般配合自定义代码使用。5.3 窗口函数row_number必会lag/lead必用Hive对窗口函数的支持已经很完善。row_number() over (partition by col order by col)是最经典的行号函数常用于取每个分组的前N条。rank允许并列且会跳号1,1,3dense_rank允许并列但不跳号1,1,2。这三个的区别是面试高频题实际使用时也很关键。取每个用户最近一条订单通常用row_number而不是rank因为业务上不存在并列第一的情况。lag(col, offset)取分组排序后的上一行值lead取下下一行值。这个函数在做环比、会话间隔分析时能救命的。比如求每个用户相邻两次登录的时间间隔SELECT user_id, login_time, lag(login_time) over (partition by user_id order by login_time) AS prev_login FROM login_log;然后在外层做unix_timestamp差值。注意lag的默认值参数lag(col, 1, 0)表示如果上一行为NULL则返回0。不写默认值结果就是NULL不影响业务但容易在计算里带出NULL。窗口函数的框架定义rows between unbounded preceding and current row常用作累加。默认的窗口范围是从分区开始到当前行但如果order by字段有重复值默认会包含所有重复值的行导致累加结果偏大。要精确控制用rows between显式指定。这个细节我踩过必须用rows between unbounded preceding and current row强制行为。5.4 增强型聚合grouping sets与cube基础group by之外Hive支持grouping sets、cube、rollup。比如一次SQL同时统计总订单量、按地区统计、按品类统计SELECT region, category, count(*) FROM orders GROUP BY region, category GROUPING SETS ((region), (category), ());rollup是grouping sets的子集生成上卷聚合cube生成所有维度组合的笛卡尔积。这些函数能减少多次扫描但结果里会出现NULL占位使用时需要配合grouping__id计算字段来区分纬度。我自己更常用grouping sets因为cube生成的组合过多业务上往往用不到。6. 复杂类型与特殊函数解析JSON、MAP和ARRAY的实战招法6.1 集合函数size、sort_array、array_containssize(Map)返回map的键值对数量size(Array)返回数组长度。判断数组是否为空用size(arr) 0。array_contains(arrayT, T value)判断数组中是否包含某值常用于筛选标签数据where array_contains(tags, vip)。sort_array(ArrayT)对数组排序默认升序sort_array(arr, false)降序。注意sort_array只支持基本类型数组不支持复杂结构体数组。如果要按元素的某个字段排序得先用explode再排序聚合操作比较绕。map_keys(Map)和map_values(Map)提取键列表和值列表。两者的返回类型是数组可以继续和size、array_contains嵌套使用。str_to_map(string str, string pairDelim, string kvDelim)将形如k1v1k2v2的字符串解析为Map在解析URL参数时非常常用。默认pairDelim是逗号kvDelim是冒号但据我所知很多版本默认值不同显式传参最稳妥。6.2 explode与posexplode列转行的核心工具explode是UDTF表生成函数把一行的array或map展开成多行。展开Arrayselect explode(array(a,b))会产生两行。展开Map产生两列一列是key一列是value。posexplode额外返回数组下标下标从0开始在做行列转置时经常需要。实战经典场景标签表里每个用户有多个标签存在一个array字段里要展开成用户-标签明细表SELECT user_id, tag FROM user_tag_table LATERAL VIEW explode(tags) t AS tag;注意lateral view是UDTF的标准搭配把explode生成的虚拟表和原表行关联。一个常见错误是忘写as别名导致后续字段无法引用。另外explode在MapReduce里会产生数据膨胀如果数组极大比如上万元素要评估下游数据量防止OOM。6.3 json_tuple与get_json_objectJSON解析的两个流派JSON解析是数据分析里最常遇到的需求。get_json_object(string json, string path)用JSONPath语法提取值get_json_object({name:张三,age:20}, $.name)返回张三。$.是根路径也可以用$[0]访问数组元素。注意path里不要有空格否则匹配失败。json_tuple(jsonStr, k1, k2, ...)一次提取多个字段返回一个虚拟表配合lateral view使用SELECT t.name, t.age FROM json_table LATERAL VIEW json_tuple(json_str, name, age) t AS name, age;json_tuple比多个get_json_object性能好因为只需解析一次JSON。如果JSON里字段缺失对应位置返回NULL不会报错。但如果JSON本身解析失败不是合法JSONjson_tuple和get_json_object都会返回NULL不会抛异常所以排查时要先确认数据格式。处理嵌套JSON时我的习惯是先get_json_object取到子对象再继续解析两层套用。6.4 反射函数与加密函数reflect、md5、sha2reflect(class, method, arg1, arg2, ...)可以调用Java静态方法。这是一个很暴力的扩展点比如调用java.lang.Math.powreflect(java.lang.Math, pow, 2, 3)返回8.0。在需要一些Hive没有包装好的小功能时非常方便但也要注意性能损耗不要在高频路径里随便用。加密函数中md5(string)返回32位MD5十六进制字符串sha2(string, int)支持SHA-224/256/384/512。做脱敏和特征值关联时很常用。注意md5对NULL返回NULL所以生成ID时要先处理NULL。Hive还提供aes_encrypt和aes_decrypt但要注意密钥类型是二进制使用前最好测试。uuid()返回随机UUID字符串适合生成唯一任务ID。但注意uuid()每次调用都生成新值在MapReduce中如果并行度高生成的UUID无序而且性能开销不小。批量生成唯一ID时我更推荐用concat(date_format(current_date,yyMMdd), lpad(abs(hash(rand())) % 1000000, 6, 0))等业务型方案。7. 窗口函数深度排名、累计、跨行引用的完整攻略7.1 排名三兄弟row_number、rank、dense_rank的细节差异三者语法一致row_number() over (partition by ... order by ...)。row_number生成连续递增的行号不处理并列rank遇到相同的排序值会并列下一个名次跳号dense_rank并列后不跳号。举例说明SELECT id, score, row_number() over (order by score desc) AS rn, rank() over (order by score desc) AS rk, dense_rank() over (order by score desc) AS dr FROM scores;假设score数据为90, 90, 80则rn为1,2,3rk为1,1,3dr为1,1,2。业务上“取前10名”应该用row_number还是rank如果需求是“不管有没有并列只要10条”用row_number如果需求是“有并列的都要”用rank配合where rk 10。具体场景具体分析没有万能答案但默认推荐row_number。7.2 累加与移动窗口rows between的正确姿势求和累加最经典sum(sales) over (partition by region order by month rows between unbounded preceding and current row)。如果不加rows between默认的窗口是range between unbounded preceding and current row也就是包括所有排序字段值相同的行。如果month有重复值累加结果就会出现跳跃——比如两个月的数据相同先出现的那个会把两个月的值都算进去导致第一个月累加值偏大。这是窗口函数里最隐蔽的坑之一。移动平均的例子avg(sales) over (partition by region order by month rows between 3 preceding and current row)表示计算当月和前三个月的平均值。如果窗口边界超出分区会自动截断。rows between current row and unbounded following则从当前行到分区末尾常用于占比计算。7.3 lag/lead跨行引用的正确打开方式lag和lead在会话分析、涨跌判断里必不可少。举个真实场景判断用户每一步操作是否属于同一次会话可以取当前时间与上一条记录时间的差值超过30分钟就视为新会话。SELECT user_id, op_time, unix_timestamp(op_time) - unix_timestamp(lag(op_time) over (partition by user_id order by op_time)) AS gap_seconds FROM user_ops;注意lag的第二个参数offset必须非负lead的offset同样非负。如果想取上和下的多行可以分别用lag(col, 2)。first_value与last_value用来取窗口内首尾值但last_value有个大坑——默认窗口是到当前行所以如果不用rows between unbounded preceding and unbounded followinglast_value拿到的不是全部分区的最后一个值而是当前行附近的最后一个值。很多人在这里出错要显式声明窗口。7.4 ntile分箱均匀分组的利器ntile(n)把分区内数据按顺序分成n桶返回桶编号从1开始。这个函数常用来做“随机分组”或“均匀抽样”。比如把用户按消费金额分成5档SELECT user_id, amount, ntile(5) over (order by amount desc) AS bucket FROM user_orders;注意ntile要求每个分区的行数至少等于n否则部分桶没有数据。它按行数均分不是按金额均分所以分箱的阈值和金额边界没有直接关系。要按金额区间分桶得自己用case when或自定义区间映射。8. 常见问题与排查技巧这些坑我替你踩过了8.1 类型隐式转换带来的结果不一致Hive的类型转换规则很宽松但也容易出意外。12会被当作整数加法返回3但要小心字符串无法转换时报错或返回NULL。concat(1, 2)返回12还是NULL这取决于版本。在Hive 3.x里concat(1,2)会将数字转成字符串然后拼接返回12。但旧版本可能对INT直接拼接行为不同。稳妥做法是显式castconcat(cast(1 as string), cast(2 as string))。另一个常见问题是cast(2023-10-15 as date)正常但cast(20231015 as date)会失败需先转标准格式。日期比较时不要把字符串日期直接和date类型比较尽量统一类型。cast(2023-10-15 as timestamp)会得到2023-10-15 00:00:00转成字符串时也可能出现格式变化。8.2 正则表达式转义与中文匹配Hive正则默认基于Java正则反斜杠需要双重转义。匹配中文时Java正则支持[\u4e00-\u9fa5]但Hive SQL里要写成[\\u4e00-\\u9fa5]直接写\u会被解析成普通字符。很多人在清洗中文文本时踩这个坑。另外正则函数匹配不到时regexp_extract返回空字符串regexp_replace返回原字符串这不是bug是设计。8.3 大表join时的函数下推真相当你在where条件里写substr(id, 1, 3) abcHive会把整个表读出来对每一行应用函数再过滤无法利用分区裁剪或索引。而如果写成id like abc%某些执行引擎可以优化为范围扫描性能差距可能达到几十倍。所以在分区表上尽量让过滤条件保持原始列不要包一层函数。实在需要函数考虑提前把函数结果写成新列或用子查询先过滤再处理。8.4 聚合结果中NULL的处理sum(col)当所有行都为NULL时返回NULL而不是0。业务上往往期望0所以习惯用sum(nvl(col, 0))。但注意count不受影响count(col)统计非NULL行数count(*)统计总行数。avg同理如果所有行NULL则返回NULL。用coalesce(sum(col), 0)兜底更简洁但要注意如果col本身就包含NULLcoalesce也会把总和里的NULL分段抵消结果不会自动处理应该在聚合前先用nvl清洗。8.5 日期函数的时区偏移之前提过集群时区问题这里再强调current_timestamp取到的是session时区时间如果任务运行在UTC显示时间比北京时间晚8小时。处理方式是统一在ETL抽取时做转换不在下游开发时做。可以用from_utc_timestamp(current_timestamp, Asia/Shanghai)固定转换到东八区。同时unix_timestamp(2023-10-15 00:00:00)的解析也受时区影响不同时区得到不同的秒数这会导致跨时区计算时间差时出现偏差。做全球业务时一定不能用本地时区硬编码。8.6 函数不可变与UDTF的局限性Hive内置函数大多是确定性的deterministic但像rand()、uuid()等每次调用结果不同属于非确定性函数。这类函数在查询重试、物化视图计算时可能结果不一致。另外explode等UDTF只能在select表达式里使用不能直接在where或group by里用必须先lateral view展开成虚拟表再过滤。如果要开发自定义函数必须考虑序列化问题和类加载顺序。永久函数注册到系统库后如果类路径没有更新会使用旧版本。改了JAR后要drop function再create function或者加版本号。临时函数只对当前会话有效多个任务并行时要各自注册。这些细节文档里提得少但生产环境必踩。9. 实用速查一行一条写出健壮Hive SQL的核心建议最后分享几条我从实际项目里提炼的写函数相关SQL的硬性建议。第一写函数前先确认参数类型的定义。比如date_add接受字符串yyyy-MM-dd如果你传2023/10/15有的版本会报错或返回NULL。尽量统一日期格式。用date_format做格式化是最可靠的方式。第二聚合函数里混合使用count(distinct ...)时要谨慎。Hive对不同列的count(distinct col1, col2)支持有限某些版本会退化为Map端聚合性能极差。能用近似函数approx_count_distinct的就尽量用在超大基数下误差可控但速度提升巨大。第三多字段拼接用concat_ws而不是concat。concat遇到NULL直接返回NULL而concat_ws跳过NULL让结果里保留分隔符。在线日志清洗时一行日志少一个字段很常见用concat_ws能避免整行丢失。第四使用窗口函数时如果order by后面跟的是多字段要注意desc只作用于最后一个字段其他字段仍默认升序。想要多个字段都倒序需要分别写desc。这个细节在排序里特别容易错。第五重视函数的执行效率。避免在where里用regexp_*这种高开销函数优先使用like、instr等简单匹配。like本身也有陷阱比如like %a%在全表上会全扫描如果数据量大且匹配频率低建议先做关键词字典表再join。第六在复杂ETL里宁可多用子查询分层也不要把十几个函数嵌套在一行。嵌套层数越多调试越痛苦而且执行计划难以优化。把中间结果落成临时表或者直接拆成CTE代码可读性和运行效率往往更好。10. 个人体会函数只是一个工具组合设计才是关键写了这多年的Hive SQL我最大的体会是单个函数再熟练也不会让数据仓库自动产出高质量结果。函数的价值在于组合、在于场景匹配。同样是截取字符串substr和regexp_extract各有适用场合同样是聚合collect_set和count(distinct)在不同数据量下结论完全不同。你需要根据数据特征和业务目标去选择函数而不是记住所有函数。我也建议你在自己的笔记里整理一份“函数-场景”映射表比如“手机号脱敏”对应regexp_replace“取上一条记录”对应lag“分组内去重拼接”对应collect_set concat_ws。这张表比我给你的任何清单都珍贵因为它是从你自己的数据里长出来的。最后一个小技巧出现“函数不存在”报错时不要只怀疑拼写。检查一下当前Hive版本是否支持检查函数名大小写是否敏感Hive函数名不区分大小写但某些第三方驱动会转换检查是否是Calcite解析器下海豚调度里被语法限制。先show functions看一下名字再describe function function_name看参数说明这比翻网络文档快得多。希望这份Hive函数大全能成为你日常工作里的“字典”。下次遇到函数问题不用慌先分类再对表最后写出来跑一遍你就能比大多数人更快更准地完成任务。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
