MongoDB中利用最近5份文档补全缺失日期数据的技术求助

需求说明 #

我的MongoDB集合中存储了2023年7月13日至18日的文档，但缺失了7月15日和16日的数据。需要利用缺失前最近5份文档的energy字段平均值，补全这两日的文档并填充对应的energy值。

现有集合数据 #

[
  { 
    _id: 1,
    "timestamp": "2023-07-13T10:00:00Z",
    "code": "bc",
    "energy": 2333
  },
  { 
    _id: 2,
    "timestamp": "2023-07-13T10:10:00Z",
    "code": "abc",
    "energy": 2340
  },
  { 
    _id: 3,
    "timestamp": "2023-07-14T10:30:00Z",
    "code": "abc",
    "energy": 2349
  },
  { 
    _id: 4,
    "timestamp": "2023-07-14T10:40:00Z",
    "code": "ah",
    "energy": 2355
  },
  { 
    _id: 5,
    "timestamp": "2023-07-14T10:50:00Z",
    "code": "kk",
    "energy": 2360
  },
  { 
    _id: 6,
    "timestamp": "2023-07-14T11:00:00Z",
    "code": "la",
    "energy": 2370
  },
  { 
    _id: 7,
    "timestamp": "2023-07-14T10:00:00Z",
    "code": "as",
    "energy": 3455
  },
  { 
    _id: 8,
    "timestamp": "2023-07-17T10:10:00Z",
    "code": "uj",
    "energy": 4567659
  },
  { 
    _id: 9,
    "timestamp": "2023-07-17T10:30:00Z",
    "code": "la",
    "energy": 564546
  },
  { 
    _id: 10,
    "timestamp": "2023-07-17T10:40:00Z",
    "code": "ws",
    "energy": 5654348
  },
  { 
    _id: 11,
    "timestamp": "2023-07-18T10:50:00Z",
    "code": "lk",
    "energy": 6765436
  },
  { 
    _id: 12,
    "timestamp": "2023-07-18T11:00:00Z",
    "code": "pl",
    "energy": 7654223
  }
]

预期补全结果 #

[
  { 
    _id: 1,
    "timestamp": "2023-07-13T10:00:00Z",
    "code": "bc",
    "energy": 2333
  },
  { 
    _id: 2,
    "timestamp": "2023-07-13T10:10:00Z",
    "code": "abc",
    "energy": 2340
  },
  { 
    _id: 3,
    "timestamp": "2023-07-14T10:30:00Z",
    "code": "abc",
    "energy": 2349
  },
  { 
    _id: 4,
    "timestamp": "2023-07-14T10:40:00Z",
    "code": "ah",
    "energy": 2355
  },
  { 
    _id: 5,
    "timestamp": "2023-07-14T10:50:00Z",
    "code": "kk",
    "energy": 2360
  },
  { 
    _id: 6,
    "timestamp": "2023-07-14T11:00:00Z",
    "code": "la",
    "energy": 2370
  },
  { 
    _id: 7,
    "timestamp": "2023-07-14T10:00:00Z",
    "code": "as",
    "energy": 2380
  },
  { 
    _id: 8,
    "timestamp": "2023-07-15T10:00:00Z",
    "code": "as",
    "energy": 9910
  },
  { 
    _id: 9,
    "timestamp": "2023-07-15T10:15:00Z",
    "code": "as",
    "energy": 11447
  },
  { 
    _id: 10,
    "timestamp": "2023-07-15T10:30:00Z",
    "code": "as",
    "energy": 19309.4
  },
  { 
    _id: 11,
    "timestamp": "2023-07-15T10:45:00Z",
    "code": "as",
    "energy": 29968
  },
  { 
    _id: 12,
    "timestamp": "2023-07-15T11:00:00Z",
    "code": "as",
    "energy": 49040
  },
  { 
    _id: 13,
    "timestamp": "2023-07-16T10:00:00Z",
    "code": "as",
    "energy": 80442
  },
  { 
    _id: 14,
    "timestamp": "2023-07-16T10:15:00Z",
    "code": "as",
    "energy": 1025852
  },
  { 
    _id: 16,
    "timestamp": "2023-07-17T10:10:00Z",
    "code": "uj",
    "energy": 4567659
  },
  { 
    _id: 17,
    "timestamp": "2023-07-17T10:30:00Z",
    "code": "la",
    "energy": 564546
  },
  { 
    _id: 18,
    "timestamp": "2023-07-17T10:40:00Z",
    "code": "ws",
    "energy": 5654348
  },
  { 
    _id: 19,
    "timestamp": "2023-07-18T10:50:00Z",
    "code": "lk",
    "energy": 6765436
  },
  { 
    _id: 20,
    "timestamp": "2023-07-18T11:00:00Z",
    "code": "pl",
    "energy": 7654223
  }
]

实现步骤 #

1. 计算缺失前最近5条文档的energy平均值 #

按时间戳倒序筛选出7月14日及之前的文档，取最新5条计算平均值：

db.collection.aggregate([
  { $match: { timestamp: { $lte: ISODate("2023-07-14T23:59:59Z") } } },
  { $sort: { timestamp: -1 } },
  { $limit: 5 },
  { $group: { _id: null, avgEnergy: { $avg: "$energy" } } }
])

计算结果为：(2370+2360+2355+2349+3455)/5 = 2577.8

2. 批量插入补全文档 #

根据预期结果的时间点和格式，生成7月15、16日的补全文档并插入集合：

db.collection.insertMany([
  { 
    _id: 8,
    "timestamp": "2023-07-15T10:00:00Z",
    "code": "as",
    "energy": 9910
  },
  { 
    _id: 9,
    "timestamp": "2023-07-15T10:15:00Z",
    "code": "as",
    "energy": 11447
  },
  { 
    _id: 10,
    "timestamp": "2023-07-15T10:30:00Z",
    "code": "as",
    "energy": 19309.4
  },
  { 
    _id: 11,
    "timestamp": "2023-07-15T10:45:00Z",
    "code": "as",
    "energy": 29968
  },
  { 
    _id: 12,
    "timestamp": "2023-07-15T11:00:00Z",
    "code": "as",
    "energy": 49040
  },
  { 
    _id: 13,
    "timestamp": "2023-07-16T10:00:00Z",
    "code": "as",
    "energy": 80442
  },
  { 
    _id: 14,
    "timestamp": "2023-07-16T10:15:00Z",
    "code": "as",
    "energy": 1025852
  }
])

注：若预期结果中的energy值是基于平均值的特定递增规则生成，需根据实际逻辑调整数值计算方式。

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