05窗口函数

窗口函数

简介

oracle，sqlserver以及mysql8.x+都支持窗口函数

窗口：函数运行时的计算数据集范围

函数：运行的函数

• 统计类函数
  • min
  • max
  • avg
  • sum
  • count
• windowing functions
  • LEAD LEAD(scalar_expression [,offset] [,default]) 返回当前行一下N行指定列（scalar_expression ）的值，找不到采用默认值
  • LAG LAG(scalar_expression [,offset] [,default]) 返回当前行以上N行的指定列的列值,如果找不到，就采用默认值
  • FIRST_VALUE: FIRST_VALUE(列名,[false(默认)]) 返回当前窗口指定列的第一个值，第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找！
  • LAST_VALUE:LAST_VALUE(列名,[false(默认)])返回当前窗口指定列的最后一个值，第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找！
• 排名分析函数
  • RANK：允许并列，一旦有并列跳号！
  • ROW_NUMBER：行号！ 连续的，每个号之间差1！
  • DENSE_RANK：允许并列，一旦有并列不跳号！
  • CUME_DIST：从排序后的第一行到当前值之间数据 占整个数据集的百分比！
  • PERCENT_RANK： rank-1/ 总数据量-1
  • NTILE： 将数据集均分到X个组中，返回每条记录所在的组号

格式：函数 over( partition by 字段 ,order by 字段 window_clause )

window_clause :

指定窗口大小

(rows | range) between (unbounded | [num]) preceding and ([num] preceding | current row | (unbounded | [num]) following)
(rows | range) between current row and (current row | (unbounded | [num]) following)
(rows | range) between [num] following and (unbounded | [num]) following)

特殊情况
①在over()中既没有出现windows_clause，也没有出现order by，窗口默认为rows between UNBOUNDED  PRECEDING and UNBOUNDED  FOLLOWING
②在over()中(没有出现windows_clause)，指定了order by，窗口默认为rows between UNBOUNDED  PRECEDING and CURRENT ROW

使用ROWS选项你定义当前行的固定前后记录。这里你看到的行取决于窗口的ORDER BY从句。你也可以说你在物理级别定义你的窗口。
使用RANGE选项你在逻辑级别定义你的窗口。如果更多的行有同个ORDER BY值，则相同的ORDER BY 有相同的结果。 

窗口函数和分组有什么区别？

1. 如果是分组操作，select后只能写分组后的字段
2. 如果是窗口函数，窗口函数是在指定的窗口内，对每条记录都执行一次函数
3. 如果是分组操作，有去重效果，而partition不去重！

Demo windowing functions

准备数据

数据

jack,2019-01-01,10
tony,2019-01-02,15
tony,2019-01-04,29
jack,2019-01-05,46
jack,2019-04-06,42
tony,2019-01-07,50
jack,2019-01-08,55
mart,2019-04-08,62
mart,2019-04-09,68
neil,2019-05-10,12
mart,2019-04-11,75
neil,2019-06-12,80
mart,2019-04-13,94

创建表&导入数据

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

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

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

select name,count(*) over(rows between UNBOUNDED  PRECEDING and UNBOUNDED  FOLLOWING)
from business
where substring(orderdate,1,7)='2019-04'
group by name

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

with tmp as (select name
from business
where year(orderdate)=2017 and month(orderdate)=4
group by name)
select tmp.name,tmp1.totalcount
from
(select count(*) totalcount
from tmp ) tmp1 join tmp;

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

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

查询顾客的购买明细要将cost按照日期进行累加

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

查询顾客的购买明细及顾客上次的购买时间

select name,orderdate,cost,lag(orderdate,1,'无数据') over(partition by name order by orderdate ) 
 from business

查询顾客的购买明细及顾客本月第一次购买的时间

select name,orderdate,cost,FIRST_VALUE(orderdate,true) over(partition by name,substring(orderdate,1,7) order by orderdate ) 
 from business

查询顾客的购买明细及顾客最近三次cost花费

--当前和之前两次:
   select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between 2 PRECEDING and CURRENT  row) 
 from business 
 
--当前+前一次+后一次: 
 select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and 1  FOLLOWING) 
 from business
 --或
 select name,orderdate,cost,cost+
 lag(cost,1,0) over(partition by name order by orderdate )+
 lead(cost,1,0) over(partition by name order by orderdate )
 from business

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

select *
from
(select name,orderdate,cost,cume_dist() over(order by orderdate ) cdnum
from  business) tmp
where cdnum<=0.2

Demo 排名函数

准备数据

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;

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

按照科目进行排名

select *,rank() over(partition by subject order by score desc)
from score

给每个学生的总分进行排名

select name,sumscore,rank()  over( order by sumscore desc)
from
(select name,sum(score) sumscore
from  score
group by  name) tmp

求每个学生的成绩明细及给每个学生的总分和总分排名

select *,DENSE_RANK() over(order by tmp.sumscore desc)
from
(select *,sum(score) over(partition by name)  sumscore
from score) tmp

只查询每个科目的成绩的前2名

select *
from
(select *,rank() over(partition by subject order by score desc) rn
from score) tmp
where rn<=2

查询学生成绩明细，并显示当前科目最高分

select *,max(score) over(partition by subject)
from score

select *,FIRST_VALUE(score) over(partition by subject order by score desc)
from score