[Hive系列12] 开窗函数详解（下）

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

2550

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

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

大家好，我是风影楼，一名互联网公司的数据分析师。之前我曾在CSDN编写了Hive系列的付费博客专栏，目前收获了25W次的访问，不过由于CSDN的付费设置无法取消，所以我决定在公众号重新免费分享一遍。这一篇会介绍Hive中较为复杂的一些开窗函数，分别是ntile、cume_dist、lag、lead、first_value、last_value六个函数。

1、ntile(k)

ntile(k)函数的作用是等频分箱，把观测值进行有序排列（默认升序），根据观测值的总个数等分为k部分，每部分当作一个分箱，即百分位数的概念。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。

然后可以根据桶号，选取前或后 n分之几的数据。数据会完整展示出来，只是给相应的数据打标签；具体要取几分之几的数据，需要再嵌套一层根据标签取出。

1.1 举例

现有表字段如下：第一列是月份，第二列代表商铺名称，第三列代表该商铺该月营业额（万元）。

select * from temp_test11;


结果：
month  shop  money
2019-01 a 1
2019-01 b 2
2019-01 c 3
2019-01 d 4
2019-01 e 5
2019-01 f 6
2019-02 a 2
2019-02 b 4
2019-02 c 6
2019-02 d 8
2019-02 e 10
2019-02 f 12

‍

需求1：按月份分区按营业额从大到小排序，将数据等分成3份，rk=1的是每个分区内前1/3营业额的数据，rk=2的是为每个分区内中间1/3营业额的数据，rk=3的是为每个分区内后1/3营业额的数据。

select month
      ,shop
      ,money
      ,ntile(3)over (partition by month order by money desc) as rk
from temp_test11
order by month
      ,shop;
      


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

需求2：按月份分区按营业额从小到大排序，将数据分成4份，但是由于每个分组内只有6行数据，无法等分成4组，所以前两组分别多分1行数据。

select month
      ,shop
      ,money
      ,ntile(4)over (partition by month order by money) as rk
from temp_test11
order by month
      ,shop;
      


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

1.2 应用场景

ntile(n)函数在数据分析中应用很广，常见场景有以下几种：

1）要取数据集的n分位数，n>10，此时如果使用percentile函数会十分麻烦，需要指定出每个分位数在1中的占比。通过使用ntile函数分组后，再聚合计算每个分组的最大最小值。这样可以便捷快速的达到目的。

2）计算两个指标的相关系数有个问题，数据量过小(<10)结果会偏高，且大概率无法通过T检验，数据量过大(>50)可以通过T检验，但是结果又会偏低。所以最好将数据量控制在30到40的范围内。

但工作中计算相关系数的指标都是几万、几十万行的数据量起步，直接计算相关系数的话结果会无限接近于0，此时可以使用ntile函数对数据进行分组，然后使用分组编号作为其中一个指标，再对另一个指标进行聚合，即可计算出二者的相关系数。

3）要取出数据集的前1/n或者其他分位段的数据，使用ntile函数十分方便。

2、cume_dist

cume_dist可以统计这样一个比值：小于等于当前值的行数/分组内总行数。

举例：

这里依然使用上文使用的表格进行举例，为了使结果更加清晰，我们只取4个商铺。比如要统计每个月小于等于当前销售额的门店数占总门店数的比例。

SELECT month
      ,shop
      ,MONEY
      ,cume_dist() OVER (PARTITION BY month ORDER BY money) AS rk
FROM temp_test11   
where shop not in ('e','f') 


结果：
month  shop  money  rk
2019-01  a  1  0.25
2019-01  b  2  0.5
2019-01  c  3  0.75
2019-01  d  4  1.0
2019-02  a  2  0.25
2019-02  b  4  0.5
2019-02  c  6  0.75
2019-02  d  8  1.0

3、lag(col,n,default)

lag(col,n,default)用于统计分组内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，不填默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）。

举例：

现有如下表数据：

select * from temp_test12;


结果：
month  shop  money
2019-01 a 1
2019-04 a 4
2019-02 a 2
2019-03 a 3
2019-06 a 6
2019-05 a 5
2019-01 b 2
2019-02 b 4
2019-03 b 6
2019-04 b 8
2019-05 b 10
2019-06 b 12

