窗口函数查询问题？

Question

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

查询结果格式如下所示。

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;

Answer 1

您被要求只计算他们的第二次登录是否是连续的。第一个查询仅通过计算第二行的差1来实现这一点。

WHERE t.rn = 2
AND t.difference = 1

第二个查询将接受任何连续登录。

游行示威

请注意，这两个查询都需要group by tp.all_players。MySQL，在某些模式下，有时会为您推断组。但不要指望它，其他数据库也不会。见MySQL对组的处理。考虑在MySQL中运行ANSI模式，同时学习SQL以使其更好地遵循标准。