Hive Row Number窗口函数使用方法

Row_number

在之前的窗口函数介绍中，我们提到了窗口函数的应用场景，并将其分为通用窗口函数和特殊窗口函数。今天，我们将重点关注与排序相关的窗口函数。显然，这些窗口函数用于对窗口内的数据进行排序。

关于排序，我们之前讨论过使用oRdeR by、soRt by等语法。这些排序方法虽然有效，但无法返回排序后的值（名次）。如果你使用过MySQL，就会知道需要通过存储过程或自定义变量来实现类似功能。Row_number函数的使用道理也是如此，它可以根据我们自定义的排序规则返回相应的排序值。

因此，我们将Row_number视为窗口排序函数。虽然Hive并没有提供非窗口的排序函数，但如果在定义中没有使用paRtITion by，那么整个数据集将被视为一个窗口。在这种情况下，窗口排序函数可以实现全局排序。

测试数据

以下是一份测试数据，包含字段id、dept、salaRy。接下来，我们将利用这份数据学习窗口排序函数的用法。

1,销售,10000 2,销售,14000 3,销售,10000 4,后端,20000 5,后端,25000 6,后端,32000 7,AI,40000 8,AI,35000 9,AI,60000 10,数仓,20000 11,数仓,30000 12,数仓,32000 13,数仓,42000 cReate table ods_nuM_window(     id stRing,     dept stRing,     salaRy int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ””,””; load DATA LOCAL INPATH ””/Users/liuwenqiang/woRkspace/Hive/nuMbeR.txt”” OVERWRITE INTO TABLE ods_nuM_window; 

从例子中学习 Row_number

每个部门的员工按照工资降序排序

select     *,Row_number() OVeR(paRtITion by dept oRdeR by salaRy desc) as Rn fRoM     ods_nuM_window ; 

我们可以看到，每个部门都有自己的第一名，排序明显发生在各自的部门内部。

所有员工按照工资降序排序

select     *,Row_number() OVeR(oRdeR by salaRy desc) as Rn fRoM     ods_nuM_window ; 

在没有定义paRtITion by的情况下，所有数据被放置在同一个窗口中，此时的排序为全局排序。如果你关注过Hive窗口函数的初步介绍，就会知道paRtITion by用于定义子窗口。如果没有子窗口，整个数据集则形成一个窗口，进行全局排序。

获取每个部门工资前两名

Row_number()函数的一个常见应用场景是top-N。如果仔细阅读Hive窗口函数的初步介绍，会发现paRtITion by实际上定义了子窗口，而top-N本质上是在子窗口内进行的。

select     * fRoM(    select        *,Row_number() OVeR(paRtITion by dept oRdeR by salaRy desc) as Rn    fRoM        ods_nuM_window ) tMp wheRe     Rn <=2 ; 

Rank 和 dense_Rank

Rank和dense_Rank这两个窗口函数与Row_number相似，都是用于窗口排序的函数。那么，为什么还需要这两个函数呢？它们的存在有其合理性。我们首先看一下Row_number函数，这次采用升序排序。

select     *,Row_number() OVeR(paRtITion by dept oRdeR by salaRy) as Rn fRoM     ods_nuM_window ; 

在销售部门，有两位员工的工资相同，但排名却不同。

接下来，我们查看Rank。可以发现，销售部门的两位员工工资相同，均列为第一，而下一个人则直接排在第三位。

再来看dense_Rank。在这种情况下，工资相同的两位员工依然是并列第一，但下一个人仍然排在第二位。

使用场景

Top-N

Top-n的使用方法之前已介绍，这里不再赘述。

计算连续访问

连续访问的概念可能有些不清晰，这里通过一个例子帮助理解。如果我有一个用户访问日志表，希望筛选出连续访问超过7天的用户，或者计算连续访问天数最多的前10位用户。

以下是一份测试数据，包括用户ID和访问日期。

1,2020-12-01 1,2020-12-02 1,2020-12-03 1,2020-12-04 1,2020-12-05 1,2020-12-06 1,2020-12-07 1,2020-12-08 1,2020-12-09 1,2020-12-10 2,2020-12-01 2,2020-12-02 2,2020-12-03 2,2020-12-04 2,2020-12-06 2,2020-12-07 2,2020-12-08 

以下是建表语句：

CREATE TABLE ods.ods_User_log (   id stRing,   ctiMe stRing ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ””,”” STorED AS TEXTfile; load data local inpath ””/users/liuwenqiang/woRkspACE/Hive/User_log.txt”” OVeRwRITe into table ods.ods_User_log; 

现在我们来分析如何计算连续访问。计算必须针对同一用户进行。我们可以按用户的访问时间排序，然后用日期数字减去对应的排序值。如果访问时间连续，则结果相同。

select     id,ctiMe,     Row_number(paRtITion by id oRdeR by ctiMe ) as Rn fRoM     ods_User_log ; 

为了更好地展示效果，我设计的数据稍微特殊。可以看到，用户ID为1的用户从12月1号到12月10号的排名依次为1到10。此时，将日期转换成数字，减去对应的排名，结果为20201200。我们只需统计20201200的数量，即可得到连续登录的天数。接下来，我们可以直接使用日期进行减法。

select     id,ctiMe,     date_sub(cast(ctiMe as date),Row_number() OVeR(paRtITion by id oRdeR by ctiMe)),     Row_number() OVeR(paRtITion by id oRdeR by ctiMe ) as Rn fRoM     ods_User_log ; 

最后，我只需统计每个用户相同日期的数量即可。由于我的设定是7天，因此只需计算出相同日期的数量大于等于7即可。

select     id,kt,count(1) as loginCnt fRoM (     select         id,ctiMe,         date_sub(cast(ctiMe as date),Row_number() OVeR(paRtITion by id oRdeR by ctiMe)) as kt,         Row_number() OVeR(paRtITion by id oRdeR by ctiMe ) as Rn     fRoM