如何在Cosmos DB中查询指定位置的包裹最新记录？

要实现仅保留指定位置范围内每个包裹的最新位置记录，可以通过两种高效的方式编写查询：

方法1：使用窗口函数（推荐，逻辑清晰） #

利用ROW_NUMBER()窗口函数按包裹分组，按时间倒序排序后取每组第一条（最新记录），同时先通过ST_DISTANCE过滤位置范围：

SELECT result.packagekey, result.geometry, result.inserted
FROM (
    SELECT 
        c.packagekey, 
        c.geometry, 
        c.inserted,
        -- 按包裹分组，插入时间倒序，每组第一条标记为1
        ROW_NUMBER() OVER (PARTITION BY c.packagekey ORDER BY c.inserted DESC) AS row_num
    FROM c
    -- 先筛选出指定位置1单位范围内的记录
    WHERE ST_DISTANCE(c.geometry, {'type':'Point', 'coordinates':[16.788592, 54.454656]}) <= 1
) AS result
-- 只保留每组的第一条（最新记录）
WHERE result.row_num = 1

方法2：子查询关联验证最新时间 #

先筛选位置符合条件的记录，再通过子查询确认当前记录是该包裹的最新插入时间：

SELECT c.packagekey, c.geometry, c.inserted
FROM c
WHERE 
    -- 位置范围筛选
    ST_DISTANCE(c.geometry, {'type':'Point', 'coordinates':[16.788592, 54.454656]}) <= 1
    -- 验证当前记录是该包裹的最新时间
    AND c.inserted = (
        SELECT MAX(c1.inserted)
        FROM c c1
        WHERE c1.packagekey = c.packagekey
    )

原查询问题说明 #

你之前的查询未添加位置筛选条件，且子查询是对所有包裹分组取最新时间，之后再关联原集合，会导致返回所有包裹的最新记录（无论是否在指定位置范围内），不符合需求。

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