Hive 1.2.1 窗口函数

2020-06-27 本文已影响0人孙瑞锴

1. 借鉴

网名在线生成器
 HIVE over() 超全讲解
 Hive分析窗口函数(一) SUM,AVG,MIN,MAX

2. 开始

数据准备

create external table db_company.hotel_order
(
  id bigint,
  name string,
  order_date string,
  price bigint,
  code string
)
row format delimited
fields terminated by ',';

加载数据

load data local inpath '/opt/envs/datas/hotel/hotel_order.txt' into table db_company.hotel_order;

窗口函数

OVER()
指定分析函数工作的数据窗口大小
注意点：
① over前面必须是指定的函数
② 括号里面可以写内容，限定窗口大小，如果不写，表示对查询出来的所有数据集进行开窗
③ 为每一组数据都进行开窗

那么括号里可以写那些内容呢？

内容	释义
CURRENT ROW	表示当前行
n PRECEDING	表示往前n行数据
n FOLLOWING	表示往后n行数据
UNBOUNDED PRECEDING	表示从前面的起点
UNBOUNDED FOLLOWING	表示到后面的终点

那个又有哪些指定的函数呢？

内容	释义
MAX(col)	取最大值
MIN(col)	取最小值
AVG(col)	取平均值
COUNT(col)	取总数
LAG(col,n)	往前第n行数据，第三个参数为默认值，可选
LEAD(col,n)	往后第n行数据，第三个参数为默认值，可选
NTILE(n)	把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。
RANK()	排序相同时会重复，总数不会变(并列) [4个人，排名为：1，1，3，4]
DENSE_RANK()	排序相同时会重复，总数会减少 [4个人，排名为：1，1，2，3]
ROW_NUMBER()	会根据顺序计算 [4个人，排名为：1，2，3，4]

栗子

我们通过一些栗子来看下窗口函数

① 查询2020-06，每个人入住总数以及总入住人数
```
select name, count(*), count(*) over()
from db_company.hotel_order
where date_format(order_date, 'yyyy-MM') = '2020-06' group by name;
```
结果如下：
```
name    _c1     count_window_0
齐钰    2       6
酒博瀚  3       6
碧千易  2       6
源采文  2       6
掌蓉城  2       6
丰雅丽  4       6
```
我们分析一下，因为我们使用了count()和count() over()。看样子后面就多了一个over()为啥结果前面和后面不一样呢？group by之后，count()表示统计各个分组之后的数量，而count() over()则是对统计分组的数量(因为over里面我们没有写内容，且over前面的聚合函数式count)。

② 查询用户的入住明细以及所有用户的入住总金额

select *, sum(price) over()
from db_company.hotel_order;

结果如下：

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0
6       源采文  2020-06-02      103     00174553        1461
6       源采文  2020-06-01      103     00174553        1461
5       掌蓉城  2020-06-02      103     01341433        1461
5       掌蓉城  2020-06-01      103     01341433        1461
4       酒博瀚  2020-06-04      101     00277553        1461
4       酒博瀚  2020-06-03      101     00277553        1461
4       酒博瀚  2020-06-02      101     00277553        1461
3       齐钰    2020-06-02      101     02371493        1461
3       齐钰    2020-06-01      101     02371493        1461
2       碧千易  2020-06-13      93      02371493        1461
2       碧千易  2020-06-13      93      02371493        1461
1       丰雅丽  2020-06-13      91      02371493        1461
1       丰雅丽  2020-06-03      89      10271563        1461
1       丰雅丽  2020-06-02      89      10271563        1461
1       丰雅丽  2020-06-01      89      10271563        1461

可以看到over() + 前面的聚合函数，不一定非得跟着group by，但是它的概念还是包含了组的。

③ 根据时间排序，将price进行累加

select *, sum(price) over(order by order_date rows between UNBOUNDED PRECEDING and CURRENT ROW) 
from db_company.hotel_order;

