CosmosDB NoSQL API实现不同状态报价的订单去重计数问题

需要统计拥有ACTIVE和VOID状态报价的订单总数，要求去重计数——若某订单的报价列表中有多个同状态的报价，该订单在对应状态的计数中仅计为1次。

数据模型 #

• 容器：order
  包含字段：
  • orderId（字符串类型）
  • orderStatus（字符串类型）
  • dateCreatedStamp（LocalDateTime类型）
  • dateModifiedStamp（LocalDateTime类型）
  • quotes（Quote对象列表）
• Quote对象字段：
  • quoteId（字符串类型）
  • quoteStatus（字符串类型，可选值：ACTIVE、VOID）
  • quoteDateCreatedStamp（LocalDateTime类型）
  • quoteDateModifiedStamp（LocalDateTime类型）

原查询问题 #

原查询未实现去重，当一个订单包含多个同状态报价时会重复计数（例如某订单有2个VOID报价，VOID状态计数为2而非1）：

SELECT q.quoteStatus, COUNT(or)
FROM order or
JOIN q IN or.quotes
WHERE q.quoteStatus IN ("ACTIVE", "VOID")
GROUP BY q.quoteStatus

解决方案 #

方案1：子查询+DISTINCT #

先通过子查询生成唯一的orderId与quoteStatus组合，再按状态汇总计数：

SELECT grouped.quoteStatus, COUNT(grouped.orderId)
FROM (
    SELECT DISTINCT or.orderId, q.quoteStatus
    FROM order or
    JOIN q IN or.quotes
    WHERE q.quoteStatus IN ("ACTIVE", "VOID")
) grouped
GROUP BY grouped.quoteStatus

方案2：直接使用COUNT(DISTINCT) #

更简洁的写法，直接在分组时对orderId去重计数：

SELECT q.quoteStatus, COUNT(DISTINCT or.orderId)
FROM order or
JOIN q IN or.quotes
WHERE q.quoteStatus IN ("ACTIVE", "VOID")
GROUP BY q.quoteStatus

两种方案都能实现需求，方案2更简洁高效，推荐使用。

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