如何用MongoDB聚合管道补全30分钟时间桶缺失数据为0

需求说明 #

• 传入某日的开始/结束时间戳，按30分钟（1800000毫秒）划分时间桶
• 无数据的时间桶需填充totalIn: 0、count: 0并保留时间戳

现有聚合管道 #

[
  {
    $match: {
      $and: [
        {
          timestamp: {
            $gte: ISODate('2023-01-16T00:00:00.000Z'),
            $lt: ISODate('2023-01-16T23:59:59.000Z')
          }
        }
      ]
    }
  },
  {
    $group: {
      _id: {
        $toDate: {
          $subtract: [
            { $toLong: '$timestamp' },
            { $mod: [{ $toLong: '$timestamp' }, 1800000] }
          ]
        }
      },
      in: { $sum: '$a' },
      out: { $sum: '$b' },
      Count: { $sum: 1 }
    }
  },
  {
    $addFields: {
      totalIn: { $add: ['$in', '$out'] }
    }
  },
  {
    $sort: { _id: 1 }
  }
]

（注：原管道存在语法错误，已修正$addFields的闭合括号）

当前结果（仅含有数据的时间桶） #

[
  {
    "_id": ISODate("2023-01-16T12:00:00.000Z"),
    "totalIn": 397,
    "count": 22
  },
  {
    "_id": ISODate("2023-01-16T01:30:00.000Z"),
    "totalIn": 222,
    "count": 2
  }
  ...
]

期望结果（包含所有时间桶，无数据字段填0） #

[
  {
    "_id": ISODate("2023-01-16T12:00:00.000Z"),
    "totalIn": 397,
    "count": 22
  },
  {
    "_id": ISODate("2023-01-16T12:30:00.000Z"),
    "totalIn": 0,
    "count": 0
  },
  {
    "_id": ISODate("2023-01-16T01:00:00.000Z"),
    "totalIn": 0,
    "count": 0
  },
  ...
]

解决方案 #

要补全无数据的时间桶，核心是先生成当日所有30分钟间隔的时间序列，再与现有聚合结果做左连接：

[
  // 第一步：生成当日所有30分钟时间桶
  {
    $documents: (function() {
      const start = ISODate('2023-01-16T00:00:00.000Z');
      const buckets = [];
      // 一天有48个30分钟桶
      for (let i = 0; i < 48; i++) {
        buckets.push({
          _id: new Date(start.getTime() + i * 1800000)
        });
      }
      return buckets;
    })()
  },
  // 第二步：左连接现有聚合结果
  {
    $lookup: {
      from: 'your_collection_name', // 替换为你的集合名
      let: { bucketId: '$_id' },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $gte: ['$timestamp', ISODate('2023-01-16T00:00:00.000Z')] },
                { $lt: ['$timestamp', ISODate('2023-01-17T00:00:00.000Z')] },
                {
                  $eq: [
                    {
                      $toDate: {
                        $subtract: [
                          { $toLong: '$timestamp' },
                          { $mod: [{ $toLong: '$timestamp' }, 1800000] }
                        ]
                      }
                    },
                    '$$bucketId'
                  ]
                }
              ]
            }
          }
        },
        {
          $group: {
            _id: null,
            in: { $sum: '$a' },
            out: { $sum: '$b' },
            count: { $sum: 1 }
          }
        },
        {
          $addFields: {
            totalIn: { $add: ['$in', '$out'] }
          }
        }
      ],
      as: 'bucketData'
    }
  },
  // 第三步：展开并填充默认值
  {
    $unwind: {
      path: '$bucketData',
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $addFields: {
      totalIn: { $ifNull: ['$bucketData.totalIn', 0] },
      count: { $ifNull: ['$bucketData.count', 0] }
    }
  },
  // 第四步：清理字段并排序
  {
    $project: {
      bucketData: 0
    }
  },
  {
    $sort: { _id: 1 }
  }
]

关键说明 #

• 使用$documents生成当日全部48个30分钟时间桶，若使用MongoDB 5.0+，可替换为动态生成逻辑避免硬编码：

  { $range: [0, 48, 1] },
  {
    $addFields: {
      _id: {
        $toDate: {
          $add: [
            { $toLong: ISODate('2023-01-16T00:00:00.000Z') },
            { $multiply: ['$$this', 1800000] }
          ]
        }
      }
    }
  }
  

• 通过$lookup左连接原集合的聚合结果，匹配对应时间桶的数据
• 用$ifNull为无数据的桶填充0值

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