hive 函数之二窗口函数与分析函数

最新推荐文章于 2023-09-21 11:08:08 发布

chenshitong96

最新推荐文章于 2023-09-21 11:08:08 发布

阅读量495

点赞数

分类专栏： hive 文章标签： hive

本文链接：https://blog.csdn.net/qq_36998916/article/details/115548951

版权

hive 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

hive 函数之二窗口函数与分析函数

①排序函数

1.  order by   -- 全局排序
    所有map端数据，只在一个reduce端进行，全局排序
    
2.  sort  by   -- 对每一个reduce中进行局部排序
    因此在使用sort by之前，需要先设置Reduce的数量>1（set mapreduce.job.reduces=n;)才会做局部排序
    
    distribute by 是控制 map 的输出在reduer 中是如何划分的。 
    distribute by 会根据 KEY 的hash码 对 reduce 的个数 取模， 这样相同的KEY 会进入到同一个 reduce中
    
    因此  distribute by 和 sort  by  联合使用

②行列转换

1. 列转行

collect_list – 转为数组不去重

collect_set – 转为数组并去重

是hive提供的现成的UDAF函数

-- 列转行 collect_list 先将转化为不去重数组 concat_ws 再将数据遍历为指定字符遍历的字符串
select name, concat_ws(',',collect_list( concat(subject,":",result) ))  
from tmp_stu
group by name
-- 行转列 collect_list 先将转化为去重数组 concat_ws 再将数据遍历为指定字符遍历的字符串
select name, concat_ws(',',collect_set( concat(subject,":",result) ))      
from tmp_stu
group by name

2. 列转行

explode()

接受array（或map）作为输入，并将array（map）的元素作为单独的行输出。

UDTFs可以在 SELECT expression list 中使用，也可以作为 LATERAL VIEW 的一部分。 – 官方解释

注：当SELECT expression list 使用UDTF函数的时候,hive只允许对拆分字段进行访问，不能与原字段组合。

2-1 LATERAL VIEW

Lateral View 用于和UDTF函数【如：explode,split】结合来使用。

lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一或者多行。lateral view再把结果组合，产生一个支持别名表的虚拟表。

主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF，不能包含其它字段以及多个UDTF的情况。

LATERAL VIEW 主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF，不能与原字段组合。

因此 lateral view与explode等udtf就是天生好搭档，explode将复杂结构一行拆成多行，然后再用lateral view做各种聚合。

-- 1. explode() 在 SELECT expression list  中使用, 其结果不能与tmp_stu2原始字段组合
select explode(split(cj,',')) AS myNewCol 
from tmp_stu2

-- 2. explode() 与 LATERAL VIEW 联合适用   结果与tmp_stu2原始字段 name 组合
select name, cj_1
from tmp_stu2
lateral view explode(split(cj,',')) temp_view as cj_1

2-2. posexplode() 函数

posexplode() 与 explode() 的区别是： 行转列以后，还多一列数组元素对应下标的列

可以引申出一些其他的应用：

-- posexplode
select name, cj_1, cj_2
from tmp_stu2
lateral view posexplode(split(cj,',')) temp_view as cj_1, cj_2

-- posexplode 利用数组下标 产出对应 日期的多行
SELECT a1.id,
       DATE_ADD(a1.start_date,a2.pos) AS data_date
