如何在Cosmos DB中统计score字段不同值的出现次数

嗨，针对你给出的Cosmos DB文档结构，我来帮你实现score各值的出现次数统计，具体方案如下：

你的原始文档结构 #

[ 
  { 
    "class": "class01", 
    "student": { 
      "lastReport": [ 
        { "Name": "st01", "score": "C" }, 
        { "Name": "st02", "score": "B" } 
      ], 
      "lastTime": "2018-05-10" 
    } 
  }, 
  { 
    "class": "class02", 
    "student": { 
      "lastReport": [ 
        { "Name": "st03", "score": "C" }, 
        { "Name": "st01", "score": "A" } 
      ], 
      "lastTime": "2018-05-10" 
    } 
  }, 
  { 
    "class": "class01", 
    "student": { 
      "lastReport": [ 
        { "Name": "st01", "score": "C" }, 
        { "Name": "st02", "score": "A" }, 
        { "Name": "st03", "score": "B" } 
      ], 
      "lastTime": "2018-05-10" 
    } 
  } 
]

实现统计的Cosmos DB SQL查询 #

SELECT 
    report.score, 
    COUNT(report.score) AS Num
FROM 
    c
JOIN 
    report IN c.student.lastReport
GROUP BY 
    report.score
ORDER BY 
    Num DESC

查询逻辑说明 #

• JOIN 子句：通过JOIN report IN c.student.lastReport把嵌套在lastReport数组里的每个元素展开为独立行，确保每个score都能被单独统计。
• GROUP BY 子句：按照report.score进行分组，搭配COUNT()函数统计每个分组的元素数量。
• ORDER BY 子句：可选的排序操作，让结果按次数从多到少排列，和你期望的输出顺序完全匹配。

查询结果 #

[ 
  { "score": "C", "Num" : 3 }, 
  { "score": "B", "Num" : 2 }, 
  { "score": "A", "Num" : 2 } 
]

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