如何自己制作首页网站,现在的网站开发方式,作业代做网站,科技创新小发明1.什么是留存率
留存率是指在特定时间段内#xff0c;仍然继续使用某项产品或服务的用户占用户总数的百分比。
通常#xff0c;留存率会以日#xff0c;周#xff0c;或月为单位进行统计和分析。 2.SQL留存率常见问题
1.计算新用户登录的日期的次日留存率以及3日留存率 …1.什么是留存率
留存率是指在特定时间段内仍然继续使用某项产品或服务的用户占用户总数的百分比。
通常留存率会以日周或月为单位进行统计和分析。 2.SQL留存率常见问题
1.计算新用户登录的日期的次日留存率以及3日留存率
CREATE TABLE ods_user_login_log (user_id VARCHAR(255) NOT NULL,login_dt VARCHAR(255) NOT NULL
);
INSERT INTO ods_user_login_log VALUES
(001,20240701),
(001,20240701),
(002,20240701),
(003,20240701),
(001,20240702),
(002,20240702),
(002,20240702),
(001,20240703),
(002,20240704),
(004,20240704)
;
有一张用户登录日志表 ods_usr_login_log包含 user_id用户ID和 login_dt登录日期。每个用户在同一天可能登录多次。
问题计算有新用户登录的日期的次日留存率和3日留存率。
N日留存用户数指某日活跃的用户在第N日再次活跃的用户数量。
本道题思路如下 找出每个用户首次登录的日期 使用子查询 t1通过 MIN(login_dt) 函数获取每个用户的首次登录日期并将其转换为日期格式 date。 将所有登录记录转换为日期格式 子查询 t2 将 login_dt 字段转换为日期格式。 计算次日留存率和3日留存率 在最终的 SELECT 语句中使用 DATEDIFF(t2.dt, t1.dt) 来计算用户登录日期与其首次登录日期的天数差。对于次日留存率当天数差为1时使用 COUNT(DISTINCT t2.user_id) 统计次日再次登录的用户数量并将其除以首次登录的用户数量 COUNT(DISTINCT t1.user_id)得到次日留存率。对于3日留存率类似地当天数差为3时计算第3日再次登录的用户数并进行相应的计算。 LEFT JOIN 进行多表联查
为什么使用LEFT JOIN?LEFT JOIN 的作用是保留左表中的所有记录即使在右表中没有匹配项。在这个SQL语句中左表是子查询 t1它包含每个用户的首次登录日期。右表是 t2它包含用户的所有登录记录。通过 LEFT JOIN我们可以确保所有首次登录的用户都会出现在最终的结果中即使这些用户在后续的指定日期如次日或第3日没有再次登录。这样可以保证在计算留存率时所有首次登录的用户都被纳入分母即使他们在后续日期没有登录确保留存率计算准确无误。
一分钟搞明白Join、Left Join、Right Join的区别_join left join right join-CSDN博客
-- 计算用户首次登录的日期
with t1 as (select user_id,cast(min(login_dt) as date) as dtfrom ods_user_login_loggroup by user_id, cast(login_dt as date)
),
-- 将数据转为date格式
t2 as (select user_id, cast(login_dt as date) as dtfrom ods_user_login_loggroup by user_id, cast(login_dt as date)
)select t1.dt-- 确保用户在同一天登录的数据只被计算一次
,count(distinct case when datediff(t2.dt, t1.dt) 1 then t2.user_id else null end) / count(distinct t1.user_id) as retain_1d_rate
,count(distinct case when datediff(t2.dt, t1.dt) 3 then t2.user_id else null end) / count(distinct t1.user_id) as retain_3d_rate
from t1
left join t2
on t1.user_id t2.user_id
group by t1.dt
;
2.2021年11月每天新用户的次日留存率_牛客题霸_牛客网
在本道题中我们同样是计算新用户的次日留存率。
但数据列却完全不同第一道题中我们的数据列就只有登录时间。
在本题中我们既有in_time进入时间又有离开时间out_time。
那么就会存在一个情况当在一条记录中如果in_time-进入时间和out_time-离开时间跨天了。
如(101, 9002, 2021-11-04 11:00:55, 2021-11-05 11:00:59, 0);
这时我们也算该用户在两天里都活跃过
那么我们该如果计算才能实现该逻辑呢
具体来说就是将in_time和out_time合并为同一个数据列记录每个用户在每天的活跃情况。
select uid, date(in_time) as active_dt from tb_user_logunion all
select uid, date(out_time) as active_dt from tb_user_log
具体来说我们就是把out_time也算作是活跃日。
在这里我们使用UNION ALL是因为
它可以保留所有的用户行为记录包括重复的日期。这对于准确计算次日留存率很重要。我们需要考虑每个用户可能在同一天多次访问的情况。使用 UNION ALL 确保了我们捕获到所有这些访问记录。然后distinct去重
剩下的操作就跟第一道题一样计算用户首次登录的日期然后通过计算datediff(end_time, start_time)来确定日期间隔。
with t1 as (select uid,min(in_time) as dtfrom tb_user_loggroup by uid
),
t2 as (select uid, date(in_time) as active_dt from tb_user_logunion allselect uid, date(out_time) as active_dt from tb_user_log
)select date_format(t1.dt,%Y-%m-%d) as dt,round(count(distinct case when datediff(t2.active_dt,t1.dt) 1 then t2.uid else null end) /count(distinct t1.uid),2) as uv_left_ratefrom t1left join t2on t1.uid t2.uidwhere date_format(t1.dt,%Y-%m) 2021-11-- t1.dt BETWEEN 2021-11-01 AND 2021-11-30group by date_format(t1.dt,%Y-%m-%d)优化小建议
使用 DATE_FORMAT(t1.dt, %Y-%m) 2021-11 进行筛选这样的条件会在查询时对所有结果再次进行格式化可能会影响性能。 格式化 筛选
使用 t1.dt BETWEEN 2021-11-01 AND 2021-11-30 进行时间范围过滤这样的条件可以利用之前已经格式化好的数据性能更好。 只有筛选
