HIVE窗口函数

最新推荐文章于 2021-02-26 12:23:41 发布

原创最新推荐文章于 2021-02-26 12:23:41 发布 · 173 阅读

0 ·

CC 4.0 BY-SA版权

HIVE 同时被 2 个专栏收录

26 篇文章

订阅专栏

数据库

22 篇文章

订阅专栏

这篇博客介绍了Hive中的窗口函数使用，包括sum函数、row_number()和rank()。通过示例展示了如何在分区(name)内进行聚合计算、排序和分配排名，帮助理解窗口函数在数据处理中的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

我自己建的一个临时表：

select 'A' as name ,1 as num,2 as dd
union all
select 'A' as name ,2 as num,3 as dd
union all
select 'A' as name ,3 as num,7 as dd
union all
select 'B' as name ,4 as num,1 as dd
union all
select 'B' as name ,6 as num,4 as dd
union all
select 'C' as name ,2 as num,3 as dd
union all
select 'C' as name ,5 as num,5 as dd
union all
select 'C' as name ,8 as num,6 as dd

第一：

sum函数

select *, sum(num) over (partition by name) from
(select 'A' as name ,1 as num,2 as dd
union all
select 'A' as name ,2 as num,3 as dd
union all
select 'A' as name ,3 as num,7 as dd
union all
select 'B' as name ,4 as num,1 as dd
union all
select 'B' as name ,6 as num,4 as dd
union all
select 'C' as name ,2 as num,3 as dd
union all
select 'C' as name ,5 as num,5 as dd
union all
select 'C' as name ,8 as num,6 as dd)a

结果：

窗口函数最大的特点就是分组后行数不变，直接在每行后加了一个字段。

第二：row_number() 默认为降序

select *, row_number() over (partition by name order by num) from
(select 'A' as name ,1 as num,2 as dd
union all
select 'A' as name ,2 as num,3 as dd
union all
select 'A' as name ,3 as num,7 as dd
union all
select 'B' as name ,4 as num,1 as dd
union all
select 'B' as name ,6 as num,4 as dd
union all
select 'C' as name ,2 as num,3 as dd
union all
select 'C' as name ,5 as num,5 as dd
union all
select 'C' as name ,8 as num,6 as dd)a

有相同数据时排序

select *, row_number() over (partition by name order by dd) from
(select 'A' as name ,1 as num,2 as dd
union all
select 'A' as name ,2 as num,3 as dd
union all
select 'A' as name ,3 as num,3 as dd
union all
select 'B' as name ,4 as num,1 as dd
union all
select 'B' as name ,6 as num,4 as dd
union all
select 'C' as name ,2 as num,3 as dd
union all
select 'C' as name ,5 as num,6 as dd
union all
select 'C' as name ,8 as num,6 as dd
union all
select 'C' as name ,8 as num,8 as dd
)a

第三、rank()

select *, rank() over (partition by name order by dd) from
(select 'A' as name ,1 as num,2 as dd
union all
select 'A' as name ,2 as num,3 as dd
union all
select 'A' as name ,3 as num,3 as dd
union all
select 'B' as name ,4 as num,1 as dd
union all
select 'B' as name ,6 as num,4 as dd
union all
select 'C' as name ,2 as num,3 as dd
union all
select 'C' as name ,5 as num,6 as dd
union all
select 'C' as name ,8 as num,6 as dd
union all
select 'C' as name ,8 as num,8 as dd
)a