[Hive系列11] 开窗函数详解（上）

数据分析师的FIRE人生 2021-07-14

2172

点击上方「数据分析师的FIRE人生」→

点击右上角「...」→设为星标⭐

大家好，我是风影楼，一名互联网公司的数据分析师。之前我曾在CSDN编写了Hive系列的付费博客专栏，目前收获了25W次的访问，不过由于CSDN的付费设置无法取消，所以我决定在公众号重新免费分享一遍。这一篇主要介绍Hive中两类功能的开窗函数，分别是组内累计统计函数和组内排序函数。

1、什么是开窗函数

这类函数叫法很多，包括分析函数、窗口函数、开窗函数、分析窗口函数，其实说的都是一类函数。开窗函数用于为行定义一个窗口（这里的窗口是指运算将要操作的行的集合），它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

2、开窗函数语法

开窗函数 over(partition by 列名1,列名2 …… order by 列名3,列名4 …… [desc])

括号中的两个关键词partition by 和order by 可以只出现一个。

partition by 和order by 后面的列名可以根据需求设定任意数量个列名。

order by后面可以选择是否跟desc，加上为倒序排序（从大到小），不加则默认为从小到大排序。

3、组内累计统计函数 sum、avg、min、max

讲解这几个窗口函数前，先创建一个表，以实际例子讲解大家更容易理解。现有表字段如下：第一列是月份，第二列代表商铺名称，第三列代表该商铺该月营业额（万元）。

select * from temp_test9;


month  shop  money
2019-01  a  10
2019-02  a  20
2019-03  a  30
2019-01  b  10
2019-02  b  20
2019-03  b  30

‍

3.1 累计求和 sum(xx) over

需求1：求商店a每个月从1月累计到该月的总营业额，即：

1月的数据是1月的营业额

2月的数据是1月+2月的营业额

3月的数据是1月+2月+3月的营业额

SELECT month
      ,SUM(MONEY) OVER (
ORDER BY month  --按照月份进行排序，然后默认从起点行到当前行做累计求和
            ) AS money_leiji
FROM temp_test9
WHERE shop = 'a'; --开窗函数不用写group by


结果：
month  money_leiji
2019-01  10.0
2019-02  30.0
2019-03  60.0

需求2：同时求出商店a、b每个月从1月累计到该月的总营业额

SELECT shop
      ,month
      ,SUM(MONEY) OVER (
PARTITION BY shop ORDER BY month --先按照shop进行分组，然后每个组内再按照月份进行排序，最后默认从起点行到当前行做累计求和
            ) AS money_leiji
FROM temp_test9;




结果：
shop  month  money_leiji
a  2019-01  10.0
a  2019-02  30.0
a  2019-03  60.0
b  2019-01  10.0
b  2019-02  30.0
b  2019-03  60.0

3.2 累计求平均值 avg(xx) over

其他的开窗函数和求和的累计逻辑都是相同的。

举例：

同时求出商店a、b每个月从1月累计到该月的平均营业额

SELECT shop
      ,month
      ,AVG(MONEY) OVER (
PARTITION BY shop ORDER BY month --先按照shop进行分组，然后每个组内再按照月份进行排序，最后默认从起点行到当前行做累计求均值
            ) AS money_leiji
FROM temp_test9;






结果：
shop  month  money_leiji
a  2019-01  10.0
a  2019-02  15.0
a  2019-03  20.0
b  2019-01  10.0
b  2019-02  15.0
b  2019-03  20.0

3.3 累计求最大值 max(xx) over

举例：

同时求出商店a、b每个月从1月累计到该月的营业额最大值

SELECT shop
      ,month
      ,MAX(MONEY) OVER (
PARTITION BY shop ORDER BY month --先按照shop进行分组，然后每个组内再按照月份进行排序，最后默认从起点行到当前行做累计求最大值
            ) AS money_leiji_max
FROM temp_test9;






结果：
shop  month  money_leiji_max
a  2019-01  10
a  2019-02  20
a  2019-03  30
b  2019-01  10
b  2019-02  20
b  2019-03  30

3.4 累计求最小值 min(xx) over

举例：

同时求出商店a、b每个月从1月累计到该月的营业额最小值

SELECT shop
      ,month
      ,MIN(MONEY) OVER (
PARTITION BY shop ORDER BY month --先按照shop进行分组，然后每个组内再按照月份进行排序，最后默认从起点行到当前行做累计求最小值
            ) AS money_leiji_min
FROM temp_test9;


结果：
shop  month  money_leiji_min
a  2019-01  10
a  2019-02  10
a  2019-03  10
b  2019-01  10
b  2019-02  10
b  2019-03  10

3.5 自定义累计方式

除了上面常用的累计统计方式之外，Hive还允许自定义累计方式。默认的累计方式是按起点行到当前行做累计，自定义累计方式主要有以下4种：

当前行+往前几行

当前行+往后几行

当前行+往前几行+往后几行

当前行+往后所有行

这几种自定义的累计方式应用场景较少，这里只介绍一下语法：

1）当前行+往前几行

1 PRECEDING指往前1行，这里可以根据需求任意指定数值

