查询特定玩家在指定日期首次游玩的关卡数量

假设你的游玩记录表名为game_plays，包含核心字段：

• player_id: 玩家唯一ID
• level_id: 关卡唯一ID
• play_time: 游玩时间（支持日期或datetime类型）

方案一：利用聚合函数获取首次游玩时间 #

先通过分组聚合，得到每个玩家每个关卡的首次游玩日期，再筛选目标玩家和指定日期，统计符合条件的关卡数：

SELECT COUNT(DISTINCT level_id) AS first_play_level_count
FROM (
    -- 子查询：获取每个玩家每个关卡的首次游玩日期
    SELECT player_id, level_id, MIN(play_time) AS first_play_date
    FROM game_plays
    GROUP BY player_id, level_id
) AS player_level_first_play
-- 筛选目标玩家和指定日期
WHERE player_id = 267 
  AND first_play_date = '2022-09-01';

方案二：利用窗口函数标记首次游玩记录 #

通过窗口函数给每个玩家的每个关卡游玩记录按时间排序，标记首次游玩的记录，再筛选统计：

SELECT COUNT(level_id) AS first_play_level_count
FROM (
    -- 子查询：给每个玩家的每个关卡游玩记录按时间排序，标记顺序
    SELECT 
        player_id, 
        level_id, 
        play_time,
        ROW_NUMBER() OVER (PARTITION BY player_id, level_id ORDER BY play_time) AS play_order
    FROM game_plays
) AS ranked_plays
-- 筛选目标玩家、首次游玩记录、指定日期
WHERE player_id = 267 
  AND play_order = 1 
  AND play_time = '2022-09-01';

注意事项 #

如果play_time是带时分秒的datetime类型，需要提取日期部分进行匹配，以方案一为例修改如下：

SELECT COUNT(DISTINCT level_id) AS first_play_level_count
FROM (
    SELECT player_id, level_id, MIN(DATE(play_time)) AS first_play_date
    FROM game_plays
    GROUP BY player_id, level_id
) AS player_level_first_play
WHERE player_id = 267 
  AND first_play_date = '2022-09-01';

对应你给出的例子：玩家ID=267此前已游玩过关卡1和2，这两个关卡的首次游玩日期早于2022-09-01，因此只有首次游玩日期恰好是2022-09-01的关卡会被统计，最终结果为1。

内容的提问来源于stack exchange，提问作者Bebo