AQL查询优化：为未下单的食物-日期组合添加0计数

我有一个食品订单集合foodorders，每个文档包含dayOfTheWeek（星期几）和food（所选食品）字段。需要统计一周内各类食品的下单数量，但当前的AQL查询无法为未下单的食品-日期组合补充0计数。

当前使用的AQL查询：

for fo in foodorders
 collect day = fo.dayOfTheWeek, chosenfood = fo.food with count into length
 return {dateOrder: day, food: chosenFood, cnt: length}

当前查询结果（部分示例）：

[
  {
    "dayOfTheWeek": "Monday",
    "food": "Burger",
    "cnt": 400
  },
  {
    "date": "Monday",
    "food": "Salad",
    "cnt": 100
  },
  {
    "dayOfTheWeek": "Tuesday",
    "food": "Fries",
    "cnt": 200
  },
  {
    "dayOfTheWeek": "Tuesday",
    "food": "Burger",
    "cnt": 200
  },
  {
    "dayOfTheWeek": "Tuesday",
    "food": "Salad",
    "cnt": 120
  },...
]

期望结果（包含未下单食品的0计数，部分示例）：

[
  {
    "dayOfTheWeek": "Monday",
    "food": "Burger",
    "cnt": 400
  },
  {
    "date": "Monday",
    "food": "Salad",
    "cnt": 100
  },
  {
    "date": "Monday",
    "food": "Fries",
    "cnt": 0
  },
  {
    "dayOfTheWeek": "Tuesday",
    "food": "Fries",
    "cnt": 200
  },
  {
    "dayOfTheWeek": "Tuesday",
    "food": "Burger",
    "cnt": 200
  },
  {
    "dayOfTheWeek": "Tuesday",
    "food": "Salad",
    "cnt": 120
  },...
]

要实现补全0计数，需要先生成一周所有日期和所有食品的笛卡尔积，再和原统计结果做左关联，不存在的组合填充0。

完整AQL查询（动态适配食品种类） #

// 1. 提取集合中所有唯一的食品种类
LET allFoods = (FOR fo IN foodorders RETURN DISTINCT fo.food)
// 2. 定义一周的所有日期（按顺序）
LET allDays = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]
// 3. 生成日期与食品的全量组合
LET allCombinations = (
  FOR day IN allDays
  FOR food IN allFoods
  RETURN { dayOfTheWeek: day, food: food }
)
// 4. 统计原订单中各组合的下单数量
LET orderStats = (
  FOR fo IN foodorders
  COLLECT day = fo.dayOfTheWeek, food = fo.food WITH COUNT INTO cnt
  RETURN { dayOfTheWeek: day, food: food, cnt: cnt }
)
// 5. 关联全量组合与统计数据，补全0计数
FOR combo IN allCombinations
  LET matchedStat = FIRST(FOR s IN orderStats WHERE s.dayOfTheWeek == combo.dayOfTheWeek AND s.food == combo.food RETURN s)
  RETURN {
    dayOfTheWeek: combo.dayOfTheWeek,
    food: combo.food,
    cnt: matchedStat ? matchedStat.cnt : 0
  }

关键说明 #

• allFoods通过DISTINCT自动获取集合中所有食品种类，无需手动枚举，适配数据变化。
• allDays固定一周7天，确保每个日期都被覆盖。
• allCombinations生成每个日期对应每种食品的全量组合，这是补全0计数的核心基础。
• 最后通过左关联匹配统计数据，无匹配结果时直接将cnt设为0。

如果食品种类固定（比如仅Burger、Salad、Fries），可以直接将allFoods替换为["Burger", "Salad", "Fries"]，提升查询效率。

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