Azure Cosmos DB：按指定球员ID数组分组统计所属球队数量

1. 修正查询，仅统计指定球员的球队数 #

你的现有查询会返回球队中所有球员（只要球队包含至少一个指定球员），核心问题是WHERE EXISTS的条件针对整个team文档，而非单个球员。需要在JOIN后直接过滤r（即单个playerId）属于指定数组：

SELECT r AS playerId, COUNT(1) AS nTeams
FROM c
JOIN r IN c.playerIds
WHERE ARRAY_CONTAINS([
    "cdb5394b-401a-a752-50196bc99f23",
    "22b95c43-4466-82e5-24a2ae12faca",
    "1e62ed86-5a29bbae-0a791e578578",
    "df6679dd4f86-84e9-d44611c72451",
    "6074fa26-4842-8768-eaa23b89bdbd"
], r)
GROUP BY r

这个查询会返回所有存在于至少一个球队的指定球员的统计数，但不会返回无所属球队的球员（即nTeams:0的情况）。

2. 补充返回无所属球队的球员（nTeams:0） #

要包含nTeams:0的结果，需要将指定球员数组作为虚拟数据源，和统计结果做左连接。可以用UNION ALL生成静态球员列表，再关联统计数据：

WITH targetPlayers AS (
    SELECT "cdb5394b-401a-a752-50196bc99f23" AS playerId UNION ALL
    SELECT "22b95c43-4466-82e5-24a2ae12faca" AS playerId UNION ALL
    SELECT "1e62ed86-5a29bbae-0a791e578578" AS playerId UNION ALL
    SELECT "df6679dd4f86-84e9-d44611c72451" AS playerId UNION ALL
    SELECT "6074fa26-4842-8768-eaa23b89bdbd" AS playerId
),
playerTeamCounts AS (
    SELECT r AS playerId, COUNT(1) AS nTeams
    FROM c
    JOIN r IN c.playerIds
    WHERE ARRAY_CONTAINS((SELECT VALUE p.playerId FROM p IN targetPlayers), r)
    GROUP BY r
)
SELECT 
    tp.playerId,
    ISNULL(ptc.nTeams, 0) AS nTeams
FROM targetPlayers tp
LEFT JOIN playerTeamCounts ptc ON tp.playerId = ptc.playerId

关键逻辑说明： #

• targetPlayers：通过UNION ALL构建指定球员ID的虚拟集合，避免硬编码重复。
• playerTeamCounts：基于虚拟集合过滤，统计每个指定球员的所属球队数。
• 最终通过LEFT JOIN关联虚拟列表和统计结果，用ISNULL将缺失的统计值替换为0，确保所有指定球员都出现在结果中。

3. 性能优化建议 #

• 索引检查：确保playerIds字段的范围索引处于开启状态（默认开启，若手动修改过需确认），提升ARRAY_CONTAINS和JOIN操作的效率。
• 参数化查询：在应用程序中调用时，将目标球员ID数组作为参数传入，避免硬编码，同时提升查询复用性。
• 减少扫描范围：若集合数据量较大，确保WHERE条件有效过滤无关文档，避免全表扫描。

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