MongoDB聚合嵌套分组需求：按category_id与24小时时段分组

需求说明 #

• 按category_id进行一级分组
• 以API传入的start_time为起始，按每24小时一个时段进行二级分组
• 需应用传入的start_time和end_time作为时间过滤条件
• 返回每个分类下各时段的数据及统计字段（总和、最大值、最小值、平均值等）

文档结构 #

[{"category_id":"651e50405d2605dcb8d8e868","value":200,"created_at":{"$date":"2023-10-05T06:58:53.728Z"}},{"category_id":"651e50405d2605dcb8d8e86c","value":2000,"created_at":{"$date":"2023-10-25T09:17:56.723Z"}},{"category_id":"651e50405d2605dcb8d8e86b","value":1000,"created_at":{"$date":"2023-10-25T09:18:14.930Z"}},{"category_id":"651e50405d2605dcb8d8e872","value":2000,"created_at":{"$date":"2023-10-26T12:00:41.761Z"}},{"category_id":"651e50405d2605dcb8d8e86e","value":2000,"created_at":{"$date":"2023-10-26T12:00:59.349Z"}},{"category_id":"651e50405d2605dcb8d8e86c","value":1800,"created_at":{"$date":"2023-10-26T12:08:47.094Z"}},{"category_id":"651e50405d2605dcb8d8e86c","value":200,"created_at":{"$date":"2023-10-27T04:28:06.099Z"}},{"category_id":"651e50405d2605dcb8d8e86c","value":1000,"created_at":{"$date":"2023-10-27T04:28:18.356Z"}},{"category_id":"651e50405d2605dcb8d8e86e","value":2000,"created_at":{"$date":"2023-10-27T04:29:12.440Z"}}]

当前已实现的聚合查询 #

let data = await this.model.aggregate([
  {"$match":{"created_at":{"$gt":moment.utc(body.start_time).toDate(),"$lte":moment.utc(body.end_time).toDate()}}},
  {"$sort":{"created_at":1}},
  {"$set":{"total":0}},
  {"$group":{
    "_id":"$category_id",
    "total_data":{"$push":"$$ROOT"},
    "total":{"$sum":"$value"},
    "max":{"$max":{value:"$value",category_id:"$category_id"}},
    "min":{"$min":"$value"},
    "avg":{"$avg":"$value"}
  }}
])

当前返回结果 #

[{"_id":"651e50405d2605dcb8d8e86f","total_data":[{"_id":"651e50405d2605dcb8d8e868","category_id":"651e50405d2605dcb8d8e86f","value":1500,"created_at":"2023-10-27T04:28:35.870Z","total":0}],"total":1500,"max":{"value":1500,"category_id":"651e50405d2605dcb8d8e86f"},"min":1500,"avg":1500,"day":"2023-09-30T18:30:00.000Z"},{"_id":"651e50405d2605dcb8d8e86c","total_data":[{"_id":"651e50405d2605dcb8d8e868","category_id":"651e50405d2605dcb8d8e86f","value":1500,"created_at":"2023-10-27T04:28:35.870Z","total":0},{"category_id":"651e50405d2605dcb8d8e86c","value":1800,"created_at":{"$date":"2023-10-26T12:08:47.094Z"}},{"category_id":"651e50405d2605dcb8d8e86c","value":200,"created_at":{"$date":"2023-10-27T04:28:06.099Z"}},{"category_id":"651e50405d2605dcb8d8e86c","value":1000,"created_at":{"$date":"2023-10-27T04:28:18.356Z"}}],"total":1500,"max":{"value":1500,"category_id":"651e50405d2605dcb8d8e86f"},"min":1500,"avg":1500,"day":"2023-09-30T18:30:00.000Z"},.........]

期望结果结构 #

[{"_id":"651e50405d2605dcb8d8e86f","day_wise":[{"day":"2023-09-30T18:30:00.000Z","data":[{"_id":"651e50405d2605dcb8d8e868","category_id":"651e50405d2605dcb8d8e86f","value":1500,"created_at":"2023-10-27T04:28:35.870Z","total":0}]}],....other keys},{"_id":"651e50405d2605dcb8d8e86c","day_wise":[{"day":"2023-09-26T18:30:00.000Z","data":[{"category_id":"651e50405d2605dcb8d8e86c","value":1800,"created_at":{"$date":"2023-10-26T12:08:47.094Z"}}]},{"day":"2023-09-27T18:30:00.000Z","data":[{"_id":"651e50405d2605dcb8d8e868","category_id":"651e50405d2605dcb8d8e86f","value":1500,"created_at":"2023-10-27T04:28:35.870Z","total":0},{"category_id":"651e50405d2605dcb8d8e86c","value":200,"created_at":{"$date":"2023-10-27T04:28:06.099Z"}},{"category_id":"651e50405d2605dcb8d8e86c","value":1000,"created_at":{"$date":"2023-10-27T04:28:18.356Z"}}]}],...other keys}]

解决方案 #

要实现嵌套分组，需先计算每个文档所属的24小时时段，再通过两次分组完成聚合：第一次按分类+时段分组，第二次按分类汇总时段数据。

完整聚合管道代码 #

const startTime = moment.utc(body.start_time).toDate();
const endTime = moment.utc(body.end_time).toDate();
const oneDayMs = 24 * 60 * 60 * 1000;

let data = await this.model.aggregate([
  // 1. 时间范围过滤
  {
    "$match": {
      "created_at": { "$gt": startTime, "$lte": endTime }
    }
  },
  // 2. 计算每个文档所属的24小时时段起始时间
  {
    "$addFields": {
      "total": 0,
      // 计算当前文档与startTime的时间差，确定时段偏移量
      "time_diff": { "$subtract": ["$created_at", startTime] },
      "period_offset": { "$floor": { "$divide": ["$time_diff", oneDayMs] } }
    }
  },
  {
    "$addFields": {
      // 最终时段起始时间 = startTime + 偏移量*24小时
      "period_start": { "$add": [startTime, { "$multiply": ["$period_offset", oneDayMs] }] }
    }
  },
  // 3. 第一次分组：按category_id + 时段分组，计算时段内统计数据
  {
    "$group": {
      "_id": {
        "category_id": "$category_id",
        "day": "$period_start"
      },
      "data": { "$push": "$$ROOT" },
      "period_total": { "$sum": "$value" },
      "period_max": { "$max": { "value": "$value", "category_id": "$category_id" } },
      "period_min": { "$min": "$value" },
      "period_avg": { "$avg": "$value" }
    }
  },
  // 4. 第二次分组：按category_id聚合所有时段数据
  {
    "$group": {
      "_id": "$_id.category_id",
      "day_wise": {
        "$push": {
          "day": "$_id.day",
          "data": "$data",
          "total": "$period_total",
          "max": "$period_max",
          "min": "$period_min",
          "avg": "$period_avg"
        }
      },
      // 计算分类整体统计数据
      "total": { "$sum": "$period_total" },
      "max": { "$max": "$period_max.value" },
      "min": { "$min": "$period_min" },
      "avg": { "$avg": "$period_avg" }
    }
  },
  // 可选：按分类ID排序
  { "$sort": { "_id": 1 } }
])

关键说明 #

• 时段计算逻辑：以传入的start_time为基准，将每个created_at按24小时间隔划分时段，确保时段起始时间与start_time的时分秒完全一致。
• 两次分组设计：第一次分组先聚合分类下的单时段数据与统计，第二次分组将同一分类的所有时段数据汇总到day_wise数组中。
• 统计字段保留：最终结果同时包含分类整体的统计值（total、max等）和每个时段对应的统计值，满足需求中的数据展示要求。

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