文章目录
- 准备
-
- 小需求一：最大连续登录天数
- 小需求二：最近连续登录天数(求用户最后一次获取活跃的连续登录天数)
- 小需求三：最大连续未登录天数(截止到某一天，这里随便取一个吧'2021-01-20')
- 小需求四：用户自从安装app以来的连续未登录天数历史
- 关于上述几个方法二思考
准备
函数准备
select datediff('2021-01-19','2021-01-18');-- 1
- 窗口函数
- Hive–开窗函数–窗口分析函数：ROW_NUMBER、RANK、DENSE_RANK、NTILE
准备临时表
- hzy_0930_test表
- 表字段：用户、新增日期、活跃日期
DROP TABLE IF EXISTS tmpdb.hzy_0930_test;
-- 用户、新增日期、活跃日期
create table tmpdb.hzy_0930_test as
select '张三' as name,'2021-01-01' as install_date, '2021-01-01' as active_date
union all
select '张三' as name,'2021-01-01' as install_date, '2021-01-02' as active_date
union all
select '张三' as name,'2021-01-01' as install_date, '2021-01-03' as active_date
union all
select '张三' as name,'2021-01-01' as install_date, '2021-01-04' as active_date
union all
select '张三' as name,'2021-01-01' as install_date, '2021-01-07' as active_date
union all
select '张三' as name,'2021-01-01' as install_date, '2021-01-09' as active_date
union all
select '张三' as name,'2021-01-01' as install_date, '2021-01-10' as active_date
union all
select '李四' as name,'2021-01-01' as install_date, '2021-01-01' as active_date
union all
select '李四' as name,'2021-01-01' as install_date, '2021-01-02' as active_date
union all
select '李四' as name,'2021-01-01' as install_date, '2021-01-03' as active_date
union all
select '李四' as name,'2021-01-01' as install_date, '2021-01-04' as active_date
union all
select '李四' as name,'2021-01-01' as install_date, '2021-01-11' as active_date
union all
select '李四' as name,'2021-01-01' as install_date, '2021-01-14' as active_date
union all
select '李四' as name,'2021-01-01' as install_date, '2021-01-15' as active_date
union all
select '李四' as name,'2021-01-01' as install_date, '2021-01-16' as active_date
;
select *
from tmpdb.hzy_0930_test
;
张三 2021-01-01 2021-01-01
张三 2021-01-01 2021-01-02
张三 2021-01-01 2021-01-03
张三 2021-01-01 2021-01-04
张三 2021-01-01 2021-01-07
张三 2021-01-01 2021-01-09
张三 2021-01-01 2021-01-10
李四 2021-01-01 2021-01-01
李四 2021-01-01 2021-01-02
李四 2021-01-01 2021-01-03
李四 2021-01-01 2021-01-04
李四 2021-01-01 2021-01-11
李四 2021-01-01 2021-01-14
李四 2021-01-01 2021-01-15
李四 2021-01-01 2021-01-16
小需求一：最大连续登录天数
select name
,max(activedays) as `最大连续登录天数`
from (select
name
,date_sub(active_date,rank) as date_value
,count(1) as activedays
from (select distinct --防止用户每日活跃数据重复（即用户在某一天登陆两次）
name
,active_date
-- 相同日期排名一样，下一日期的排名与上一日期连续(不留空位)
,dense_rank() over(partition by name order by active_date) as rank
from tmpdb.hzy_0930_test
)a
group by name,date_sub(active_date,rank)
)b
group by name
;
张三 4
李四 4
-- 给个中间结果方便理解
select distinct --防止用户每日活跃数据重复（即用户在某一天登陆两次）
name
,active_date
,rank
-- 如果连续，那么这个date_value应该是一样的
,date_sub(active_date,rank) as date_value
from (select name
,active_date
-- 相同日期排名一样，下一日期的排名与上一日期连续(不留空位)
,dense_rank() over(partition by name order by active_date) as rank
from tmpdb.hzy_0930_test
)a
;
张三 2021-01-01 1 2020-12-31
张三 2021-01-02 2 2020-12-31
张三 2021-01-03 3 2020-12-31
张三 2021-01-04 4 2020-12-31
张三 2021-01-07 5 2021-01-02
张三 2021-01-09 6 2021-01-03
张三 2021-01-10 7 2021-01-03
李四 2021-01-01 1 2020-12-31
李四 2021-01-02 2 2020-12-31
李四 2021-01-03 3 2020-12-31
李四 2021-01-04 4 2020-12-31
李四 2021-01-11 5 2021-01-06
李四 2021-01-14 6 2021-01-08
李四 2021-01-15 7 2021-01-08
李四 2021-01-16 8 2021-01-08
- 方法二
- diff→表示每用户每活跃日期距新增日期间隔天数
- rank→表示每用户按活跃日期生序排的序号
- diff-rank→两者相减可理解为计算用户是否连续活跃的衡量指标，如果连续活跃则相减值value应该相等。
select name
,max(activedays) as `最大连续登录天数`
from ( select name
,diff-rank AS value
,count(1) as activedays
from ( select distinct
name
,active_date
,dense_rank() over(partition by name order by active_date) as rank
,datediff(active_date,install_date) as diff
from tmpdb.hzy_0930_test
)a
group by name
,diff-rank
)b
group by name
;
张三 4
李四 4
-- 给个中间结果方便理解
select distinct
name
,active_date
,dense_rank() over(partition by name order by active_date) as rank
,datediff(active_date,install_date) as diff
from tmpdb.hzy_0930_test
;
张三 2021-01-01 1 0
张三 2021-01-02 2 1
张三 2021-01-03 3 2
张三 2021-01-04 4 3
张三 2021-01-07 5 6
张三 2021-01-09 6 8
张三 2021-01-10 7 9
李四 2021-01-01 1 0
李四 2021-01-02 2 1
李四 2021-01-03 3 2
李四 2021-01-04 4 3
李四 2021-01-11 5 10
李四 2021-01-14 6 13
李四 2021-01-15 7 14
李四 2021-01-16 8 15
小需求二：最近连续登录天数(求用户最后一次获取活跃的连续登录天数)
with active as
(
select distinct --防止用户每日活跃数据重复（即用户在某一天登陆两次）
name
,active_date
,rank
-- 如果连续，那么这个date_value应该是一样的
,date_sub(active_date,rank) as date_value
from (select name
,active_date
-- 相同日期排名一样，下一日期的排名与上一日期连续(不留空位)
,dense_rank() over(partition by name order by active_date) as rank
from tmpdb.hzy_0930_test
)a
)
select t1.name
,max(t1.active_date) as `最近登录日期` --last_active_date
,count(1) as `最近连续登录天数`
from active t1
join (select name
,max(date_value) as last_date_value -- max的一定包含最近的登录日期
from active
group by name
)t2
on t1.name=t2.name
and t1.date_value=t2.last_date_value
group by t1.name
;
张三 2021-01-10 2
李四 2021-01-16 3
--给个中间结果方便理解
select distinct --防止用户每日活跃数据重复（即用户在某一天登陆两次）
name
,active_date
,rank
-- 如果连续，那么这个date_value应该是一样的
,date_sub(active_date,rank) as date_value
from (select name
,active_date
-- 相同日期排名一样，下一日期的排名与上一日期连续(不留空位)
,dense_rank() over(partition by name order by active_date) as rank
from tmpdb.hzy_0930_test
)a
张三 2021-01-01 1 2020-12-31
张三 2021-01-02 2 2020-12-31
张三 2021-01-03 3 2020-12-31
张三 2021-01-04 4 2020-12-31
张三 2021-01-07 5 2021-01-02
张三 2021-01-09 6 2021-01-03
张三 2021-01-10 7 2021-01-03
李四 2021-01-01 1 2020-12-31
李四 2021-01-02 2 2020-12-31
李四 2021-01-03 3 2020-12-31
李四 2021-01-04 4 2020-12-31
李四 2021-01-11 5 2021-01-06
李四 2021-01-14 6 2021-01-08
李四 2021-01-15 7 2021-01-08
李四 2021-01-16 8 2021-01-08
- 方法二
- diff→表示每用户每活跃日期距新增日期间隔天数
- rank→表示每用户按活跃日期生序排的序号
- diff-rank→两者相减可理解为计算用户是否连续活跃的衡量指标，如果连续活跃则相减值value应该相等。
with active as
( select name
,active_date
,rank
,diff
,diff-rank as value
from ( select name
,active_date
,dense_rank() over(partition by name order by active_date) as rank
,datediff(active_date,install_date) as diff
from tmpdb.hzy_0930_test
)tmp
)
select t1.name,count(1) as `最近连续登录天数`
from active t1
join ( -- 没用用户的当前的最后一次活跃日期记录
select name
,max(value) lastactive
from active
group by name
) as t2
on t1.name = t2.name
and t1.value = t2.lastactive
group by t1.name
;
张三 2
李四 3
--给个中间结果方便理解
select name
,active_date
,rank
,diff
,diff-rank as value
from ( select name
,active_date
,dense_rank() over(partition by name order by active_date) as rank
,datediff(active_date,install_date) as diff
from tmpdb.hzy_0930_test
)tmp
张三 2021-01-01 1 0 -1
张三 2021-01-02 2 1 -1
张三 2021-01-03 3 2 -1
张三 2021-01-04 4 3 -1
张三 2021-01-07 5 6 1
张三 2021-01-09 6 8 2
张三 2021-01-10 7 9 2
李四 2021-01-01 1 0 -1
李四 2021-01-02 2 1 -1
李四 2021-01-03 3 2 -1
李四 2021-01-04 4 3 -1
李四 2021-01-11 5 10 5
李四 2021-01-14 6 13 7
李四 2021-01-15 7 14 7
李四 2021-01-16 8 15 7
小需求三：最大连续未登录天数(截止到某一天，这里随便取一个吧’2021-01-20’)
- 计算用户"最大连续未登录天数"，还需考虑用户最后一次活跃日期距某一天的时间间隔，然后再取最大值
- 方法一
with active as
(
select distinct --防止用户每日活跃数据重复（即用户在某一天登陆两次）
name
,active_date
,rank
-- 如果连续，那么这个date_value应该是一样的
,date_sub(active_date,rank) as date_value
-- 需考虑用户最后一次活跃日期距某一天的时间间隔
,max(active_date) over(partition by name) as last_active_date
from (select name
,active_date
-- 相同日期排名一样，下一日期的排名与上一日期连续(不留空位)
,dense_rank() over(partition by name order by active_date) as rank
from tmpdb.hzy_0930_test
)a
)
select name
,max(no_active_days) as `最大连续未登录天数`
from (select t1.name
,t1.active_date as start_date
,t2.active_date as end_date
,datediff(t2.active_date,t1.active_date)-1 as no_active_days --这里需要和方法二区分，方法二不需要减1
from active t1
join active t2
on t1.name=t2.name
and t1.rank=t2.rank-1
union all
-- 用户最后一次活跃日期距某一天的时间间隔，只需要一天即可
select distinct
name
,max(active_date) as start_date
,'2021-01-20' as end_date -- 截止到某一天，当然可以取当天 current_date
,datediff('2021-01-20',max(active_date))-1 as no_active_days -- 这里不需要减1
from active
group by name
)t3
group by name
;
张三 10
李四 6
-- 给个中间结果方便理解
with active as
(
select distinct --防止用户每日活跃数据重复（即用户在某一天登陆两次）
name
,active_date
,rank
-- 如果连续，那么这个date_value应该是一样的
,date_sub(active_date,rank) as date_value
from (select name
,active_date
-- 相同日期排名一样，下一日期的排名与上一日期连续(不留空位)
,dense_rank() over(partition by name order by active_date) as rank
from tmpdb.hzy_0930_test
)a
)
select t1.name
,t1.active_date as start_date
,t2.active_date as end_date
,datediff(t2.active_date,t1.active_date)-1 as no_active_days --这里需要和方法二区分，方法二不需要减1
from active t1
join active t2
on t1.name=t2.name
and t1.rank=t2.rank-1
;
张三 2021-01-01 2021-01-02 0
张三 2021-01-02 2021-01-03 0
张三 2021-01-03 2021-01-04 0
张三 2021-01-04 2021-01-07 2
张三 2021-01-07 2021-01-09 1
张三 2021-01-09 2021-01-10 0
李四 2021-01-01 2021-01-02 0
李四 2021-01-02 2021-01-03 0
李四 2021-01-03 2021-01-04 0
李四 2021-01-04 2021-01-11 6
李四 2021-01-11 2021-01-14 2
李四 2021-01-14 2021-01-15 0
李四 2021-01-15 2021-01-16 0
- 方法二
- diff→表示每用户每活跃日期距新增日期间隔天数
- rank→表示每用户按活跃日期生序排的序号
- diff-rank→两者相减可理解为计算用户是否连续活跃的衡量指标，如果连续活跃则相减值value应该相等。
with active as
(
select name
,active_date
,rank
,diff
,diff-rank as value
from ( select name
,active_date
,row_number() over(partition by name order by active_date) as rank
,datediff(active_date,install_date) as diff
from tmpdb.hzy_0930_test
)tmp
)
select name
,max(noactivedays) as `最大连续未登录天数`
from ( select t1.name
,t1.active_date as start_date
,t2.active_date as end_date
,t2.value - t1.value as noactivedays
from active as t1
join active as t2
on t1.name = t2.name
and t1.rank = t2.rank - 1
union all
select name
,max(active_date)as start_date
,'2021-01-20' as end_date
,datediff('2021-01-20',max(active_date)) as noactivedays
from tmpdb.hzy_0930_test
group by name
) t
group by name
order by name;
张三 10
李四 6
小需求四：用户自从安装app以来的连续未登录天数历史
with active as
(
select distinct --防止用户每日活跃数据重复（即用户在某一天登陆两次）
name
,active_date
,rank
-- 如果连续，那么这个date_value应该是一样的
,date_sub(active_date,rank) as date_value
from (select name
,active_date
-- 相同日期排名一样，下一日期的排名与上一日期连续(不留空位)
,dense_rank() over(partition by name order by active_date) as rank
from tmpdb.hzy_0930_test
)a
)
select name
,start_date
,end_date
,no_active_days as `连续未登录天数`
from (select t1.name
,t1.active_date as start_date
,t2.active_date as end_date
,datediff(t2.active_date,t1.active_date)-1 as no_active_days --这里需要和方法二区分，方法二不需要减1
from active t1
join active t2
on t1.name=t2.name
and t1.rank=t2.rank-1
union all
select name
,max(active_date) as start_date
,'2021-01-20' as end_date -- 截止到某一天，当然可以取当天 current_date
,datediff('2021-01-20',max(active_date)) as no_active_days
from active
group by name
)t3
where no_active_days >0
;
张三 2021-01-04 2021-01-07 2
张三 2021-01-07 2021-01-09 1
张三 2021-01-10 2021-01-20 10
李四 2021-01-04 2021-01-11 6
李四 2021-01-11 2021-01-14 2
李四 2021-01-16 2021-01-20 4
with active as
(
select name
,active_date
,rank
,diff
,diff-rank as value
from ( select name
,active_date
,row_number() over(partition by name order by active_date) as rank
,datediff(active_date,install_date) as diff
from tmpdb.hzy_0930_test
)tmp
)
select name
,start_date
,end_date
,noactivedays as `连续未登录天数`
from ( select t1.name
,t1.active_date as start_date
,t2.active_date as end_date
,t2.value - t1.value as noactivedays
from active as t1
join active as t2
on t1.name = t2.name and t1.rank = t2.rank - 1
union all
select name
,max(active_date)as start_date
,'2021-01-20' as end_date
,datediff('2021-01-20',max(active_date)) as noactivedays
from tmpdb.hzy_0930_test
group by name
) t
where noactivedays>0
;
张三 2021-01-04 2021-01-07 2
张三 2021-01-07 2021-01-09 1
张三 2021-01-10 2021-01-20 10
李四 2021-01-04 2021-01-11 6
李四 2021-01-11 2021-01-14 2
李四 2021-01-16 2021-01-20 4
关于上述几个方法二思考
- 数据运营人员常常会需要查找活跃用户名单，而活跃用户很多情况下被定义为连续在或发单n天及以上的用户。一方面我们可以根据n的值直接进行筛选；更具一般性地，就要求我们去求取每个用户某段时间内的最大连续在线或者发单天数了。
- SQL求连续在线天数是一个非常经典的问题，该问题在不考虑计算成本下有非常多的解法。
- 该问题的最大难点在于如何判断日期与日期间是否连续，那这就要涉及到处理行与行之间的关系了。说到这对SQL比较熟悉的同学应该就会反应出使用join或者窗口函数来处理了。
- 假设我们有19年一月份每日用户发单数据存储于订单表order_base:
user_id order_id create_time
234520012 1231512416323 2019-01-02 12:21:11
123149908 2412298719221 2019-01-04 01:11:34
… …
- 解法1（通过与特定日期的日期差判定连续）：
- 连续的时间点与某一个特定时间点的时间差也是连续的，从下表可以直观理解这一点：
日期 特定日期 日期差d
2019-01-01 2019-01-01 0
2019-01-02 2019-01-01 1
2019-01-04 2019-01-01 3
2019-01-05 2019-01-01 4
2019-01-06 2019-01-01 5
- 那么我们对该日期差d进行个排序，如果连续的话，d与序号的差值应该是相同的，如下表：
日期 特定日期 日期差d 序号r 日期差d与序号r的差值
2019-01-01 2019-01-01 0 0 0
2019-01-02 2019-01-01 1 1 0
2019-01-04 2019-01-01 3 2 1
2019-01-05 2019-01-01 4 3 1
2019-01-06 2019-01-01 5 4 1
- 这样答案就显而易见了，只需要对上面这个子查询的最后一列进行分组统计行数，变得到了每次连续的天数，再取连续天数的最大值，便是我们想要的答案。
select
user_id,
max(date_cnt) as max_continuation_date_cnt
from
(
select
user_id,
d-d_ranking as d_group, -- 连续日期的组标记
count(1) as date_cnt
from
(
select
user_id,
d, --与标记日期的日期差
row_number() over(partition by user_id order by d) as d_ranking --与标记日期的日期差的排序
from
(
select
user_id,
datediff(create_date,'2019-01-01') as d --与标记日期的日期差
from
(
select
user_id,
to_date(create_time) as create_date
from
order_base
group by
user_id,
date(create_time)
)a -- 在这一层获取用户的发单日期并去重
)b --这一层获取与标记日期的日期差
)c --获取连续日期的排序
group by
user_id,
d-d_ranking
)d -- 获取每一个连续日期组的连续天数
group by
user_id
- 解法二（lead或lag）
- 最后介绍一个最为直观，也是计算成本最小的方法。假设我们需要求连续登陆n天（假设n为7）及以上的用户，那么对于一个存在该行为的用户，他去重和排序后的发单日期信息中，必存在某一天，往前回溯(往后推)6条记录的日期，等于该日期减6(加6)。
