按组获取最新变更记录及locations表用户状态变更时间查询需求

针对你提到的位置追踪应用场景——需要查询用户位置state最后一次变更的时间，从而展示“您自[timestamp]起处于在/不在范围内”的信息，我给你一套高效的实现方案，用SQL窗口函数就能搞定，而且性能表现出色。

先明确问题核心 #

我们需要为每个用户找到当前状态（指定timestamp时的state）的起始时间，也就是上一次状态变更后，当前状态首次出现的时间点。比如用户1在timestamp=400时的state是0，那要找到这个连续0状态的最早timestamp。

假设表结构 #

先假设你的locations表结构如下（如果实际字段名不同，替换即可）：

CREATE TABLE locations (
    user_id INT,
    state TINYINT, -- 1=在范围内，0=在范围外
    timestamp BIGINT, -- 时间戳，数值型
    PRIMARY KEY (user_id, timestamp) -- 建议先建这个复合主键或索引
);

高效查询SQL实现 #

这里用窗口函数LAG()和累计求和来标记连续状态组，避免低效的自连接操作：

WITH state_change_markers AS (
    SELECT 
        user_id,
        state,
        timestamp,
        -- 标记状态变更点：当前记录和上一条state不同，或者是用户的第一条记录
        CASE 
            WHEN LAG(state) OVER (PARTITION BY user_id ORDER BY timestamp) != state 
                 OR LAG(state) OVER (PARTITION BY user_id ORDER BY timestamp) IS NULL
            THEN 1 
            ELSE 0 
        END AS is_state_change
    FROM locations
),
state_continuous_groups AS (
    SELECT 
        user_id,
        state,
        timestamp,
        -- 用累计求和为每个连续状态生成唯一分组ID
        SUM(is_state_change) OVER (PARTITION BY user_id ORDER BY timestamp) AS state_group_id
    FROM state_change_markers
)
-- 查询用户1在timestamp<=400时的当前状态起始时间
SELECT 
    state,
    MIN(timestamp) AS current_state_start_time
FROM state_continuous_groups
WHERE user_id = 1 AND timestamp <= 400
GROUP BY user_id, state, state_group_id
ORDER BY state_group_id DESC
LIMIT 1;

为什么这个方案高效？ #

1. 窗口函数的优势：LAG()和SUM()都是基于有序数据集的计算，只要你给locations表建了(user_id, timestamp)的复合索引，数据库可以直接利用索引完成排序和分组，不需要额外的排序操作，速度非常快。
2. 避免自连接：传统的自连接方法需要多次扫描表，对于大数据量的位置表来说性能很差，而窗口函数只需要一次扫描就能完成标记和分组。

索引优化建议 #

为了让查询效率最大化，一定要创建这个复合索引：

CREATE INDEX idx_locations_user_timestamp ON locations(user_id, timestamp);

示例验证 #

比如用户1的位置记录如下：

user_id | state | timestamp
1 | 1 | 100
1 | 1 | 200
1 | 0 | 300
1 | 0 | 400

执行查询后，会返回state=0，current_state_start_time=300，正好对应“您自300起处于不在范围内”的展示需求。

如果用户1从始至终都是state=1：

user_id | state | timestamp
1 | 1 | 50
1 | 1 | 150
1 | 1 | 400

查询结果会返回state=1，current_state_start_time=50，符合预期。

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