Hive-窗口函数/开窗函数(重点理解~~~)

秒速五厘米 2022-08-28 05:53 450阅读 0赞

### 思路:通过开窗函数进行分组,然后实现topN的排序 ###

*  OVER函数
 *   *  按需求查询数据:
     *   *  （1）查询在 2017 年 4 月份购买过的顾客及总人数
         *  （2） 查询顾客的购买明细及月购买总额
         *  （3）将每个顾客的 cost 按照日期进行累加
         *  （4）查看顾客上次的购买时间
         *  （5） 查询前 20%时间的订单信息
 *  RANK函数
 *   *  使用rank()函数进行排序:
     *  使用DESENS\_RANK():
     *  使用ROW\_NUMBER():

# OVER函数 #

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

`CURRENT ROW`：当前行  
`n PRECEDING`：往前 n 行数据  
`n FOLLOWING`：往后 n 行数据  
`UNBOUNDED`：起点，  
`CURRENT ROW`：当前行  
`n PRECEDING`：往前 n 行数据  
`n FOLLOWING`：往后 n 行数据  
`UNBOUNDED`：起点，  
`UNBOUNDED PRECEDING` : 表示从前面的起点，  
`UNBOUNDED FOLLOWING` :表示到后面的终点  
`LAG(col,n,default_val)`：往前第 n 行数据  
`LEAD(col,n, default_val)`：往后第 n 行数据

数据准备:

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 
    mart,2017-04-08,62 
    mart,2017-04-09,68 
    neil,2017-05-10,12 
    mart,2017-04-11,75 
    neil,2017-06-12,80 
    mart,2017-04-13,94

创建business表:

create table business(
    name string,
    orderdate string,
    cost string
    )
    row format delimited fields terminated by ',';

将数据导入到business表当中:

load data local inpath '/opt/module/datas/business.txt'
    overwrite into table business;

查看一下数据:

hive (default)> select * from business;
    OK
    business.name	business.orderdate	business.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 
    mart	2017-04-08	62 
    mart	2017-04-09	68 
    neil	2017-05-10	12 
    mart	2017-04-11	75 
    neil	2017-06-12	80 
    mart	2017-04-13	94
    Time taken: 0.335 seconds, Fetched: 14 row(s)

## 按需求查询数据: ##

### （1）查询在 2017 年 4 月份购买过的顾客及总人数 ###

**思考:做这道题的思路是,要取出2017年4月份,购买过的顾客,也就是name,最后再求总人数,求总人数的话是想到使用count(*)来计算,而name字段是有多行,count(*)只有一行,因此要将他们一同输出想到的就是使用开窗函数。  
然后求顾客的话,要对他们去重,在开窗函数中根据name进行分区,然后where条件筛选出2017年4月即可。**  
在求时间的时候,需要用到substring函数,因此,查看substring方法的具体用法。

hive (default)> desc function substring;
    OK
    tab_name
    substring(str, pos[, len]) - returns the substring of str that starts at pos and is of length len orsubstring(bin, pos[, len]) - returns the slice of byte array that starts at pos and is of length len
    Time taken: 0.093 seconds, Fetched: 1 row(s)

截取字符串,start定义为pos,截取长度为len。

hive (default)> select substring(orderdate,0,7) from business;
    OK
    _c0
    2017-01
    2017-01
    2017-02
    2017-01
    2017-01
    2017-04
    2017-01
    2017-01
    2017-04
    2017-04
    2017-05
    2017-04
    2017-06
    2017-04
    Time taken: 0.218 seconds, Fetched: 14 row(s)

如果是用以下的sql的话

select 
        name,
        count(*) 
    from business 
    where substring(orderdate,0,7)='2017-04'
    group by name;

他所得出的结果是根据name的个数来进行计算的,结果如下:

OK
    name	_c1
    jack	1
    mart	4

这个结果和我们题目中的需求不相符,我们需要的是,前面是name,后面是加总人数,在这里只要稍作修改,就能扭转结果,如下:

select 
        name,
        count(*) over()
    from business 
    where substring(orderdate,0,7)='2017-04'
    group by name;

结果:

name	count_window_0
    jack	2
    mart	2

