Hive 窗口函数

hylpeace

已于 2023-01-28 16:37:02 修改

阅读量1.9k

点赞数 1

分类专栏： Hive 文章标签： hive 大数据

于 2021-07-09 09:34:32 首次发布

本文链接：https://blog.csdn.net/hylpeace/article/details/118407435

版权

Hive 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文通过具体案例介绍了SQL中的窗口函数在不同场景的应用，包括查询特定时间段的顾客人数、累计消费金额、计算用户登录时长等。文章详细解释了窗口函数如SUM、LAG、LEAD和NTILE的具体用法，并提供了丰富的示例。

摘要由CSDN通过智能技术生成

over(分组排序窗口) 中的order by后的语法

1、物理窗口（真实往上下移动多少行rows between）

2、逻辑窗口(满足条件上下多少行):(金融行业、保险行业、p2p等)

四、查询顾客上次购买时间，以及下次购买时间(电商网站常用于求页面跳转的前后时间)

五、查询前20%购买的订单信息

lead和lag详解

--窗口聚合函数
sum(col) over() :  分组对col累计求和
count(col) over() : 分组对col累计
min(col) over() : 分组对col求最小
max(col) over() : 分组求col的最大值
avg(col) over() : 分组求col列的平均值

--窗口分析函数
first_value(col) over() : 某分区排序后的第一个col值
last_value(col) over() : 某分区排序后的最后一个col值
lag(col,n,DEFAULT) : 统计往前n行的col值，n可选，默认为1，DEFAULT当往上第n行为NULL时候，取DEFAULT，如不指定，则为NULL
lead(col,n,DEFAULT) : 统计往后n行的col值，n可选，默认为1，DEFAULT当往下第n行为NULL时候，取DEFAULT，如不指定，则为NULL
ntile(n) : 用于将分组数据按照顺序切分成n片，返回当前切片值。注意：n必须为int类型。

--窗口排名函数：
row_number() over() : 排名函数，不会重复，适合于生成主键或者不并列排名
rank() over() :  排名函数，有并列名次，后续名次不连续。如:1,1,3
dense_rank() over() : 排名函数，有并列名次，后续名次连续。如：1，1，2

窗口聚合函数

sum+窗口函数总共有四种用法（其他类似），注意整体聚合还是累积聚合

sum(col) over() : 对表所有行求和

sum(col) over(order by ··· ) :连续累积求和

sum(col) over(partition by ··· ) :同组内所有行求和

sum(col) over(partition by ··· order by ··· ) :在每个分组内，连续累积求和

窗口表达式

在sum(...) over( partition by... order by ... )语法完整的情况下，进行的累积聚合操作，默认累积聚合行为是：聚合每个分组下的所有行。

Window expression窗口表达式给我们提供了一种控制行范围的能力，比如向前2行，向后3行。

1、物理窗口（真实往上下移动多少行rows between）

UNBOUNDED PRECEDING |                          --窗口起始行
[num] PRECEDING |                              --前n行
CURRENT ROW |                                  --当前行
[num] FOLLOWING|                               --后n行
UNBOUNDED FPLLOWING |                          --窗口结束行

--窗口范围为 当前前n行与后m行(窗口大小共m+n+1行)
over(partition by [num] order by rows between m preceding and n fllowing)

如：over(partition by col order by rows between 1 preceding and 1 fllowing)

preview

2、逻辑窗口(满足条件的窗口范围):(金融行业、保险行业、p2p等)

range between [num] PRECEDING AND [num] FOLLOWING  --数值的浮动范围

如: over(partition by col order by range between 5 preceding and 5 fllowing)

preview

注意:窗口函数一般不和group by搭配使用。

窗口分析函数

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）；
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）；
FIRST_VALUE 取分组内排序后，截止到当前行，第一个值；
LAST_VALUE 取分组内排序后，截止到当前行，最后一个值

窗口排序函数

窗口排序函数用于给每个分组内的数据打上排序的标号。注意窗口排序函数不支持窗口表达式。总共有4个函数需要掌握：
row_number：在每个分组中，为每行分配一个从1开始的唯一序列号，递增，不考虑重复；
rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，挤占后续位置；
dense_rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，不挤占后续位置；

应用: 某天某产品的累计销售额。
物理窗口：
求多维度累计(累计退款金额、累计交易额、累计订单数量)
逻辑窗口：
范围累计环比情况、某值上下加减。

有以下数据：字段名为：name、orderdate、cost

Jack,2017-01-01,10

Tony,2017-01-02,15

