511/512/534/550. 游戏玩法分析 I/Ⅱ/Ⅲ/Ⅳ

511. 游戏玩法分析 I

活动表 Activity：

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
表的主键是 (player_id, event_date)。
这张表展示了一些游戏玩家在游戏平台上的行为活动。
每行数据记录了一名玩家在退出平台之前，当天使用同一台设备登录平台后打开的游戏的数目（可能是 0 个）。

写一条 SQL 查询语句获取每位玩家 第一次登陆平台的日期。

查询结果的格式如下所示：

Activity 表：

+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-05-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+

Result 表：

+-----------+-------------+
| player_id | first_login |
+-----------+-------------+
| 1         | 2016-03-01  |
| 2         | 2017-06-25  |
| 3         | 2016-03-02  |
+-----------+-------------+

题目条件

DROP TABLE IF EXISTS `activity`;

CREATE TABLE `activity` (
  `player_id` int(11) NOT NULL,
  `device_id` int(11) DEFAULT NULL,
  `event_date` date NOT NULL,
  `games_played` int(11) DEFAULT NULL,
  PRIMARY KEY (`player_id`,`event_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

insert  into `activity`(`player_id`,`device_id`,`event_date`,`games_played`) values (1,2,'2016-03-01',5),(1,2,'2016-05-02',6),(2,3,'2017-06-25',1),(3,1,'2016-03-02',0),(3,4,'2018-07-03',5);

使用min()函数和GROUP BY分句

算法

由于每个玩家的ID(player_id)号是不变的，那么对每个玩家进行分组；再使用min()函数找出每个玩家的登陆记历史录中最早的记录。

#  MySQL
SELECT 
  `player_id`,
  MIN(`event_date`) AS min_event_date 
FROM
  `activity` 
GROUP BY `player_id` ;

结果

512. 游戏玩法分析 Ⅱ

请编写一个 SQL 查询，描述每一个玩家首次登陆的设备名称

Result table:

+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
| 1         | 2         |
| 2         | 3         |
| 3         | 1         |
+-----------+-----------+

使用Where条件分句查询

算法

将以上查出来每位玩家 第一次登陆平台的日期的结果作为一个临时表，以这个临时表做为where条件分句查询的条件，查出每一个玩家首次登陆的设备名称。

# MySQL
SELECT 
  `player_id`,`device_id` 
FROM
  `activity` 
WHERE (`player_id`, `event_date`) IN 
  (SELECT 
    `player_id`,min_event_date 
  FROM
    (SELECT 
      `player_id`,MIN(`event_date`) AS min_event_date 
    FROM
      `activity` 
    GROUP BY `player_id`) AS a) ;

结果

534. 游戏玩法分析 Ⅲ

编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家到目前为止玩了多少游戏。也就是说，在此日期之前玩家所玩的游戏总数。

Result table:

+-----------+------------+---------------------+
| player_id | event_date | games_played_so_far |
+-----------+------------+---------------------+
|         1 | 2016-03-01 |                   5 |
|         1 | 2016-05-02 |                  11 |
|         2 | 2017-06-25 |                   1 |
|         3 | 2016-03-02 |                   0 |
|         3 | 2018-07-03 |                   5 |
+-----------+------------+---------------------+
对于 ID 为 1 的玩家，2016-05-02 共玩了 5+6=11 个游戏。
对于 ID 为 3 的玩家，2018-07-03 共玩了 0+5=5 个游戏。
请注意，对于每个玩家，我们只关心玩家的登录日期。

使用 窗口函数

算法

语法：

• sum(字段1) over(partition by 字段2 order by 字段3 rows between unbounded
  preceding and current row) as 新字段名

函数说明:

• sum(tota_amount)的求和是针对后面over()窗口的求和， over中partition by name order by mon 针对name这一组按照月份排序， rows between unbounded preceding and current row限定了行是按照在当前行不限定的往前处理，通俗就是处理当前以及之前的所有行的sum，即3月时sum(amount)求的时1、2、3月的和，2月时sum(amount)求的是1、2月的和。unbounded意思无限的preceding在之前的，current row当前行。

# MySQL
SELECT 
  player_id,event_date,SUM(games_played) over (PARTITION BY player_id ORDER BY event_date) AS games_played_so_far 
FROM
  Activity ;

结果

550. 游戏玩法分析 Ⅳ

编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的比率，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

Result table:

+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33

方法一：使用 AVG() ROUND()函数 和 datediff()函数

算法

先过滤出每个用户的首次登陆日期，然后左关联，筛选次日存在的记录的比例

# MySQL
SELECT ROUND(AVG(a.event_date IS NOT NULL), 2) fraction
FROM 
    (SELECT player_id, MIN(event_date) AS login
    FROM activity
    GROUP BY player_id) p 
LEFT JOIN activity a 
ON p.player_id=a.player_id AND DATEDIFF(a.event_date, p.login)=1;

结果

[说明：此结果为力扣平台上亲测可行的结果，本人使用的是MYSQL8.0.18,预测不出来结果，暂时还没有找到原因，希望哪位大佬知道原因的，劳烦告诉小生一下 ~ 谢谢]

方法二：使用count()函数和 datediff()函数

算法

1. 首次登陆的第二天再次登陆的玩家数 / 总人数
2. Activity表 内连接 (玩家，首次登陆日期)表
3. 连接条件： ID相同 且 登陆日期 - 首次登陆日期 = 1

# MySQL
SELECT
	ROUND(COUNT(DISTINCT a.player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM
	Activity AS a
	INNER JOIN (
		SELECT
			player_id, MIN(event_date) AS first_login
		FROM
			Activity
		GROUP BY 
			player_id
	) AS b
ON a.player_id = b.player_id AND DATEDIFF(a.event_date, b.first_login) = 1;

结果

[说明：此结果为力扣平台上亲测可行的结果，本人使用的是MYSQL8.0.18,预测不出来结果，暂时还没有找到原因，希望哪位大佬知道原因的，劳烦告诉小生一下 ~ 谢谢]

来源：力扣（LeetCode）

上一题：182. 查找/删除重复的电子邮箱