Hive常用函数-EW帮帮网

Hive作为基于Hadoop的数据仓库工具，提供了丰富的内置函数用于数据清洗、转换、聚合和分析。这些函数覆盖了字符串处理、日期计算、条件判断、聚合统计、窗口分析等常见场景。以下按功能分类介绍Hive中最常用的查询函数，包括说明、语法和实战示例。

一、聚合函数（Aggregate Functions）

聚合函数用于对分组数据进行统计，需配合GROUP BY使用（或作为窗口函数的聚合部分），返回单个值。

1. `count([distinct] col)`

功能：统计非空值的行数；count(distinct col)统计去重后的非空值数量。
语法：count(col) 或 count(*)（统计所有行，包括null）。

示例：
统计订单表中的总订单数和去重用户数：

SELECT 
  count(*) AS total_orders,  -- 所有行（包括null行）
  count(order_id) AS valid_orders,  -- 非空order_id的行数
  count(distinct user_id) AS distinct_users  -- 去重用户数
FROM orders;

2. `sum(col)` / `avg(col)` / `max(col)` / `min(col)`

功能：
- sum(col)：计算分组内数值列的总和（忽略null）；
- avg(col)：计算平均值（sum/非null行数）；
- max(col)/min(col)：取分组内的最大值/最小值。

示例：
统计每个用户的订单总金额、平均金额、最大订单金额：

SELECT 
  user_id,
  sum(amount) AS total_amount,  -- 总金额
  avg(amount) AS avg_amount,    -- 平均金额
  max(amount) AS max_amount     -- 最大金额
FROM orders
GROUP BY user_id;

3. `collect_set(col)`

功能：将分组内的col值收集为去重数组（与collect_list的区别：自动去重）。

示例：
收集每个商品的去重标签（排除重复标签）：

SELECT 
  product_id,
  collect_set(tag) AS unique_tags  -- 标签去重
FROM product_tags
GROUP BY product_id;

二、字符串函数（String Functions）

用于字符串的拼接、切割、转换等处理，是数据清洗的核心工具。

1. `concat(str1, str2, ...)`

功能：拼接多个字符串（若任一参数为null，结果为null）。
示例：
拼接用户姓名和手机号（格式：“姓名-手机号”）：
```
SELECT concat(name, '-', phone) AS user_info FROM users;
```

2. `concat_ws(separator, str1, str2, ...)`

功能：用指定分隔符separator拼接字符串（忽略null参数，避免concat的null问题）。

示例：
用逗号拼接用户标签（忽略null标签）：

SELECT 
  user_id,
  concat_ws(',', tag1, tag2, tag3) AS all_tags  -- 若tag2为null，直接拼接tag1和tag3
FROM user_tags;

3. `substr(str, pos[, len])` / `substring(str, pos[, len])`

功能：从str的pos位置（1-based索引）截取子串，可选len指定长度（默认到末尾）。

示例：
提取手机号的前3位（运营商代码）：

SELECT substr(phone, 1, 3) AS operator_code FROM users;  -- 如“138”

4. `split(str, regex)`

功能：按正则表达式regex切割字符串，返回数组。
示例：
切割URL中的路径（如“/home/goods/101” → 数组["", "home", "goods", "101"]）：
```
SELECT split(url, '/') AS url_parts FROM user_behavior;
```

5. `lower(str)` / `upper(str)`

功能：将字符串转为全小写/全大写（常用于统一格式）。

示例：
统一商品名称为小写：

SELECT lower(product_name) AS lower_name FROM products;

三、日期函数（Date Functions）

处理日期类型数据的转换、加减、差值计算，适用于时间序列分析。

1. `to_date(timestamp)`

功能：从时间戳（timestamp）中提取日期部分（格式：yyyy-MM-dd）。

示例：
提取订单时间的日期：

SELECT to_date(order_time) AS order_date FROM orders;  -- 如“2025-07-24”

2. `date_add(date, days)` / `date_sub(date, days)`

功能：计算date加/减days天后的日期。

示例：
计算订单日期的前7天（上周同期）：

SELECT date_sub(order_date, 7) AS last_week_date FROM orders;

3. `datediff(end_date, start_date)`

功能：计算两个日期的差值（end_date - start_date，单位：天）。

示例：
计算用户注册到首次下单的间隔天数：

SELECT 
  user_id,
  datediff(first_order_date, register_date) AS days_to_first_order