FROM 
(   SELECT '1' as id ,
           '2021-01-01' AS start_date,
           '2021-01-31' AS end_date
) a1
LATERAL VIEW POSEXPLODE(SPLIT(SPACE(DATEDIFF(LEAST(a1.end_date,date_add(current_date(), -1)),a1.start_date)),'')) a2 AS pos,val
WHERE DATE_ADD(a1.start_date,a2.pos)<=date_add(current_date(), -1)
-- date_add(current_date(), -1)  昨日的日期
-- LEAST(n1, n2, n3)             获取中的最小值
-- DATEDIFF(d1, d2）             日期天数差值
-- SPACE(n1)                     产生对应个数个空间
-- SPLIT()                       产生数组 去列转行

③窗口函数

over()

SQL标准允许将所有聚合函数用做开窗函数，只需要在聚合函数后加over()即可。

– partition by对表进行分区然后聚合计算每个分区的数据

– order by子句可以对结果集按照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。

over() 函数可以控制计算时的窗口大小。例如，over() 内部无参数时，默认将当前查询的所有结果作为输入。

over(partition by name) 每一行根据 name来区分窗口

over( order by id range between 1 preceding and 1 following ) range代表范围, preceding 向前, following向后

– 窗口范围当前行前后一行

over( order by id range between CURRENT ROW AND 1 following ) CURRENT ROW 代表当前行

– 窗口范围当前行和后面一行

故：索引 over() 是伴随着聚合函数进行聚合操作的。

使用窗口聚合函数

1-1. first_value() 按分组排序后，取范围内第一个值。

-- 根据name分组，result排序 取第一个值 赋值给组内每一行
select name, subject, result,
       first_value(subject) over(partition by name order by cast(result as int) asc) first_value_asc,   
       first_value(subject) over(partition by name order by cast(result as int) desc) first_value_desc
from bpit_back_tmpdb.tmp_stu

1-2. last_value() last_value,按分组排序后，取范围内，最后一个值, 因为使用了默认定义框架：第一行到当前行的最后一个值。

-- 根据name分组，result排序 取第一个值 赋值给组内每一行
select name, subject, result,
       first_value(subject) over(partition by name order by cast(result as int) asc) first_value_asc, 
       last_value(subject) over(partition by name order by cast(result as int) asc) last_value_desc
       -- last_city的结果不是我们想要的。是因为使用了默认定义框架：第一行到当前行的最后一个值。
from bpit_back_tmpdb.tmp_stu

1-3. lag() 和 lead()

lag(移动的字段, 移动的行数, 默认值) 向下移动
lead(移动的字段, 移动的行数, 默认值) 向上移动

-- 分组 字段值移动    可以用来比较
select name, subject, result,
       lag(result,1,'默认值') over(partition by name order by cast(result as int) asc) lag_1_value,
       lead(result,1,'默认值') over(partition by name order by cast(result as int) asc) lead_1_value
from bpit_back_tmpdb.tmp_stu

1-4. ntile(n)

将每组平均分为n部分。

如果可以平均分配行数则平均分配到指定的数量（n）个桶中

如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。

(1). -- 分组内 行数平均分配为2份
select name, subject, result,
       ntile(2) over(partition by name order by cast(result as int) asc) ntile_value
from bpit_back_tmpdb.tmp_stu

(2). -- 课程总成绩平均分配成2份  与sum配合
select subject,
       sum(cast(result as int)) as result,
       ntile(2) over(order by sum(cast(result as int)) asc) ntile_value   --over窗口为整体数据
from bpit_back_tmpdb.tmp_stu
group by subject

1-5. rank() , dense_rank()与 row_number()

排序方式不同:

rank() 1,2,3,3,5
dense_rank() 1,2,3,3,4
row_number() 1,2,3,4,5

1-6. CUME_DIST() 和 PERCENT_RANK()

CUME_DIST 小于等于当前值的行数/分组内总行数

PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1

注： rank值的排序方式是 1，2，3，3，5

-- CUME_DIST    全局排序   小于等于当前值的行数/分组内总行数 
select name, subject, result,
       CUME_DIST() over(order by cast(result as int) asc)  as cume_1
from bpit_back_tmpdb.tmp_stu
where name='小丽'

-- CUME_DIST    分组排序    小于等于当前值的行数/分组内总行数 
select name, subject, result,
       CUME_DIST() over(partition by name order by cast(result as int) asc)  as cume_2
from bpit_back_tmpdb.tmp_stu

…

-- PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1
select name, subject, result,
       PERCENT_RANK() over(order by cast(result as int) asc)  as cume_1
from bpit_back_tmpdb.tmp_stu
where name='小丽'

④OLAP 多维度聚合函数

1-1. GROUPING SETS

grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。

等价于将不同维度的GROUP BY结果集进行UNION ALL。

GROUPING__ID，表示结果属于哪一个分组集合。

-- 分别对 name，subject，name+subject 进行聚合
select name, 
       subject, 
       sum(cast(result as int)) as sum_result,
       GROUPING__ID
from bpit_back_tmpdb.tmp_stu
group by name, subject
GROUPING SETS (name,subject,(name, subject)) 
-- 等价于
select name, subject, sum(result) as sum_result, 3 as GROUPING__ID from tmp_stu group by name, subject  
union all
select name, null as subject, sum(result) as sum_result, 1 as GROUPING__ID from tmp_stu group by name 
union all
select null as name, subject, sum(result) as sum_result, 2 as GROUPING__ID from tmp_stu group by subject

1-2. CUBE

根据GROUP BY的维度的所有组合进行聚合。包括对所有数据group

-- 对所有纬度聚合 
select name, 
       subject, 
       sum(cast(result as int)) as sum_result,
       GROUPING__ID
from bpit_back_tmpdb.tmp_stu
group by name, subject
with CUBE
-- 等价于
select null as name, null as subject, sum(result) as sum_result, 0 as GROUPING__ID from tmp_stu
union all
select name, subject, sum(result) as sum_result, 3 as GROUPING__ID from tmp_stu group by name, subject  
union all
select name, null as subject, sum(result) as sum_result, 1 as GROUPING__ID from tmp_stu group by name 
union all
select null as name, subject, sum(result) as sum_result, 2 as GROUPING__ID from tmp_stu group by subject

1-3. ROLLUP

是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。

-- 比如：
select name, 
       subject, 
       sum(cast(result as int)) as sum_result,
       GROUPING__ID
from bpit_back_tmpdb.tmp_stu
group by name, subject
with ROLLUP

结果包含上面cube中的  0， 1， 3 （带有最左侧 name的group  和 对所有数据group）

chenshitong96

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
hive 函数之二窗口函数与分析函数

hive 函数之二窗口函数与分析函数①排序函数1. order by -- 全局排序所有map端数据，只在一个reduce端进行，全局排序 2. sort by -- 对每一个reduce中进行局部排序因此在使用sort by之前，需要先设置Reduce的数量>1（set mapreduce.job.reduces=n;)才会做局部排序 distribute by 是控制 map 的输出在reduer 中是如何划分的。
复制链接

扫一扫