窗口函数
窗口函数over简介
先来看一下这个需求:求每个部门的员工信息以及部门的平均工资。在mysql中如何实现呢
求部门平均工资 select deptno ,avg(sal) from emp group by deptno;
查看所有员工信息 select * from emp
如何将这两个记过进行信息绑定呢? 使用两个语句进行子查询或join
select * from emp join(
> select deptno ,avg(sal) from emp group by deptno
> )s on emp.deptno = s.deptno;
通过这个需求我们可以看到,如果要查询详细记录和聚合数据,必须要经过两次查询,比较麻烦。
-1) 窗口函数又名开窗函数,属于分析函数的一种。
-2) 是一种用于解决复杂报表统计需求的函数。
-3) 窗口函数常用于计算基于组的某种值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。
-4) 开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化。
-5) 窗口函数一般不单独使用
-6) 窗口函数内也可以分组和排序
参考下图:
**注意:**默认mysql老版本没有支持,在最新的8.0版本中支持, Oracle和Hive中都支持窗口函数
基本案例演示
数据准备(order.txt)
姓名,购买日期,购买数量
-1. 创建order表:
create table if not exists t_order
(
name string,
orderdate string,
cost int
) row format delimited fields terminated by ',';
-2. 加载数据:
load data local inpath "./data/order.txt" into table t_order;
需求:查询每个订单的信息,以及订单的总数
– 1.不使用窗口函数
-- 查询所有明细
select * from t_order;
# 查询总量
select count(*) from t_order;
– 2.使用窗口函数:通常格式为 可用函数+over()函数
select *, count(*) over() from t_order;
注意:
窗口函数是针对每一行数据的.
如果over中没有指定参数,默认窗口大小为全部结果集
需求:查询在2018年1月份购买过的顾客购买明细及总人数
select *,count(*) over()
from t_order
where substring(orderdate,1,7) = '2018-01';
distribute by子句
在over窗口中进行分组,对某一字段进行分组统计,窗口大小就是同一个组的所有记录
语法:
over(distribute by colname[,colname.....])
需求:查看顾客的购买明细及月购买总额
select name, orderdate, cost, sum(cost) over (distribute by month(orderdate))
from t_order;
saml 2018-01-01 10 205
saml 2018-01-08 55 205
tony 2018-01-07 50 205
saml 2018-01-05 46 205
tony 2018-01-04 29 205
tony 2018-01-02 15 205
saml 2018-02-03 23 23
mart 2018-04-13 94 341
saml 2018-04-06 42 341
mart 2018-04-11 75 341
mart 2018-04-09 68 341
mart 2018-04-08 62 341
neil 2018-05-10 12 12
neil 2018-06-12 80 80
需求:查看顾客的购买明细及每个顾客的月购买总额
select name, orderdate, cost, sum(cost) over (distribute by name, month(orderdate))
from t_order;
mart 2018-04-13 94 299
mart 2018-04-11 75 299
mart 2018-04-09 68 299
mart 2018-04-08 62 299
neil 2018-05-10 12 12
neil 2018-06-12 80 80
saml 2018-01-01 10 111
saml 2018-01-08 55 111
saml 2018-01-05 46 111
saml 2018-02-03 23 23
saml 2018-04-06 42 42
tony 2018-01-07 50 94
tony 2018-01-04 29 94
tony 2018-01-02 15 94
sort by子句
sort by子句会让输入的数据强制排序 (强调:当使用排序时,窗口会在组内逐行变大)
语法: over([distribute by colname] [sort by colname [desc|asc]])
需求:查看顾客的购买明细及每个顾客的月购买总额,并且按照日期降序排序
select name, orderdate, cost,
sum(cost) over (distribute by name, month(orderdate) sort by orderdate desc)
from t_order;
注意:可以使用partition by + order by 组合来代替distribute by+sort by组合
select name, orderdate, cost,
sum(cost) over (partition by name, month(orderdate) order by orderdate desc)
from t_order;
注意:也可以在窗口函数中,只写排序,窗口大小是全表记录。
select name, orderdate, cost,
sum(cost) over (order by orderdate desc)
from t_order;
neil 2018-06-12 80 80 -统计信息会逐行增加
neil 2018-05-10 12 92
mart 2018-04-13 94 186
mart 2018-04-11 75 261
mart 2018-04-09 68 329
mart 2018-04-08 62 391
saml 2018-04-06 42 433
saml 2018-02-03 23 456
saml 2018-01-08 55 511
tony 2018-01-07 50 561
saml 2018-01-05 46 607
tony 2018-01-04 29 636
tony 2018-01-02 15 651
saml 2018-01-01 10 661
Window子句
如果要对窗口的结果做更细粒度的划分,那么就使用window子句,常见的有下面几个
current row :当前行
n preceding(破c定) : (n行数) 往前n行数据
n following : (n行数) 往后n行数据
unbounded(安邦得德): 起点状态
起点: unbounded pereceding 表示从前面的起点
终点: unbounded pereceding 表示到后面的终点
一般window子句都是rows开头
案例:
select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,-- 按name分组,组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,-- 按name分组,组内数据累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,-- 与sample3一样,由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, -- 当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,-- 当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 -- 当前行及后面所有行
from t_order;
需求:查看顾客到目前为止的购买总额
select name,
t_order.orderdate,
cost,
sum(cost)
over (partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as allCount
from t_order;
mart 2018-04-08 62 62
mart 2018-04-09 68 130
mart 2018-04-11 75 205
mart 2018-04-13 94 299
neil 2018-05-10 12 12
neil 2018-06-12 80 92
saml 2018-01-01 10 10
saml 2018-01-05 46 56
saml 2018-01-08 55 111
saml 2018-02-03 23 134
saml 2018-04-06 42 176
tony 2018-01-02 15 15
tony 2018-01-04 29 44
tony 2018-01-07 50 94
需求:求每个顾客最近三次的消费总额
select name,orderdate,cost,
sum(cost) over(partition by name order by orderdate rows between 2 preceding and current row)
from t_order;