怎样在SQL中查找每个用户首次和末次访问页面

在用户行为分析的业务场景中，统计每个用户的首次访问页面和末次访问页面是常见需求，借助SQL的窗口函数FIRST_VALUE和LAST_VALUE可以快速实现该统计目标。这两个函数属于窗口函数的范畴，能够在指定的分组和排序规则下，获取分组内第一行或最后一行的字段值。

基础表结构说明

首先我们构造一张用户访问记录表user_page_visit，表结构如下：

表中存储了每个用户不同时间的页面访问记录，每条记录对应一次用户访问行为。

函数基本用法介绍

FIRST_VALUE函数

FIRST_VALUE函数的作用是返回窗口分区内第一行指定列的值，基本语法如下：

FIRST_VALUE(列名) OVER (
    PARTITION BY 分组列 
    ORDER BY 排序列 [ASC|DESC]
    [窗口帧设置]
)

其中PARTITION BY用于指定分组的列，这里我们需要按用户分组，所以分组列是user_id；ORDER BY用于指定分组内的排序规则，统计首次访问需要按访问时间升序排列。

LAST_VALUE函数

LAST_VALUE函数的作用是返回窗口分区内最后一行指定列的值，基本语法和FIRST_VALUE类似：

LAST_VALUE(列名) OVER (
    PARTITION BY 分组列 
    ORDER BY 排序列 [ASC|DESC]
    [窗口帧设置]
)

需要注意的是，LAST_VALUE的默认窗口帧设置可能导致结果不符合预期，后续会详细说明。

完整查询实现

要实现查询每个用户的首次和末次访问页面，我们需要先按用户分组，再按访问时间排序，分别调用两个函数获取对应页面地址，最后去重得到每个用户的结果。完整SQL语句如下：

SELECT DISTINCT
    user_id,
    -- 获取首次访问页面，按访问时间升序，取分组第一行
    FIRST_VALUE(page_url) OVER (
        PARTITION BY user_id 
        ORDER BY visit_time ASC
    ) AS first_visit_page,
    -- 获取末次访问页面，需要设置窗口帧为整个分区
    LAST_VALUE(page_url) OVER (
        PARTITION BY user_id 
        ORDER BY visit_time ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_visit_page
FROM user_page_visit
ORDER BY user_id;

关键注意事项

• LAST_VALUE的默认窗口帧是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是只到当前行，如果不手动设置窗口帧为整个分区，会返回当前行及之前最后一行的数据，无法得到真正的末次访问页面，所以必须添加ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
• 如果同一个用户在同一时间有多次访问记录，排序时可以添加第二排序字段，比如访问记录的ID，避免排序结果不稳定导致统计结果偏差。
• 如果只需要统计有访问记录的用户，不需要额外添加过滤条件，因为表中存在的用户才会被统计到；如果需要排除异常用户，可以在外层添加WHERE条件过滤。

结果验证示例

假设user_page_visit表中有如下测试数据：

INSERT INTO user_page_visit (user_id, visit_time, page_url) VALUES
(1, '2024-05-01 10:00:00', '/home'),
(1, '2024-05-01 10:05:00', '/list'),
(1, '2024-05-01 10:10:00', '/detail'),
(2, '2024-05-01 09:00:00', '/home'),
(2, '2024-05-01 09:03:00', '/cart');

执行上述查询语句后，得到的结果如下：

结果符合每个用户首次和末次访问页面的预期，说明SQL语句逻辑正确。

SQLFIRST_VALUELAST_VALUE用户访问记录窗口函数修改时间：2026-07-01 09:15:28