Cosmos DB中无法获取按指定值排序的前5条记录问题求助

我来帮你搞定这个排序问题！先拆解下你之前两次尝试里的核心问题，再给你一个能正常运行的查询方案。

你之前查询的问题分析 #

1. 第一次尝试的错误：你用JOIN子查询计算的是全集合的Compliant/NonCompliant/NullCompliant数量，再在分组里SUM这些值——这会导致每个HerdProfileId的统计结果都是全集合的总数（每个分组都会重复关联全集合的统计值），同时Cosmos DB无法识别这种嵌套子查询返回的字段作为ORDER BY的目标，所以报错“无法映射到文档路径”。
2. 第二次尝试的错误：Cosmos DB的SQL语法不支持在包含GROUP BY的子查询里直接使用ORDER BY，必须把排序逻辑放到外层查询中处理。

正确的查询写法 #

我们可以用CASE WHEN在GROUP BY内部直接计算每个HerdProfileId分组下的三种状态数量，然后在外层进行排序和取TOP 5：

SELECT TOP 5 cr.HerdProfileId, cr.Compliant, cr.NonCompliant, cr.NullCompliant
FROM (
    SELECT 
        c.HerdProfileId,
        COUNT(CASE WHEN c.Compliant = true THEN 1 END) AS Compliant,
        COUNT(CASE WHEN c.Compliant = false THEN 1 END) AS NonCompliant,
        COUNT(CASE WHEN c.Compliant IS NULL THEN 1 END) AS NullCompliant
    FROM c
    WHERE c.Period = 201948
    GROUP BY c.HerdProfileId
) cr
WHERE cr.NonCompliant > 0
ORDER BY cr.NonCompliant DESC

为什么这个查询可行？ #

• 正确的聚合逻辑：通过CASE WHEN，我们在每个HerdProfileId的分组内分别统计符合条件的记录数，结果是每个分组的真实统计值，而非全集合的重复累加。
• 符合Cosmos DB语法规则：排序操作放在外层查询，针对已经聚合完成的结果集进行，此时cr.NonCompliant是明确的字段，能被正确识别用于排序。

额外提示 #

如果查询性能不理想，建议给Period字段创建范围索引，它作为WHERE子句里的过滤条件，合适的索引能大幅提升聚合查询的速度。

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