如何查询Cosmos DB按多条件统计各站点条目总数？

问题场景 #

Cosmos DB中文档结构示例：

{
    "id": "a683-89e01def5310",
    "assignmentId": "123",
    "auctionSiteId": 100,
    "status": "Scheduled",
    ...
}

单站点统计查询可正常执行：

SELECT VALUE 
    COUNT(c.id)
FROM 
    c
WHERE 
    c.auctionSiteId = 100 AND c.status = "Scheduled"

但尝试一次性统计多个站点的"Scheduled"状态条目数时，查询报错，错误信息翻译为：

{"code":"BadRequest","message":"输入值之一无效。\r\nActivityId: 5a1a98e3-aca1-40b4-8378-6cd5b4ba2bbf, Wind...

原错误查询语句：

with temp as (
  select c.auctionSiteId from c
  where c.status = "Scheduled"
),
logic as(
  select
    case when c.auctionSiteId = 96 then 1 else 0 end as count_montreal,
    case when c.auctionSiteId = 97 then 1 else 0 end as count_vancouver,
    case when c.auctionSiteId = 98 then 1 else 0 end as count_calgary,
    case when c.auctionSiteId = 99 then 1 else 0 end as count_edmonton,
    case when c.auctionSiteId = 100 then 1 else 0 end as count_toronto,
    
  from temp
)
select
  sum(count_montreal) as countMontreal,
  sum(count_vancouver) as countVancouver,
  sum(count_calgary) as countCalgary,
  sum(count_edmonton) as countEdmonton,
  sum(count_toronto) as countToronto,
from logic

错误原因分析 #

1. 引用错误：logic子查询中，from temp后使用c.auctionSiteId是错误的，temp子查询的结果集没有别名c，应改为temp.auctionSiteId
2. 多余逗号：多个case语句末尾、select列末尾的多余逗号，违反SQL语法规范
3. 写法冗余：CTE的写法过于繁琐，Cosmos DB支持更高效的条件聚合写法

修复后的查询 #

修正原CTE写法 #

with temp as (
  select c.auctionSiteId from c
  where c.status = "Scheduled"
),
logic as(
  select
    case when temp.auctionSiteId = 96 then 1 else 0 end as count_montreal,
    case when temp.auctionSiteId = 97 then 1 else 0 end as count_vancouver,
    case when temp.auctionSiteId = 98 then 1 else 0 end as count_calgary,
    case when temp.auctionSiteId = 99 then 1 else 0 end as count_edmonton,
    case when temp.auctionSiteId = 100 then 1 else 0 end as count_toronto
  from temp
)
select
  sum(count_montreal) as countMontreal,
  sum(count_vancouver) as countVancouver,
  sum(count_calgary) as countCalgary,
  sum(count_edmonton) as countEdmonton,
  sum(count_toronto) as countToronto
from logic

更高效的条件聚合写法（推荐） #

无需CTE，直接用条件聚合统计，性能更优：

SELECT
  SUM(CASE WHEN c.auctionSiteId = 96 THEN 1 ELSE 0 END) AS countMontreal,
  SUM(CASE WHEN c.auctionSiteId = 97 THEN 1 ELSE 0 END) AS countVancouver,
  SUM(CASE WHEN c.auctionSiteId = 98 THEN 1 ELSE 0 END) AS countCalgary,
  SUM(CASE WHEN c.auctionSiteId = 99 THEN 1 ELSE 0 END) AS countEdmonton,
  SUM(CASE WHEN c.auctionSiteId = 100 THEN 1 ELSE 0 END) AS countToronto
FROM c
WHERE c.status = "Scheduled"

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