英文:
Azure Cosmos DB query groups and sum totals
问题
我有一个包含"teams"和一个名为playerIds（这是一个id字符串数组）字段的Cosmos DB。
我需要能够传递一个玩家ID数组并返回以下结果：
[
{playerId: "qwerty", nTeams: 3},
{playerId: "6yhjk", nTeams: 0},
{playerId: "uytgyu", nTeams: 22}
]
我已经能够返回playerIds数组中的所有团队。
我还能够计算数据库中所有用户所在的所有团队的数量。
有什么建议吗？
示例文档：
[
{
teamName: "Wonder Mutt",
id: "3456789",
playerIds: [
"uygduygf",
"37nfyefyewf",
"u298y2hf3f"]
}
]
我有以下代码，但它返回数据库中的所有玩家，而不仅仅是ID数组中的玩家：
SELECT COUNT(r) as nTeams, r as Players FROM c JOIN r IN c.PlayerIds
WHERE EXISTS
(SELECT VALUE z FROM z in c.PlayerIds WHERE ARRAY_CONTAINS(
[
"cdb5394b-401a-a752-50196bc99f23",
"22b95c43-4466-82e5-24a2ae12faca",
"1e62ed86-5a29bbae-0a791e578578",
"df6679dd4f86-84e9-d44611c72451",
"6074fa26-4842-8768-eaa23b89bdbd"
], z))
GROUP BY r
希望这有帮助。
英文:
I have a Cosmos DB that contains "teams" with a field called playerIds (which is an array of id strings]
I need to be able to pass an array of player id and return the following:
[
{playerId: "qwerty", nTeams: 3},
{playerId: "6yhjk", nTeams: 0},
{playerId: "uytgyu", nTeams: 22}
]
I've been able to return ALL the teams the playerIds array are in
I've also been able to count ALL the teams ALL users in the database are in.
Any advice?
Example document:
[
{
teamName: "Wonder Mutt",
id: "3456789",
playerIds: [
"uygduygf",
"37nfyefyewf",
"u298y2hf3f"]
}
]
I have this, but it returns ALL the players in the database NOT JUST the ones in the array of ids
SELECT COUNT(r) as nTeams, r as Players FROM c JOIN r IN c.PlayerIds
WHERE EXISTS
(SELECT VALUE z FROM z in c.PlayerIds WHERE ARRAY_CONTAINS(
[
    "cdb5394b-401a-a752-50196bc99f23",
    "22b95c43-4466-82e5-24a2ae12faca",
    "1e62ed86-5a29bbae-0a791e578578",
    "df6679dd4f86-84e9-d44611c72451",
    "6074fa26-4842-8768-eaa23b89bdbd"
], z))
GROUP BY r
答案1
得分: 1
以下是已翻译的内容：
如果你不介意跳过没有团队的球员，以下代码应该可以工作：
SELECT p AS playerId, COUNT(1) AS nTeams
FROM c
JOIN p IN c.playerIds
WHERE ARRAY_CONTAINS([ 'qwerty', '6yhjk', 'uygduygf' ], p)
GROUP BY p
这假设一个球员只在一个团队中出现一次。
英文:
If you don't mind it skipping players that have no teams the following should work:
SELECT p AS playerId, COUNT(1) AS nTeams
FROM c
JOIN p IN c.playerIds
WHERE ARRAY_CONTAINS([ 'qwerty', '6yhjk', 'uygduygf' ], p)
GROUP BY p
This assumes that a player only appears once in a team.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论