新添一列每个商铺上个月的营业额，结果字段如下：月份商铺本月营业额上月营业额

实现这种格式的中间表后，可以很容易计算出营业额每个月比上个月的涨幅或降幅。

如果没有lag函数，我们想实现这个需求需要这样做：

实现这个需求我们需要先使用row_number()over按商铺分组，按月份排序得出这样一个结果：
SELECT month
      ,shop
      ,money
      ,ROW_NUMBER() OVER (
PARTITION BY shop ORDER BY month
            ) AS rn
FROM temp_test12;


结果：
month  shop  money  rn
2019-01  a  1  1
2019-02  a  2  2
2019-03  a  3  3
2019-04  a  4  4
2019-05  a  5  5
2019-06  a  6  6
2019-01  b  2  1
2019-02  b  4  2
2019-03  b  6  3
2019-04  b  8  4
2019-05  b  10  5
2019-06  b  12  6


然后进行偏移自关联，将每个商铺的每个月的营业额和上个月的关联在一起：


WITH a
AS (
SELECT month
            ,shop
            ,MONEY
            ,ROW_NUMBER() OVER (
PARTITION BY shop ORDER BY month
                  ) AS rn
FROM temp_test12
      )
SELECT a1.month
      ,a1.shop
      ,a1.MONEY
      ,nvl(a2.month, '2018-12') before_month  --为了便于理解，这里加入上月的月份。如果上月没有的月份取为2018-12
      ,nvl(a2.MONEY, 1) before_money          --上月没有的营业额取为1
FROM a a1 --代表本月
LEFT JOIN a a2  --代表上月
ON a1.shop = a2.shop
AND a1.month = substr(add_months(CONCAT (
                              a2.month
                              ,'-01'
                              ), 1), 1, 7) --增加月份的函数add_months中至少要传入年月日
GROUP BY a1.month
      ,a1.shop
      ,a1.MONEY
      ,nvl(a2.month, '2018-12')
      ,nvl(a2.MONEY, 1);


结果：
a1.month  a1.shop  a1.money  before_month  before_money
2019-01  a  1  2018-12  1
2019-02  a  2  2019-01  1
2019-03  a  3  2019-02  2
2019-04  a  4  2019-03  3
2019-05  a  5  2019-04  4
2019-06  a  6  2019-05  5
2019-01  b  2  2018-12  1
2019-02  b  4  2019-01  2
2019-03  b  6  2019-02  4
2019-04  b  8  2019-03  6
2019-05  b  10  2019-04  8
2019-06  b  12  2019-05  10

有了lag函数后实现这个需求变得极为简单：

SELECT month
      ,shop
      ,MONEY
      ,LAG(MONEY, 1, 1) OVER ( --取分组内上一行的营业额，如果没有上一行则取1
PARTITION BY shop ORDER BY month  --按商铺分组，按月份排序
            ) AS before_money
FROM temp_test12;




结果：
month  shop  money  before_money
2019-01  a  1  1
2019-02  a  2  1
2019-03  a  3  2
2019-04  a  4  3
2019-05  a  5  4
2019-06  a  6  5
2019-01  b  2  1
2019-02  b  4  2
2019-03  b  6  4
2019-04  b  8  6
2019-05  b  10  8
2019-06  b  12  10

再调整参数，演示下lag函数的几种其他用法：

SELECT month
      ,shop
      ,MONEY
      ,LAG(MONEY, 1, 1) OVER (
PARTITION BY shop ORDER BY month
            ) AS before_money
      ,LAG(MONEY, 1) OVER (
PARTITION BY shop ORDER BY month
            ) AS before_money   --第三个参数不写的话，如果没有上一行值，默认取null
      ,LAG(MONEY) OVER (
PARTITION BY shop ORDER BY month
            ) AS before_money   --第二个参数不写默认为1，第三个参数不写的话，如果没有上一行值，默认取null，结果与上一列相同
      ,LAG(MONEY, 2, 1) OVER (
PARTITION BY shop ORDER BY month
            ) AS before_2month_money  --取两个月前的营业额
FROM temp_test12;