结果如下：

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0
3       齐钰    2020-06-01      101     02371493        101
1       丰雅丽  2020-06-01      89      10271563        190
6       源采文  2020-06-01      103     00174553        293
5       掌蓉城  2020-06-01      103     01341433        396
6       源采文  2020-06-02      103     00174553        499
5       掌蓉城  2020-06-02      103     01341433        602
4       酒博瀚  2020-06-02      101     00277553        703
1       丰雅丽  2020-06-02      89      10271563        792
3       齐钰    2020-06-02      101     02371493        893
1       丰雅丽  2020-06-03      89      10271563        982
4       酒博瀚  2020-06-03      101     00277553        1083
4       酒博瀚  2020-06-04      101     00277553        1184
2       碧千易  2020-06-13      93      02371493        1277
2       碧千易  2020-06-13      93      02371493        1370
1       丰雅丽  2020-06-13      91      02371493        1461

另外我还发现以下两种写法，虽然这两种写法跟我们的需求不符，但是通过对以下这两种方式的观察我们发现是先执行over函数，再执行order排序，所以以后使用over函数时需要注意这一点。
第一种：排序写在over外面。

select *, sum(price) over(rows between UNBOUNDED PRECEDING and CURRENT ROW) 
from db_company.hotel_order
order by order_date;

这种方式的结果如下：

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0
1       丰雅丽  2020-06-01      89      10271563        1461
3       齐钰    2020-06-01      101     02371493        917
6       源采文  2020-06-01      103     00174553        206
5       掌蓉城  2020-06-01      103     01341433        412
3       齐钰    2020-06-02      101     02371493        816
1       丰雅丽  2020-06-02      89      10271563        1372
4       酒博瀚  2020-06-02      101     00277553        715
5       掌蓉城  2020-06-02      103     01341433        309
6       源采文  2020-06-02      103     00174553        103
4       酒博瀚  2020-06-03      101     00277553        614
1       丰雅丽  2020-06-03      89      10271563        1283
4       酒博瀚  2020-06-04      101     00277553        513
1       丰雅丽  2020-06-13      91      02371493        1194
2       碧千易  2020-06-13      93      02371493        1010
2       碧千易  2020-06-13      93      02371493        1103

第二种，不根据order_date进行排序

select *, sum(price) over( rows between UNBOUNDED PRECEDING and current row) 
from db_company.hotel_order;

结果如下：

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0
6       源采文  2020-06-02      103     00174553        103
6       源采文  2020-06-01      103     00174553        206
5       掌蓉城  2020-06-02      103     01341433        309
5       掌蓉城  2020-06-01      103     01341433        412
4       酒博瀚  2020-06-04      101     00277553        513
4       酒博瀚  2020-06-03      101     00277553        614
4       酒博瀚  2020-06-02      101     00277553        715
3       齐钰    2020-06-02      101     02371493        816
3       齐钰    2020-06-01      101     02371493        917
2       碧千易  2020-06-13      93      02371493        1010
2       碧千易  2020-06-13      93      02371493        1103
1       丰雅丽  2020-06-13      91      02371493        1194
1       丰雅丽  2020-06-03      89      10271563        1283
1       丰雅丽  2020-06-02      89      10271563        1372
1       丰雅丽  2020-06-01      89      10271563        1461

拓展：解释一下这个HQL
我们看下下面这个HQL

select *, sum(price) over(order by order_date) 
from db_company.hotel_order;

结果如下：

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0
3       齐钰    2020-06-01      101     02371493        396
1       丰雅丽  2020-06-01      89      10271563        396
6       源采文  2020-06-01      103     00174553        396
5       掌蓉城  2020-06-01      103     01341433        396
6       源采文  2020-06-02      103     00174553        893
5       掌蓉城  2020-06-02      103     01341433        893
4       酒博瀚  2020-06-02      101     00277553        893
1       丰雅丽  2020-06-02      89      10271563        893
3       齐钰    2020-06-02      101     02371493        893
1       丰雅丽  2020-06-03      89      10271563        1083
4       酒博瀚  2020-06-03      101     00277553        1083
4       酒博瀚  2020-06-04      101     00277553        1184
2       碧千易  2020-06-13      93      02371493        1461
2       碧千易  2020-06-13      93      02371493        1461
1       丰雅丽  2020-06-13      91      02371493        1461

