一、基础知识

我们常见的count()over;sum()over();avg()over()等，其中的count()\sum()\avg()是分析函数，over才是窗口函数。over()函数常与窗口函数()结合使用。

窗口函数的优势在于：在分组统计的同时，可以显示出所有信息，这也弥补了group by“只能显示一条记录”的缺陷。

下面从两方面进行学习，

1、over（）窗口函数语法结构
2、常与over（）一起使用的分析函数

1、over（）窗口函数语法结构

分析函数 over([partition by 列名 order by 列名 rows between 开始位置 and 结束位置])

其中，partition by 列名类似group by 的功能

2、常于over（）一起使用的分析函数

二、练习

准备工作：

create table test_zyq
(logday string,    
userid string, 
score int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

#利用shell脚本加载数据
20191020,11111,85
20191020,22222,83
20191020,33333,86
20191021,11111,87
20191021,22222,65
20191021,33333,98
20191022,11111,67
20191022,22222,34
20191022,33333,88
20191023,11111,99
20191023,22222,33

注意：创建表的时候不要用“#”加注释。

第一波练习：
1、使用 over() 函数进行数据统计, 统计用户明细及表中数据的总数
？其实这个题目刚开始没有看太明白，
“用户明细”是说查询后显示每个用户的信息,“表中数据总数”是sum所有记录条，
不用窗口函数的写法是分两步：
（1）查表中数据记录总数用count(1)

select count(1) as total from test_zyq;

(2)把每个用户和count结果连接起来

select * from test_zyq,(select count(1) as total from test_zyq) t;

而采用窗口函数可以一步到位：

select
*,
count(userid)over() as total
from test_zyq;

注意：此处的 over() 与 select count(1) 有相同的作用，而且over()函数内的部分可以为空。
2、求用户明细并统计每天的用户总数
既然又要有用户明细信息，又要求分组统计，选用窗口函数就可以一步到位。
“统计总数”--分析函数用count()，“每天”是分组的依据

select *,
count(userid)over(partition by logday)
from test_zyq;

3、计算从第一天到当前日期的所有 score 大于80分的用户总数
思路：需要用到rows...between...and...

“总数”--分析函数用cout();“第一天到当前日期” --排序依据（累加）

select *,
count(userid) over(order by logday rows between unbounded preceding and current row) as total
from test_zyq
where score>80;

知识点补充：

rows between 开始位置 and 结束位置
是指定窗口范围，比如第一行到当前行。而这个范围是随着数据变化的。over(rows between 开始位置 and 结束位置)搭配分析函数时，分析函数按照这个范围进行计算的。
窗口范围说明：
我们常使用的窗口范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行），常用该窗口来计算累加。
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点（一般结合PRECEDING，FOLLOWING使用）
UNBOUNDED PRECEDING 表示该窗口最前面的行（起点）
UNBOUNDED FOLLOWING：表示该窗口最后面的行（终点）
比如说：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行）
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING（表示往前2行到往后1行）
ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW（表示往前2行到当前行）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（表示当前行到终点）
窗口函数

4、计算每个用户到当前日期分数大于80的天数
"每个用户"--分组依据；;计算天数" ---count(logday) ； "到当前日期" rows...between..and..

select *,
count(logday) over(partition  by userid order by logday rows between unbounded preceding and current row) as total
from test_zyq
where score>80;

第二波练习：
数据准备：

create table business
(
name string, 
orderdate string,
cost int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

#数据
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


#加载数据
load  data local inpath "/home/frog005/business.txt" into table business;

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

select *,
count(1) over(partition by month(orderdate))
from business
where month(orderdate)=4;

或者：
筛选条件利用：
‘where substr(orderdate,1,7)='2017-04' ’
2、查询顾客的购买明细及月购买总额

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

可以在分组依据上加上name，如下：

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

3、查询顾客的购买明细及到目前为止每个顾客购买总金额

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

**4、查询顾客上次的购买时间
----lag()over()偏移量分析函数的运用,可用户计算上次购买时间
----lead()over()可计算下一次购买时间
语法：
lead(field, num, defaultvalue) field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值。

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

**5、查询前20%时间的订单信息

select *
from
(select *,
ntile(5)over(order by cost) a 
from business) t 
where t.a=1;

Hive窗口函数基础及练习

Hive窗口函数基础及练习

一、基础知识

1、over（）窗口函数语法结构

2、常于over（）一起使用的分析函数

二、练习