结果：
month  shop  money  before_money  before_money  before_money  before_2month_money
2019-01  a  1  1  NULL  NULL  1
2019-02  a  2  1  1  1  1
2019-03  a  3  2  2  2  1
2019-04  a  4  3  3  3  2
2019-05  a  5  4  4  4  3
2019-06  a  6  5  5  5  4
2019-01  b  2  1  NULL  NULL  1
2019-02  b  4  2  2  2  1
2019-03  b  6  4  4  4  2
2019-04  b  8  6  6  6  4
2019-05  b  10  8  8  8  6
2019-06  b  12  10  10  10  8

解释说明：

shop为a时，before_money指定了往上第1行的值，如果没有上一行值，默认取null，这里指定为1。

a的第1行，往上1行值为NULL,指定第三个参数取1，不指定取null 。

a的第2行，往上1行值为第1行营业额值，1。

a的第6行，往上1行值为为第5行营业额值，5。

4、lead(col,n,default)

lead(col,n,default)与lag相反，统计分组内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，不填默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）。

举例：

新添一列每个商铺下个月的营业额，结果字段如下：月份商铺本月营业额下月营业额

SELECT month
      ,shop
      ,MONEY
      ,LEAD(MONEY, 1, 7) OVER (
PARTITION BY shop ORDER BY month
            ) AS after_money
      ,LEAD(MONEY, 1) OVER (
PARTITION BY shop ORDER BY month
            ) AS after_money   --第三个参数不写的话，如果没有下一行值，默认取null
      ,LEAD(MONEY, 2, 7) OVER (
PARTITION BY shop ORDER BY month
            ) AS after_2month_money  --取两个月后的营业额
FROM temp_test12;




结果：
month  shop  money  after_money  after_money  after_2month_money
2019-01  a  1  2  2  3
2019-02  a  2  3  3  4
2019-03  a  3  4  4  5
2019-04  a  4  5  5  6
2019-05  a  5  6  6  7
2019-06  a  6  7  NULL  7
2019-01  b  2  4  4  6
2019-02  b  4  6  6  8
2019-03  b  6  8  8  10
2019-04  b  8  10  10  12
2019-05  b  10  12  12  7
2019-06  b  12  7  NULL  7

解释说明：

shop为a时，after_money指定了往下第1行的值，如果没有下一行值，默认取null，这里指定为1。

a的第1行，往下1行值为第2行营业额值，2。

a的第2行，往下1行值为第3行营业额值，4。

a的第6行，往下1行值为NULL,指定第三个参数取7，不指定取null。

5、first_value(col)

用于取分组内排序后，截止到当前行，第一个col的值。

举例：

SELECT month
      ,shop
      ,MONEY
      ,first_value(MONEY) OVER (
PARTITION BY shop ORDER BY month
            ) AS first_money
FROM temp_test12;




结果：
month  shop  money  first_money
2019-01  a  1  1
2019-02  a  2  1
2019-03  a  3  1
2019-04  a  4  1
2019-05  a  5  1
2019-06  a  6  1
2019-01  b  2  2
2019-02  b  4  2
2019-03  b  6  2
2019-04  b  8  2
2019-05  b  10  2
2019-06  b  12  2

解释说明：

shop为a时，截止到每一行时，分组内的第一行值都是1。

shop为b时，截止到每一行时，分组内的第一行值都是2。

6、last_value(col)

用于取分组内排序后，截止到当前行，最后一个col的值。

举例：

SELECT month
      ,shop
      ,MONEY
      ,last_value(MONEY) OVER (
PARTITION BY shop ORDER BY month
            ) AS last_money
FROM temp_test12;


结果：
month  shop  money  last_money
2019-01  a  1  1
2019-02  a  2  2
2019-03  a  3  3
2019-04  a  4  4
2019-05  a  5  5
2019-06  a  6  6
2019-01  b  2  2
2019-02  b  4  4
2019-03  b  6  6
2019-04  b  8  8
2019-05  b  10  10
2019-06  b  12  12

解释说明：

shop为a时，截止到每一行时，分组内的最后一行值都是该行本身。

shop为b时，截止到每一行时，分组内的最后一行值都是该行本身。

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

·END·

点个在看你最好看

数据库

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

[Hive系列12] 开窗函数详解（下）

评论