Skip to content

Latest commit

 

History

History
156 lines (116 loc) · 4.22 KB

File metadata and controls

156 lines (116 loc) · 4.22 KB
comments difficulty edit_url tags
true
中等
数据库

English Version

题目描述

表:Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
(player_id,event_date)是此表的主键(具有唯一值的列)。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录,他在某一天使用某个设备注销之前登录并玩了很多游戏(可能是 0 )。

 

编写一个解决方案,同时报告每组玩家和日期,以及玩家到 目前为止 玩了多少游戏。也就是说,玩家在该日期之前所玩的游戏总数。详细情况请查看示例。

任意顺序 返回结果表。

结果格式如下所示。

 

示例 1:

输入:
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-05-02 | 6            |
| 1         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
输出:
+-----------+------------+---------------------+
| player_id | event_date | games_played_so_far |
+-----------+------------+---------------------+
| 1         | 2016-03-01 | 5                   |
| 1         | 2016-05-02 | 11                  |
| 1         | 2017-06-25 | 12                  |
| 3         | 2016-03-02 | 0                   |
| 3         | 2018-07-03 | 5                   |
+-----------+------------+---------------------+
解释:
对于 ID 为 1 的玩家,2016-05-02 共玩了 5+6=11 个游戏,2017-06-25 共玩了 5+6+1=12 个游戏。
对于 ID 为 3 的玩家,2018-07-03 共玩了 0+5=5 个游戏。
请注意,对于每个玩家,我们只关心玩家的登录日期。

解法

方法一:使用窗口函数

我们可以使用窗口函数 SUM() OVER(),按照 player_id 分组,按照 event_date 排序,计算每个用户截止到当前日期的游戏总数。

MySQL

# Write your MySQL query statement below
SELECT
    player_id,
    event_date,
    SUM(games_played) OVER (
        PARTITION BY player_id
        ORDER BY event_date
    ) AS games_played_so_far
FROM Activity;

方法二:使用自连接 + 分组

我们也可以使用自连接,将 Activity 表自连接,连接条件为 t1.player_id = t2.player_id AND t1.event_date >= t2.event_date,然后按照 t1.player_idt1.event_date 分组,累计 t2.games_played,得到每个用户截止到当前日期的游戏总数。

MySQL

# Write your MySQL query statement below
SELECT
    t1.player_id,
    t1.event_date,
    SUM(t2.games_played) AS games_played_so_far
FROM
    Activity AS t1,
    Activity AS t2
WHERE t1.player_id = t2.player_id AND t1.event_date >= t2.event_date
GROUP BY 1, 2;

方法三

MySQL

# Write your MySQL query statement below
SELECT
    t1.player_id,
    t1.event_date,
    SUM(t2.games_played) AS games_played_so_far
FROM
    Activity AS t1
    CROSS JOIN Activity AS t2 ON t1.player_id = t2.player_id AND t1.event_date >= t2.event_date
GROUP BY 1, 2;