MongoDB聚合查询优化求助：计算用户订单收支总额

需求说明 #

针对单个用户（如u1），关联其所有订单的history记录，计算所有event为placed/order_placed的金额总和，再减去所有event为do_refund的金额总和，得到最终收支差额。

集合数据结构 #

orders集合 #

{
 "order_id":"123",
 "user_id":"u1",
 "history": [ "sh1","sh2" ]
}
{
 "order_id":"111",
 "user_id":"u1",
 "history": [ "sh3" ]
}

status_history集合 #

{
  "id": "sh1",
  "event": "order_placed",
  "amount": 100,
  "timestamp": ".."
},
{
  "id": "sh2",
  "event": "do_refund",
  "amount": 50,
  "timestamp": ".."
},
{
  "id": "sh3",
  "event": "placed",
  "amount": 300,
  "timestamp": ".."
}

现有代码问题分析 #

你当前的聚合代码存在几个关键问题：

• $lookup的foreignField写为_id，但status_history集合的主键字段是id，导致关联失败；
• 只过滤了event为placed的记录，未处理do_refund和order_placed的情况；
• 注释的$group阶段路径错误，实际数据中金额字段是status_history_object.amount，而非payload.prices.amount_paid。

优化后的聚合管道 #

下面是修正并优化后的聚合代码，能直接算出目标结果：

db.getCollection('orders').aggregate([
    // 过滤目标用户的订单，减少后续处理数据量
    {
        $match: { user_id: 'u1' }
    },
    // 展开history数组，每个元素单独处理
    {
        $unwind: '$history'
    },
    // 关联status_history集合，匹配对应记录
    {
        $lookup: {
            from: "status_history",
            localField: "history",
            foreignField: "id", // 修正为正确的字段名id
            as: "status"
        }
    },
    // 展开关联后的status数组，确保每个记录对应一个状态
    {
        $unwind: '$status'
    },
    // 根据event类型计算金额的正负值
    {
        $addFields: {
            adjusted_amount: {
                $cond: [
                    { $in: ['$status.event', ['order_placed', 'placed']] },
                    '$status.amount', // 下单类事件加金额
                    { $cond: [
                        { $eq: ['$status.event', 'do_refund'] },
                        { $multiply: ['$status.amount', -1] }, // 退款事件减金额
                        0 // 其他事件不影响结果
                    ]}
                ]
            }
        }
    },
    // 按用户分组，计算总差额及明细
    {
        $group: {
            _id: '$user_id',
            total_placed: {
                $sum: {
                    $cond: [
                        { $in: ['$status.event', ['order_placed', 'placed']] },
                        '$status.amount',
                        0
                    ]
                }
            },
            total_refund: {
                $sum: {
                    $cond: [
                        { $eq: ['$status.event', 'do_refund'] },
                        '$status.amount',
                        0
                    ]
                }
            },
            final_balance: { $sum: '$adjusted_amount' }
        }
    },
    // 格式化输出，让结果更清晰
    {
        $project: {
            _id: 0,
            user_id: '$_id',
            total_placed: 1,
            total_refund: 1,
            final_balance: 1
        }
    }
])

优化点说明 #

1. 修正关联字段：将$lookup的foreignField改为id，确保关联成功；
2. 统一处理事件类型：同时识别order_placed和placed为下单事件，do_refund为退款事件；
3. 一步计算差额：通过$addFields给不同事件的金额赋予正负值，再用$sum直接得到总差额；
4. 丰富输出信息：不仅返回最终差额，还统计总收入和总退款，便于核对；
5. 减少冗余阶段：去掉不必要的$project（原代码中保留的字段都是默认保留的，无需额外指定）。

运行上述代码后，针对用户u1会得到如下结果：

{
  "user_id": "u1",
  "total_placed": 400,
  "total_refund": 50,
  "final_balance": 350
}

内容的提问来源于stack exchange，提问作者Maulik Pipaliya Joyy