Hive - 快速入门教程5（高级函数1：分组排序取TopN）

2024-09-18 发布：hangge 阅读：18

一、高级函数使用1：分组排序取 TopN

1，基本介绍

（1）Hive 是一个主要用来做数据分析的工具。为了满足各种各样的统计需求，它内置包含很多函数，我们可以通过如下语句来查看 Hive 中支持的内置函数。

show functions;

（2）一共 290 个，下面只是其中的一部分：

提示：MySQL 中支持的函数 Hive 大部分都支持，在 Hive 中可以直接使用，并且 Hive 提供的函数比 MySQL 的还要多。

2，常用函数介绍

（1）字符串函数：

CONCAT(str1, str2, ...)：连接多个字符串。
SUBSTRING(str, start, len)：截取字符串的子串。
LOWER(str)：将字符串转换为小写。
UPPER(str)：将字符串转换为大写。
LENGTH(str)：返回字符串的长度。
TRIM(str)：去除字符串两端的空格。
REPLACE(str, old, new)：替换字符串中的子串。
REGEXP_EXTRACT(str, regex)：使用正则表达式从字符串中提取匹配的部分。
INSTR(str, substr)：返回子串在字符串中第一次出现的位置。

（2）数学函数：

ROUND(number, decimals)：将数字四舍五入到指定的小数位数。
ABS(number)：返回数字的绝对值。
CEIL(number)：向上取整。
FLOOR(number)：向下取整。
POWER(base, exponent)：返回一个数的指定次幂。
SQRT(number)：返回一个数的平方根。
RAND()：返回一个 0 到 1 之间的随机数。
LOG(number)：返回一个数的自然对数。

（3）日期和时间函数：

CURRENT_DATE：返回当前日期。
CURRENT_TIMESTAMP：返回当前时间戳。
TO_DATE(dateString)：将日期字符串转换为日期类型。
YEAR(date)：返回日期的年份。
MONTH(date)：返回日期的月份。
DATEDIFF(endDate, startDate)：返回两个日期之间的天数差。
FROM_UNIXTIME(epoch)：将 Unix 时间戳转换为日期时间字符串。
DATE_ADD(date, days)：将指定天数添加到日期。

（4）集合函数：

COUNT(expr)：返回非空行的数量。
SUM(expr)：返回表达式的总和。
AVG(expr)：返回表达式的平均值。
MIN(expr)：返回表达式的最小值。
MAX(expr)：返回表达式的最大值。
COLLECT_LIST(expr)：将表达式的值收集到一个列表中。

（5）窗口函数：

ROW_NUMBER() OVER (ORDER BY column)：为结果集中的行分配唯一的行号。
RANK() OVER (PARTITION BY partition_column ORDER BY column)：为结果集中的行分配排名，相同值得到相同排名。
DENSE_RANK() OVER (PARTITION BY partition_column ORDER BY column)：为结果集中的行分配密集排名，相同值得到相同排名，但排名不间断。

（6）条件函数：

CASE：条件表达式，类似于编程语言中的 switch-case。
COALESCE(val1, val2, ...)：返回参数列表中的第一个非空值。
IF(expr, true_value, false_value)：根据条件返回不同的值。

（6）类型转换函数：

CAST(expr AS type)：将表达式转换为指定类型。

（7）其他函数：

NVL(expr, default)：如果表达式为 null，则返回默认值。
NULLIF(expr1, expr2)：如果两个表达式相等，则返回 null。

3，分组排序取 TopN 样例

（1）在工作中有一个典型的应用场景：分组排序取 TopN。要实现这个需求，需要借助于 Hive 中的 ROW_NUMBER() 和 OVER() 函数。

ROW_NUMBER()：对数据编号，编号从 1 开始。
OVER()：把数据划分到一个窗口内，然后对窗口内的数据进行分区和排序操作。

（2）假设需求是这样的：有一份学生的考试成绩信息，包括语文、数学和英语这 3 个科目，需要计算出班级中单科排名前 3 名学生的信息。测试数据如下：

（3）建表语句如下：

create external table score(
  id int,
  name string,
  sub string,
  score int
)
row format delimited
fields terminated by '\t'
location '/data/score';

（3）向表中加载数据：

hdfs dfs -put /usr/local/score.txt /data/score

（4）获取单科排名前 3 名学生的信息：

使用 ROW_NUMBER() 时，当上下两条记录的 score 相等时，这两条记录的行号会按照默认排序进行递增编号。此时会出现两个 score 相等的学生的排名是不一样的。;

select * from(
  select *,
  row_number() over (partition by sub order by score desc) as num 
  from score
)s where s.num <= 3;

（5）上面语句中的 ROW_NUMBER() 函数可以根据需求转换为 RANK() 函数或者 DENSE_RANK() 函数，它们区别如下：

使用 RANK() 时，当上下两条记录的 score 相等时，这两条记录的行号是一样的，但是下一个 score 的行号递增 N（N 是重复的次数）。此时会出现两个并列第 1 名，没有第 2 名，下一个是第 3 名。

select * from(
  select *,
  rank() over (partition by sub order by score desc) as num 
  from score
)s where s.num <= 3;

使用 DENSE_RANK() 时，当上下两条记录的 score 相等时，这两条记录的行号是一样的，但是下一个 score 的行号递增 1。此时会出现两个并列第 1 名，下一个是第 2 名。

select * from(
  select *,
  dense_rank() over (partition by sub order by score desc) as num 
  from score
)s where s.num <= 3;