我们上面说了，over不一定跟着group by，但是它的概念包含了组，就是说这个组里面可以一条也可以多条数据，也可以说每条成组或者多条成组。我们按照order_date进行排序，我们也说了它会为每一组数据开窗，啥叫每一组？我们按照order_date排序，第一组的2020-06-01日期都分到了一组。如果你要问为什么？那有没有比它小的？没有。有没有比它大的？有，比它大的分到它们对应的组。那它是不是分为一组？是，所以[2020-06-01]分为一组。

所以总共分为以下组
[2020-06-01]，
[2020-06-01,2020-06-02]，
[2020-06-01,2020-06-02,2020-06-03]，
[2020-06-01,2020-06-02,2020-06-03,2020-06-04]，
[2020-06-01,2020-06-02,2020-06-03,2020-06-04,2020-06-13]
然后对组内进行sum(price)计算

④ 计算每个月的总金额

select *, sum(price) over(distribute by order_date) 
from db_company.hotel_order;

结果如下:

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0
3       齐钰    2020-06-01      101     02371493        396
1       丰雅丽  2020-06-01      89      10271563        396
6       源采文  2020-06-01      103     00174553        396
5       掌蓉城  2020-06-01      103     01341433        396
6       源采文  2020-06-02      103     00174553        497
5       掌蓉城  2020-06-02      103     01341433        497
4       酒博瀚  2020-06-02      101     00277553        497
1       丰雅丽  2020-06-02      89      10271563        497
3       齐钰    2020-06-02      101     02371493        497
1       丰雅丽  2020-06-03      89      10271563        190
4       酒博瀚  2020-06-03      101     00277553        190
4       酒博瀚  2020-06-04      101     00277553        101
2       碧千易  2020-06-13      93      02371493        277
2       碧千易  2020-06-13      93      02371493        277
1       丰雅丽  2020-06-13      91      02371493        277

⑤ 计算每个用户的累加金额

select *, sum(price) over(distribute by name sort by order_date) 
from db_company.hotel_order;

结果如下:

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0
1       丰雅丽  2020-06-01      89      10271563        89
1       丰雅丽  2020-06-02      89      10271563        178
1       丰雅丽  2020-06-03      89      10271563        267
1       丰雅丽  2020-06-13      91      02371493        358
5       掌蓉城  2020-06-01      103     01341433        103
5       掌蓉城  2020-06-02      103     01341433        206
6       源采文  2020-06-01      103     00174553        103
6       源采文  2020-06-02      103     00174553        206
2       碧千易  2020-06-13      93      02371493        186
2       碧千易  2020-06-13      93      02371493        186
4       酒博瀚  2020-06-02      101     00277553        101
4       酒博瀚  2020-06-03      101     00277553        202
4       酒博瀚  2020-06-04      101     00277553        303
3       齐钰    2020-06-01      101     02371493        101
3       齐钰    2020-06-02      101     02371493        202

⑥ 查询每个用户上次的入住时间
这里基于⑤，并且用到lag函数

select *, 
sum(price) over(distribute by name sort by order_date),
lag(order_date, 1) over(distribute by name sort by order_date)
from db_company.hotel_order;

结果如下：

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0    lag_window_1
1       丰雅丽  2020-06-01      89      10271563        89      NULL
1       丰雅丽  2020-06-02      89      10271563        178     2020-06-01
1       丰雅丽  2020-06-03      89      10271563        267     2020-06-02
1       丰雅丽  2020-06-13      91      02371493        358     2020-06-03
5       掌蓉城  2020-06-01      103     01341433        103     NULL
5       掌蓉城  2020-06-02      103     01341433        206     2020-06-01
6       源采文  2020-06-01      103     00174553        103     NULL
6       源采文  2020-06-02      103     00174553        206     2020-06-01
2       碧千易  2020-06-13      93      02371493        186     NULL
2       碧千易  2020-06-13      93      02371493        186     2020-06-13
4       酒博瀚  2020-06-02      101     00277553        101     NULL
4       酒博瀚  2020-06-03      101     00277553        202     2020-06-02
4       酒博瀚  2020-06-04      101     00277553        303     2020-06-03
3       齐钰    2020-06-01      101     02371493        101     NULL
3       齐钰    2020-06-02      101     02371493        202     2020-06-01

