PostgreSQL多列IN子查询转换为Cosmos DB等效SQL的实现问题

错误原因 #

Cosmos DB SQL 不支持 PostgreSQL 特有的行值组合匹配语法，你写的 (c.timestamp, c.device_id) IN (子查询) 写法无法被解析，因此触发了逗号附近的语法错误。

可直接运行的等效SQL #

-- 可返回指定设备列表中每个设备最新上报时间对应的完整数据
SELECT * 
FROM c
WHERE 
    -- 限定查询的设备范围
    c.device_id IN ('00137A100000D2DB', '00137A100000D299')
    -- 匹配当前设备的最大上报时间
    AND EXISTS (
        SELECT 1 
        FROM c2 
        WHERE c2.device_id = c.device_id 
        GROUP BY c2.device_id 
        HAVING MAX(c2.timestamp) = c.timestamp
    )

逻辑说明 #

1. 外层查询先筛选出属于指定设备列表的所有上报数据
2. 内层EXISTS子查询按设备分组计算最大上报时间，仅保留外层数据中时间等于对应设备最大时间的记录，也就是每个设备的最新上报数据
3. 如果不需要返回全量字段，把SELECT *换成你需要的字段列表即可，比如SELECT c.device_id, c.timestamp, c.battery

性能优化建议 #

建议给容器创建/device_id + /timestamp的组合索引，可大幅降低该查询的RU消耗，提升查询速度。

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