窗口函数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;开窗函数一般不单独使用而是跟另外一些函数一起使用比如 countover() 这个的窗口是多大呢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 子句关键字释义PRECEDING向前、前面的行FOLLOWING向后、后面的行CURRENT ROW当前这一行UNBOUNDED PRECEDING分区里第一行无限向前分区起点UNBOUNDED FOLLOWING分区里最后一行无限向后分区终点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中都支持窗口函数。