OVER (PARTITION BY 列名1 ORDER BY 列名2 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)

2）当前行+往后几行

1 FOLLOWING指往后1行，这个值可以根据需求指定任意数值

OVER (PARTITION BY 列名1 ORDER BY 列名2 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)

3）当前行+往前几行+往后几行

1 PRECEDING指往前1行，1 FOLLOWING指往后1行，这两个值均可以根据需求指定任意数值



OVER (PARTITION BY 列名1 ORDER BY 列名2 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

4）当前行+往后所有行

OVER (PARTITION BY 列名1 ORDER BY 列名2 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

4、组内排序函数

4.1 row_number()

row_number()会生成数据项在分组中的排名，排名即便相等也不会有并列排名，相同排名随机排序。

现有数据如下表：
select * from temp_test10;


month  shop  money
2019-01  a  4
2019-01  b  3
2019-01  c  3
2019-01  d  2
2019-01  e  1
2019-02  a  1
2019-02  b  2
2019-02  c  3
2019-02  d  3
2019-02  e  4

应用场景1：生成排序后的唯一序号

举例：首先按照月份进行分组，然后每个组内按照营业额从大到小排序，为组内每一行数据得到一个唯一序号。

SELECT month
      ,shop
      ,MONEY
      ,row_number() OVER (
PARTITION BY month ORDER BY MONEY DESC --按照月份进行分组，然后每个组内按照营业额从大到小排序
            ) AS rk  --生成的排序序号
FROM temp_test10




结果：
month  shop  money  rk
2019-01  a  4  1
2019-01  c  3  2
2019-01  b  3  3
2019-01  d  2  4
2019-01  e  1  5
2019-02  e  4  1
2019-02  d  3  2
2019-02  c  3  3
2019-02  b  2  4
2019-02  a  1  5

应用场景2：取top n

举例：取出1月和2月每个月营业额排名前3的店铺及营业额。

SELECT *
FROM (
SELECT month
      ,shop
      ,MONEY
      ,row_number() OVER (
PARTITION BY month ORDER BY MONEY DESC --按照月份进行分组，然后每个组内按照营业额从大到小排序
          ) AS rk
FROM temp_test10
    ) a
WHERE rk <= 3;


结果：
a.month  a.shop  a.money  a.rk
2019-01  a  4  1
2019-01  c  3  2
2019-01  b  3  3
2019-02  e  4  1
2019-02  d  3  2
2019-02  c  3  3

应用场景3：每个分组内取出n个随机值

row_number()配合rand()函数即可实现每个分组内取出n个随机值的需求。

举例：每个月随机抽取两家店铺

SELECT *
FROM (
SELECT month
            ,shop
            ,MONEY
            ,row_number() OVER (
PARTITION BY month ORDER BY rand(1) --可以使用任意数作为种子进行随机排序，也可以不填，直接使用rand()
                  ) AS rk
FROM temp_test10
      ) a
WHERE rk <= 2; --限制rk来取出n个随机值


结果：
a.month  a.shop  a.money  a.rk
2019-01  c  3  1
2019-01  d  2  2
2019-02  a  1  1
2019-02  e  4  2

4.2 rank()

rank()可以生成数据项在分组中的排名，排名相等时会产生并列排名，然后会在名次中留下空位。

举例：首先按照月份进行分组，然后每个组内按照营业额从大到小排序，生成排名，并列排名后留下空位。

SELECT month
      ,shop
      ,MONEY
      ,rank() OVER (
PARTITION BY month ORDER BY MONEY DESC --按照月份进行分组，然后每个组内按照营业额从大到小排序
            ) AS rk  --生成的排序序号
FROM temp_test10;


结果：
month  shop  money  rk
2019-01  a  4  1
2019-01  c  3  2
2019-01  b  3  2
2019-01  d  2  4
2019-01  e  1  5
2019-02  e  4  1
2019-02  d  3  2
2019-02  c  3  2
2019-02  b  2  4
2019-02  a  1  5

4.3 dense_rank()

dense_rank()可以生成数据项在分组中的排名，排名相等时会产生并列排名，但不会在名次中留下空位。应用场景也不多，很少需要使用，了解即可。

举例：首先按照月份进行分组，然后每个组内按照营业额从大到小排序，生成排名，并列排名后不要留下空位。

SELECT month
      ,shop
      ,MONEY
      ,dense_rank() OVER (
PARTITION BY month ORDER BY MONEY DESC --按照月份进行分组，然后每个组内按照营业额从大到小排序
            ) AS rk  --生成的排序序号
FROM temp_test10;


结果：
month  shop  money  rk
2019-01  a  4  1
2019-01  c  3  2
2019-01  b  3  2
2019-01  d  2  3
2019-01  e  1  4
2019-02  e  4  1
2019-02  d  3  2
2019-02  c  3  2
2019-02  b  2  3
2019-02  a  1  4

由于公众号平台更改了推送规则，如果不想错过后续内容，记得点下“赞”和“在看”，这样下次有新文章推送，就会第一时间出现在你的订阅号列表里。

·END·

点个在看你最好看

数据库

文章转载自数据分析师的FIRE人生，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

[Hive系列11] 开窗函数详解（上）

评论