Jack,2017-02-03,23

Tony,2017-01-04,29

Jack,2017-01-05,46

Jack,2017-04-06,42

Tony,2017-01-07,50

Jack,2017-01-08,55

Mark,2017-04-08,62

Mart,2017-04-09,68

Meil,2017-05-10,12

Mart,2017-04-11,75

Meil,2017-06-12,80

Mart,2017-04-13,94

需求

1、查询2017-04购买的顾客总人数

2、顾客购买明细及月份总额

3、上述场景，将cost按日期累加

4、查询顾客上次购买时间

5、查询前20%购买的订单信息

一、建表并导入数据：

-- 建表
create table business(
    name string,
    orderdate string,
    cost int)
row format delimited
fields terminated by ",";

-- 查询表：
select * from business;

--导入数据
load data local inpath "/business.txt" into table business;

需求分析应用

一、查询2017-04购买的顾客总人数

a、首先想到使用聚合函数count()

-- 先求出2017-04这月一共有多少条记录
SELECT COUNT(*) FROM BUSINESS WHERE SUBSTR(ORDERDATE, 1, 7) = "2017-04";
-- 或则
SELECT COUNT(*)
  FROM BUSINESS
 WHERE DATE_FORMAT(ORDERDATE, 'yyyy-MM') = '2017-04'

结果如下图：

b、现在按照顾客进行分组

SELECT NAME, COUNT(*)
  FROM BUSINESS
 WHERE DATE_FORMAT(ORDERDATE, 'yyyy-MM') = "2017-04"
 GROUP BY NAME;

结果如下图：

数据被分成了三组

使用over()函数：over只对聚合函数起作用，count分别对上面三个组内进行计数，over统计一共有多少个组(有一个count进行累加一次)

SELECT NAME, COUNT(*) OVER(PARTITION BY NAME)
  FROM BUSINESS
 WHERE DATE_FORMAT(ORDERDATE, 'yyyy-MM') = '2017-04'

结果如下所示：

二、查询顾客购买明细及月份总额

a、首先选出所有明细信息：

b、求总额：(这是所有数据的总和，因为没有分组(group by)，所以over()的针对的是每一条数据)

c、针对四月份的数据，我们需要进行求总额

思路：分区或者分组，但是使用group by date，只能查询date，(select date ,name group by date)其它字段不能查询

解决：使用窗口函数，并对窗口函数进行分区over(distribute by()) 或者over(partition by())

SELECT *,SUM(COST) OVER(DISTRIBUTE BY DATE_FORMAT(ORDERDATE,'yyyy-MM')) SUM
FROM BUSINESS;

结果如图所示：

三、上述场景，将cost按时间累加

a、先按照购买时间进行排序

SELECT * FROM BUSINESS SORT BY ORDERDATE;

结果如图所示：

b.计算从开始到当前时间的总花费

SELECT *,
       SUM(COST) OVER(SORT BY ORDERDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) SUM_ADD
  FROM BUSINESS;

-- 参数讲解

-- sort by orderdate：按照购买日期进行排序

-- UNBOUNDED PRECEDING：从分区起点开始

-- CURRENT ROW：到当前行 -- 计算从开始到当前时间的总花费

结果如下图所示：

demo1(对以下参数进行测试)

-- 计算相邻三行的值(第一行计算当前行 + 后一行；最后一行计算当前行 + 前一行)

row函数：

current row：当前行

n PRECEDING：往前n行

n FOLLOWING：往后n行

UNBOUNDED：起点

UNBOUNDED PRECEDING：从前面起点

UNBOUNDED FOLLOWING：到后面终点

LAG(col,n)：往前的第n行

LEAD(col,n)：往后的第n行

--参数讲解 -- sort by orderdate：按照时间排序

-- 1 preceding：当前行的前1行

-- 1 following：当前行的后一行

