![c9fb416a302e1aab1535862e64d3719d.png](https://i-blog.csdnimg.cn/blog_migrate/dad3a3e6d2cd3beb4ab02a75ad923cf2.jpeg)
有以下数据:字段名为:name、orderdate、cost
Jack,2017-01-01,10
Tony,2017-01-02,15
Jack,2017-02-03,23
Tony,2017-01-04,29
Jack,2017-01-05,46
Jack,2017-04-06,42
Tony,2017-01-07,50
Jack,2017-01-08,55
Mark,2017-04-08,62
Mart,2017-04-09,68
Meil,2017-05-10,12
Mart,2017-04-11,75
Meil,2017-06-12,80
Mart,2017-04-13,94
需 求
1、查询2017-04购买的顾客总人数
2、顾客购买明细及月份总额
3、上述场景,将cost按日期累加
4、查询顾客上次购买时间
5、查询前20%购买的订单信息
一、建表并导入数据:
-- 建表
--导入数据
load data local inpath "/business.txt" into table business;
-- 查询表:
select * from business;
![2a6b91ea718c1f8909627e92d7aef68a.png](https://i-blog.csdnimg.cn/blog_migrate/3ca074f092e337740e24aba00a1ad07b.jpeg)
需求分析
一、查询2017-04购买的顾客总人数
a、首先想到使用聚合函数count()
-- 先求出2017-04这月一共有多少条记录
select count(*) from business where substr(orderdate,1,7) = "2017-04";
结果如下图:
![1ad1e7f167c1a4420677a1c890667217.png](https://i-blog.csdnimg.cn/blog_migrate/136928c247018366144ec8d960c06ca5.png)
b、现在按照顾客进行分组
select name,count(*) from business where substr(orderdate,1,7) = "2017-04" group by name;
结果如下图:
![9ee0fe765d09a7a7a28942c706e16187.png](https://i-blog.csdnimg.cn/blog_migrate/eacdb77397a160e3de5de55f3db2a0fe.png)
数据被分成了三组:
![3907cffed76c15e4a4116755531dd25a.png](https://i-blog.csdnimg.cn/blog_migrate/acba587f52df4055b36fa85d50cf5d08.png)
![e0c6dd8c37156f711bbe866f1acaa879.png](https://i-blog.csdnimg.cn/blog_migrate/649eb83acc385839b1f68945e77e882f.png)
使用over()函数:over只对聚合函数起作用,count分别对上面三个组内进行计数,over统计一共有多少个组(有一个count进行累加一次)
select
name,count(*) over() total_num
from business where substr(orderdate,1,7) = "2017-04" group by name;
结果如下所示:
![4e758bd438cab7e9ce6b938607dea882.png](https://i-blog.csdnimg.cn/blog_migrate/4fe572f4144e974870bad2e861f2d359.png)
二、查询顾客购买明细及月份总额
a、首先选出所有明细信息:
select * from business;
![0af80632a621cc2c81411ca654ba7a4e.png](https://i-blog.csdnimg.cn/blog_migrate/686ab2e985f76b5a79070eb7c4cbb566.jpeg)
b、求总额:(这是所有数据的总和,因为没有分组(group by),所以over()的针对的是每一条数据)
select *, sum(cost) over() from business;
![da17244ea63689c05a9591a8d807c730.png](https://i-blog.csdnimg.cn/blog_migrate/ec15bb14cddf9346bbd726b8c17095bf.jpeg)
c、针对四月份的数据,我们需要进行求总额,
思路:分区或者分组,但是使用group by date,只能查询date,(select date ,name group by date)其它字段不能查询
解决:使用窗口函数,并对窗口函数进行分区over(distribute by()) 或者over(partition by())
select *,sum(cost) over(distribute by month(orderdate)) from business;
结果如图所示:
![7648a38ae1d010b42754f5a713f1a1de.png](https://i-blog.csdnimg.cn/blog_migrate/166e1d7919d7a351a69fb462c4be93f4.jpeg)
三、上述场景,将cost按时间累加
a、先按照购买时间进行排序
select * from business sort by orderdate;
结果如图所示:
![ef80f00cee75ca50aaa378ab8bf06d79.png](https://i-blog.csdnimg.cn/blog_migrate/cd731f498f88c16a8761bb8b0d5c575c.jpeg)
-- 参数讲解
-- sort by orderdate:按照购买日期进行排序
-- UNBOUNDED PRECEDING:从起点开始
-- CURRENT ROW:到当前行
-- 计算从开始到当前时间的总花费
select
*,
sum(cost) over(sort by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW)
from business;
结果如下图所示:
![a63a0a98d792d1c5b4c5fde00c57cc34.png](https://i-blog.csdnimg.cn/blog_migrate/37cf20dcaebfe51304e6bb173b7dbed2.jpeg)
row函数:
current row:当前行
n PRECEDING:往前n行
n FOLLOWING:往后n行
UNBOUNDED:起点
UNBOUNDED PRECEDING:从前面起点
UNBOUNDED FOLLOWING:到后面终点
LAG(col,n):往前的第n行
LEAD(col,n):往后的第n行
--参数讲解
-- sort by orderdate:按照时间排序
-- 1 preceding:当前行的前1行
-- 1 following:当前行的后一行
-- 计算相邻三行的值(第一行计算当前行 + 后一行; 最后一行计算当前行 + 前一行)
select
*,
sum(cost) over(sort by orderdate rows between 1 preceding and 1 following)
from business;
结果如下如所示:
![9029dc8f7774c75aab5326ec3dc8a687.png](https://i-blog.csdnimg.cn/blog_migrate/c12a9c80473fc613a2a7b15b898ca135.jpeg)
demo2:
-- 参数详解:
-- distribute by name:按名字进行分区
-- sort by orderdate:在每个分区中按照时间进行排序
-- UNBOUNDED PRECEDING and current row:从起点行到当前行
-- 计算每个人一共的总花费
select
*,
sum(cost) over(distribute by name
sort by orderdate
rows between UNBOUNDED PRECEDING and current row)
from business;
结果如下图所示:
![e74d28e9a80afe352d676a1bdfda8bcd.png](https://i-blog.csdnimg.cn/blog_migrate/3fc700ebd2a447d02e3cd56c2a15ccec.jpeg)
demo3:
--参数讲解:
-- sort by orderdate:按照时间排序
-- current row and unbounded following:当前行到终点行
select
*,
sum(cost) over(sort by orderdate
rows between current row and unbounded following)
from business;
结果如下图所示:
![0697b9f4a57f53f32dbe8046e200eb2f.png](https://i-blog.csdnimg.cn/blog_migrate/dd4067986c37cde3b16a25b77be31e07.jpeg)
四、查询顾客上次购买时间,以及下次购买时间(电商网站常用于求页面跳转的前后时间)
分析:lag(clo,n):返回的是当前行的第前n行
-- 参数详解:
-- distribute by name:按照姓名分组
-- sort by orderdate:按照时间排序
-- lag(orderdate,1):返回当前orderdate行的前一行
-- lead(orderdate,1):返回当前orderdate行的后一行
select *,
lag(orderdate,1) over(distribute by name sort by orderdate),
lead(orderdate,1) over(distribute by name sort by orderdate)
from business;
结果如下图所示:
![caf35cd569d7b049efd66599ee4d1f2a.png](https://i-blog.csdnimg.cn/blog_migrate/beb9fa4990c73d7d5e7b9917d8f77134.jpeg)
五、查询前20%购买的订单信息
分析:可以按照时间分成五等份,然后返回其中的第一份
NTILE(n):将数据等分成n份
select *, ntile(5) over(sort by orderdate) from business;
结果如下图所示:
![207a27352690e412550b52fb4f3434d1.png](https://i-blog.csdnimg.cn/blog_migrate/f006377d828fa04990844f49a2e729b4.jpeg)
-- 下面语句报错,因为 ntile、sum、agg等函数不能放在where后面当做查询条件
select
*, ntile(5) over(sort by orderdate) as sorted
from business
where sorted = 1;
-- 下面语句报错,因为having必须跟在group by 语句后面
select
*, ntile(5) over(sort by orderdate) as sorted
from business
having sorted = 1;
-- 所以使用了子查询,将上一步查询的结果放在子句中
select
name,orderdate,cost
from (
select *,ntile(5) over(order by orderdate) sorted from business
) t
where sorted = 1;
-- Tips:子查询不能使用select *
![e97d3ccdb9de845767a6eda5c64ec0ac.png](https://i-blog.csdnimg.cn/blog_migrate/dfc2de41995655b5170b96b111a1ced6.png)
到此,任务完成。我们下期再见。
![16ca1fd9f23485c8ca045446ad1cf0e1.png](https://i-blog.csdnimg.cn/blog_migrate/c05aabfacdf5176fa5ad8f17d6013d5d.png)
一个不务正业的技术博主