写在前面
今天继续学习hive部分的知识。
以下是您提供的内容转成的 Markdown 格式：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;
- 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;
- 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
- select get_json_object('{"name":"zhangsan","age":18,"score":[{"course_name":"math","score":100},{"course_name":"english","score":60}]}',"$.score[0].score"); -- 100
常用的三种排名类窗口函数及区别
- 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')
- 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这几个窗口函数上。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
