SQL（中间表的使用）_sql中间表使用方法

作者：从前慢现在也慢 | 2024-05-28 03:02:09

踩

sql中间表使用方法

##有时候你需要创造一张中间表来进行业务的计算

1097. 游戏玩法分析 V

Activity 活动记录表

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

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

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

查询结果格式如下所示：

Activity 表：
±----------±----------±-----------±-------------+
| player_id | device_id | event_date | games_played |
±----------±----------±-----------±-------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-03-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-01 | 0 |
| 3 | 4 | 2016-07-03 | 5 |
±----------±----------±-----------±-------------+

Result 表：
±-----------±---------±---------------+
| install_dt | installs | Day1_retention |
±-----------±---------±---------------+
| 2016-03-01 | 2 | 0.50 |
| 2017-06-25 | 1 | 0.00 |
±-----------±---------±---------------+
玩家 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

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/game-play-analysis-v
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

1.首先构造一张这样的表

player_id	event_date	first_day	internal

这张表的含义：每一条记录中此用户首次在那天登录，并且当前登录的时间，和与首次登录的间隔时间

那么从上表中：通过fisrt_day 分组，计算 internal = 1 的记录数就是首次登录后，一天剩余的用户数

构造中间表sql

select d.player_id,d.event_date,c.first_day,(d.event_date-c.first_day) internal
  from
     (select b.player_id,b.event_date
     from Activity b) d
  join 
     (select a.player_id,Min(event_date ) first_day
     from Activity a 
     group by a.player_id) c 
  on d.player_id = c.player_id
1
2
3
4
5
6
7
8
9

2.最终sql

sum(case when e.event_date=e.first_day then 1 else 0 end) 计算在首日登录的人数
sum(e.internal=1) 在first_day登录后一天的人数

select e.first_day install_dt,sum(case when e.event_date=e.first_day then 1 else 0 end) installs,Round(sum(e.internal=1)/sum(case when e.event_date=e.first_day then 1 else 0 end),2) as Day1_retention 
from 
  (select d.player_id,d.event_date,c.first_day,(d.event_date-c.first_day) internal
  from
     (select b.player_id,b.event_date
     from Activity b) d
  join 
     (select a.player_id,Min(event_date ) first_day
     from Activity a 
     group by a.player_id) c 
  on d.player_id = c.player_id) e
group by e.first_day
1
2
3
4
5
6
7
8
9
10
11
12

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/635331