MongoDB交叉连接查询实现用户-店铺交易金额全组合求和

集合原始数据 #

{User:'user1',transaction:10,shop:'shop1'},
{User:'user1',transaction:20,shop:'shop2'},
{User:'user2',transaction:60,shop:'shop3'},
{User:'user3',transaction:80,shop:'shop4'},
{User:'user1',transaction:50,shop:'shop2'},
{User:'user2',transaction:5,shop:'shop1'},
{User:'user3',transaction:11,shop:'shop4'},
{User:'user2',transaction:32,shop:'shop2'},
{User:'user3',transaction:56,shop:'shop1'},
{User:'user1',transaction:89,shop:'shop3'},
{User:'user2',transaction:12,shop:'shop4'}

期望结果 #

User1 Shop1 10
User1 Shop2 70
User1 Shop3 89
User1 Shop4 0
User2 Shop1 5
User2 Shop2 32
User2 Shop3 60
User2 Shop4 12
User3 Shop1 56
User3 Shop2 0
User3 Shop3 0
User3 Shop4 91

当前尝试的查询 #

test> db.transaction.aggregate(
[{$lookup: 
{
  from: 'transaction',
  pipeline: [{$project: {_id:0, User:1}}],
  as: 'User'
}}, 
{$unwind: 
{
  path: "$User"
}}, 
{$project: {
User: 1,
shop: 1
}}
])
test>

最优实现方案 #

要实现所有用户-店铺的全量组合并计算交易总和，核心是先生成用户与店铺的笛卡尔积，再关联原始数据求和。以下分两种版本给出方案：

方案1（MongoDB 5.0+ 推荐） #

利用MongoDB 5.0新增的$crossJoin运算符直接生成笛卡尔积，代码更简洁：

db.transaction.aggregate([
  // 1. 获取所有唯一用户
  { $group: { _id: null, users: { $addToSet: "$User" } } },
  // 2. 获取所有唯一店铺并与用户列表做笛卡尔积
  { $lookup: {
      from: "transaction",
      pipeline: [ { $group: { _id: null, shops: { $addToSet: "$shop" } } } ],
      as: "shopsData"
    } },
  { $unwind: "$shopsData" },
  { $crossJoin: {
      keys: {
        user: "$users",
        shop: "$shopsData.shops"
      }
    } },
  // 3. 关联原始交易数据，计算每个组合的交易总和
  { $lookup: {
      from: "transaction",
      let: { currentUser: "$user", currentShop: "$shop" },
      pipeline: [
        { $match: {
            $expr: {
              $and: [
                { $eq: [ "$User", "$$currentUser" ] },
                { $eq: [ "$shop", "$$currentShop" ] }
              ]
            }
          } },
        { $group: { _id: null, total: { $sum: "$transaction" } } },
        { $project: { _id: 0, total: 1 } }
      ],
      as: "transactionTotal"
    } },
  // 4. 保留无交易的组合
  { $unwind: {
      path: "$transactionTotal",
      preserveNullAndEmptyArrays: true
    } },
  // 5. 无交易时总和设为0，整理输出字段
  { $project: {
      _id: 0,
      User: "$user",
      Shop: "$shop",
      Total: { $ifNull: [ "$transactionTotal.total", 0 ] }
    } },
  // 6. 按用户、店铺排序，匹配期望结果格式
  { $sort: { User: 1, Shop: 1 } }
])

方案2（兼容MongoDB 5.0以下版本） #

通过嵌套$lookup和$unwind实现笛卡尔积，兼容低版本：

db.transaction.aggregate([
  // 1. 获取所有唯一用户
  { $group: { _id: null, users: { $addToSet: "$User" } } },
  { $unwind: "$users" },
  // 2. 获取所有唯一店铺
  { $lookup: {
      from: "transaction",
      pipeline: [ { $group: { _id: null, shops: { $addToSet: "$shop" } } } ],
      as: "shopsData"
    } },
  { $unwind: "$shopsData" },
  { $unwind: "$shopsData.shops" },
  // 3. 关联交易数据求和
  { $lookup: {
      from: "transaction",
      let: { currentUser: "$users", currentShop: "$shopsData.shops" },
      pipeline: [
        { $match: {
            $expr: {
              $and: [
                { $eq: [ "$User", "$$currentUser" ] },
                { $eq: [ "$shop", "$$currentShop" ] }
              ]
            }
          } },
        { $group: { _id: null, total: { $sum: "$transaction" } } },
        { $project: { _id: 0, total: 1 } }
      ],
      as: "transactionTotal"
    } },
  // 4. 保留无交易组合
  { $unwind: { path: "$transactionTotal", preserveNullAndEmptyArrays: true } },
  // 5. 处理无交易情况，整理输出
  { $project: {
      _id: 0,
      User: "$users",
      Shop: "$shopsData.shops",
      Total: { $ifNull: [ "$transactionTotal.total", 0 ] }
    } },
  // 6. 排序匹配期望结果
  { $sort: { User: 1, Shop: 1 } }
])

关键逻辑说明 #

• 先用$group+$addToSet提取唯一用户和店铺，避免生成重复组合
• 通过笛卡尔积生成所有可能的用户-店铺配对
• 带条件的$lookup关联原始交易数据，用$sum计算每个组合的交易总和
• 用$ifNull将无交易组合的总和设为0
• 最后排序让输出结果与期望格式一致

内容的提问来源于stack exchange，提问作者Jayesh.c