**在这里的话,我们知道over()函数也可以和group by 联合使用。**  
这里有一点也是需要注意的,很多人会想到在上面进行分区的时候,如果直接在over()函数中写paritition by name的话,似乎也是可以进行分组的。这里要解释一下,如果那么做的话,结果是没有去重,按照上面的语句执行:

select 
        name,
        count(*) over(partition by name)
    from business 
    where substring(orderdate,0,7)='2017-04';

结果:

OK
    name	count_window_0
    jack	1
    mart	4
    mart	4
    mart	4
    mart	4
    Time taken: 49.564 seconds, Fetched: 5 row(s)

很明显,上面的结果比较冗余。

这里通过添加over()就将结果改变了,深入理解over的用法,over他的这个用法的话,相当于在name的后面进行开窗,每个name都开窗,本来name有14行,count(\*)只有一行,现在count也有14行.注意一下,over()括号里面没有添加任何字段的时候,每行的数据都有一样的窗口大小。

### （2） 查询顾客的购买明细及月购买总额 ###

第一步,计算出顾客的购买明细以及购买总额:

select 
        name,
        orderdate,
        cost,
        sum(cost) over(partition by name)
    from business;

第二部计算出顾客的购买明细以及月购买总额:

select 
        name,
        orderdate,
        cost,
        sum(cost) over(partition by name,month(orderdate))
    from business;

结果如下:

OK
    name	orderdate	cost	sum_window_0
    jack	2017-01-01	10 	111.0
    jack	2017-01-05	46 	111.0
    jack	2017-01-08	55 	111.0
    jack	2017-02-03	23 	23.0
    jack	2017-04-06	42 	42.0
    mart	2017-04-13	94	299.0
    mart	2017-04-08	62 	299.0
    mart	2017-04-09	68 	299.0
    mart	2017-04-11	75 	299.0
    neil	2017-05-10	12 	12.0
    neil	2017-06-12	80 	80.0
    tony	2017-01-04	29 	94.0
    tony	2017-01-07	50 	94.0
    tony	2017-01-02	15 	94.0

### （3）将每个顾客的 cost 按照日期进行累加 ###

select 
        name,
        orderdate,
        cost,
        sum(cost) over(partition by name order by orderdate)
    from business;

结果如下:

OK
    name	orderdate	cost	sum_window_0
    jack	2017-01-01	10 	10.0
    jack	2017-01-05	46 	56.0
    jack	2017-01-08	55 	111.0
    jack	2017-02-03	23 	134.0
    jack	2017-04-06	42 	176.0
    mart	2017-04-08	62 	62.0
    mart	2017-04-09	68 	130.0
    mart	2017-04-11	75 	205.0
    mart	2017-04-13	94	299.0
    neil	2017-05-10	12 	12.0
    neil	2017-06-12	80 	92.0
    tony	2017-01-02	15 	15.0
    tony	2017-01-04	29 	44.0
    tony	2017-01-07	50 	94.0

根据name 进行分区,然后每一行的开窗,都是从第一行开到当前行,进行一个累加。除了上面那种写法还有另外一种写法,因为上面在order by 之后没有指定窗口,默认值的话就是第一行开窗到当前行, 相当于对上面那种写法的补全:

select 
        name,
        orderdate,
        cost,
        sum(cost) over(partition by name order by orderdate rows 
            between unbounded preceding and current row)
    from business;

结果如下:

注意:如果over()后面接的是空的话,可以理解成对每一行都元素都开窗了,用下面这个例子来理解:

//建表导数据
    create table num(id string);
    load data local inpath '/opt/module/datas/num.txt' into table num; 
    
    hive (default)> select * from num;
    OK
    num.id
    1
    2
    3
    3
    4
    5
    
    hive (default)> select id,sum(id) over() from num;
    //查询结果:
    id	sum_window_0
    1	18.0
    2	18.0
    3	18.0
    3	18.0
    4	18.0
    5	18.0
    
    
    hive (default)> select id,sum(id) over(order by id) from num;
    OK
    id	sum_window_0
    1	1.0
    2	3.0
    3	9.0
    3	9.0
    4	13.0
    5	18.0
    //注意:这里出现两个id为3的情况,进行开窗的时候,设置相同大小

如果排序中遇到两个id都是一样的,在开窗的时候,值为一样的会被统一默认是一样

### （4）查看顾客上次的购买时间 ###

通过使用lag函数可以选取之前的数据,lag函数有三个参数,字段,往前多少行,默认值,下面这个例子加深理解:

