如何在CosmosDB中查询重复s_id组的最新日期完整记录？

方案一：使用关联子查询 #

SELECT c.s_id, c.my_date, c.a_value
FROM c
WHERE c.s_id IN (
    SELECT VALUE cs.s_id
    FROM cs
    GROUP BY cs.s_id
    HAVING COUNT(1) > 1
)
AND c.my_date = (
    SELECT MAX(cm.my_date)
    FROM cm
    WHERE cm.s_id = c.s_id
)

说明 #

1. 内层子查询筛选出所有存在重复记录的s_id值。
2. 外层查询先过滤出s_id在上述列表中的文档，再通过关联子查询确认该文档的my_date是对应s_id的最大值（最新日期），确保只获取每个重复分组的最新完整记录。

方案二：使用ARRAY_AGG和TOP 1 #

SELECT 
    grouped.s_id,
    latest.my_date,
    latest.a_value
FROM (
    SELECT 
        c.s_id,
        ARRAY(SELECT TOP 1 * FROM ARRAY_AGG(c) ORDER BY my_date DESC)[0] AS latest,
        COUNT(1) AS record_count
    FROM c
    GROUP BY c.s_id
) grouped
WHERE grouped.record_count > 1

说明 #

1. 内层查询按s_id分组，用ARRAY_AGG收集每个分组的所有记录，再通过SELECT TOP 1 ... ORDER BY my_date DESC提取分组内的最新记录。
2. 外层查询过滤掉仅含单条记录的分组，并投影出最新记录的字段。

两种方案都避免了原查询中使用MAX(a_value)导致的字段不匹配问题，直接获取对应最新日期的完整记录。

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