SQL29 计算用户的平均次日留存率

计算用户的平均次日留存率_牛客题霸_牛客网 (nowcoder.com)icon-default.png?t=M85Bhttps://www.nowcoder.com/practice/126083961ae0415fbde061d7ebbde453?tpId=199&tqId=1975681&ru=/exam/oj&qru=/ta/sql-quick-study/question-ranking&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D199所谓次日留存,指的是同一用户(在本题中则为同一设备,即device_id)在当天和第二天都进行刷题。注意,在这题我们不关心同一用户(设备)在这天答了什么题、答题结果如何,只关心他是否答题,因此对于这题来说存在重复的数据(如下图红框所示),需要使用 DISTINCT 去重

 

而次日留存率可以这样表示:

 

具体而言,使用两个子查询,查询出两个去重的数据表,并使用条件(q2.date应该是q1.date的后一天)进行筛选,如下所示(数据未显示完全,从左至右顺序,列表名为 q1.device_id, q1.date, q2.device_id, q2.date)。

感觉计算的是次日留存率。

具体而言,使用两个子查询,查询出两个去重的数据表,并使用条件(q2.date应该是q1.date的后一天)进行筛选,因为使用的是q1左级联q2,所以q1的所有信息是显示的;而q2中只显示留存的信息,否则为null。

SELECT *
FROM 
    (SELECT DISTINCT device_id, date FROM question_practice_detail)as q1
LEFT JOIN
    (SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2
ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day)

结果如下图

 

最后,分别统计q1.device_id 和 q2.device_id 作去重后的所有条目数和去重后的次日留存条目数,即可算出次日留存率。

注意,MySQL中 COUNT在对列进行计数时不统计值为 null的条目,具体看删除行.聚合函数(汇总数据)_愈努力俞幸运的博客-CSDN博客

SELECT
	COUNT(q2.device_id)/COUNT(q1.device_id) AS avg_ret
FROM (SELECT
	DISTINCT device_id,
    date
FROM question_practice_detail) AS q1
LEFT JOIN (SELECT
	DISTINCT device_id,
    date
FROM question_practice_detail) AS q2
ON q1.device_id = q2.device_id AND DATE_ADD(q1.date, INTERVAL 1 day) = q2.date

 

 

 

 

 

 

 

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
### 回答1: hive中计算次日留存率的代码可能长这样: ``` WITH temp_table AS ( SELECT date, user_id, COUNT(DISTINCT event_name) AS event_count FROM events WHERE event_name = 'register' GROUP BY date, user_id ) SELECT retention_date, retention_count / total_count as retention_rate FROM ( SELECT retention_date, retention_count, total_count, total_count - retention_count as churn_count FROM ( SELECT DATE_ADD(temp_table.date, 1) AS retention_date, COUNT(DISTINCT temp_table.user_id) AS retention_count, COUNT(DISTINCT temp_table.user_id) OVER (PARTITION BY temp_table.date) AS total_count FROM temp_table JOIN events ON temp_table.user_id = events.user_id AND temp_table.date = events.date WHERE events.event_name = 'login' ) ) ``` 第一部分是使用temp_table表,统计每个用户每天的注册次数。 第二部分是将temp_table和event表进行连接,统计昨天注册的用户今天登陆的次数,并计算留存率。 注意:这只是一个示例代码,应该根据您的数据结构和需求进行修改。 ### 回答2: 计算次日留存率,即统计用户在某一天登录后,第二天仍然登录的比例,可以使用Hive编写代码来实现。 假设我们有一个用户登录的数据表login_data,表结构如下: user_id string login_date string 我们首先需要筛选出每个用户的登录日期和第二天的登录日期,并将结果保存到临时表中。代码如下: CREATE TABLE tmp_table AS SELECT user_id, login_date, lag(login_date) OVER(PARTITION BY user_id ORDER BY login_date ASC) AS next_date FROM login_data; 接下来,我们根据第二天的登录日期是否存在来计算次日留存率。如果第二天没有登录记录,则认为用户次日留存。代码如下: SELECT COUNT(DISTINCT user_id) AS ret_user_count, COUNT(DISTINCT CASE WHEN next_date IS NULL THEN user_id ELSE NULL END) AS non_ret_user_count, COUNT(DISTINCT CASE WHEN next_date IS NOT NULL THEN user_id ELSE NULL END) AS total_user_count, COUNT(DISTINCT CASE WHEN next_date IS NOT NULL AND login_date = next_date THEN user_id ELSE NULL END) AS ret_user_count FROM tmp_table GROUP BY next_date; 上述代码中,ret_user_count表示次日留存用户数,non_ret_user_count表示未次日留存用户数,total_user_count表示总用户数。 最后,我们可以计算次日留存率,即次日留存用户数除以总用户数。代码如下: SELECT ret_user_count / total_user_count AS retention_rate FROM ( SELECT COUNT(DISTINCT CASE WHEN next_date IS NOT NULL AND login_date = next_date THEN user_id ELSE NULL END) AS ret_user_count, COUNT(DISTINCT CASE WHEN next_date IS NOT NULL THEN user_id ELSE NULL END) AS total_user_count FROM tmp_table GROUP BY next_date ); ### 回答3: 在Hive中计算次日留存率,我们可以使用Hive的内置函数和语法来实现。 首先,我们需要确保用户访问数据已经导入到Hive表中。假设我们有一个名为user_logs的表,其中包含用户ID(user_id)和访问日期(visit_date)的字段。 接下来,我们可以使用Hive的语法来编写查询,计算次日留存率。以下是一个可能的实现示例: ```sql -- 选择当天访问过的用户 SELECT DISTINCT user_id FROM user_logs WHERE visit_date = '当前日期'; -- 选择次日仍然访问过的用户 SELECT DISTINCT l.user_id FROM user_logs l JOIN ( -- 当天访问过的用户 SELECT DISTINCT user_id FROM user_logs WHERE visit_date = '当前日期' ) d ON l.user_id = d.user_id WHERE l.visit_date = '次日日期'; -- 计算次日留存率 SELECT COUNT(DISTINCT l.user_id) * 100.0 / COUNT(DISTINCT d.user_id) AS retention_rate FROM user_logs l JOIN ( -- 当天访问过的用户 SELECT DISTINCT user_id FROM user_logs WHERE visit_date = '当前日期' ) d ON l.user_id = d.user_id WHERE l.visit_date = '次日日期'; ``` 在上面的代码中,我们先选择当天访问过的用户,然后通过将用户ID与次日仍然访问过的用户ID进行匹配,筛选出次日留存的用户。最后,通过计算次日留存用户数占当天访问用户数的比例,得到次日留存率。 注意,代码中的"当前日期"和"次日日期"应替换为具体的日期,以便计算相应日期的次日留存率

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值