返回 导航

大数据

hangge.com

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

作者：hangge | 2024-09-18 08:35

一、高级函数使用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;
评论

全部评论（0）

回到顶部