有以下数据:字段名为: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;
需求分析
一、查询2017-04购买的顾客总人数
a、首先想到使用聚合函数count()
-- 先求出2017-04这月一共有多少条记录
select count(*) from business where substr(orderdate,1,7) = "2017-04";
结果如下图:
b、现在按照顾客进行分组
select name,count(*) from business where substr(orderdate,1,7) = "2017-04" group by name;
结果如下图:
数据被分成了三组:
使用over()函数:over只对聚合函数起作用,count分别对上面三个组内进行计数,over统计一共有多少个组(有一个count进行累加一次)
select
name,count(*) over() total_num
from business where substr(orderdate,1,7) = "2017-04" group by name;
结果如下所示:
二、查询顾客购买明细及月份总额
a、首先选出所有明细信息:
select * from business;
b、求总额:(这是所有数据的总和,因为没有分组(group by),所以over()的针对的是每一条数据)
select *, sum(cost) over() from business;
c、针对四月份的数据,我们需要进行求总额,
思路:分区或者分组,但是使用group by date,只能查询date,(select date ,name group by date)其它字段不能查询
解决:使用窗口函数,并对窗口函数进行分区over(distribute by()) 或者over(partition
select *,sum(cost) over(distribute by month(orderdate)) from business;
结果如图所示:
三、上述场景,将cost按时间累加
a、先按照购买时间进行排序
select * from business sort by orderdate;
结果如图所示:
-- 参数讲解
-- sort by orderdate:按照购买日期进行排序
-- UNBOUNDED PRECEDING:从起点开始
-- CURRENT ROW:到当前行
-- 计算从开始到当前时间的总花费
select
*,
sum(cost) over(sort by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW)
from business;
结果如下图所示:
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;
结果如下如所示:
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;
结果如下图所示:
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;
结果如下图所示:
四、查询顾客上次购买时间,以及下次购买时间(电商网站常用于求页面跳转的前后时间)
分析: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;
结果如下图所示:
五、查询前20%购买的订单信息
分析:可以按照时间分成五等份,然后返回其中的第一份
NTILE(n):将数据等分成n份
select *, ntile(5) over(sort by orderdate) from business;
结果如下图所示:
-- 下面语句报错,因为 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 *