hive函数

sql 练习

1. count(*)、count(1)、count('字段名')的区别
   从执行结果来看

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL是最慢的
• count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL是最快的
• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或 者0，而是表示null）的计数，即某个字段值为NULL时不统计，仅次于count(1)
  从执行效率来看
  如果列为主键，count(列名)效率大于count(1),不为主键反之，如果表中存在主键count(主键列名效率最优)，如果表有多列，且并不存在主键，则count(1)效率最优。

2. hive语句的执行顺序

from >join on或lateral view explode >where>group by>聚合函数 如sum() avg() count(1)等>having>select若包含over（）开窗函数，此时select中的内容作为窗口函数的输入，窗口中所选的数据范围也是在group by，having之后，并不是针对where后的数据进行开窗，这点要注意>distinct>order by>limit(在做大数据开发时要养成加limit的习惯)

3. where条件里不支持不等式子查询，实际上是支持in、not in、exists、not exists(hive3.x版本是支持的)

4. hive中大小写不敏感

5. 在hive中，数据如果有null字符串，加载到表中的时候会变成null(不是字符串)

hive函数

关系运算

// 等值比较 = == <=>
// 不等值比较 !=<>
//区间比较：select * from default.students where id between 1001 and 1010;
//空值/非空值判断：isnull、isnotnull、nvl()、isnull()
// like、rlike、regexp用法

数值计算

• 取整函数(四舍五入)：round
• 向上取整:ceil
• 向下取整:floor

条件函数

• if :if（表达式，成立返回值，不成立返回值）(重点)
• 条件表达式?表达式1:表达式2;
• case when

日期函数

select from_unixtime(1717666208,'YYYY年MM月dd日 hh时mm分ss秒');
select from_unixtime(unix_timestamp(),'YYYY/MM/dd HH:mm:ss');
// '2021年01月14日' -> '2021-01-14' select from_unixtime(unix_timestamp('2024年06月06日','yyyy年MM月dd日'),'yyyy-MM-dd');

字符串函数

• concat() //如果里面包含null值，则返回null
• concat_ws() // 可以指定分隔符，并且会自动忽略null
• select substring() //HQL中涉及到位置的时候 是从1开始计数的
• select split() // 数组的下标依旧是从0开始

窗口函数

普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每行都返回一个值。简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。开窗函数一般就是说的是over()函数，其窗口是由一个 OVER 子句 定义的多行记录。开窗函数一般分为两类,聚合开窗函数和排序开窗函数。

注意点

• over()函数中的分区、排序、指定窗口范围可组合使用也可以不指定，根据不同的业务需求结合使用
• over()函数中如果不指定分区，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区的数据

1. 聚合开窗函数

sum(求和)
min(最小)
max(最大)
avg(平均值)
count(计数)
lag(获取当前行的上一行的数据)

rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量。

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n,default_val)：往前第n行数据，col是列名，n是往上的行数，当第n行为null的时候取default_val
LEAD(col,n, default_val)：往后第n行数据，col是列名，n是往下的行数，当第n行为null的时候取default_val
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。

2. 排序开窗函数（重点）

• RANK() 排序相同时会重复，总数不会变
• DENSE_RANK() 排序相同时会重复，总数会减少
• ROW_NUMBER() 会根据顺序计算
• PERCENT_RANK()计算给定行的百分比排名。可以用来计算超过了百分之多少的人(当前行的rank值-1)/(分组内的总行数-1)

hive行转列

lateral view explode

hive列转行

• collect_list(列名)
• collect_set(列名)

自定义函数UserDefineFunction

编写代码继承org.apache.hadoop.hive.ql.exec.UDF(UDTF),实现evaluate方法，在evaluate方法中实现自己的逻辑
打成jar包上传Linux虚拟机中
在hive shell中使用add jar 路径将jar包作为资源加添到hive环境中
使用jar包资源注册一个临时函数，fxxx1是你的函数名，'MyUDF'是主类名
create temporary function fxxx1 as 'MyUDF';