|
在正常生产或者练习中，会碰到要求统计在一段时间内有连续登录行为的用户数量，此要求的实现方式有多种， 现在整理一下自己会的和自己从其他大佬学到的方式 供共同学习使用，如果内容有错误的地方，请务必留言，给小弟改正的机会，不胜感激！
实现方式1：
实现思想：计算每个用户本次活跃与下下次活跃相差天数 如果天数为2 则证明本次到下下为连续活跃三天
步骤：-- 1 将本次活跃与下下此活跃放在同一条数据中心
– 2 计算本次活跃与下下次活跃相差天数
– 3 筛选相差天数为2的用户 统计用户数量 可能会出现一个用户连续活跃3天的次数超过一次 故需要去重
select
count(distinct user_id)
from
(
select
user_id, -- 用户id
datediff(times,dt) diff --计算本次活跃与下下此活跃相差天数
from
(
select
user_id, -- 用户id
dt, -- 活跃时间
lead(dt,2,'1970-01-01') over (partition by user_id order by dt) times -- 按照用户进行分区 然后按照时间进行排序
-- 获取下下次活跃时间 如果没有下下次数据 则默认时间为计算机元年
from dws_user_action_daycount
)t1
)t2
where diff=2;
函数直达：lead()函数：函数值达 ；datefiff():函数直达；
实现方式2：
思想：将最近7天用户登录按照用户id进行分组 分组后对dt进行排序 然后对排序后的组内信息进行排名
– 计算登录时间和排名之间的差值 如果登录时间按着 则减去排名后的结果则相同 统计每个用户id 差值相等的个数
– 如果个数大于等于3则连续3天活跃
步骤：1 按照用户id分组 按照dt进行排序 后 使用rank() 函数进行排名
2 计算活跃时间dt 与排名之间的差值 将少于三次的数据进行过滤掉
3将用户id进行去重后统计数量
-- 将用户id进行去重后统计数量
select
count(distinct user_id)
from
(
-- 按照用户和时间差值进行分组 统计相同时间的次数
select
user_id,
diff,
count(*)
from
(
-- 计算活跃时间dt 与排名之间的差值
select
user_id,
date_add(dt,-rk) diff
from
(
-- 按照用户id分组 按照dt进行排序 后 使用rank() 函数进行排名
select
user_id,
dt,
rank() over (partition by user_id order by dt) rk
from dws_user_action_daycount
)t1
)t2
group by user_id, diff
having count(*)>=3 -- 将少于三次的数据进行过滤掉
)t3
函数直达：rank()函数直达;date_add():函数直达；
实现方式3；
思想：七天按照天数先后顺序不同分别分为1-1000000 num 然后将同一用户的num组进行相加 如果相加结果出现连续三个1 则此用户三天内连续活跃
步骤：1 --增加一列数据 num 按照 dt分别赋值1-1000000
2 – 计算每个user_id 的num和
3 – 对num进行匹配 如果出现111 则证明此用户连续三天活跃
-- 对num进行匹配 如果出现111 则证明此用户连续三天活跃
select
count(*)
from
(
-- 计算每个user_id 的num和
select
user_id,
sum(num) count
from
(
--增加一列数据 num 按照 dt分别赋值1-1000000
select
user_id,
dt,
case
when dt='2020-06-14' then 1000000
when dt='2020-06-13' then 100000
when dt='2020-06-12' then 10000
when dt='2020-06-11' then 1000
when dt='2020-06-10' then 100
when dt='2020-06-09' then 10
when dt='2020-06-08' then 1
end num
from dws_user_action_daycount
where dt>=date_add('2020-06-14',-6)
)t1
group by user_id
)t2
where count like "%111%"
|