FROM user_stats;

4. `unix_timestamp([date[, pattern]])` / `from_unixtime(unixtime[, pattern])`

功能：
- unix_timestamp：将日期字符串转为Unix时间戳（秒级，默认转换当前时间）；
- from_unixtime：将Unix时间戳转为指定格式的日期字符串。

示例：
转换“2025-07-24 12:30:00”为时间戳，再转回指定格式：

-- 转为时间戳：1753365000
SELECT unix_timestamp('2025-07-24 12:30:00', 'yyyy-MM-dd HH:mm:ss') AS ts;

-- 时间戳转回“月-日 时:分”：07-24 12:30
SELECT from_unixtime(1753365000, 'MM-dd HH:mm') AS formatted_time;

四、条件函数（Conditional Functions）

用于处理null值、多条件判断，实现灵活的逻辑分支。

1. `case when ... then ... else ... end`

功能：多条件判断（类似编程语言的if-else if-else）。

语法：

case 
  when condition1 then result1 
  when condition2 then result2 
  else default_result 
end

示例：
按订单金额划分等级（0-100：普通，101-500：白银，>500：黄金）：

SELECT 
  order_id,
  amount,
  case 
    when amount <= 100 then '普通订单'
    when amount <= 500 then '白银订单'
    else '黄金订单'
  end AS order_level
FROM orders;

2. `if(condition, true_result, false_result)`

功能：简单双分支判断（条件为true返回第一个值，否则返回第二个）。

示例：
判断订单是否为大额订单（金额>1000标记为1，否则0）：

SELECT order_id, if(amount > 1000, 1, 0) AS is_large_order FROM orders;

3. `nvl(col, default_value)`

功能：若col为null，返回default_value；否则返回col本身（处理null值的常用函数）。

示例：
用“未知”填充用户职业的null值：

SELECT nvl(occupation, '未知') AS occupation FROM users;

五、窗口函数（Window Functions）

窗口函数用于分组内的每行数据计算结果（不压缩行数），常用于排序、累计统计、分组内排名等场景。核心是通过over()定义“窗口”（即分组范围）。

1. `row_number() over(partition by col order by sort_col)`

功能：在partition by分组内，按sort_col排序，为每行分配唯一序号（1,2,3…，无重复）。

示例：
按用户分组，对其订单按金额倒序排名（取每个用户的最大金额订单）：

SELECT user_id, order_id, amount,
  row_number() over(partition by user_id order by amount desc) AS rn
FROM orders;
-- 结果中rn=1的即为该用户最大金额订单

2. `rank() over(partition by col order by sort_col)` / `dense_rank()`

功能：
- rank()：分组内排序，相同值排名相同，后续排名跳号（如1,1,3…）；
- dense_rank()：相同值排名相同，后续排名不跳号（如1,1,2…）。

示例：
按商品分类分组，对销量排名：

SELECT category, product_id, sales,
  rank() over(partition by category order by sales desc) AS rk,
  dense_rank() over(partition by category order by sales desc) AS drk
FROM products;

3. `sum(col) over(partition by col order by sort_col rows between ... and ...)`

功能：在窗口内计算累计和（rows between定义窗口范围，如“从开头到当前行”）。

示例：
按用户分组，计算累计订单金额（按时间顺序）：

SELECT user_id, order_time, amount,
  sum(amount) over(
    partition by user_id 
    order by order_time 
    rows between unbounded preceding and current row  -- 从分组第一行到当前行
  ) AS cumulative_amount
FROM orders;

六、数组/Map函数（Array/Map Functions）

处理Hive中的复杂类型（数组array、键值对map）。

1. `explode(array_col)`

功能：将数组array_col的每个元素拆分为单行（“行转列”核心函数）。

示例：
将用户标签数组拆分为多行：

SELECT user_id, tag
FROM user_tags
lateral view explode(tags_array) tmp AS tag;  -- lateral view配合explode使用

2. `size(array_col)` / `size(map_col)`

功能：返回数组的元素个数，或map的键值对数量。

示例：
统计每个商品的标签数量：

SELECT product_id, size(tags_array) AS tag_count FROM products;

3. `array_contains(array_col, value)`

功能：判断数组array_col中是否包含value，返回true/false。

示例：
判断商品是否包含“促销”标签：

SELECT product_id,
  array_contains(tags_array, '促销') AS has_promotion_tag
FROM products;

总结

