第 1 题 连续问题
如下数据为蚂蚁森林中用户领取的减少碳排放量
id dt lowcarbon
1001 2021-12-12 123
1002 2021-12-12 45
1001 2021-12-13 43
1001 2021-12-13 45
1001 2021-12-13 23
1002 2021-12-14 45
1001 2021-12-14 230
1002 2021-12-15 45
1001 2021-12-15 23
.......找出连续3天及以上减少碳排放量在100以上的用户
分析：
遇到这类问题，我们可以用等差数列法来求解，何为等差数列法？
等差数列法:两个等差数列如果等差相同,则相同位置的数据相减到的结果相同
比如有一个等差数列：2 3 4 5 6 7 8 对他们排序后的顺序为 1 2 3 4 5 6 7，转成列展示为：
num rank 相同位置相减得(flag)
2 1 1
3 2 1
4 3 1
5 4 1
6 5 1
7 6 1
8 7 1
此时按照flag分组求和，就得到连续的条数
再如：
num rank 相同位置相减得(flag)
2 1 1
3 2 1
4 3 1
7 4 3
8 5 3
9 6 3
10 7 3
14 8 6
15 9 6
按照flag分组求和，就得到连续的条数有三组：234(3)，789 10(4)，14 15(2)
基于这样的结果再做一次筛选就能得到想要的答案。解法：
-- 1) 按照用户id及时间字段分组,计算每个用户单日减少的碳排放量
select id,
dt,
sum(lowcarbon) lowcarbon
from test1
group by
id,dt
having lowcarbon>100; 记为 t1
得到：
1001 2021-12-12 123
1001 2021-12-13 111
1001 2021-12-14 230
-- 2) 按照用户分组,同时按照时间排序,计算每条数据的rank值
select id,
dt,
lowcarbon,
rank() over(partition by id order by dt) rk
from t1; 记为 t2
得到：
1001 2021-12-12 123 1
1001 2021-12-13 111 2
1001 2021-12-14 230 3
-- 3) 将每行数据中的日期减去rank值
select id,
dt,
lowcarbon,
date_sub(dt,rk) flag
from t2; 记为 t3
得到：
1001 2021-12-12 123 2021-12-11
1001 2021-12-13 111 2021-12-11
1001 2021-12-14 230 2021-12-11
-- 4) 按照用户及flag分组,求每个组有多少条数据,并找出大于等于3条的数据
select id,
flag,
count(*) ct
from t3
group by
id,flag
having ct >= 3;
得到：
1001 2021-12-11 3
-- 5) 最终将sql拼接在一起
select id,
flag,
count(*) ct
from
( select id,
dt,
lowcarbon,
date_sub(dt,rk) flag
from
( select id,
dt,
lowcarbon,
rank() over(partition by id order by dt) rk
from
( select id,
dt,
sum(lowcarbon) lowcarbon
from test1
group by
id,dt
having lowcarbon > 100
)t1
)t2
)t3
group by
id,flag
having ct >= 3;这里的分组不是简单的分组，而是会话的分组。
比如说，进入一个网站以后，可以连续的点击很多个页面，后台会记录用户的行为日志；如果t日上午连续点击几个页面后退出了网站，直到第二天的下午才再次进入网站，单单从时间线上来看，昨天退出的那条日志跟今天进入的那条日志是连在一起的，但这两条数据实际上并不是一个会话产生的，如果需要对这样的数据进行分组，将其分在两个不同的会话当中，应该怎么做呢？组与组之间的时间间隔应该是多少呢？
这个就得看具体的业务逻辑了，比如接下来的例子：
如下为电商公司用户访问时间数据
id ts
1001 17523641234
1001 17523641256
1002 17523641278
1001 17523641334
1002 17523641434
1001 17523641534
1001 17523641544
1002 17523641634
1001 17523641638
1001 17523641654
时间间隔小于60秒，则分为同一个组
1001 17523641234 1
1001 17523641256 1
1001 17523641334 2
1001 17523641534 3
1001 17523641544 3
1001 17523641638 4
1001 17523641654 4
1002 17523641278 1
1002 17523641434 2
1002 17523641634 3分析：
这个问题可以看做：判断连续的两条数据是否属于同一个组(时间有序)，这就涉及到当前行数据及前一行数据或者后一行数据的时间差是否在60秒以内，如果是就属于同一组，反之就不是同一组。
我们应该想到有两个窗口函数，用来获取当前行数据的前n行或者后n行数据：
- 返回位于当前行的前n行的expr的值：lag(expr,n,defval)
- 返回位于当前行的后n行的expr的值：lead(expr,n,defval)
用法：
lag(exp_str,offset,defval) over(partion by ..order by …)
lead(exp_str,offset,defval) over(partion by ..order by …)详细的用法感兴趣可以移步我的另一篇博客，干货满满：mysql/hive 常用窗口函数详解及相关面试题
解法：
-- 1) 按照id分组，将上一行时间数据下移，即将当前行的上一行时间移到当前行，
-- 如果前面没有数据，取默认值0
select id,
ts,
lag(ts,1,0) over(partition by id order by ts) lagts
from test2; 记为 t1
得到：
1001 17523641234 0
1001 17523641256 17523641234
1001 17523641334 17523641256
1001 17523641534 17523641334
1001 17523641544 17523641534
1001 17523641638 17523641544
1001 17523641654 17523641638
1002 17523641278 0
1002 17523641434 17523641278
1002 17523641634 17523641434
-- 2) 将当前行时间数据减去上一行时间数据，得到两行数据的时间差
select id,
ts,
ts-lagts tsdiff
from t1; 记为 t2
得到：
1001 17523641234 17523641234
1001 17523641256 22
1001 17523641334 78
1001 17523641534 200
1001 17523641544 10
1001 17523641638 94
1001 17523641654 16
1002 17523641278 17523641278
1002 17523641434 156
1002 17523641634 200
-- 3) 计算每个用户范围内从第一行到当前行tsdiff大于等于60的总个数(分组号)
select id,
ts,
sum(if(tsdiff >= 60,1,0)) over(partition by id order by ts) groupid -- 这一行将得到从第一行到当前行的 sum(if(tsdiff >= 60,1,0)) 值
from t2;
得到：
1001 17523641234 1
1001 17523641256 1
1001 17523641334 2
1001 17523641534 3
1001 17523641544 3
1001 17523641638 4
1001 17523641654 4
1002 17523641278 1
1002 17523641434 2
1002 17523641634 3
-- 4) 最终将sql拼接在一起
select id,
ts,
sum(if(tsdiff>=60,1,0)) over(partition by id order by ts) groupid
from
( select id,
ts,
ts-lagts tsdiff
from
( select id,
ts,
lag(ts,1,0) over(partition by id order by ts) lagts
from test2
)t1
)t2;某游戏公司记录的用户每日登录数据
1001 2021-12-12
1002 2021-12-12
1001 2021-12-13
1001 2021-12-14
1001 2021-12-16
1002 2021-12-16
1001 2021-12-19
1002 2021-12-17
1001 2021-12-20计算每个用户最大的连续登录天数，可以间隔一天。解释：如果一个用户在 1,3,5,6 登录游戏，则视为连续 6 天登录。
分析：
先将数据按照id,dt排序后得到：
id dt
1001 2021-12-12
1001 2021-12-13
1001 2021-12-14
1001 2021-12-16
1001 2021-12-19
1001 2021-12-20
1002 2021-12-12
1002 2021-12-16
1002 2021-12-17依题意分析得到：
- 1001用户12、13、14、16号为连续登录，连续天数为5
- 1001用户19、20号为连续登录，连续天数为2
- 1002用户12号为连续登录，连续天数为1
- 1002用户16、17号为连续登录，连续天数为2
如果我们将以上四种情况分为四组，那四组的连续天数计算方式分别为：max(dt)-min(dt) 1，即
- 16-12 1=5
- 20-19 1=2
- 12-12 1=1
- 17-16 1=2
由此可见，该类问题就可以转换为，先将数据进行分组，再由组内最大日期减最小日期 1得到。分组问题也就是第二题。
以1001用户的数据为例，流程应该是：
时间下移 diff sum(if(diff>2,1,0)) over(...) as group_flag
1001 2021-12-12 1970-01-01 564564 1
1001 2021-12-13 2021-12-12 1 1
1001 2021-12-14 2021-12-13 1 1
1001 2021-12-16 2021-12-14 2 1
1001 2021-12-19 2021-12-16 4 2
1001 2021-12-20 2021-12-19 1 2
然后按照group_flag分组取组内的最大日期和最小日期求diff 1，就是最大连续登录天数
解法：
-- 1) 将上一行时间数据下移
select id,
dt,
lag(dt,1,'1970-01-01') over(partition by id order by dt) lagdt
from test3;记为 t1
得到：
1001 2021-12-12 1970-01-01
1001 2021-12-13 2021-12-12
1001 2021-12-14 2021-12-13
1001 2021-12-16 2021-12-14
1001 2021-12-19 2021-12-16
1001 2021-12-20 2021-12-19
-- 2) 将当前行时间减去上一行时间数据(datediff(dt1,dt2))
select id,
dt,
datediff(dt,lagdt) flag
from t1; 记为 t2
得到：
1001 2021-12-12 564564
1001 2021-12-13 1
1001 2021-12-14 1
1001 2021-12-16 2
1001 2021-12-19 3
1001 2021-12-20 1
-- 3) 按照用户分组,同时按照时间排序,计算从第一行到当前行大于2的数据的总条数(sum(if(flag>2,1,0)))
select id,
dt,
sum(if(flag>2,1,0)) over(partition by id order by dt) flag
from t2;记为 t3
得到：
1001 2021-12-12 1
1001 2021-12-13 1
1001 2021-12-14 1
1001 2021-12-16 1
1001 2021-12-19 2
1001 2021-12-20 2
-- 4) 按照用户和flag分组,求最大时间减去最小时间并加上1
select id,
flag,
datediff(max(dt),min(dt)) days
from t3
group by
id,flag; 记为 t4
得到：
1001 5
1001 2
-- 5)取连续登录天数的最大值
select id,
max(days) 1
from t4
group by
id;
得到：
1001 5
1002 2 （1002用户明细略）
-- 6) 将sql拼接起来
select id,
max(days) 1
from
( select id,
flag,
datediff(max(dt),min(dt)) days
from
( select id,
dt,
sum(if(flag>2,1,0)) over(partition by id order by dt) flag
from
( select id,
dt,
datediff(dt,lagdt) flag
from
( select id,
dt,
lag(dt,1,'1970-01-01') over(partition by id order by dt) lagdt
from test3
)t1
)t2
)t3
group by
id,flag
)t4
group by
id如下为平台商品促销数据：字段为品牌，打折开始日期，打折结束日期
id stt edt
oppo 2021-06-05 2021-06-09
oppo 2021-06-11 2021-06-21
vivo 2021-06-05 2021-06-15
vivo 2021-06-09 2021-06-21
redmi 2021-06-05 2021-06-21
redmi 2021-06-09 2021-06-15
redmi 2021-06-17 2021-06-26
huawei 2021-06-05 2021-06-26
huawei 2021-06-09 2021-06-15
huawei 2021-06-17 2021-06-21计算每个品牌总的打折销售天数，注意其中的交叉日期，比如 vivo 品牌，第一次活动时间为 2021-06-05 到 2021-06-15，第二次活动时间为 2021-06-09 到 2021-06-21 其中 9 号到 15 号为重复天数，只统计一次，即 vivo 总打折天数为 2021-06-05 到 2021-06-21 共计 17 天。
分析：
题意要求求某品牌总的打折天数，有两种情景需要考虑：
情景一：两次活动的日期没有交叉。如oppo的两次活动：
id stt edt
oppo 2021-06-05 2021-06-09
oppo 2021-06-11 2021-06-21对于这种情况，我们直接分别求diff再做sum即可，即(edt-stt 1)：
id stt edt diff
oppo 2021-06-05 2021-06-09 5
oppo 2021-06-11 2021-06-21 11
再按照 id 分组求sum(diff)=16天情景二：两次活动的日期有交叉。如vivo的两次活动：
id stt edt
vivo 2021-06-05 2021-06-15
vivo 2021-06-09 2021-06-21第一次活动时间为[2021-06-05 , 2021-06-15]，第二次活动时间为 [2021-06-09, 2021-06-21] 其中 9 号到 15 号为重复天数，只统计一次，即 vivo 总打折天数为 [2021-06-05,2021-06-21] 共计 17 天。
如果不考虑交叉，仍然按照情景一的方式计算，两段活动日期的diff分别为：11、13，加起来是24天，如何将中间重复的天数只计算一次呢？
注意观察，出现日期交叉的原因是因为第二次活动的开始时间小于第一次活动的结束时间，换句话说，第一次活动还没结束第二次活动就开始了。
换个角度思考，如果第二次活动在第一次活动结束后再开始，就不会出现日期交叉了，我们试试将第二次活动的开始时间改为第一次活动结束 1看看会是什么样：
-- 2021-06-09 改成 2021-06-16
id stt edt
vivo 2021-06-05 2021-06-15
vivo 2021-06-16 2021-06-21我们再用情景一的计算方式计算出来，活动天数为：11 6=17，符合题意。
通过这样的转换，就能将交叉重复的日期只计算一次，所以到此可以总结为：在计算时，先将本次活动的起始时间改为上次活动的结束时间 1，再分别做diff再求和即可。这样我们就可以用lag()或者lead()将edt字段下移做计算。但这样真的可以吗？会存在一个问题，看个redmi例子：
-- 原数据(人工计算出来的天数应该是22天)：
id stt edt
redmi 2021-06-05 2021-06-21
redmi 2021-06-09 2021-06-15
redmi 2021-06-17 2021-06-26
-- 按照分析，将"本次"活动的开始时间改为"上次"活动的结束时间 1：
id stt edt edt下移 diff(edt-stt 1)
redmi 2021-06-05 2021-06-21 1970-01-01 --不变 21-5 1=17
redmi 2021-06-09 2021-06-15 2021-06-21 --2021-06-09改为2021-06-22 15-22 1= -6
redmi 2021-06-17 2021-06-26 2021-06-15 --2021-06-17改为2021-06-16 26-16 1= 11 会发现，有负数出现，但这个无关紧要，在做sum时会过滤掉 <0 的天数
非负数求和加起来是28天，跟真实的22天不符。
问题出在第三行，2021-06-17改为2021-06-16，其实第三行的开始时间应该改成第三行前面活动的最大结束时间 1，即改成2021-06-22即可。
所以，前面的总结需要修改一下：在计算时，先将本次活动的起始时间改为前几次活动的最大结束时间 1，再分别做diff再求和即可。
实现过程中会用到开窗取前n行数据的最大值的知识点，具体用法感兴趣可以移步我的另一篇博客，干货满满：mysql/hive 常用窗口函数详解及相关面试题
解法：
-- 以 redmi 数据为例
-- 1) 将当前行以前的数据中最大的edt放置当前行
select id,
stt,
edt,
max(edt) over(partition by id order by stt rows between unbounded preceding and 1 preceding) maxedt
from test4; 记为 t1
得到：
redmi 2021-06-05 2021-06-21 null
redmi 2021-06-09 2021-06-15 2021-06-21
redmi 2021-06-17 2021-06-26 2021-06-21
-- 2) 比较开始时间与移动下来的数据,如果开始时间大,则不需要操作,
-- 反之则需要将移动下来的数据加一替换当前行的开始时间
-- 如果是第一行数据,maxedt为null,则不需要操作
select id,
if(maxedt is null,stt,if(stt>maxedt,stt,date_add(maxedt,1))) stt,
edt
from t1; 记为 t2
得到：
redmi 2021-06-05 2021-06-21
redmi 2021-06-22 2021-06-15
redmi 2021-06-22 2021-06-26
-- 3) 将每行数据中的结束日期减去开始日期
select id,
datediff(edt,stt) days
from t2; 记为 t3
得到：
redmi 16
redmi -4
redmi 4
-- 4) 按照品牌分组,计算每条数据加一的总和
select id,
sum(if(days>=0,days 1,0)) days
from t3
group by
id;
得到：
redmi 22
-- 5) 最终sql
select id,
sum(if(days>=0,days 1,0)) days
from
( select id,
datediff(edt,stt) days
from
(
select id,
if(maxedt is null,stt,if(stt>maxedt,stt,date_add(maxedt,1))) stt,
edt
from
( select id,
stt,
edt,
max(edt) over(partition by id order by stt rows between unbounded preceding and 1 preceding) maxedt
from test4
)t1
)t2
)t3
group by
id如下为某直播平台主播开播及关播时间，根据该数据计算出平台最高峰同时在线的主播人数。
id stt edt
1001 2021-06-14 12:12:12 2021-06-14 18:12:12
1003 2021-06-14 13:12:12 2021-06-14 16:12:12
1004 2021-06-14 13:15:12 2021-06-14 20:12:12
1002 2021-06-14 15:12:12 2021-06-14 16:12:12
1005 2021-06-14 15:18:12 2021-06-14 20:12:12
1001 2021-06-14 20:12:12 2021-06-14 23:12:12
1006 2021-06-14 21:12:12 2021-06-14 23:15:12
1007 2021-06-14 22:12:12 2021-06-14 23:10:12分析：
采用流式数据的思想，将一条数据拆分成两条(id,dt,p)，并且对数据进行标记：开播为1，关播为-1，1表示有主播开播在线，-1表示有主播关播离线，其中dt为开播时间或者关播时间：
id dt p
1001 2021-06-14 12:12:12 1
1001 2021-06-14 18:12:12 -1
1001 2021-06-14 20:12:12 1
1001 2021-06-14 23:12:12 -1
1002 2021-06-14 15:12:12 1
1002 2021-06-14 16:12:12 -1
1003 2021-06-14 13:12:12 1
1003 2021-06-14 16:12:12 -1
1004 2021-06-14 13:15:12 1
1004 2021-06-14 20:12:12 -1
1005 2021-06-14 15:18:12 1
1005 2021-06-14 20:12:12 -1
1006 2021-06-14 21:12:12 1
1006 2021-06-14 23:15:12 -1
1007 2021-06-14 22:12:12 1
1007 2021-06-14 23:10:12 -1然后按照dt排序，求某一时刻的主播在线人数，直接对那时刻之前的p求和即可。
那要求一天中最大的同时在线人数，就需要先分别求出每个时刻的同时在线人数，再取最大值即可。需要用到开窗函数：sum() over(...)
解法：
-- 1) 对数据分类,在开始数据后添加正1,表示有主播上线,同时在关播数据后添加-1,表示有主播下线
select id,stt as dt, 1 as p from test5
union
select id,edt as dt,-1 as p from test5 记为 t1
得到：
1001 2021-06-14 12:12:12 1
1001 2021-06-14 18:12:12 -1
1001 2021-06-14 20:12:12 1
1001 2021-06-14 23:12:12 -1
1002 2021-06-14 15:12:12 1
1002 2021-06-14 16:12:12 -1
1003 2021-06-14 13:12:12 1
1003 2021-06-14 16:12:12 -1
1004 2021-06-14 13:15:12 1
1004 2021-06-14 20:12:12 -1
1005 2021-06-14 15:18:12 1
1005 2021-06-14 20:12:12 -1
1006 2021-06-14 21:12:12 1
1006 2021-06-14 23:15:12 -1
1007 2021-06-14 22:12:12 1
1007 2021-06-14 23:10:12 -1
-- 2) 按照时间排序,计算累加人数
select id,
dt,
sum(p) over(order by dt) sum_p -- 重点
from t1; 记为 t2
-- 3) 找出同时在线人数最大值
select max(sum_p)
from
( select id,
dt,
sum(p) over(order by dt) sum_p
from
( select id,stt as dt, 1 as p from test5
union
select id,edt as dt,-1 as p from test5
)t1
)t2