mongoose MongoDB聚集记录，按从当前时间起15分钟分组

我尝试从当前时间开始每隔15分钟分组一次来获取数据。当前它返回的结果基数为15分钟。示例：9点、9点15分、9点30分、9点45分、10点、10点15分、10点30分等等。
我试图实现的是从当前时间开始的间隔。假设现在是9：08，间隔应该是9：08、9：23、9：38、9：53、10：08、10：23等

Stat_Snapshots.aggregate([
        {
            $group:
            {
                _id: {
                    "$toDate": {
                        "$subtract": [
                            { "$toLong": "$date" },
                            { "$mod": [{ "$toLong": "$date" }, 1000 * 60 * 15] }
                        ]
                    }
                }
                ,
                floorPrice: { "$min": "$floorPrice" },
                listedCount: { "$min": "$listedCount" }
            },

        }
    ])

当前结果

{
                "_id": "2022-09-28T07:30:00.000Z",
                "floorPrice": 5000000000,
                "listedCount": 375
            },
            {
                "_id": "2022-09-28T07:15:00.000Z",
                "floorPrice": 5000000000,
                "listedCount": 376
            },
            {
                "_id": "2022-09-28T07:00:00.000Z",
                "floorPrice": 5000000000,
                "listedCount": 375
            },
            {
                "_id": "2022-09-28T06:45:00.000Z",
                "floorPrice": 5000000000,
                "listedCount": 375
            }

输入文档示例

{
    "_id" : ObjectId("62fb40acd762c3e1150e0133"),
    "collections" : ObjectId("62fb24130d026bd9140c3f3a"),
    "floorPrice" : 297500000,
    "listedCount" : "259",
    "date" : ISODate("2022-08-16T07:01:00.035Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("62fb40acd762c3e1150e0135"),
    "collections" : ObjectId("62fb39140d026bd9140c44d2"),
    "floorPrice" : -1369803776,
    "listedCount" : "101",
    "date" : ISODate("2022-08-16T07:01:00.035Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("62fb40acd762c3e1150e0137"),
    "collections" : ObjectId("62fb38fc0d026bd9140c44cd"),
    "floorPrice" : 1010065408,
    "listedCount" : "1017",
    "date" : ISODate("2022-08-16T07:01:00.035Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("62fb40acd762c3e1150e0139"),
    "collections" : ObjectId("62fb38da0d026bd9140c44c8"),
    "floorPrice" : 95032704,
    "listedCount" : "327",
    "date" : ISODate("2022-08-16T07:01:00.035Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("62fb40acd762c3e1150e013b"),
    "collections" : ObjectId("62fb24030d026bd9140c3f32"),
    "floorPrice" : -1189934592,
    "listedCount" : "273",
    "date" : ISODate("2022-08-16T07:01:00.035Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("62fb4430d762c3e1150e013e"),
    "collections" : ObjectId("62fb39140d026bd9140c44d2"),
    "floorPrice" : -1369803776,
    "listedCount" : "100",
    "date" : ISODate("2022-08-16T07:16:00.033Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("62fb4430d762c3e1150e0140"),
    "collections" : ObjectId("62fb38da0d026bd9140c44c8"),
    "floorPrice" : -4967296,
    "listedCount" : "325",
    "date" : ISODate("2022-08-16T07:31:00.033Z"),
    "__v" : 0
}

是不是我遗漏了什么导致了这个结果？

一个选项是修改查询：

db.collection.aggregate([
  {$group: {
      _id: {
        $toDate: {
          $subtract: [
            {$toLong: new Date()},
            {$multiply: [
              {$ceil: {
                  $divide: [
                    {$subtract: [{$toLong: new Date()}, {$toLong: "$date"}]}, 
                    1000 * 60 * 15]
                  }
                }, 1000 * 60 * 15]
            }
          ]
        }
      },
      floorPrice: {
        $min: "$floorPrice"
      },
      listedCount: {
        $min: "$listedCount"
      }
    }
  }
])

了解它在playground example上的工作原理
或者用现代的mongoDB：

db.collection.aggregate([
  {$group: {
    _id: {
      $dateAdd: {
        startDate: new Date(),
        unit: "minute",
        amount: {
          $multiply: [
            {$ceil: {
              $divide: [
                {$dateDiff: {startDate: "$date", endDate: new Date(), unit: "minute"}},  
                15]
            }}, -15]
          }
        }
      },
      floorPrice: {
        "$min": "$floorPrice"
      },
      listedCount: {
        "$min": "$listedCount"
      }
    }
  }
])

了解它在playground example上的工作原理

mongoose MongoDB聚集记录，按从当前时间起15分钟分组

1条答案

相关问题

热门标签

最新问答