SQL练习00012

最新推荐文章于 2025-05-28 23:57:38 发布

qq_41885821

最新推荐文章于 2025-05-28 23:57:38 发布

阅读量328

点赞数

文章标签： sql 数据库 mysql hive

本文链接：https://blog.youkuaiyun.com/qq_41885821/article/details/111570925

版权

该博客介绍如何通过SQL查询分析游戏活动留存率。玩家活动记录表中包含玩家ID和活动日期，主键为（player_id，event_date）。留存时间定义为安装日期后一天重新登录的玩家比例。博客提供了SQL查询示例，展示每个安装日期的玩家数量及第一天留存时间。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Activity 活动记录表
在这里插入图片描述

（player_id，event_date）是此表的主键
这张表显示了某些游戏的玩家的活动情况
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）

我们将玩家的安装日期定义为该玩家的第一个登录日。

我们还将某个日期 X 的第 1 天留存时间定义为安装日期为 X 的玩家的数量，他们在 X 之后的一天重新登录，除以安装日期为 X 的玩家的数量，四舍五入到小数点后两位。

编写一个 SQL 查询，报告每个安装日期、当天安装游戏的玩家数量和第一天的留存时间。

查询结果格式如下所示：
在这里插入图片描述

玩家 1 和 3 在 2016-03-01 安装了游戏，但只有玩家 1 在 2016-03-02 重新登录，所以 2016-03-01 的第一天留存时间是 1/2=0.50
玩家 2 在 2017-06-25 安装了游戏，但在 2017-06-26 没有重新登录，因此 2017-06-25 的第一天留存时间为 0/1=0.00

--建表语句
Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int)
Truncate table Activity
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5')
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-02', '6')
insert into Activity (player_id, device_id, event_date, games_played) values ('2', '3', '2017-06-25', '1')
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-01', '0')
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5')


--答案1 窗口函数
select 
  install_dt,
  count(distinct player_id) installs,
  round(sum(if(datediff(event_date,install_dt)=1,1,0))/count(distinct player_id),2) Day1_retention 
from 
  (
  select 
    *,
    min(event_date) over(partition by player_id) install_dt
  from 
  Activity
  )
  t1   
group by 
  install_dt