select 
        name,
        orderdate,
        cost,
        lag(orderdate,1) over(partition by name order by orderdate)
    from business;

结果如下:

OK
    name	orderdate	cost	lag_window_0
    jack	2017-01-01	10 	NULL
    jack	2017-01-05	46 	2017-01-01
    jack	2017-01-08	55 	2017-01-05
    jack	2017-02-03	23 	2017-01-08
    jack	2017-04-06	42 	2017-02-03
    mart	2017-04-08	62 	NULL
    mart	2017-04-09	68 	2017-04-08
    mart	2017-04-11	75 	2017-04-09
    mart	2017-04-13	94	2017-04-11
    neil	2017-05-10	12 	NULL
    neil	2017-06-12	80 	2017-05-10
    tony	2017-01-02	15 	NULL
    tony	2017-01-04	29 	2017-01-02
    tony	2017-01-07	50 	2017-01-04
    Time taken: 99.989 seconds, Fetched: 14 row(s)

我如果lag函数没有传入默认值的话,如果遇到NULL,则输出NULL.  
想把NULL做修改,如果遇到的值为NULL,则修改为默认值,可以用下面这个hive sql:

select 
        name,
        orderdate,
        cost,
        lag(orderdate,1,'1990-01-01') over(partition by name order by orderdate)
    from business;

结果如下,如果遇到的是null,则输出默认值:

name	orderdate	cost	lag_window_0
    jack	2017-01-01	10 	1990-01-01
    jack	2017-01-05	46 	2017-01-01
    jack	2017-01-08	55 	2017-01-05
    jack	2017-02-03	23 	2017-01-08
    jack	2017-04-06	42 	2017-02-03
    mart	2017-04-08	62 	1990-01-01
    mart	2017-04-09	68 	2017-04-08
    mart	2017-04-11	75 	2017-04-09
    mart	2017-04-13	94	2017-04-11
    neil	2017-05-10	12 	1990-01-01
    neil	2017-06-12	80 	2017-05-10
    tony	2017-01-02	15 	1990-01-01
    tony	2017-01-04	29 	2017-01-02
    tony	2017-01-07	50 	2017-01-04

本来第一行数据的最后一个字段应该是为null,现在则输出了默认值。

如果将默认值,写成字段本身,那么遇到NULL,则输出自己:

select 
        name,
        orderdate,
        cost,
        lag(orderdate,1,orderdate) over(partition by name order by orderdate)
    from business;

输出结果:

OK
    name	orderdate	cost	lag_window_0
    jack	2017-01-01	10 	2017-01-01
    jack	2017-01-05	46 	2017-01-01
    jack	2017-01-08	55 	2017-01-05
    jack	2017-02-03	23 	2017-01-08
    jack	2017-04-06	42 	2017-02-03
    mart	2017-04-08	62 	2017-04-08
    mart	2017-04-09	68 	2017-04-08
    mart	2017-04-11	75 	2017-04-09
    mart	2017-04-13	94	2017-04-11
    neil	2017-05-10	12 	2017-05-10
    neil	2017-06-12	80 	2017-05-10
    tony	2017-01-02	15 	2017-01-02
    tony	2017-01-04	29 	2017-01-02
    tony	2017-01-07	50 	2017-01-04
    Time taken: 39.097 seconds, Fetched: 14 row(s)

第一行的数据本来是为null,现在输出的他本身。

那么lead函数,就是往后n行:

select 
        name,
        orderdate,
        cost,
        lead(orderdate,1,orderdate) over(partition by name order by orderdate)
    from business;

最后一行的结果为null,输出自己,结果如下:

OK
    name	orderdate	cost	lead_window_0
    jack	2017-01-01	10 	2017-01-05
    jack	2017-01-05	46 	2017-01-08
    jack	2017-01-08	55 	2017-02-03
    jack	2017-02-03	23 	2017-04-06
    jack	2017-04-06	42 	2017-04-06
    mart	2017-04-08	62 	2017-04-09
    mart	2017-04-09	68 	2017-04-11
    mart	2017-04-11	75 	2017-04-13
    mart	2017-04-13	94	2017-04-13
    neil	2017-05-10	12 	2017-06-12
    neil	2017-06-12	80 	2017-06-12
    tony	2017-01-02	15 	2017-01-04
    tony	2017-01-04	29 	2017-01-07
    tony	2017-01-07	50 	2017-01-07
    Time taken: 13.057 seconds, Fetched: 14 row(s)