SELECT *,
       SUM(COST) OVER(SORT BY ORDERDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
  FROM BUSINESS;

结果如下如所示：

demo2：-- 计算每个人截至到当天的总花费

-- 参数详解：

-- distribute/partition by name：按名字进行分区

-- sort/order by orderdate：在每个分区中按照时间进行排序

-- UNBOUNDED PRECEDING AND CURRENT ROW：从起点行到当前行

SELECT *,
       SUM(COST) OVER(PARTITION BY NAME ORDER BY ORDERDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM business

结果如下图所示：

demo3：--测试

--参数讲解：

-- sort by orderdate：按照时间排序

-- current row and unbounded following：当前行到终点行

SELECT *,
       SUM(COST) OVER(PARTITION BY NAME ORDER BY ORDERDATE ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM BUSINESS

结果如下图所示：

四、查询顾客上次购买时间，以及下次购买时间(电商网站常用于求页面跳转的前后时间)

分析：lag(clo,n)：返回的是当前行的第前n行

-- 参数详解：

-- distribute by name：按照姓名分组

-- sort by orderdate：按照时间排序

-- lag(orderdate,1)：返回当前orderdate行的前一行

-- lead(orderdate,1)：返回当前orderdate行的后一行

SELECT *,
       LAG(ORDERDATE, 1) OVER(DISTRIBUTE BY NAME SORT BY ORDERDATE),
       LEAD(ORDERDATE, 1) OVER(DISTRIBUTE BY NAME SORT BY ORDERDATE)
  FROM BUSINESS

结果如下图所示：

五、查询前20%购买的订单信息

分析：可以按照时间分成五等份，然后返回其中的第一份

NTILE(n)：将数据等分成n份

SELECT *, NTILE(5) OVER(SORT BY ORDERDATE) FROM BUSINESS;

结果如下图所示：

-- 下面语句报错，因为 ntile、sum、avg等函数不能放在where后面当做查询条件
SELECT *, NTILE(5) OVER(SORT BY ORDERDATE)  SORTED
  FROM BUSINESS
 WHERE SORTED = 1;
-- 下面语句报错，因为having必须跟在group by 语句后面
SELECT *, NTILE(5) OVER(SORT BY ORDERDATE) AS SORTED
  FROM BUSINESS
HAVING SORTED = 1;
-- 所以使用了子查询，将上一步查询的结果放在子句中
SELECT NAME, ORDERDATE, COST
  FROM (SELECT *, NTILE(5) OVER(ORDER BY ORDERDATE) SORTED FROM BUSINESS) T
 WHERE SORTED = 1;
-- Tips：子查询不能使用select *

lead和lag详解

Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率，其中over()表示当前查询的结果集对象，括号里面的语句则表示对这个结果集进行处理。

场景计算一天内的总的登录时长，建表语句如下

--建立一个用户登录流水表
create table user_log
(
uid         string         comment '用户id',
ts            string      comment '时间',
log_type    string        comment '状态枚举值：login（登录） logout（登出）'
) comment '用户登录流水';

1.1 对原始数据，根据uid,to_date(dt)分区，对dt进行排序

select * ,row_number() over (partition by uid,to_date(ts) order by ts asc) rn from dwd_db.user_log

------------------------结果------------------
uid,ts,log_type,rn
aa,2020-04-28 09:30:00,login,1
aa,2020-04-28 10:15:00,logout,2
aa,2020-04-28 13:30:00,login,3
aa,2020-04-28 14:15:00,logout,4
aa,2020-04-29 18:31:00,login,1
aa,2020-04-29 23:15:00,logout,2
bb,2020-04-28 06:30:00,login,1
bb,2020-04-28 09:15:00,logout,2
bb,2020-04-28 12:30:00,login,3
bb,2020-04-29 12:30:00,logout,1
cc,2020-04-28 11:25:00,logout,1
cc,2020-04-28 16:30:00,login,2
cc,2020-04-28 20:10:00,logout,3
dd,2020-04-28 05:25:00,logout,1
dd,2020-04-28 10:30:00,login,2
dd,2020-04-28 11:10:00,logout,3
dd,2020-04-28 21:15:00,login,4

1.2 将登录时间与登出时间放到一行，
--1、如果某一用户当天第一次时间状态为登出，则设置该用户的登录时间为当天的00:00:00
--2、如果某一用户当天最后一次时间状态为登入，则设置该用户的登录时间为当天的23:59:59

select uid,log_type,ts login_time,
       case when log_type ='login' then lead(ts,1,concat(to_date(ts),' 23:59:59')) over (partition by uid,to_date(ts) order by ts asc)
            else concat(to_date(ts),' 00:00:00')    end logout_time,  --else 中将logout对应的登入时间都定为当天 的00:00:00,如果该条记录在排序的第一条直接取即可
       row_number() over (partition by uid,to_date(ts) order by ts asc) rn --根据uid，日期分区对时间排序
from dwd_db.user_log

----------------------查询结果-------------------------------
uid,log_type,login_time,logout_time,rn
aa,login,2020-04-28 09:30:00,2020-04-28 10:15:00,1
aa,logout,2020-04-28 10:15:00,2020-04-28 00:00:00,2
aa,login,2020-04-28 13:30:00,2020-04-28 14:15:00,3
aa,logout,2020-04-28 14:15:00,2020-04-28 00:00:00,4
aa,login,2020-04-29 18:31:00,2020-04-29 23:15:00,5
aa,logout,2020-04-29 23:15:00,2020-04-29 00:00:00,6
bb,login,2020-04-28 06:30:00,2020-04-28 09:15:00,1
bb,logout,2020-04-28 09:15:00,2020-04-28 00:00:00,2
bb,login,2020-04-28 12:30:00,2020-04-28 23:59:59,3
bb,logout,2020-04-29 12:30:00,2020-04-29 00:00:00,4
cc,logout,2020-04-28 11:25:00,2020-04-28 00:00:00,1
cc,login,2020-04-28 16:30:00,2020-04-28 20:10:00,2
cc,logout,2020-04-28 20:10:00,2020-04-28 00:00:00,3
dd,logout,2020-04-28 05:25:00,2020-04-28 00:00:00,1
dd,login,2020-04-28 10:30:00,2020-04-28 11:10:00,2
dd,logout,2020-04-28 11:10:00,2020-04-28 00:00:00,3
dd,login,2020-04-28 21:15:00,2020-04-28 23:59:59,4

1.3 此时的数据包含两种数据，第一种是登录时间和对应的登出时间，第二种是登出时间和当天的23:59:59,应该保留

1、该用户登录状态为login状态的数据

2、该用户当天第一次只有登出状态的数据

select uid,log_type,login_time,logout_time
from (select uid,log_type,ts login_time,
       case when log_type ='login' then lead(ts,1,concat(to_date(ts),' 23:59:59')) over (partition by uid,to_date(ts) order by ts asc)
            else concat(to_date(ts),' 00:00:00')    end logout_time,  --else 中将logout对应的登入时间都定为当天 的00:00:00,如果该条记录在排序的第一条直接取即可
       row_number() over (partition by uid,to_date(ts) order by ts asc) rn --根据uid，日期分区对时间排序
from dwd_db.user_log)A
where log_type ='login' or (log_type='logout' and rn = 1)

-----------------------------结果---------------------------
uid,log_type,login_time,logout_time
aa,login,2020-04-28 09:30:00,2020-04-28 10:15:00
aa,login,2020-04-28 13:30:00,2020-04-28 14:15:00
aa,login,2020-04-29 18:31:00,2020-04-29 23:15:00
bb,login,2020-04-28 06:30:00,2020-04-28 09:15:00
bb,login,2020-04-28 12:30:00,2020-04-28 23:59:59
bb,logout,2020-04-29 12:30:00,2020-04-29 00:00:00
cc,logout,2020-04-28 11:25:00,2020-04-28 00:00:00
cc,login,2020-04-28 16:30:00,2020-04-28 20:10:00
dd,logout,2020-04-28 05:25:00,2020-04-28 00:00:00
dd,login,2020-04-28 10:30:00,2020-04-28 11:10:00
dd,login,2020-04-28 21:15:00,2020-04-28 23:59:59

1.4 计算出每个用户每天的登录时长

select uid,to_date(login_time) date,sum(case when log_type='login' then unix_timestamp(logout_time,'yyyy-MM-dd HH:mm:ss') -
       unix_timestamp(login_time,'yyyy-MM-dd HH:mm:ss') else unix_timestamp(login_time,'yyyy-MM-dd HH:mm:ss') -
       unix_timestamp(logout_time,'yyyy-MM-dd HH:mm:ss') end) dura_oneday
from (select uid,log_type,login_time,logout_time
from (select uid,log_type,ts login_time,
       case when log_type ='login' then lead(ts,1,concat(to_date(ts),' 23:59:59')) over (partition by uid,to_date(ts) order by ts asc)
            else concat(to_date(ts),' 00:00:00')    end logout_time,  --else 中将logout对应的登入时间都定为当天 的00:00:00,如果该条记录在排序的第一条直接取即可
       row_number() over (partition by uid,to_date(ts) order by ts asc) rn --根据uid，日期分区对时间排序
from dwd_db.user_log)A
where log_type ='login' or (log_type='logout' and rn = 1))A
group by uid,to_date(login_time)

-----结果------
uid,date,duar_oneday
aa,2020-04-28,5400
aa,2020-04-29,17040
bb,2020-04-28,51299
bb,2020-04-29,45000
cc,2020-04-28,54300
dd,2020-04-28,31799