⑦ 查询每日每个人的订单金额排名

select name, price, order_date,
rank() over(distribute by order_date sort by price desc),
dense_rank() over(distribute by order_date sort by price desc),
row_number() over(distribute by order_date sort by price desc)
from db_company.hotel_order;

结果如下:

name    price   order_date      rank_window_0   dense_rank_window_1     row_number_window_2
源采文  103     2020-06-01      1       1       1
掌蓉城  103     2020-06-01      1       1       2
齐钰    101     2020-06-01      3       2       3
丰雅丽  89      2020-06-01      4       3       4
源采文  103     2020-06-02      1       1       1
掌蓉城  103     2020-06-02      1       1       2
酒博瀚  101     2020-06-02      3       2       3
齐钰    101     2020-06-02      3       2       4
丰雅丽  89      2020-06-02      5       3       5
酒博瀚  101     2020-06-03      1       1       1
丰雅丽  89      2020-06-03      2       2       2
酒博瀚  101     2020-06-04      1       1       1
碧千易  93      2020-06-13      1       1       1
碧千易  93      2020-06-13      1       1       2
丰雅丽  91      2020-06-13      3       2       3

⑧ 查询用户在2020年，连续2天(或以上)的都有入住记录，且订单金额大于90元的流水。
分析文件
第一步：查询2020年订单金额大于90的订单流水，并计为t1。

select 
  name, order_date, price, code
from 
  db_company.hotel_order
where 
  substring(order_date, 1, 4) = '2020' and price > 90;t1

第二步：查询前一天后一天的日期数据，并计为t2。

select
  name, order_date, price, code,
  lag(order_date, 1, '0000-00-00') over(partition by name order by order_date) lag1,
  lead(order_date, 1, '0000-00-00') over(partition by name order by order_date) lead1
from t1;t2

第三步：计算时间差，并计为t3。
我们用：
Ⅰ. 当前时间 - 前一天的时间（为0或者1即合法）。其中为0表示同一天有多笔订单，为1表示前一天有订单
Ⅱ. 当前时间 - 后一天的时间（为0或则-1即合法）。其中为0表示同一天有多笔订单，为-1表示后一天有订单

select 
  name, order_date, price, code,
  datediff(order_date, lag1) diff_lag1,
  datediff(order_date, lead1) diff_lead1
from t2;t3

第四步：同步比对时间差，得出最后流水明细

select 
  name, order_date, price, code
from
  t3
where 
  diff_lag1 = 0 or diff_lag1  = 1 or diff_lead1 = -1 or diff_lead1 = 0;

最后的sql为：

select 
  name, order_date, price, code
from
(
  select 
    name, order_date, price, code,
    datediff(order_date, lag1) diff_lag1,
    datediff(order_date, lead1) diff_lead1
  from 
  (
    select
        name, order_date, price, code,
        lag(order_date, 1, '0000-00-00') over(partition by name order by order_date) lag1,
        lead(order_date, 1, '0000-00-00') over(partition by name order by order_date) lead1
    from 
    (
       select name, order_date, price, code
       from 
          db_company.hotel_order
       where 
          substring(order_date, 1, 4) = '2020' and price > 90
    )t1
  )t2
)t3
where 
  diff_lag1 = 0 or diff_lag1  = 1 or diff_lead1 = -1 or diff_lead1 = 0;

最后的结果如下：

name    order_date      price   code
掌蓉城  2020-06-01      103     01341433
掌蓉城  2020-06-02      103     01341433
源采文  2020-06-01      103     00174553
源采文  2020-06-02      103     00174553
碧千易  2020-06-13      93      02371493
碧千易  2020-06-13      93      02371493
酒博瀚  2020-06-02      101     00277553
酒博瀚  2020-06-03      101     00277553
酒博瀚  2020-06-04      101     00277553
齐钰    2020-06-01      101     02371493
齐钰    2020-06-02      101     02371493

Hive 1.2.1 窗口函数

1. 借鉴

2. 开始

数据准备

窗口函数

栗子

3. 大功告成

猜你喜欢

热点阅读