一.SQL中的进阶函数
1.窗口函数
窗口函数和普通聚合函数的区别：
①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。
②聚合函数也可以用于窗口函数。
原因就在于窗口函数的执行顺序（逻辑上的）是在FROM，JOIN，WHERE，GROUP BY，HAVING之后，在ORDER BY，LIMIT，SELECT DISTINCT之前。它执行时GROUP BY的聚合过程已经完成了，所以不会再产生数据聚合。
2.窗口函数的基本用法：
函数名 OVER 子句over关键字用来指定函数执行的窗口范围,若后面括号中什么都不写,则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空,则支持以下4中语法来设置窗口。
①window_name:给窗口指定一个别名。如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读
②partition by子句：窗口按照哪些字段进行分组,窗口函数在不同的分组上分别执行
③order by子句：按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号
④frame子句：frame是当前分区的一个子集,子句用来定义子集的规则,通常用来作为滑动窗口使用
3.序号函数:row_number(),rank(),dense_rank()
ROW_NUMBER():顺序排序——1、2、3
RANK():并列排序，跳过重复序号——1、1、3
DENSE_RANK():并列排序，不跳过重复序号——1、1、2
4.分布函数:percent_rank(),cume_dist()
percent_rank():
每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
--给窗口指定别名：WINDOW w AS (PARTITION BY stu_id ORDER BY score) rows = 5
mysql> SELECT
-> RANK() OVER w AS rk,
-> PERCENT_RANK() OVER w AS prk,
-> stu_id, lesson_id, score
-> FROM t_score
-> WHERE stu_id = 1
-> WINDOW w AS (PARTITION BY stu_id ORDER BY score)
-> ;
+----+------+--------+-----------+-------+
| rk | prk | stu_id | lesson_id | score |
+----+------+--------+-----------+-------+
| 1 | 0 | 1 | L003 | 79 |
| 2 | 0.25 | 1 | L002 | 86 |
| 3 | 0.5 | 1 | L004 | 88 |
| 4 | 0.75 | 1 | L005 | 98 |
| 4 | 0.75 | 1 | L001 | 98 |
+----+------+--------+-----------+-------+cume_dist():
分组内小于、等于当前rank值的行数 / 分组内总行数 eg:查询小于等于当前成绩（score）的比例
--cd1：没有分区，则所有数据均为一组，总行数为8
--cd2：按照lesson_id分成了两组，行数各为4
mysql> SELECT stu_id, lesson_id, score,
-> CUME_DIST() OVER (ORDER BY score) AS cd1,
-> CUME_DIST() OVER (PARTITION BY lesson_id ORDER BY score) AS cd2
-> FROM t_score
-> WHERE lesson_id IN ('L001','L002')
-> ;
+--------+-----------+-------+-------+------+
| stu_id | lesson_id | score | cd1 | cd2 |
+--------+-----------+-------+-------+------+
| 2 | L001 | 84 | 0.125 | 0.25 |
| 1 | L001 | 98 | 0.75 | 0.5 |
| 4 | L001 | 99 | 0.875 | 0.75 |
| 3 | L001 | 100 | 1 | 1 |
| 1 | L002 | 86 | 0.25 | 0.25 |
| 4 | L002 | 88 | 0.375 | 0.5 |
| 2 | L002 | 90 | 0.5 | 0.75 |
| 3 | L002 | 91 | 0.625 | 1 |
+--------+-----------+-------+-------+------+5.前后函数:lag(expr,n),lead(expr,n)
- 用途：返回位于当前行的前n行（
LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值 - 应用场景：查询前1名同学的成绩和当前同学成绩的差值
mysql> SELECT stu_id, lesson_id, score, pre_score,
-> score-pre_score AS diff
-> FROM(
-> SELECT stu_id, lesson_id, score,
-> LAG(score,1) OVER w AS pre_score
-> FROM t_score
-> WHERE lesson_id IN ('L001','L002')
-> WINDOW w AS (PARTITION BY lesson_id ORDER BY score)) t
-> ;
+--------+-----------+-------+-----------+------+
| stu_id | lesson_id | score | pre_score | diff |
+--------+-----------+-------+-----------+------+
| 2 | L001 | 84 | NULL | NULL |
| 1 | L001 | 98 | 84 | 14 |
| 4 | L001 | 99 | 98 | 1 |
| 3 | L001 | 100 | 99 | 1 |
| 1 | L002 | 86 | NULL | NULL |
| 4 | L002 | 88 | 86 | 2 |
| 2 | L002 | 90 | 88 | 2 |
| 3 | L002 | 91 | 90 | 1 |
+--------+-----------+-------+-----------+------+6.头尾函数：FIRST_VALUE(expr),LAST_VALUE(expr)
- 用途：返回第一个（
FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值 - 应用场景：截止到当前成绩，按照日期排序查询第1个和最后1个同学的分数
mysql> SELECT stu_id, lesson_id, score, create_time,
-> FIRST_VALUE(score) OVER w AS first_score,
-> LAST_VALUE(score) OVER w AS last_score
-> FROM t_score
-> WHERE lesson_id IN ('L001','L002')
-> WINDOW w AS (PARTITION BY lesson_id ORDER BY create_time)
-> ;
+--------+-----------+-------+-------------+-------------+------------+
| stu_id | lesson_id | score | create_time | first_score | last_score |
+--------+-----------+-------+-------------+-------------+------------+
| 3 | L001 | 100 | 2018-08-07 | 100 | 100 |
| 1 | L001 | 98 | 2018-08-08 | 100 | 98 |
| 2 | L001 | 84 | 2018-08-09 | 100 | 99 |
| 4 | L001 | 99 | 2018-08-09 | 100 | 99 |
| 3 | L002 | 91 | 2018-08-07 | 91 | 91 |
| 1 | L002 | 86 | 2018-08-08 | 91 | 86 |
| 2 | L002 | 90 | 2018-08-09 | 91 | 90 |
| 4 | L002 | 88 | 2018-08-10 | 91 | 88 |
+--------+-----------+-------+-------------+-------------+------------+二.case专题-商品订单数据
数据表:
订单表orders,大概字段有（order_id'订单号,'user_id‘用户编号’, order_pay‘订单金额’ , order_time‘下单时间’,'商品一级类目commodity_level_I','商品二级类目commodity_level_2'）
1.求最近7天内每一个一级类目下成交总额排名前3的二级类目:
select commodity_level_1 as '商品一级类目',
commodity_level_2 as '商品二级类目',
total_pay as '成交总额'
from
(
select commodity_level_1,commodity_level_2,total_pay
row_number()over(partition by commodity_level_1 order by a.total_pay desc) as rank
from
(
select commodity_level_1,
commodity_level_2,
sum(order_pay) as total_pay
from orders
where datediff(now(),order_time) <= 7
group by commodity_level_1,commodity_level_2,
) a
) b
where rank <= 32.提取8.1-8.10每一天消费金额排名在101-195的user_id
select order_date as '订单日期',
user_id,
total_pay as '消费金额'
from
(
select order_date,user_id,total_pay
row_number()over(partition by order_date order by a.total_pay desc) as rank
from
(
select convert(order_time,date) as order_date,
user_id,
sum(order_pay) as total_pay
from orders
where convert(order_time,date) between '20190801' and '20190810'
group by convert(order_time,date),user_id
) a
) b
where rank between 101 and 195三.case专题-活动运营数据分析
数据表
表1——订单表orders,大概字段有（user_id‘用户编号’, order_pay‘订单金额’ , order_time‘下单时间’)
表2——活动报名表act_apply,大概字段有（act_id‘活动编号’, user_id‘报名用户’,act_time‘报名时间’）
1.活动运营数据分析-统计每个活动对应所有用户在报名后产生的总订单金额，总订单数
select act_id as '活动编号', COUNT(*) as '订单数', SUM(order_pay) as '总金额'
from orders LEFT JOIN act_apply on orders.user_id=act_apply.user_id
where order_time >= act_time
GROUP BY act_id2.统计每个活动从开始后到当天（考试日）平均每天产生的订单数，活动开始时间定义为最早有用户报名的时间。（涉及到时间的数据类型均为：datetime）
解题思路:
(1)找到活动最早用户报名的时间 用窗口函数min()over()
(2)计算最早报名时间到当前时间的天数差,datediff(now(),act_start)
select act_id as '活动编号',count(*)/datediff(now(),act_start)
from orders a
left join
(
select act_id,user_id,act_time,min()over(partition by act_id order act_time) as act_start
from act_apply
)b
on a.user_id = b.user_id
where order_time >= act_time
group by act_id,act_start四.case专题-用户行为路径分析
表1——用户行为表tracking_log，大概字段有（user_id‘用户编号’,opr_id‘操作编号’,log_time‘操作时间’）
1.统计每天符合以下条件的用户数：A操作之后是B操作，AB操作必须相邻
解题思路:运用窗口函数在每行数据生成一列数据:下次操作编号
注意要把datetime格式转换成date格式来表示天
select Date,count(*) as 'A-B路径用户计数'
from
(
select distinct user_id,opr_id as 'cur_opr',convert(log_time,date) as 'Date',
lead(opr_id,1)over(partition by user_id,convert(log_time,date) order by log_time) as 'next_opr'
from tracking_log
) t
where t.cur_opr = t.next_opr
group by Date2.统计用户行为序列为A-B-D的用户数
其中:A-B之间可以有任何其他浏览记录(如C,E等),B-D之间除了C记录可以有任何其他浏览记录(如A,E等)
select count(user_id) as result
from
(
select user_id,group_concat(opr_id order by log_time) as user_behavior_path
from tracking_log
group by user_id
having (user_behavior_path like '%A%B%D%')
and (user_behavior_path not like '%A%B%C%D%')
) t五.case专题-用户留存分析
表1——用户登陆表user_log，大概字段有（user_id‘用户编号’，log_time‘登陆时间’)
1.求每天新增用户数，以及他们第2天、30天的留存率
select a.date as '日期',a.new as '新增用户',
b.2_back / a.new as '第2天回访率',
c.30_back / a.new as '第3天回访率',
from
(
select convert(aa.first_time,date) as 'date',count(distinct user_id) as 'new'
from
(
--找到每个用户第一次登陆时间,按第一次登陆日期聚合,统计user_id,即每天新增用户量
select user_id,min(log_time)over(partition by user_id) as first_time,log_time
from user_log
)aa
group by convert(aa.first_time,date)
)a
left join
(
--计算第二天回访的用户量,计算当前时间与第一次登陆的时间差
select convert(bb.first_time,date) as 'date',count(distinct bb.user_id) as '2_back'
from
(
select user_id,min(log_time)over(partition by user_id) as first_time,log_time
from user_log
where datediff(log_time,first_time) = 1
)bb
group by convert(bb.first_time,date)
)b on a.date = b.date
left join
(
--计算第二天回访的用户量,计算当前时间与第一次登陆的时间差
select convert(cc.first_time,date) as 'date',count(distinct cc.user_id) as '30_back'
from
(
select user_id,min(log_time)over(partition by user_id) as first_time,log_time
from user_log
where datediff(log_time,first_time) = 29
)cc
group by convert(bb.first_time,date)
)c on a.date = c.date2.找近90天,30天,7天的登录人数
select count(distinct case when datediff(now(),log_time)<=90 then user_id else null end) as 90_log_users,
count(distinct case when datediff(now(),log_time)<=30 then user_id else null end) as 30_log_users,
count(distinct case when datediff(now(),log_time)<=7 then user_id else null end) as 7_log_users
from user_log3.求用户近一个月平均登录时间间隔（按天）
select user_id,avg(diff)
from
(
select user_id,lead(log_time)over(partition user_id order by log_time) - log_time as diff
from user_log
where datediff(now(),log_time)<=30
)
group by user_id六.case专题-统计特征(中位数,众数,四分位数)
字段：订单号(order_id),店铺id(shop_id),销量(sale),商品id(commodity_id)
1.求每个店铺订单销量排名的中位数
select avg(sale)
from
(
select sale,
count(*)over(partition by shop_id) as total, --shop对应数据行数
--用来计算偶数中位数
cast(count(*)over(partition by shop_id) as decimal) / 2 as even_mid,
--用来计算奇数中位数
ceiling(cast(count(*)over(partition by shop_id) as decimal)/2)as odd_mid,
row_number()over(partition by shop_id order by sale) as sale_rank
from orders
) t
where ( total%2=0 and sale_rank in (even_mid,even_mid+1)) or ( total%2=1 and sale_rank = mid)
group by shop_id2.求每个店铺订购商品的众数
预备知识:
all(子查询) 表示子查询中的所有数据 eg: >= all(子查询) 表示大于等于子查询中所有数据
any(子查询)表示子查询中的任一数据 eg: >= any(子查询) 表示只有大于等于子查询中任意一个数据 就返回true
select shop_id,commodity_id,order_num
from
(
select shop_id,commodity_id,order_num,max(order_num)over(partition by shop_id) as max_order_num
(
select shop_id,commodity_id,count(*) as order_num
from orders
group by shop_id,commodity_id
) t1
) t2
where order_num = max_order_num3.求四分位数
三个表
T1：good_id,cate_id(分类)
T2:mall_id(店铺), good_id
T3:mall_id, credit_score(信用分)
问，在不同分类中,店铺的信用分前100和求top25%
select cate_id,mall_id,credit_score
from
(
select cate_id,mall_id,
credit_score,
row_number()over(partition by cate_id order by credit_score desc) as score_rn
count(*)over(partition by cate_id) as mall_total
from
( --选择商品分类id,店铺id,商品信用分数
select T1.cate_id,T2.mall_id,credit_score
from T1 left join (T2 left join T3 on T2.mall_id = T3.mall_id) on T1.good_id = T2.good_id
group by T1.cate_id,T2.mall_id
)t
)
where score_rn <= mall_total*0.25七.case专题-GMV周同比统计
字段:时间(sale_date),店铺类别(cate_id),店铺数量(mall_num),gmv
1.拼多多618前后一周内各店铺类别gmv的日均提升幅度和比例
select cate_id,avg(gmv_diff),avg(gmv_rate)
from
(
select sale_date,cate_id,mall_num,gmv,
(lead(gmv,7)over(partition by cate_id order by sale_date)-gmv) as gmv_diff,
(lead(gmv,7)over(partition by cate_id order by sale_date)-gmv) / gmv as gmv_rate
from T
where sale_date between '20190611' and '20190624'
)t
where sale_date between '20190611' and '20190617'
group by cate_id,mall_num,gmv
