Cosmos DB子查询GROUP BY外层过滤重复计数异常问题求助

问题描述 #

由于Azure Cosmos DB不支持HAVING子句，我尝试通过以下查询统计文档中的重复记录数：

select d.firstName, d.lastName, d.dateOfBirth, d.Duplicates
from (
    SELECT  c.personalDetails.firstName, c.personalDetails.lastName, c.personalDetails.dateOfBirth, count(1) as Duplicates
    FROM c
    where c.personalDetails.firstName = "TestEducator" 
    group by c.personalDetails.firstName, c.personalDetails.lastName, c.personalDetails.dateOfBirth
    ) as d
where d.Duplicates > 1

子查询结果显示存在一条Duplicates值为4的记录，但在外层查询添加d.Duplicates > 1过滤条件后，原本的Duplicates=4被拆分成两条Duplicates=2的记录。我期望仅得到一条Duplicates=4的结果，请问该现象的原因是什么？如何解决？

原因分析 #

• 这是Cosmos DB查询引擎的执行逻辑导致的：当外层添加过滤条件时，引擎会对分组后的结果进行二次拆分处理，将原分组结果拆分为多个小批次执行过滤，每个批次内的count(1)会被重新计算，而非直接沿用子查询的聚合结果。
• 本质是Cosmos DB在嵌套处理分组聚合结果时，未正确保留子查询的聚合计算值，而是重新触发了部分聚合逻辑，导致统计值被拆分。

解决办法 #

方法一：嵌套子查询保留聚合结果 #

通过内层子查询单独计算重复数，确保聚合值不被二次修改：

SELECT d.firstName, d.lastName, d.dateOfBirth, d.Duplicates
FROM (
    SELECT 
        c.personalDetails.firstName, 
        c.personalDetails.lastName, 
        c.personalDetails.dateOfBirth, 
        (SELECT VALUE COUNT(1) FROM c2 WHERE c2.personalDetails.firstName = c.personalDetails.firstName AND c2.personalDetails.lastName = c.personalDetails.lastName AND c2.personalDetails.dateOfBirth = c.personalDetails.dateOfBirth) AS Duplicates
    FROM c
    WHERE c.personalDetails.firstName = "TestEducator"
    GROUP BY c.personalDetails.firstName, c.personalDetails.lastName, c.personalDetails.dateOfBirth
) AS d
WHERE d.Duplicates > 1

方法二：用ARRAY_AGG包装后统计长度 #

通过ARRAY_AGG将分组内的所有文档聚合为数组，再通过ARRAY_LENGTH获取重复数，避免二次聚合计算：

SELECT 
    d.groupedData.firstName, 
    d.groupedData.lastName, 
    d.groupedData.dateOfBirth, 
    ARRAY_LENGTH(d.items) AS Duplicates
FROM (
    SELECT 
        {
            "firstName": c.personalDetails.firstName,
            "lastName": c.personalDetails.lastName,
            "dateOfBirth": c.personalDetails.dateOfBirth
        } AS groupedData,
        ARRAY_AGG(c) AS items
    FROM c
    WHERE c.personalDetails.firstName = "TestEducator"
    GROUP BY c.personalDetails.firstName, c.personalDetails.lastName, c.personalDetails.dateOfBirth
) AS d
WHERE ARRAY_LENGTH(d.items) > 1

方法三：直接使用HAVING子句（若支持） #

目前Cosmos DB的部分API版本已支持HAVING子句，可直接替代外层过滤，无需嵌套查询：

SELECT 
    c.personalDetails.firstName, 
    c.personalDetails.lastName, 
    c.personalDetails.dateOfBirth, 
    COUNT(1) AS Duplicates
FROM c
WHERE c.personalDetails.firstName = "TestEducator"
GROUP BY c.personalDetails.firstName, c.personalDetails.lastName, c.personalDetails.dateOfBirth
HAVING COUNT(1) > 1

注：若你的Cosmos DB账户版本不支持HAVING，可优先使用前两种方法。

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