[hive] 窗口函数详解_窗口函数的执行时机-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_42385705/article/details/105870897

本文详细介绍了Hive窗口函数的使用，包括partition by、order by、window子句，以及RANK、DENSE_RANK、ROW_NUMBER、NTILE、LAG、LEAD、first_value和last_value等函数的应用。通过实例展示了窗口函数如何实现分区排序、动态Group By、Top N、累计计算和层次查询等功能。

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

前言：面试官：窗口函数使用过吗？
：了解过啊。窗口函数应用场景：（1）用于分区排序（2）动态Group By（3）Top N（4）累计计算（5）层次查询
比如
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
OVER()：指定分析函数工作的数据窗口大小
面试官：小伙子理论还行啊，来用我这电脑建张表实际操作一下可以吗？

GG
在这里插入图片描述

在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by字句之前。

我们准备一张order表,字段分别为name,orderdate,cost.数据内容如下:

jack,2015-01-01,10
tony,2015-01-02,15
jack,2015-02-03,23
tony,2015-01-04,29
jack,2015-01-05,46
jack,2015-04-06,42
tony,2015-01-07,50
jack,2015-01-08,55
mart,2015-04-08,62
mart,2015-04-09,68
neil,2015-05-10,12
mart,2015-04-11,75
neil,2015-06-12,80
mart,2015-04-13,94

建表

 create external table order
    > (name string,
    > orderdate string,
    > cost string)
    > row format delimited fields terminated by ',';
OK

load数据

load data local inpath '/opt/order.csv' into table order;

检查一下

 select * from order;
OK
jack	2015-01-01	10
tony	2015-01-02	15
jack	2015-02-03	23
tony	2015-01-04	29
jack	2015-01-05	46
jack	2015-04-06	42
tony	2015-01-07	50
jack	2015-01-08	55
mart	2015-04-08	62
mart	2015-04-09	68
neil	2015-05-10	12
mart	2015-04-11	75
neil	2015-06-12	80
mart	2015-04-13	94

聚合函数+over

假如说我们想要查询在2015年4月份购买过的顾客及总人数,我们便可以使用窗口函数去去实现

 select name,count(*) over() from order where substring(orderdate,1,7) = '2015-04';
OK
mart	5
mart	5
mart	5
mart	5
jack	5

可见其实在2015年4月一共有5次购买记录,mart购买了4次,jack购买了1次.事实上,大多数情况下,我们是只看去重后的结果的.针对于这种情况,我们有两种实现方式

distinct
select distinct name,count(*) over() from order where substring(orderdate,1,7) = '2015-04';
OK
mart	2
jack	2

group by
select name,count(*) over () from order where substring(orderdate,1,7) = '2015-04' group by name;
OK
mart	2
jack	2

partition by子句

我们想要去看顾客的购买明细及月购买总额,可以执行如下的sql

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from order;
OK
jack	2015-01-01	10	205.0
jack	2015-01-08	55	205.0
tony	2015-01-07	50	205.0
jack	2015-01-05	46	205.0
tony	2015-01-04	29	205.0
tony	2015-01-02	15	205.0
jack	2015-02-03	23	23.0
mart	2015-04-13	94	341.0
jack	2015-04-06	42	341.0
mart	2015-04-11	75	341.0
mart	2015-04-09	68	341.0
mart	2015-04-08	62	341.0
neil	2015-05-10	12