撰寫一個 SQL 查詢來報告在他們第一次登錄后的第二天再次登錄的玩家比例，四舍五入到小數點后兩位。換句話說，您需要計算從第一次登錄之日起至少連續兩天登錄的玩家數量，然后將該數字除以玩家總數。
查詢結果格式如下例。
Example 1:
Input:
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 |
----------- ----------- ------------ --------------
Output:
-----------
| fraction |
-----------
| 0.33 |
-----------
Explanation:
Only the player with id 1 logged back in after the first day he had logged in so the answer is 1/3 = 0.33
使用此查詢時，此 leetcode問題通過了所有測驗用例：
WITH temp AS(
SELECT player_id,
event_date - LAG(event_date, 1) OVER (PARTITION BY player_id ORDER BY event_date) AS difference,
RANK() OVER (PARTITION BY player_id ORDER BY event_date) as rn
FROM activity
),
tp AS
(
SELECT count(distinct(player_id)) as all_players
FROM activity
)
SELECT ROUND(count(t.player_id)/tp.all_players,2) AS fraction
FROM temp t
JOIN tp
WHERE t.rn = 2
AND t.difference = 1
當我使用下面的查詢時，它不適用于所有測驗用例，誰能告訴我為什么這在上面的作業中不起作用：
WITH temp AS(
SELECT DISTINCT(player_id), difference FROM
(SELECT player_id, event_date - LAG(event_date, 1) OVER (PARTITION BY player_id ORDER BY event_date) AS difference FROM activity) x WHERE x.difference = 1
),
tp AS
(
SELECT count(distinct(player_id)) as all_players
FROM activity
)
SELECT ROUND(COUNT(*)/tp.all_players, 2) as fraction FROM temp, tp;
uj5u.com熱心網友回復：
僅當他們的第二次登錄是連續的時，您才被要求進行計數。第一個查詢通過僅計算差異為 1 的第二行來完成此操作。
WHERE t.rn = 2
AND t.difference = 1
第二個查詢將采用任何連續登錄。
示范
請注意，兩個查詢都需要group by tp.all_players. 在某些模式下，MySQL 有時會為您推斷組。但是不要指望它，其他資料庫不會。請參閱MySQL 處理 GROUP BY。考慮在學習 SQL 的同時以ANSI 模式運行 MySQL，使其更好地遵循標準。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/322917.html
上一篇：用php遍歷mysql表的兩列