### （5） 查询前 20%时间的订单信息 ###

通过ntile函数对数据进行分组,要取前20%,分成5组,每一组就是20%  
ntile(5)表示将数据数据分成5组:

select 
        name,
        orderdate,
        cost,
        ntile(5) over( order by orderdate) groupId
    from business;

结果如下:

name	orderdate	cost	groupid
    jack	2017-01-01	10 	1
    tony	2017-01-02	15 	1
    tony	2017-01-04	29 	1
    jack	2017-01-05	46 	2
    tony	2017-01-07	50 	2
    jack	2017-01-08	55 	2
    jack	2017-02-03	23 	3
    jack	2017-04-06	42 	3
    mart	2017-04-08	62 	3
    mart	2017-04-09	68 	4
    mart	2017-04-11	75 	4
    mart	2017-04-13	94	4
    neil	2017-05-10	12 	5
    neil	2017-06-12	80 	5

以上是分组的结果,再选取出前20%:

select 
        name,
        orderdate,
        cost
    from 
    (select 
        name,
        orderdate,
        cost,
        ntile(5) over( order by orderdate) groupId
    from business)t1
    where groupId=1;

前20%,输出结果如下:

OK
    name	orderdate	cost
    jack	2017-01-01	10 
    tony	2017-01-02	15 
    tony	2017-01-04	29

如果要取中间的20%也行。

# RANK函数 #

RANK() 排序相同时会重复，总数不会变  
DENSE\_RANK() 排序相同时会重复，总数会减少  
ROW\_NUMBER() 会根据顺序计算.  
RANK函数后面必须要跟着OVER.

数据准备 vim scoretxt:

孙悟空	语文	87
    孙悟空	数学	95
    孙悟空	英语	68
    大海	语文	94
    大海	数学	56
    大海	英语	84
    宋宋	语文	64
    宋宋	数学	86
    宋宋	英语	84
    婷婷	语文	65
    婷婷	数学	85
    婷婷	英语	78

创建表导入数据:

create table score (
    name string,
    subject string,
    score int
    )
    row format delimited fields terminated by '\t';
    
    load data local inpath '/opt/module/datas/score.txt'
    into table score;

## 使用rank()函数进行排序: ##

hive (default)> select *,rank() over(order by score) from score;

查询结果:

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm6YeMQ29kaW5n_size_20_color_FFFFFF_t_70_g_se_x_16]  
上面的查询结果,认真观察就会注意到遇到相同的,排名第6就会并排,没有排名第7.

## 使用DESENS\_RANK(): ##

hive (default)> SELECT *,DENSE_rank() over(order by score) FROM score;

查询结果如下:  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm6YeMQ29kaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 1]

注意到了,总共是抓取12行,但是其排名也就11行

## 使用ROW\_NUMBER(): ##

SELECT *,ROW_NUMBER() over(order by score) FROM score;

查询结果:

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm6YeMQ29kaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 2]

根据各个学科的成绩来进行排序:

SELECT *,ROW_NUMBER() over(partition by subject order by score) FROM score;

查询结果:![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm6YeMQ29kaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 3]  
需求:取每个学科的前三名:

第一步,对每个学科进行排名:

SELECT *,
    ROW_NUMBER() over(partition by subject order by score desc )  rk
    FROM score;

再根据rk字段取排名前三的:

select 
        name,
        subject,
        score
    from 
    (SELECT *,
    ROW_NUMBER() over(partition by subject order by score desc )  rk
    FROM score)t1
    where rk<=3;

查询结果:  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm6YeMQ29kaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 4]  
这样每个学科取得了前三名。

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm6YeMQ29kaW5n_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/86523fb6ea03445b917d067d66e758bc.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm6YeMQ29kaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/6ebe0a1e1629440983e22a5d666d9f51.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm6YeMQ29kaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220828/88ad53b28c574845817077d395d62ff9.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm6YeMQ29kaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 3]: /images/20220828/4cba84fca6bf45ea9f03e328f4d3fdf0.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm6YeMQ29kaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 4]: /images/20220828/38b73794a7df4ef28f1379e721d2008c.png