MySQL/MariaDB中获取用户位置分组最后一组的首个记录

服务器版本：10.7.3-MariaDB-log

用户位置历史表： #

CREATE TABLE `location_history` (
  `id` int(10) UNSIGNED NOT NULL,
  `userId` int(10) UNSIGNED DEFAULT NULL,
  `latitude` double(10,8) DEFAULT NULL,
  `longitude` double(11,8) DEFAULT NULL,
  `createdAt` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

区域多边形点表： #

CREATE TABLE `location_area_points` (
  `id` int(10) UNSIGNED NOT NULL,
  `location_area_id` int(10) UNSIGNED DEFAULT NULL,
  `area_group_id` int(10) UNSIGNED DEFAULT NULL,
  `latitude` double(10,8) DEFAULT NULL,
  `longitude` double(11,8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

需要统计用户在区域内的停留时长，例如查询userId=1最后进入area_group_id=24的时间。目前已通过空间查询获取每个位置点所属区域，相关查询SQL及结果如下：

已实现的查询SQL： #

SELECT
    location_history.id,
    location_history.userId,
    location_history.createdAt,
    s.location_area_id
FROM
    location_history
JOIN(
    SELECT
        location_area_points.location_area_id,
        ST_PolygonFromText(
            CONCAT(
                "POLYGON((",
                GROUP_CONCAT(
                    CONCAT(
                        location_area_points.latitude,
                        ' ',
                        location_area_points.longitude
                    ) SEPARATOR ', '
                ),
                "))"
            )
        ) AS polygon
    FROM
        location_area_points
    GROUP BY
        location_area_points.location_area_id
) s
ON
    ST_CONTAINS(
        s.polygon,
        POINT(
            location_history.latitude,
            location_history.longitude
        )
    )
ORDER BY
    createdAt
DESC

查询返回示例结果： #

id  userId  createdAt       location_area_id    
11765   1   2022-07-18 17:03:23 24  
11764   1   2022-07-18 17:03:07 24  
11763   1   2022-07-18 17:02:25 24  
11762   1   2022-07-18 17:02:16 24  
11761   1   2022-07-18 17:01:24 24  
11760   1   2022-07-18 17:00:32 24  
11759   1   2022-07-18 16:59:41 24  
11758   1   2022-07-18 16:59:40 24  <----- 需要纳入结果
11757   1   2022-07-18 16:58:49 2   
11756   1   2022-07-18 16:58:04 2   
11755   1   2022-07-18 16:57:06 2   
11754   1   2022-07-18 16:56:23 24  
11752   1   2022-07-18 16:56:14 24  
11753   1   2022-07-18 16:56:14 24  
11751   1   2022-07-18 16:54:31 24  
11750   1   2022-07-18 16:54:30 24  
11749   6   2022-07-18 16:53:39 5   
11748   6   2022-07-18 16:52:47 5   
11747   6   2022-07-18 16:51:56 5   <----- 需要纳入结果
11746   6   2022-07-18 16:51:55 24  
11744   6   2022-07-18 16:51:04 24  
11745   1   2022-07-18 16:51:04 24  
11743   1   2022-07-18 16:50:13 24  
11740   1   2022-07-18 16:49:20 24  
11738   1   2022-07-18 16:48:29 24  

对上述结果进一步查询，获取每个用户最后一个连续区域分组的第一条记录，期望最终结果如下：

id userId  createdAt       location_area_id    
11758   1   2022-07-18 16:59:40 24
11747   6   2022-07-18 16:51:56 5

可以利用窗口函数识别用户的连续区域分组，再筛选目标记录，具体SQL如下：

WITH location_with_area AS (
    -- 复用原空间查询获取位置与区域关联数据
    SELECT
        location_history.id,
        location_history.userId,
        location_history.createdAt,
        s.location_area_id
    FROM
        location_history
    JOIN(
        SELECT
            location_area_points.location_area_id,
            ST_PolygonFromText(
                CONCAT(
                    "POLYGON((",
                    GROUP_CONCAT(
                        CONCAT(
                            location_area_points.latitude,
                            ' ',
                            location_area_points.longitude
                        ) SEPARATOR ', '
                    ),
                    "))"
                )
            ) AS polygon
        FROM
            location_area_points
        GROUP BY
            location_area_points.location_area_id
    ) s
    ON
        ST_CONTAINS(
            s.polygon,
            POINT(
                location_history.latitude,
                location_history.longitude
            )
        )
),
-- 标记连续区域分组
grouped_locations AS (
    SELECT
        *,
        -- 区域变化时分组编号递增，按时间倒序生成分组
        SUM(CASE WHEN prev_area_id = location_area_id THEN 0 ELSE 1 END) OVER (PARTITION BY userId ORDER BY createdAt DESC) AS group_id
    FROM (
        SELECT
            *,
            -- 获取当前记录的上一条区域ID
            LAG(location_area_id) OVER (PARTITION BY userId ORDER BY createdAt DESC) AS prev_area_id
        FROM location_with_area
    ) t
),
-- 获取每个用户的最后分组编号
last_groups AS (
    SELECT
        userId,
        MIN(group_id) AS last_group_id -- 按时间倒序，最新分组的编号最小
    FROM grouped_locations
    GROUP BY userId
)
-- 提取最后分组中最早的记录（即连续区域的第一条进入记录）
SELECT
    gl.id,
    gl.userId,
    gl.createdAt,
    gl.location_area_id
FROM grouped_locations gl
JOIN last_groups lg ON gl.userId = lg.userId AND gl.group_id = lg.last_group_id
GROUP BY gl.userId, gl.location_area_id
HAVING gl.createdAt = MIN(gl.createdAt)
ORDER BY gl.userId;

关键逻辑说明 #

• location_with_area：复用原空间查询逻辑，输出所有位置点对应的区域信息。
• grouped_locations：通过LAG函数对比当前与上一条记录的区域ID，生成连续区域的分组编号，按时间倒序排列时，最新的连续分组编号为1。
• last_groups：筛选每个用户的最后一个连续分组（即编号最小的分组）。
• 最后关联分组信息，取每个用户最后分组中时间最早的记录，即为该用户最后一次进入目标区域的起始点。

1. 空间查询性能优化
   • 为location_history的经纬度字段创建空间索引：CREATE SPATIAL INDEX idx_location_coords ON location_history(latitude, longitude);
   • 预生成并存储区域多边形：避免每次查询都通过GROUP_CONCAT实时生成，可在区域表中新增polygon字段，定时生成并存储。
2. 窗口函数效率提升
   • 为location_history创建userId+createdAt的组合索引：CREATE INDEX idx_user_created ON location_history(userId, createdAt DESC);，加速窗口函数的分区与排序操作。
3. 数据提前过滤
   • 如果仅需特定用户或时间范围的结果，在location_with_area子查询中加入WHERE条件（如userId IN (1,6)或createdAt >= '2022-07-01'），减少后续处理的数据量。

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