案例1：连续登陆用户
当前有一份用户登录数据如下图所示，数据中有两个字段，分别是userId和loginTime userId表示唯一的用户ID，唯一标识一个用户 loginTime表示用户的登录日期，例如第一条数据就表示A在2021年3月22日登录了
数据准备
log.txt
A 2021-03-22 B 2021-03-22 C 2021-03-22 A 2021-03-23 C 2021-03-23 A 2021-03-24 B 2021-03-24
窗口函数实现
窗口函数lead 功能：用于从当前数据中基于当前行的数据向后偏移取值 语法：lead(colName，N，defautValue) colName：取哪一列的值 N：向后偏移N行 defaultValue：如果取不到返回的默认值 我们可以基于用户的登陆信息，找到如下规律： 连续两天登陆 ： 用户下次登陆时间 = 本次登陆以后的第二天 连续三天登陆 ： 用户下下次登陆时间 = 本次登陆以后的第三天 …… 我们可以对用户ID进行分区，按照登陆时间进行排序，通过lead函数计算出用户下次登陆时间 通过日期函数计算出登陆以后第二天的日期，如果相等即为连续两天登录。
（连续两天登录的实现 ）代码：
select distinct userid from ( select userId,loginTime, date_add(loginTime,1) as nextday, lead(loginTime,1,0) over (partition by userid order by loginTime) as nextlogin from tb_login) t1 where nextday=nextlogin;
（连续三天登录的实现 ）代码：
select distinct userid from ( select userId,loginTime, #本次登录日期是第三天 date_add(loginTime,2) as nextday, lead(loginTime,2,0) over (partition by userid order by loginTime) as nextlogin from tb_login) t1 where nextday=nextlogin;
连续N天登录的实现（N >= 2）
select distinct userid from ( select userId,loginTime, #本次登录日期是第三天 date_add(loginTime,N-1) as nextday, lead(loginTime,N-1,0) over (partition by userid order by loginTime) as nextlogin from tb_login) t1 where nextday=nextlogin;
案例2：级联累加求和
需求：统计每个用户每个月的消费总金额以及当前累计消费总金额
数据准备
窗口函数实现
窗口函数sum 功能：用于实现基于窗口的数据求和 语法：sum(colName) over (partition by col order by col) colName：对某一列的值进行求和 分析 基于每个用户每个月的消费金额，可以通过窗口函数对用户进行分区，按照月份排序 然后基于聚合窗口，从每个分区的第一行累加到当前和 即可得到累计消费金额。
统计每个用户每个月消费金额及累计总金额
select user_id,mth,a, sum(money) over (partition by user_id order by mth) a from tb_money
如何实现只计算前最近三个月的累计消费金额呢? 使用rows between来控制累积的行范围。
select user_id,mth,a, sum(money) over (partition by user_id order by mth rows between 1 preeding and 2 folling) a from tb_money
案例3：分组TopN
需求：统计查询每个部门薪资最高的前两名员工的薪水
实现方案分析
根据上述需求，这种情况下是无法根据group by分组聚合实现的，因为分组聚合只能实现返回一条聚合的结果，但是需求中需要每个部门返回薪资最高的前两名，有两条结果，这时候就需要用到窗口函数中的分区来实现了。
TopN函数：row_number、rank、dense_rank row_number：对每个分区的数据进行编号，如果值相同，继续编号 rank：对每个分区的数据进行编号，如果值相同，编号相同，但留下空位 dense_rank：对每个分区的数据进行编号，如果值相同，编号相同，不留下空位
数据准备
窗口函数实现
select empno,ename,deptno,salary from (select empno,ename,deptno,salary rank_num() over(partition by deptno order by salary desc ) t1 from tb_emp) where t1<3;--基于row_number实现，按照部门分区，每个部门内部按照薪水降序排序
用户信息表
商品信息表
商品分类信息表
订单信息表
订单明细表
登录明细表
商品价格变更明细表
配送信息表
好友关系表
收藏信息表
案例3:查询累积销量排名第二的商品
题目需求
查询订单明细表（order_detail）中销量（下单件数）排名第二的商品id，如果不存在返回null，如果存在多个排名第二的商品则需要全部返回。期望结果如下：
sku_id |
2 |
select sku_id from (select sku_id from ( select sku_id, order_num,rank() over ( order by order_num desc) rk from ( SELECT sku_id, sum(sku_num) order_num from order_detail group by sku_id ) t1 ) t2 where rk = 2）t3 right join --为保证，没有第二名的情况下，返回null ( select 1 ) t4 on 1 = 1;
第二种方法
select sku_id from (select sku_id, sum(sku_num) order_num, rank() over (order by sum(sku_num) desc ) rk from order_detail1 group by sku_id)t1 where rk=2
案例4:
查询至少连续三天下单的用户
题目需求
查询订单信息表(order_info)中最少连续3天下单的用户id，期望结果如下：
user_id |
101 |
2.2.2 代码实现
SELECT distinct user_id from (select user_id, datediff (lead2, create_date) diff from ( select user_id, create_date,lead (create_date, 2, 0) over ( partition by user_id order by create_date ) lead2 from ( select distinct user_id, create_date from order_info ) t1 ) t2)t3 where diff=2
第二种方法
select distinct user_id from (select user_id,date_add(create_date,2) as nextday, lead(create_date,2,0) over(partition by user_id order by create_date) as nextdorder from order_info2) t1 where nextday=nextdorder
案例5:
查询各品类销售商品的种类数及销量最高的商品
题目需求
从订单明细表(order_detail)统计各品类销售出的商品种类数及累积销量最好的商品，期望结果如下：
select category_id, category_name, sku_id, name, order_num, sku_cnt from ( select od.sku_id, sku.name, sku.category_id, cate.category_name, order_num, rank() over (partition by sku.category_id order by order_num desc) rk, count(distinct od.sku_id) over (partition by sku.category_id) sku_cnt from ( select sku_id, sum(sku_num) order_num from order_detail group by sku_id ) od left join sku_info sku on od.sku_id = sku.sku_id left join category_info cate on sku.category_id = cate.category_id ) t1 where rk = 1;
