求每个用户在最近一次会话中点击的最后一个页面（last touch）。

考察要点

这道题是“分组 Top-N”问题的一个变种，属于“分组 Top-1 中的 Top-1”，面试官想考察你对复杂业务逻辑的 SQL 拆解能力、窗口函数的熟练运用以及 SQL 性能优化意识。具体来说，这道题会看你是否掌握：

1. 窗口函数：特别是 ROW_NUMBER() 或 RANK() / DENSE_RANK() 在多层级排序场景下的应用。
2. CTE (公用表表达式)：如何使用 WITH 子句组织复杂查询，使其逻辑清晰、可读性强。
3. 索引设计：能否根据查询逻辑（PARTITION BY, ORDER BY, WHERE）设计出最高效的复合索引。

核心概念

解决此问题的核心是进行两次排序和筛选：首先按用户筛选出最近的会话，然后在这个会话内筛选出最近的一次点击。

1. 窗口函数 (Window Functions) 窗口函数对一组与当前行相关的表行（即“窗口”）执行计算。与聚合函数不同，它在返回计算结果的同时不合并行。

• 语法: FUNCTION_NAME() OVER (PARTITION BY ... ORDER BY ...)
• PARTITION BY user_id: 定义窗口的边界，这里表示按用户ID进行分组。所有计算都在每个用户的自己的数据“窗口”内独立进行。
• ORDER BY click_time DESC: 定义窗口内行的排序规则，这里是按点击时间倒序排。
• ROW_NUMBER(): 为窗口内的每一行分配一个从 1 开始的唯一排名。

示例数据 (user_clicks 表):

目标输出:

2. CTE (Common Table Expression) CTE 使用 WITH 关键字定义一个临时的、命名的结果集，可以被后续的 SELECT, INSERT, UPDATE, DELETE 语句引用。它极大地增强了复杂 SQL 的可读性和模块化。在本题中，我们可以用一个 CTE 来完成排序，再用主查询来筛选，逻辑非常清晰。

 1WITH RankedClicks AS (
 2    -- 这里是 CTE 的定义，用于排名
 3    SELECT
 4        user_id,
 5        session_id,
 6        page_url,
 7        ROW_NUMBER() OVER (PARTITION BY user_id, session_id ORDER BY click_time DESC) as click_rank_in_session,
 8        DENSE_RANK() OVER (PARTITION BY user_id ORDER BY session_id DESC) as session_rank -- 假设 session_id 递增
 9    FROM user_clicks
10)
11-- 主查询从 CTE 中筛选
12SELECT user_id, page_url
13FROM RankedClicks
14WHERE session_rank = 1 AND click_rank_in_session = 1;

多种解法

解法 1：使用窗口函数 + CTE（最优解）

这是最现代、最高效、可读性最好的解法，也是面试官最期待看到的答案。通过一次全表扫描，使用两个窗口函数同时计算出会话排名和会话内点击排名。

• 版本: ✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server；❌ MySQL 5.7 不支持窗口函数。

• 思路:

  1. 定义一个 CTE RankedData。
  2. 在 CTE 内部，使用 DENSE_RANK() OVER (PARTITION BY user_id ORDER BY session_id DESC) 对每个用户的会话进行排名。session_rank = 1 代表最新会话。（注意: 此处假设 session_id 是随时间递增的，如果不是，需要用会话内的最大时间戳来排序会话）。
  3. 同时，使用 ROW_NUMBER() OVER (PARTITION BY user_id, session_id ORDER BY click_time DESC) 对每个会话内的点击进行排名。click_rank = 1 代表会话内最后一次点击。
  4. 从 CTE RankedData 中筛选出 session_rank = 1 且 click_rank = 1 的记录。

• SQL 实现:

 1WITH RankedData AS (
 2    SELECT
 3        user_id,
 4        page_url,
 5        -- 假设 session_id 是字符串且按时间递增，如 'S1', 'S2' 或时间戳生成的ID
 6        -- 如果 session_id 是无序的（如UUID），则需要更复杂的会话时间定义，见“常见追问”
 7        DENSE_RANK() OVER (PARTITION BY user_id ORDER BY session_id DESC) AS session_rank,
 8        ROW_NUMBER() OVER (PARTITION BY user_id, session_id ORDER BY click_time DESC) AS click_rank
 9    FROM
10        user_clicks
11)
12SELECT
13    user_id,
14    page_url AS last_touch_page
15FROM
16    RankedData
17WHERE
18    session_rank = 1 AND click_rank = 1;