Hive的函数覆盖了数据处理的全流程：从基础的聚合统计（sum、count）、字符串清洗（concat、split）、日期转换（unix_timestamp），到复杂的条件判断（case when）、窗口分析（row_number）、数组处理（explode）。实际使用中，需根据业务场景组合函数（如collect_list+concat_ws生成行为路径，row_number+case when筛选分组TopN）。

注意事项：

函数对null的处理（如concat遇null返回null，concat_ws忽略null）；
聚合函数与窗口函数的区别（聚合压缩行数，窗口保留行数）；
复杂类型（数组、map）处理需配合lateral view等语法。

Hive常用函数

一、聚合函数（Aggregate Functions）

1. `count([distinct] col)`

2. `sum(col)` / `avg(col)` / `max(col)` / `min(col)`

3. `collect_set(col)`

二、字符串函数（String Functions）

1. `concat(str1, str2, ...)`

2. `concat_ws(separator, str1, str2, ...)`

3. `substr(str, pos[, len])` / `substring(str, pos[, len])`

4. `split(str, regex)`

5. `lower(str)` / `upper(str)`

三、日期函数（Date Functions）

1. `to_date(timestamp)`

2. `date_add(date, days)` / `date_sub(date, days)`

3. `datediff(end_date, start_date)`

4. `unix_timestamp([date[, pattern]])` / `from_unixtime(unixtime[, pattern])`

四、条件函数（Conditional Functions）

1. `case when ... then ... else ... end`

2. `if(condition, true_result, false_result)`

3. `nvl(col, default_value)`

五、窗口函数（Window Functions）

1. `row_number() over(partition by col order by sort_col)`

2. `rank() over(partition by col order by sort_col)` / `dense_rank()`

3. `sum(col) over(partition by col order by sort_col rows between ... and ...)`

六、数组/Map函数（Array/Map Functions）

1. `explode(array_col)`

2. `size(array_col)` / `size(map_col)`

3. `array_contains(array_col, value)`

总结

网站公告

今日签到

热门文章

最新发布

Hive常用函数

一、聚合函数（Aggregate Functions）

1. count([distinct] col)

2. sum(col) / avg(col) / max(col) / min(col)

3. collect_set(col)

二、字符串函数（String Functions）

1. concat(str1, str2, ...)

2. concat_ws(separator, str1, str2, ...)

3. substr(str, pos[, len]) / substring(str, pos[, len])

4. split(str, regex)

5. lower(str) / upper(str)

三、日期函数（Date Functions）

1. to_date(timestamp)

2. date_add(date, days) / date_sub(date, days)

3. datediff(end_date, start_date)

4. unix_timestamp([date[, pattern]]) / from_unixtime(unixtime[, pattern])

四、条件函数（Conditional Functions）

1. case when ... then ... else ... end

2. if(condition, true_result, false_result)

3. nvl(col, default_value)

五、窗口函数（Window Functions）

1. row_number() over(partition by col order by sort_col)

2. rank() over(partition by col order by sort_col) / dense_rank()

3. sum(col) over(partition by col order by sort_col rows between ... and ...)

六、数组/Map函数（Array/Map Functions）

1. explode(array_col)

2. size(array_col) / size(map_col)

3. array_contains(array_col, value)

总结

网站公告

今日签到

热门文章

最新发布

1. `count([distinct] col)`

2. `sum(col)` / `avg(col)` / `max(col)` / `min(col)`

3. `collect_set(col)`

1. `concat(str1, str2, ...)`

2. `concat_ws(separator, str1, str2, ...)`

3. `substr(str, pos[, len])` / `substring(str, pos[, len])`

4. `split(str, regex)`

5. `lower(str)` / `upper(str)`

1. `to_date(timestamp)`

2. `date_add(date, days)` / `date_sub(date, days)`

3. `datediff(end_date, start_date)`

4. `unix_timestamp([date[, pattern]])` / `from_unixtime(unixtime[, pattern])`

1. `case when ... then ... else ... end`

2. `if(condition, true_result, false_result)`

3. `nvl(col, default_value)`

1. `row_number() over(partition by col order by sort_col)`

2. `rank() over(partition by col order by sort_col)` / `dense_rank()`

3. `sum(col) over(partition by col order by sort_col rows between ... and ...)`

1. `explode(array_col)`

2. `size(array_col)` / `size(map_col)`

3. `array_contains(array_col, value)`