5.窗口函数

 数据准备：

-- 数据准备
create table business(
    name string,        -- 顾客
    orderdate string,  -- 下单日期
    cost int             -- 购买金额
) 
row format delimited fields terminated by ',';

load data local inpath "/opt/module/hive/datas/business.txt" 
into table business;

   

查询2017年4月购买过的顾客：

select distinct name from business 
where substring(orderdate, 1,7) = '2017-04'; //取1到7位下标

  如果想求2017年4月购买过的人数：

select count(distinct name)
from business
where substring(orderdate,1,7) = "2017-04";

1.over()：给聚合函数开窗口

ps.在明细查询中进行汇总，必须使用窗口函数！

如果希望将购买过的人和购买过的人次放在一张表里面展示：

select distinct name,count(distinct name) over() // 什么都不写就代表全表
from business
where substring(orderdate, 1, 7)='2017-04';

ps.count()要么跟group by结合，要么跟开窗函数结合 

查询顾客的购买明细及月购买总额：

也就是按月求下汇总

select name,
       orderdate,
       cost,
       sum(cost) over(partition by substring(orderdate, 1, 7)) month_total
from business;

查询每个顾客的截止到当日的累计消费：

select name,
       orderdate,
       cost,
       sum(cost) over(partition by name order by orderdate 
                      rows between unbounded preceding and current row),--累加
       sum(cost) over(partition by name order by orderdate 
                      rows between 1 preceding and current row) --最近两次购买
from business;

解释：首先

select * from business order by name, orderdate;

获得

                         

然后在这个表的基础上新添一行，求累计消费

因为是求每个顾客，所以是partition by name;

求累计消费，所以是order by orderdate；

order by 经常rows between and 连用

ps.这里的开窗函数表示给每行单独开窗口

CURRENT ROW	当前行
n PRECEDING	往前n行数据
n FOLLOWING	往后n行数据
UNBOUNDED	起点
UNBOUNDED PRECEDING	表示从前面的起点（unbounded 表示无界）
UNBOUNDED FOLLOWING	表示到后面的终点

求购买明细和截至当日来过店的顾客

select name, orderdate, cost,
       collect_set(name) over(order by orderdate 
                             rows between UNBOUNDED preceding and current row)
from business;

2.其他结合有序窗口使用函数

2.1 lag && lead

使用方法：lag() over() as 别名

-- lag(col, n, default_value) over(有序窗口)
-- 显示col这一列n行之前的数据，如果没有，展示default_value

-- lead(col, n, default_value) over(有序窗口)
-- 显示col这一列n行之后的数据，如果没有，展示default_value

 查询购买明细和每个人上一次的到店时间

select name, orderdate, cost,
       lag(orderdate, 1, '1970-01-01') 
           over(partition by name order by orderdate) as last_order,
       lead(orderdate, 1, '2050-01-01') 
           over(partition by name order by orderdate) as next_order
from business;

2.2  ntile

使用方式：

-- 将数据分为n组，返回当前行的组号
-- ntile(n) over(有序窗口)

例如：

订单明细按照订单时间分为5组：

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

查看前20%

-- 在上表的基础上
select name, orderdate, cost
from t1 where zu=1;

2.3 rank&&dense_rank&&row_number

使用方法：

-- 排序
rank() over (有序窗口)
dense_rank() over (有序窗口)
row_number() over (有序窗口)

数据准备：

求各科成绩排名

select name,subject,score,
       rank() over(partition by subject order by score desc) `rank`,
       dense_rank() over(partition by subject order by score desc) `d_r`,
       row_number() over(partition by subject order by score desc) `r_n`
from score;

可以看出三种排序的不同：

 在上表基础上，查询各科的前三名

select name,subject,score
from t1
where `rank`<=3;

2.4 first_value && last_value 

-- 使用方法
frist_value(col,boolean) over(有序窗口) //返回这一列的第一行，如果为true，则忽略null
last_value(col,boolean) over(有序窗口)

 以下表为例：

                      

 查询表明细，以及截至日期当日，每人的第一笔和最后一笔大于50元的消费日期

查询结果：

                                 

总结：在以下两种场景下需要使用开窗：

1.在明细查询中展示汇总结果

2.结合其他函数使用（如lag,lead,rank等） 

3.练习题

数据准备：

     

 求每个品类消费排名的前10：

同样是使用子查询：

// 在原来的表格上新加一行命名为rnk
select id, category_id,sales_sum
          rank() over(partition by category_id order by sales_sum desc ) rnk
from sales 

select category_id
from  (
select id, category_id,sales_sum
       rank() over(partition by category_id order by sales_sum desc ) rnk
from sales 
) t1
where rnk<=10

 求各品类  销量前10的商品列表  以及前10商品的销售总额