• 复杂度:
  • 时间复杂度: $O(N \log N)$，主要开销在于对窗口内数据进行排序。
  • 空间复杂度: $O(N)$，需要存储窗口函数的中间结果。
• 索引依赖:
  • 为了极致优化窗口函数的性能，可以创建一个复合索引：CREATE INDEX idx_user_session_time ON user_clicks (user_id, session_id DESC, click_time DESC);。
  • 这个索引完美匹配了两个窗口函数的 PARTITION BY 和 ORDER BY 子句，数据库可能可以利用索引的有序性进行流式计算，避免在内存中进行昂贵的排序操作，从而将时间复杂度优化到接近 $O(N)$。

解法 2：使用子查询和 JOIN（兼容老版本）

这种方法不依赖窗口函数，通过多次 GROUP BY 和 JOIN 来逐步筛选，在 MySQL 5.7 等老版本中可以使用。

• 版本: ✅ 所有主流数据库版本。

• 思路:

  1. 步骤一 (子查询 latest_sessions): 找出每个用户的最新 session_id。
  2. 步骤二 (子查询 latest_clicks_in_session): 将原表与 latest_sessions 连接，筛选出所有在最新会话中的点击记录。然后，在此基础上找出每个用户的最晚 click_time。
  3. 步骤三 (主查询): 将原表与 latest_clicks_in_session 连接，条件是 user_id 和 click_time 都匹配，从而定位到最终的 page_url。

• SQL 实现:

 1SELECT
 2    uc1.user_id,
 3    uc1.page_url AS last_touch_page
 4FROM
 5    user_clicks uc1
 6INNER JOIN (
 7    -- 步骤二：找出每个用户在最新会话中的最晚点击时间
 8    SELECT
 9        t.user_id,
10        MAX(t.click_time) AS max_click_time
11    FROM
12        user_clicks t
13    INNER JOIN (
14        -- 步骤一：找出每个用户的最新 session_id
15        SELECT
16            user_id,
17            MAX(session_id) AS max_session_id
18        FROM
19            user_clicks
20        GROUP BY
21            user_id
22    ) AS latest_sessions
23    ON
24        t.user_id = latest_sessions.user_id AND t.session_id = latest_sessions.max_session_id
25    GROUP BY
26        t.user_id
27) AS final_clicks
28ON
29    uc1.user_id = final_clicks.user_id AND uc1.click_time = final_clicks.max_click_time;

• 复杂度:
  • 时间复杂度: 较高。涉及多次 GROUP BY（通常是 $O(N)$ 的哈希聚合）和 JOIN。整体性能通常劣于窗口函数方案，因为需要多次扫描和处理数据。
  • 空间复杂度: $O(U + U)$，其中 U 是用户数。需要存储两个子查询的中间结果。
• 索引依赖:
  • 需要在 (user_id, session_id) 和 (user_id, click_time) 上建立索引来加速 GROUP BY 和 JOIN 操作。

常见低分回答

1. 只找最近点击，忽略会话：ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY click_time DESC) 然后取第一名。这会错误地找出用户的全局最后一次点击，而这个点击可能属于一个很早的、但一直未关闭的会话。
2. 在 GROUP BY 中误用非聚合列：SELECT user_id, page_url, MAX(click_time) FROM user_clicks GROUP BY user_id;。在标准的 SQL 或 MySQL 的 ONLY_FULL_GROUP_BY 模式下会直接报错。在旧版 MySQL 的宽松模式下，page_url 会返回一个不确定的值，结果是错误的。
3. 使用性能极差的相关子查询：在 WHERE 子句中为每一行都执行一次子查询来判断是否为最新，会导致 $O(N^2)$ 级别的性能灾难。
   sql

   ⧉⛶

    1-- 反例：性能极差
    2SELECT user_id, page_url, click_time
    3FROM user_clicks uc1
    4WHERE click_time = (
    5    SELECT MAX(uc2.click_time)
    6    FROM user_clicks uc2
    7    WHERE uc2.user_id = uc1.user_id
    8      AND uc2.session_id = (
    9          SELECT MAX(uc3.session_id)
   10          FROM user_clicks uc3
   11          WHERE uc3.user_id = uc1.user_id
   12      )
   13);

4. 对 NULL 的处理不当：如果 page_url 或 session_id 可能为 NULL，在 JOIN 或 GROUP BY 时可能会产生非预期的结果，需要用 COALESCE 或 IS NULL 检查来妥善处理。

工程实践与优化

