MongoDB查询指定日期范围内每月首尾邻近数据

存储的数据 #

{_id: 1, value: 4.0, date: ISODate("2024-01-01T00:00:00.000Z")}
{_id: 2, value: 6.0, date: ISODate("2024-01-03T00:00:00.000Z")}
{_id: 3, value: 9.0, date: ISODate("2024-01-08T00:00:00.000Z")}
{_id: 4, value: 11.0, date: ISODate("2024-01-14T00:00:00.000Z")}
{_id: 5, value: 16.0, date: ISODate("2024-01-28T00:00:00.000Z")}
{_id: 6, value: 20.0, date: ISODate("2024-02-03T00:00:00.000Z")}
{_id: 7, value: 24.0, date: ISODate("2024-02-07T00:00:00.000Z")}
{_id: 8, value: 30.0, date: ISODate("2024-02-19T00:00:00.000Z")}
{_id: 9, value: 37.0, date: ISODate("2024-02-25T00:00:00.000Z")}
{_id: 10, value: 40.0, date: ISODate("2024-03-02T00:00:00.000Z")}
{_id: 11, value: 44.0, date: ISODate("2024-03-06T00:00:00.000Z")}
{_id: 12, value: 52.0, date: ISODate("2024-03-10T00:00:00.000Z")}
{_id: 13, value: 60.0, date: ISODate("2024-03-17T00:00:00.000Z")}
{_id: 14, value: 60.0, date: ISODate("2024-03-26T00:00:00.000Z")}
{_id: 15, value: 68.0, date: ISODate("2024-03-29T00:00:00.000Z")}
{_id: 16, value: 40.0, date: ISODate("2024-04-04T00:00:00.000Z")}

需求说明 #

需编写MongoDB查询语句，获取指定日期范围内每个月的两条记录：

• 最接近当月月初的记录
• 最接近当月月末的记录
  若当月仅存在一条记录，则仅返回该条记录。例如查询范围为2024-01-01至2024-04-30时，返回结果如下。

期望结果 #

{_id: 1, value: 4.0, date: ISODate("2024-01-01T00:00:00.000Z")}
{_id: 5, value: 16.0, date: ISODate("2024-01-28T00:00:00.000Z")}
{_id: 6, value: 20.0, date: ISODate("2024-02-03T00:00:00.000Z")}
{_id: 9, value: 37.0, date: ISODate("2024-02-25T00:00:00.000Z")}
{_id: 10, value: 40.0, date: ISODate("2024-03-02T00:00:00.000Z")}
{_id: 15, value: 68.0, date: ISODate("2024-03-29T00:00:00.000Z")}
{_id: 16, value: 40.0, date: ISODate("2024-04-04T00:00:00.000Z")}

解决方案查询语句 #

db.collection.aggregate([
  // 1. 匹配指定日期范围的记录
  {
    $match: {
      date: {
        $gte: ISODate("2024-01-01T00:00:00.000Z"),
        $lte: ISODate("2024-04-30T23:59:59.999Z")
      }
    }
  },
  // 2. 按年-月分组，计算每组的月初、月末，同时保留所有组内记录
  {
    $group: {
      _id: {
        year: { $year: "$date" },
        month: { $month: "$date" }
      },
      records: { $push: "$$ROOT" },
      monthStart: { $min: { $dateFromParts: { year: { $year: "$date" }, month: { $month: "$date" }, day: 1 } } },
      monthEnd: { $max: { $dateFromParts: { year: { $year: "$date" }, month: { $month: "$date" }, day: { $dayOfMonth: { $lastDayOfMonth: "$date" } } } } }
    }
  },
  // 3. 对每组记录，计算每条记录与月初、月末的时间差，筛选出目标记录
  {
    $project: {
      targetRecords: {
        $filter: {
          input: "$records",
          as: "record",
          cond: {
            $or: [
              // 最接近月初：时间差最小
              { $eq: [{ $subtract: ["$$record.date", "$monthStart"] }, { $min: { $map: { input: "$records", as: "r", in: { $subtract: ["$$r.date", "$monthStart"] } } } }] },
              // 最接近月末：时间差最小
              { $eq: [{ $subtract: ["$monthEnd", "$$record.date"] }, { $min: { $map: { input: "$records", as: "r", in: { $subtract: ["$monthEnd", "$$r.date"] } } } }] }
            ]
          }
        }
      }
    }
  },
  // 4. 展开目标记录数组，还原为单条文档格式
  { $unwind: "$targetRecords" },
  // 5. 投影出原始文档字段，移除分组相关的冗余字段
  {
    $replaceRoot: { newRoot: "$targetRecords" }
  },
  // 可选：按日期排序，让结果更直观
  { $sort: { date: 1 } }
])

查询语句解释 #

1. $match阶段：过滤出指定日期范围内的记录，减少后续计算的数据量。
2. $group阶段：按年和月分组，同时计算每组的月初（当月1号）和月末（当月最后一天）日期，并收集组内所有记录。
3. $project阶段：通过$filter筛选组内记录：
   • 计算每条记录与月初的时间差，保留时间差最小的记录（最接近月初）
   • 计算每条记录与月末的时间差，保留时间差最小的记录（最接近月末）
   • 若当月只有一条记录，该记录会同时满足两个条件，但$filter会自动去重（因为是同一条记录）
4. $unwind阶段：将数组格式的目标记录展开为单个文档。
5. $replaceRoot阶段：将展开后的记录设为根文档，恢复原始文档结构。
6. $sort阶段：可选，按日期升序排列结果，让输出更清晰。

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