MongoDB聚合操作详解

引言

聚合操作是MongoDB进行复杂数据分析的强大工具。本文详细讲解聚合管道的使用。

一、聚合管道

1.1 基本概念

db.collection.aggregate([
  { $stage1: {...} },
  { $stage2: {...} },
  { $stage3: {...} }
]);

二、常用阶段

2.1 $match - 匹配

db.orders.aggregate([
  { $match: {
    status: "completed",
    amount: { $gte: 100 }
  }}
]);

2.2 $group - 分组

db.orders.aggregate([
  { $group: {
    _id: "$user_id",
    total: { $sum: "$amount" },
    count: { $sum: 1 },
    avg: { $avg: "$amount" }
  }}
]);

2.3 $project - 投影

db.orders.aggregate([
  { $project: {
    user_id: 1,
    amount: 1,
    year: { $year: "$created_at" }
  }}
]);

2.4 $sort - 排序

1
2
3

db.orders.aggregate([
  { $sort: { amount: -1 } }
]);

2.5 $limit和$skip

db.orders.aggregate([
  { $sort: { amount: -1 } },
  { $skip: 10 },
  { $limit: 10 }
]);

三、高级操作

3.1 $lookup - 关联查询

db.orders.aggregate([
  { $lookup: {
    from: "users",
    localField: "user_id",
    foreignField: "_id",
    as: "user"
  }}
]);

3.2 $unwind - 展开数组

1
2
3

db.orders.aggregate([
  { $unwind: "$items" }
]);

db.orders.aggregate([
  { $facet: {
    "by_status": [
      { $group: { _id: "$status", count: { $sum: 1 } } }
    ],
    "by_amount": [
      { $bucket: {
        groupBy: "$amount",
        boundaries: [0, 100, 500, 1000],
        default: "other"
      }}
    ]
  }}
]);

四、实战示例

4.1 统计每月销售额

db.orders.aggregate([
  { $match: {
    status: "completed",
    created_at: {
      $gte: ISODate("2023-01-01"),
      $lt: ISODate("2024-01-01")
    }
  }},
  { $group: {
    _id: {
      year: { $year: "$created_at" },
      month: { $month: "$created_at" }
    },
    total: { $sum: "$amount" },
    count: { $sum: 1 }
  }},
  { $sort: { "_id.year": 1, "_id.month": 1 } }
]);

4.2 TOP N商品

db.orders.aggregate([
  { $unwind: "$items" },
  { $group: {
    _id: "$items.product_id",
    quantity: { $sum: "$items.quantity" },
    revenue: { $sum: { $multiply: ["$items.price", "$items.quantity"] } }
  }},
  { $sort: { revenue: -1 } },
  { $limit: 10 }
]);

五、性能优化

// 1. 将$match放在管道前面
// 2. 使用索引支持的字段
// 3. 使用$project减少数据量
// 4. 避免在$group前使用$unwind

// 优化示例
db.orders.aggregate([
  { $match: { status: "completed" } },  // 先过滤
  { $project: { user_id: 1, amount: 1 } },  // 减少数据量
  { $group: {
    _id: "$user_id",
    total: { $sum: "$amount" }
  }}
]);