• 索引是关键：对于解法 1，INDEX(user_id, session_id DESC, click_time DESC) 是性能的决定性因素。没有这个索引，数据库需要对全表数据进行排序，当数据量达到千万或亿级别时，会消耗大量内存和 CPU，甚至导致查询超时。
• 执行计划分析 (EXPLAIN)：
  • 优良的计划（解法 1）: EXPLAIN 结果中，Extra 字段应显示 Using index 或 Window: ... (cost=... rows=...)，表示利用了索引来避免全表排序。type 最好是 index 或 range。
  • 糟糕的计划: type 为 ALL（全表扫描），Extra 字段出现 Using temporary; Using filesort，表示进行了磁盘文件排序，性能会非常差。
• 数据倾斜：如果某个 user_id（如爬虫或机器人账号）拥有海量点击数据，PARTITION BY user_id 会导致单个窗口过大，拖慢整个查询。工程上需要对这类超级用户进行预处理或单独处理。
• 物化视图/中间表：对于这种高频分析类查询，如果实时性要求不是秒级，可以每天 T+1 生成一个结果表（物化视图）。这样，业务查询就变成对结果表的简单 SELECT，响应速度极快。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍（百亿级），这条 SQL 性能怎样？怎么优化？

   • 回答要点 1 (瓶颈分析)：即使有最优索引，单机 MySQL 处理百亿级数据的排序和窗口计算也会非常慢，内存和 I/O 都会成为瓶颈。
   • 回答要点 2 (架构优化)：
     • 分布式计算: 使用 Spark SQL、Presto、ClickHouse 等分布式查询引擎。它们可以将数据分片（Partition），在多个计算节点上并行执行 PARTITION BY 和排序，最后汇总结果，专门为大规模数据分析设计。
     • 数据预聚合: 建立一个用户会话的聚合表 user_session_summary，该表存储 user_id, session_id, session_start_time, session_end_time, last_page_url 等字段。通过流式处理（如 Flink/Spark Streaming）或批处理任务（如 Airflow/DolphinScheduler 调度的 Spark 任务）实时或准实时地更新这张表。业务查询直接查这张聚合表，实现 O(1) 查询。

2. 追问：如果 session_id 是无序的 UUID，你的窗口函数解法要怎么改？

   • 回答要点: 这是一个很好的问题，说明面试官在考察你对业务细节的严谨性。原解法中 ORDER BY session_id DESC 的假设失效了。
   • 修正方案: 我们需要重新定义“最新会话”。一个会话的“新旧”应该由它内部包含的点击时间来决定。我们可以用会话内的最大时间戳作为该会话的排序依据。
   • SQL 实现:
     sql

     ⧉⛶

      1WITH SessionMaxTime AS (
      2    SELECT
      3        user_id,
      4        session_id,
      5        -- 计算每个会话的结束时间
      6        MAX(click_time) AS session_end_time
      7    FROM user_clicks
      8    GROUP BY user_id, session_id
      9),
     10RankedData AS (
     11    SELECT
     12        uc.user_id,
     13        uc.page_url,
     14        -- 按会话结束时间倒序，给会话排名
     15        DENSE_RANK() OVER (PARTITION BY uc.user_id ORDER BY smt.session_end_time DESC) AS session_rank,
     16        -- 按点击时间倒序，给会话内点击排名
     17        ROW_NUMBER() OVER (PARTITION BY uc.user_id, uc.session_id ORDER BY uc.click_time DESC) AS click_rank
     18    FROM user_clicks uc
     19    JOIN SessionMaxTime smt ON uc.user_id = smt.user_id AND uc.session_id = smt.session_id
     20)
     21SELECT
     22    user_id,
     23    page_url AS last_touch_page
     24FROM RankedData
     25WHERE session_rank = 1 AND click_rank = 1;

3. 追问：如果要找每个用户最近一次会话的“倒数第二个”点击页面，怎么实现？

   • 回答要点: 这凸显了窗口函数解法的巨大优势：灵活性和扩展性。
   • 实现: 只需要将解法 1 中最后的 WHERE 条件从 click_rank = 1 改为 click_rank = 2 即可。
     sql

     ⧉⛶

      1-- ... CTE 'RankedData' 部分不变 ...
      2SELECT
      3    user_id,
      4    page_url AS second_last_touch_page
      5FROM
      6    RankedData
      7WHERE
      8    session_rank = 1 AND click_rank = 2;

   • 对比: 如果使用子查询和 JOIN 的方法，要实现“倒数第二”会变得异常复杂，可能需要复杂的自连接和 HAVING COUNT(...)，代码可读性和性能都会急剧下降。这恰好证明了窗口函数在处理排名问题上的优越性。