如何查询每个用户的倒数第二个活动（单活动时输出该活动）

样本数据 #

需求 #

查询每个用户的倒数第二个活动，若用户仅有一个活动，则输出该活动，期望结果：

问题分析 #

你之前的SQL存在两个问题：

• 第一个查询只筛选seq=2，但Lion只有1个活动，对应的seq=1，无法被选中；
• 第二个查询筛选seq=1 OR seq=2，会返回每个用户的最新2个活动（比如Ace会返回Yoga和Singing），不符合仅返回倒数第二个（或唯一活动）的要求。

另外，DENSE_RANK()并非最优选择，若同一用户存在同一时间的多个活动，它会给这些活动分配相同排名，可能导致结果偏离预期。更适合用ROW_NUMBER()为每个用户的活动按时间倒序生成唯一序号。

正确SQL（PostgreSQL） #

SELECT username, activity AS penultimate_act
FROM (
    SELECT
        username,
        activity,
        ROW_NUMBER() OVER (PARTITION BY username ORDER BY "Start Time" DESC) AS seq,
        COUNT(*) OVER (PARTITION BY username) AS total_acts
    FROM activities
) t
WHERE (total_acts = 1 AND seq = 1) OR (total_acts > 1 AND seq = 2);

逻辑解释 #

1. ROW_NUMBER() OVER (PARTITION BY username ORDER BY "Start Time" DESC)：按用户分组，将每个用户的活动按开始时间倒序排列，生成唯一序号seq（最新活动为1，倒数第二为2）；
2. COUNT(*) OVER (PARTITION BY username)：统计每个用户的活动总数total_acts；
3. 外层筛选：若用户只有1个活动则取seq=1，若活动数大于1则取seq=2，完全匹配需求。

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