LeetCode --- 數(shù)據(jù)庫（會(huì)員）
簡書專欄：http://www.reibang.com/nb/42460386

一、題目描述

來源：力扣（LeetCode）

活動(dòng)表 Activity：

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
表的主鍵是 (player_id, event_date)辆床。
這張表展示了一些游戲玩家在游戲平臺(tái)上的行為活動(dòng)佳晶。
每行數(shù)據(jù)記錄了一名玩家在退出平臺(tái)之前，當(dāng)天使用同一臺(tái)設(shè)備登錄平臺(tái)后打開的游戲的數(shù)目（可能是 0 個(gè)）讼载。

二轿秧、具體要求

2.1 獲取每位玩家 第一次登陸平臺(tái)的日期

查詢結(jié)果的格式如下所示：

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  |

要求返回第一次登陸的時(shí)間，那么根據(jù)ID進(jìn)行分組咨堤，再取出每組中最小的時(shí)間即為第一次登陸的時(shí)間菇篡，sql如下：

select 
    a.player_id as player_id,
    min(a.event_date) as first_login
from 
    Activity a
group by 
    a.player_id

2.2 描述每一個(gè)玩家首次登陸的設(shè)備名稱

查詢結(jié)果格式在以下示例中：

Activity table:
+-----------+-----------+------------+--------------+
| 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 table:
+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
| 1         | 2         |
| 2         | 3         |
| 3         | 1         |
+-----------+-----------+

第一問已經(jīng)得到了初次登陸的時(shí)間，那么只需和原表進(jìn)行關(guān)聯(lián)一喘，即可得到初次登陸設(shè)備的ID

SELECT
    t.player_id,
    t.device_id 
FROM
    Activity t,
    ( SELECT a.player_id AS player_id, min( event_date ) AS first_time FROM Activity a GROUP BY a.player_id ) b 
WHERE
    t.player_id = b.player_id 
    AND t.event_date = b.first_time

2.3 編寫一個(gè) SQL 查詢驱还，同時(shí)報(bào)告每組玩家和日期，以及玩家到目前為止玩了多少游戲津滞。也就是說铝侵，在此日期之前玩家所玩的游戲總數(shù)灼伤。詳細(xì)情況請(qǐng)查看示例触徐。

查詢結(jié)果格式如下所示：

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            |
+-----------+-----------+------------+--------------+

Result table:
+-----------+------------+---------------------+
| 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                   |
+-----------+------------+---------------------+
對(duì)于 ID 為 1 的玩家，2016-05-02 共玩了 5+6=11 個(gè)游戲狐赡，2017-06-25 共玩了 5+6+1=12 個(gè)游戲撞鹉。
對(duì)于 ID 為 3 的玩家，2018-07-03 共玩了 0+5=5 個(gè)游戲颖侄。
請(qǐng)注意鸟雏，對(duì)于每個(gè)玩家，我們只關(guān)心玩家的登錄日期览祖。

進(jìn)行自關(guān)聯(lián)孝鹊，關(guān)聯(lián)條件為id相同且a表日期大于等于b表的日期，然后按照player_id,event_date進(jìn)行分組展蒂，利用sum函數(shù)統(tǒng)計(jì)即可得到到目前為止的游戲數(shù)量

select 
        a.player_id,
        a.event_date,
        sum(b.games_played) as games_played_so_far
from 
    Activity a,Activity b
where 
    a.player_id = b.player_id and a.event_date >= b.event_date
group by
    a.player_id,a.event_date

2.4 編寫一個(gè) SQL 查詢又活，報(bào)告在首次登錄的第二天再次登錄的玩家的分?jǐn)?shù)苔咪，四舍五入到小數(shù)點(diǎn)后兩位。換句話說柳骄，您需要計(jì)算從首次登錄日期開始至少連續(xù)兩天登錄的玩家的數(shù)量团赏，然后除以玩家總數(shù)。

（注意是首次登陸后的第二天耐薯，首次登陸時(shí)間在2.1中已得到解決）

查詢結(jié)果格式如下所示：

Activity table:
+-----------+-----------+------------+--------------+
| 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-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+

Result table:
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
只有 ID 為 1 的玩家在第一天登錄后才重新登錄舔清，所以答案是 1/3 = 0.33

目標(biāo)表自身去關(guān)聯(lián)首次時(shí)間的臨時(shí)表，判斷時(shí)間相差1天即可得到目標(biāo)id曲初。再在外層套用一層調(diào)用count()函數(shù)統(tǒng)計(jì)人數(shù)体谒，除以總?cè)藬?shù)，利用round函數(shù)四舍五入保留兩位小數(shù)即得到結(jié)果

SELECT
    round(count( * ) / ( SELECT COUNT( * ) FROM ( SELECT DISTINCT player_id FROM Activity ) m ),2) AS fraction 
FROM
    (
SELECT DISTINCT
    a.player_id 
FROM
    Activity a,
    ( SELECT player_id, min( event_date ) first_date FROM activity GROUP BY player_id ) b 
WHERE
    a.player_id = b.player_id 
    AND TIMESTAMPDIFF( DAY, b.first_date, a.event_date ) = 1 
    ) t