Node.js+MongoDB如何按15分钟粒度获取店铺开闭状态字段值数组

方案1：MongoDB 聚合直接实现 #

该需求完全可以通过MongoDB聚合管道完成，完整聚合语句如下：

db.shop_infos.aggregate([
  // 第一步：筛选指定日期9:00-12:00的所有数据
  {
    $match: {
      created_at: {
        $gte: ISODate("2021-06-09T09:00:00.000Z"),
        $lte: ISODate("2021-06-09T12:00:00.000Z")
      }
    }
  },
  // 第二步：按店铺+时间排序，保证状态变更顺序正确
  { $sort: { shopId: 1, created_at: 1 } },
  // 第三步：按店铺分组，收集该店铺所有状态变更记录
  {
    $group: {
      _id: "$shopId",
      changes: { $push: { time: "$created_at", status: "$isOpen" } }
    }
  },
  // 第四步：生成15分钟时间槽映射
  {
    $addFields: {
      shopId: { $toString: "$_id" },
      date: "09/06/2021",
      timeSlots: [
        "09:00", "09:15", "09:30", "09:45",
        "10:00", "10:15", "10:30", "10:45",
        "11:00", "11:15", "11:30", "11:45", "12:00"
      ],
      slotTimes: [
        ISODate("2021-06-09T09:00:00.000Z"), ISODate("2021-06-09T09:15:00.000Z"),
        ISODate("2021-06-09T09:30:00.000Z"), ISODate("2021-06-09T09:45:00.000Z"),
        ISODate("2021-06-09T10:00:00.000Z"), ISODate("2021-06-09T10:15:00.000Z"),
        ISODate("2021-06-09T10:30:00.000Z"), ISODate("2021-06-09T10:45:00.000Z"),
        ISODate("2021-06-09T11:00:00.000Z"), ISODate("2021-06-09T11:15:00.000Z"),
        ISODate("2021-06-09T11:30:00.000Z"), ISODate("2021-06-09T11:45:00.000Z"),
        ISODate("2021-06-09T12:00:00.000Z")
      ]
    }
  },
  // 第五步：遍历每个时间槽，匹配最近的状态变更
  {
    $addFields: {
      slotStatus: {
        $map: {
          input: "$slotTimes",
          as: "slotTime",
          in: {
            $let: {
              vars: {
                validChanges: {
                  $filter: {
                    input: "$changes",
                    cond: { $lte: ["$$this.time", "$$slotTime"] }
                  }
                }
              },
              in: {
                $cond: {
                  if: { $gt: [{ $size: "$$validChanges" }, 0] },
                  then: { $last: "$$validChanges.status" },
                  else: false // 无状态记录默认关店，可按需修改
                }
              }
            }
          }
        }
      }
    }
  },
  // 第六步：转成需要的输出格式
  {
    $project: {
      _id: 0,
      shopId: 1,
      date: 1,
      "09:00": { $arrayElemAt: ["$slotStatus", 0] },
      "09:15": { $arrayElemAt: ["$slotStatus", 1] },
      "09:30": { $arrayElemAt: ["$slotStatus", 2] },
      "09:45": { $arrayElemAt: ["$slotStatus", 3] },
      "10:00": { $arrayElemAt: ["$slotStatus", 4] },
      "10:15": { $arrayElemAt: ["$slotStatus", 5] },
      "10:30": { $arrayElemAt: ["$slotStatus", 6] },
      "10:45": { $arrayElemAt: ["$slotStatus", 7] },
      "11:00": { $arrayElemAt: ["$slotStatus", 8] },
      "11:15": { $arrayElemAt: ["$slotStatus", 9] },
      "11:30": { $arrayElemAt: ["$slotStatus", 10] },
      "11:45": { $arrayElemAt: ["$slotStatus", 11] },
      "12:00": { $arrayElemAt: ["$slotStatus", 12] }
    }
  }
])

如果需要导出为你示例中的CSV格式，直接遍历聚合结果按字段拼接即可。

方案2：Node.js 实现（更推荐，可扩展性更高） #

如果觉得聚合语句太复杂不好维护，可以用Node.js做业务层处理，示例代码如下（基于Mongoose）：

const dayjs = require('dayjs');
// 预定义时间槽
const timeSlots = [
  "09:00", "09:15", "09:30", "09:45",
  "10:00", "10:15", "10:30", "10:45",
  "11:00", "11:15", "11:30", "11:45", "12:00"
];
const slotTimes = timeSlots.map(slot => dayjs(`2021-06-09 ${slot}`).toDate());

async function getShopStatus() {
  // 1. 查询符合条件的所有记录
  const records = await ShopInfoModel.find({
    created_at: {
      $gte: new Date("2021-06-09T09:00:00.000Z"),
      $lte: new Date("2021-06-09T12:00:00.000Z")
    }
  }).sort({ created_at: 1 }).lean();

  // 2. 按shopId分组存储变更记录
  const shopMap = {};
  records.forEach(record => {
    const shopId = record.shopId.toString();
    if (!shopMap[shopId]) shopMap[shopId] = [];
    shopMap[shopId].push({ time: record.created_at, status: record.isOpen });
  });

  // 3. 逐个店铺计算每个时间槽的状态
  const result = [];
  for (const shopId in shopMap) {
    const changes = shopMap[shopId];
    const statusList = slotTimes.map(slotTime => {
      // 找小于等于当前时间槽的最后一次状态变更
      const validChange = changes.filter(c => c.time <= slotTime).pop();
      return validChange ? validChange.status : false;
    });
    result.push({
      shopId,
      date: "09/06/2021",
      ...Object.fromEntries(timeSlots.map((slot, idx) => [slot, statusList[idx]]))
    });
  }

  // 4. 如需输出CSV格式直接拼接即可
  const csvHeader = ['"shopId"', '"date"', ...timeSlots.map(s => `"${s}"`)].join(', ');
  const csvRows = result.map(row => {
    return [row.shopId, row.date, ...timeSlots.map(slot => row[slot])].join(', ');
  });
  const csvContent = [csvHeader, ...csvRows].join('\n');
  console.log(csvContent);
  return result;
}

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