本文共 3385 字，大约阅读时间需要 11 分钟。

over() ：窗口函数，在括号中指定开窗条件，通常和聚合函数、排名函数一起使用。如果开窗条件为空，那么聚合的是过滤后的整张表。

开窗条件

PARTITION BY：指定分组条件。

排名函数

RANK()：排名有重复，总数不会变。

练习1

create table business(name string, orderdate string,cost int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';-- 数据如下jack,2017-01-01,10tony,2017-01-02,15jack,2017-02-03,23tony,2017-01-04,29jack,2017-01-05,46jack,2017-04-06,42tony,2017-01-07,50jack,2017-01-08,55mart,2017-04-08,62mart,2017-04-09,68neil,2017-05-10,12mart,2017-04-11,75neil,2017-06-12,80mart,2017-04-13,94

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

-- over()不写开窗条件，聚合的是过滤后的整张表SELECT name,count(*) over() cntFROM business WHERE substring(orderdate,1,7)='2017-04'GROUP BY name;

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

-- 按照月份分组，组内相加SELECT name,orderdate,cost,SUM(cost) OVER(PARTITION BY substring(orderdate,1,7)) mtotalFROM business;

• 总销售额

-- over()中不写开窗条件就是统计整张表SELECT name,orderdate,cost,SUM(cost) OVER() totalFROM business;

• 每个人的消费总额

-- 根据姓名分区，组内相加SELECT name,orderdate,cost,SUM(cost) OVER(PARTITION BY name) ev_costFROM business;

• 每个人截至到当前行的消费额累加

-- 按照姓名分组，从第一行到当前行累加SELECT name,orderdate,cost,SUM(cost) OVER(PARTITION BY name ORDER BY orderdate) ev_cur_costFROM business;SELECT name,orderdate,cost,SUM(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ev_cur_cost2FROM business;

• 每个人上一次购物与此次购物的总额

-- 按姓名分组，组内前一行和当前行相加SELECT name,orderdate,cost,SUM(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ev_pre_costFROM business;

• 每个人连续三次购物的总额

-- 按姓名分组，组内连续三次相加SELECT name,orderdate,cost,SUM(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ev_con_costFROM business;

• 每个人当前行与后面所有行的累计额

-- 按照姓名分组，当前行与后面所有行的累加SELECT name,orderdate,cost,SUM(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) ev_foll_costFROM business;

• 查看顾客上次的购买时间

-- 购买日期列当前行的前1行，只查看上次购买时间，不用分组SELECT name,orderdate,cost,LAG(orderdate,1) OVER(ORDER BY orderdate) lag_cost_timeFROM business;

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

-- 数据按照时间分为5组，取第一组的数据SELECT * FROM(SELECT name,orderdate,cost,NTILE(5) OVER(ORDER BY orderdate) nFROM business) tblWHERE n=1;

有哪些顾客连续两(或n)天来过我的店

-- 1.先给数据按姓名分组，按日期排序并加行号SELECT name,orderdate,ROW_NUMBER() OVER(PARTITION BY name ORDER BY orderdate) nFROM business;-- 2.日期与行号相减，如果连续两(或n)行结果一致，那么就是连续两(或n)天都来过SELECT *,DATE_SUB(orderdate,n) dsFROM (SELECT name,orderdate,ROW_NUMBER() OVER(PARTITION BY name ORDER BY orderdate) nFROM business) t1;-- 3.count聚合，大于等于2(或n)就是连续两(或n)天都来过SELECT name,count(*) cFROM (    SELECT *,DATE_SUB(orderdate,n) ds    FROM (        SELECT name,orderdate,        ROW_NUMBER() OVER(PARTITION BY name ORDER BY orderdate) n        FROM business) t1    )t2GROUP BY name,ds HAVING c>=2;

练习2

有如下数据：

name	subject	score孙悟空  语文    87孙悟空  数学    95孙悟空  英语    68大海    语文    94大海    数学    56大海    英语    84宋宋    语文    64宋宋    数学    86宋宋    英语    84婷婷    语文    65婷婷    数学    85婷婷    英语    78

• 计算每门学科成绩排名

SELECT subject,name,score,RANK() OVER(PARTITION BY subject ORDER BY score DESC) rk,DENSE_RANK() OVER(PARTITION BY subject ORDER BY score DESC) drk,ROW_NUMBER() OVER(PARTITION BY subject ORDER BY score DESC) rnmFROM score;

转载地址：https://blog.csdn.net/FlatTiger/article/details/113914134 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！