企业官网建设流程全解析

窗口函数over

如果一个查询既要明细信息，也要聚合信息，可以直接使用开窗函数

姓名,购买日期,购买数量

saml,2018-01-01,10 saml,2018-01-08,55 tony,2018-01-07,50 tony,2018-01-04,29 tony,2018-01-02,15 mart,2018-04-13,94 mart,2018-04-08,62

创建order表:

create table if not exists t_order ( name string, orderdate string, cost int ) row format delimited fields terminated by ',';

加载数据:

load data local inpath "/home/hivedata/order.txt" into table t_order;

需求1：查询订单明细，以及订单数总数

--不使用开窗函数的写法 select *,(select count(1) from t_order) as `订单总数` from t_order ; 使用开窗函数的写法： select *, count(*) over() from t_order;

开窗函数一般不单独使用，而是跟另外一些函数一起使用，比如 count

over() 这个的窗口是多大呢？over() 是整个数据集。

窗口其实就是范围，比如统计男女比例？必须知道窗口，是统计整个班级还是统计整个学校

窗口函数是针对每一行数据的.
如果over中没有指定参数,默认窗口大小为全部结果集

需求2：查询在2018年1月份，顾客购买明细及总人数。

select *,count(distinct name) over() from t_order where substr(orderdate,1,7) = '2018-01'; -- 假如使用group by select *,(select count(1) from ( select name from t_order where substr(orderdate,1,7) ='2018-01' group by name) t) from t_order where substr(orderdate,1,7) ='2018-01'; 结论是：太麻烦了

partition by 子句

在over窗口中进行分组,对某一字段进行分组统计,窗口大小就是同一个组的所有记录

语法： over(partition by colname[,colname.....])

需求3：查看顾客的购买明细及月购买总额

错误写法：明细信息是不能跟聚合函数一起使用的，聚合多个信息变一个。

select *,sum(cost) from t_order;

使用开窗+分组

select *,sum(cost) over(partition by substr(orderdate,1,7)) from t_order;

底层原理： 先查询到第一条数据 tony 2018-01-02 15 然后进行sum统计，统计的窗口是substr(orderdate,1,7)这个分组下的所有记录。 接着查询第二条数据 xxxxx 然后进行sum统计，统计的窗口是substr(orderdate,1,7)这个分组下的所有记录 依次类推。

order by子句

order by子句排序 （强调：当使用排序时，窗口为分区首行到当前行（累积窗口））

语法： over([partition by colname] [order by colname [desc|asc]])

需求4：查看顾客的购买明细及每个顾客的月购买总额,并且按照日期降序排序

select *, sum(cost) over(partition by name,month(orderdate) order by orderdate desc) from t_order ;

注意：也可以在窗口函数中，只写排序，窗口大小是全表记录

select *,sum(cost) over(order by orderdate desc ) from t_order ;

窗含函数总结

OVER()：无分区无排序 → 全表一个窗口，全部行
OVER(PARTITION BY a)：有分区无排序 → 每个分区为完整窗口，分区内全部行
OVER(PARTITION BY a ORDER BY b)：分区 + 排序 → 分区内，默认窗口：分区首行到当前行（累积窗口）
OVER(ORDER BY b)：无分区、仅排序 → 全表作为一个分区，首行到当前行累积

over 里面只要出现 order by，就会触发 “到当前行” 的滑动帧；没有 order by 的时候窗口就是整个分区 / 整张表。

window 子句

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 *, sum(cost) over(partition by name order by orderdate rows between 2 preceding and current row) from t_order;

注意：默认mysql老版本没有支持,在最新的8.0版本中支持, Oracle和Hive中都支持窗口函数。