写在前面

今天继续学习hive部分的知识。

Hive 相关知识

hive中不同的 count 区别

select clazz
      ,count(distinct id) as cnt
      ,count(*) as cnt
      ,count(1) as cnt_1
      ,count(id) as cnt_id
from students
group by clazz;

Hive 关联的分类及区别

• join 内连接和自连接
• left join 左连接
• right join 右连接
• full outer join 全外连接
• cross join 交叉连接（笛卡尔积）

Hive 中 where 和 having 的区别

• where 是分组前的筛选，having 是针对分组后的进一步筛选且必须要有分组才有 having。
• where 里不能包含分组的字段和聚合函数，having 里可以使用分组字段。

Hive 中的几种排序及对比

• order by: 全局排序

  

• sort by: 局部排序

  

• distribute by: 分区

  

• cluster by: 分区排序

  

Hive 怎么实现多行转一行

• 使用 group by + collect_set/collect_list：

  group by 用于分组，分组后可以使用 collect_set/collect_list 对每组数据进行聚合，最终会得到 array 类型的数据，可以使用 concat_ws 转成字符串。

  • collect_set 会进行去重处理，collect_list 不会。

select clazz
      ,collect_set(age) as age_set
      ,collect_list(age) as age_list
from students
group by clazz;

Hive 中有哪些常用的字符串函数

• concat('123','456'): 结果是 123456
• concat('123','456',null): 结果是 NULL

select concat_ws('#','a','b','c');  -- a#b#c
select concat_ws('#','a','b','c',NULL);  -- a#b#c，可以指定分隔符，并且会自动忽略NULL
select concat_ws("|",cast(id as string),name,cast(age as string),gender,clazz) from students limit 10;

select substring("abcdefg",1);  -- abcdefg，HQL中涉及到位置的时候是从1开始计数

-- '2021/01/14' -> '2021-01-14'
select concat_ws("-",substring('2021/01/14',1,4),substring('2021/01/14',6,2),substring('2021/01/14',9,2));
-- 建议使用日期函数去做日期
select from_unixtime(unix_timestamp('2021/01/14','yyyy/MM/dd'),'yyyy-MM-dd');

select split("abcde,fgh",",");  -- ["abcde","fgh"]
select split("a,b,c,d,e,f",",")[2];  -- c

select explode(split("abcde,fgh",","));  -- abcde  
                                        -- fgh

• 解析 JSON 格式的数据：

select get_json_object('{"name":"zhangsan","age":18,"score":[{"course_name":"math","score":100},{"course_name":"english","score":60}]}',"$.score[0].score");  -- 100

20240920

常用的三种排名类窗口函数及区别

• rank(): 1224
• row_number(): 1234
• dense_rank(): 1223

窗口函数怎么使用

在窗口函数后面接上 over(partition by 分组字段 order by 排序字段 desc) 作为别名 rn。

Hive 中有哪些常用的窗口函数

• 排名类：

  • rank(): 1224
  • row_number(): 1234
  • dense_rank(): 1223
  • percent_rank: (rank的结果 - 1) / (分区内数据的个数 - 1)
  • cume_dist: 计算某个窗口或分区中某个值的累积分布。
    • 假定升序排序，使用以下公式确定累积分布：
      • 小于等于当前值 x 的行数 / 窗口或 partition 分区内的总行数。
      • 其中，x 等于 order by 子句中指定的列的当前行中的值。
  • ntile(n): 对分区内数据再分成 n 组，然后打上组号。

• 取值类：

  • LAG(col, n): 往前第 n 行数据
  • LEAD(col, n): 往后第 n 行数据
  • FIRST_VALUE: 取分组内排序后，第一个值
  • LAST_VALUE: 取分组内排序后，截止到当前行，最后一个值，对于并列的排名，取最后一个。

• 聚合类：

  • max, min, avg, count, sum

Hive 中有哪些 NULL 值的判断方式

• IF or CASE 函数：根据条件返回不同的值。

IF(column_name IS NULL, 'default_value', column_name)
CASE 
    WHEN column_name IS NULL THEN 'default_value'           
    ELSE column_name        
END AS result

• COALESCE 函数：返回第一个非 NULL 值。

COALESCE(column_name, 'default_value')

• NVL 函数：将 NULL 值替换为指定的值（Hive 2.1.0 及以上版本）。

NVL(column_name, 'default_value')

Hive 中时间字符串和时间戳之间如何转换？

select from_unixtime(1610611142, 'YYYY/MM/dd HH:mm:ss');
select from_unixtime(unix_timestamp(), 'YYYY/MM/dd HH:mm:ss');

• from_unixtime 将时间戳转换为日期格式字符串。
• unix_timestamp 将不是特定格式的时间转为时间戳。

select from_unixtime(unix_timestamp('2021年01月14日','yyyy年MM月dd日'),'yyyy-MM-dd');

连续登录问题

先求出每个用户每天的消费总额，再按时间去开窗排名，将日期去减这个排名数可以达到分组的效果，再分组求和求次数，得到一段连续时间的消费总额和连续登录消费次数，用 min 和 max 求每个连续时间段的开始时间和结束时间，用每个开始时间去减上一个的结束时间得到每个连续时间段中的间隔时间，或者直接用分组之间的日期差，因为只有连续时间段的才会在一个组里，组之间的差值结束间隔时间。

order by 对 SUM 窗口函数的影响

如果 sum 窗口函数中的字段被排序了，总和一列就会有一个累加的过程，没指定排序就是一列总和和外部 order by 一样。

窗口帧是什么？

窗口帧用于从分区中选择指定的多条记录，供窗口函数处理。Hive 提供了两种定义窗口帧的形式：ROWS 和 RANGE。两种类型都需要配置上界和下界。

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 表示选择分区起始记录到当前记录的所有行；
• SUM(close) RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING: 则通过字段差值来进行选择。
• 如当前行的 close 字段值是 200，那么这个窗口帧的定义就会选择分区中 close 字段值落在 100 至 400 区间的记录。以下是所有可能的窗口帧定义组合。如果没有定义窗口帧，则默认为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

只能运用在max、min、avg、count、sum、FIRST_VALUE、LAST_VALUE这几个窗口函数上。