MongoDB中用Group+Lookup实现销售额统计与最新状态关联查询

原始数据 #

[
  {_id: 1, date:'2023-07-28',sales:1,ad_id:1,status: 'Active'},
  {_id: 2 , date:'2023-07-29',sales:2,ad_id:1,status: 'Active'},
  {_id: 3, date: '2023-07-30',sales: 4,ad_id:1,status: 'Paused'},
  {_id: 4, date: '2023-07-30',sales: 4,ad_id:2,status: 'Paused'}
]

需求说明 #

• 统计2023-07-28至2023-07-29期间，按ad_id分组的sales总和
• 同时获取每个ad_id对应的**最新记录（按_id最大值判断）**的status
• 期望结果：

{ad_id: 1, sales: 3, status: 'Paused'}

MySQL 实现参考 #

with ad as (
 SELECT sum(sales) as totalSales,ad_id from table_name
    where date >= '2023-07-28' and date <= '2023-07-29'
 group by ad_id 
)
SELECT * from ad left join (
   SELECT id,ad_id,status from table_name where id in (
       select max(id) from table_name group by ad_id
   )
) t on t.ad_id = ad.ad_id

MongoDB 聚合实现（Group + Lookup） #

db.collection.aggregate([
  // 筛选指定日期范围的记录
  {
    $match: {
      date: {
        $gte: "2023-07-28",
        $lte: "2023-07-29"
      }
    }
  },
  // 按ad_id分组统计销售额总和
  {
    $group: {
      _id: "$ad_id",
      sales: { $sum: "$sales" }
    }
  },
  // 关联每个ad_id的最新记录，提取status
  {
    $lookup: {
      from: "collection", // 替换为你的集合名称
      let: { targetAdId: "$_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$ad_id", "$$targetAdId"] } } },
        { $sort: { _id: -1 } }, // 按_id倒序，最新记录排在最前
        { $limit: 1 }, // 只取第一条最新记录
        { $project: { status: 1, _id: 0 } } // 仅保留status字段
      ],
      as: "latestStatus"
    }
  },
  // 整理字段格式，提取status值
  {
    $addFields: {
      status: { $arrayElemAt: ["$latestStatus.status", 0] },
      ad_id: "$_id"
    }
  },
  // 移除冗余字段
  {
    $project: {
      _id: 0,
      latestStatus: 0
    }
  }
])

步骤说明 #

• $match：缩小数据范围，只保留目标日期内的记录
• $group：按ad_id聚合，计算每组销售额总和
• $lookup：针对每个ad_id，在原集合中查询匹配记录，通过排序+限制获取最新一条，仅提取需要的status
• $addFields：将数组形式的latestStatus转换为单独的status字段，同时把分组用的_id重命名为ad_id
• $project：清除不需要的字段，输出符合要求的结果

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