我想知道是否有人可以帮助我正确实现聚合功能。我正在尝试计算健身的访问量。我有Visits表,其中保存了每次访问(从,到)日期。也可以访问一些保存的服务。

[{
  "from": ISODate("2020-01-17T10:23:27.645Z"),
  "to": ISODate("2020-01-17T12:23:28.760Z"),
  "visits": [
    {
      "from": ISODate("2020-01-17T10:23:27.646Z"),
      "to": ISODate("2020-01-17T10:30:28.760Z"),
      "service": ObjectId("5e05f17d6b7f7920d4a62403")
    },
    {
      "from": ISODate("2020-01-17T10:30:29.760Z"),
      "to": ISODate("2020-01-17T12:23:28.760Z"),
      "service": ObjectId("5d05f17dt57f7920d4a62404")
    }
  ],
  ...
},
{
  "from": ISODate("2020-01-17T10:40:00.000Z"),
  "to": ISODate("2020-01-17T11:30:28.760Z"),
  "visits": [
    {
      "from": ISODate("2020-01-17T10:40:00.000Z"),
      "to": ISODate("2020-01-17T11:30:28.760Z"),
      "service": ObjectId("h505f17s6b2f7920d4a6295y")
    }
  ],
  ...
}
]

如果当前时间是13:35,我想从今天00:00开始获得今天的结果,然后直到13:00为止:
[{
  'date': '2020-01-18T00:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T00:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T00:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T01:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T02:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T03:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T04:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T05:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T06:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T07:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T08:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T09:00:0.000Z'
  'visits': 0
},
{
  'date': '2020-01-18T010:00:0.000Z'
  'visits': 2
},
{
  'date': '2020-01-18T11:00:0.000Z'
  'visits': 2
},
{
  'date': '2020-01-18T12:00:0.000Z'
  'visits': 1
},
{
  'date': '2020-01-18T13:00:0.000Z'
  'visits': 0
}]

尝试了太多示例,但无济于事,请帮助!

最佳答案

说明

  • 如果我们使用$$NOW,我们将获得当前日期。如果您手动设置currDatetodaynextDay,MongoDB聚合将限制所需的值。
  • 现在,我们计算today(yyyy-MM-dd 00:00:00)-currDate(yyyy-MM-dd 13:35:00)〜13小时的小时数,并使用$range运算符创建数组
  • 我们拉平间隔并应用过滤

  • 您可以使用以下查询:
    db.collection.aggregate([
      {
        $addFields: {
          nextDay: {
            $add: [
              {
                $dateFromString: {
                  dateString: {
                    $substr: [
                      {
                        $toString: "$$NOW"
                      },
                      0,
                      10
                    ]
                  },
                  format: "%Y-%m-%d"
                }
              },
              {
                $multiply: [
                  24,
                  60,
                  60,
                  1000
                ]
              }
            ]
          },
          today: {
            $dateFromString: {
              dateString: {
                $substr: [
                  {
                    $toString: "$$NOW"
                  },
                  0,
                  10
                ]
              },
              format: "%Y-%m-%d"
            }
          },
          currDate: {
            $dateFromString: {
              dateString: {
                $substr: [
                  {
                    $toString: "$$NOW"
                  },
                  0,
                  13
                ]
              },
              format: "%Y-%m-%dT%H",
              timezone: "-01"
            }
          }
        }
      },
      {
        $addFields: {
          interval: {
            $range: [
              0,
              {
                $add: [
                  {
                    $divide: [
                      {
                        $subtract: [
                          "$currDate",
                          "$today"
                        ]
                      },
                      {
                        $multiply: [
                          60,
                          60,
                          1000
                        ]
                      }
                    ]
                  },
                  1
                ]
              },
              1
            ]
          }
        }
      },
      {
        $unwind: "$interval"
      },
      {
        $addFields: {
          date: {
            $add: [
              "$today",
              {
                $multiply: [
                  "$interval",
                  60,
                  60,
                  1000
                ]
              }
            ]
          },
          nextHour: {
            $add: [
              "$today",
              {
                $multiply: [
                  {
                    $add: [
                      "$interval",
                      1
                    ]
                  },
                  60,
                  60,
                  1000
                ]
              }
            ]
          }
        }
      },
      {
        $project: {
          _id: 0,
          date: 1,
          today: 1,
          nextDay: 1,
          visits: {
            $reduce: {
              input: "$visits",
              initialValue: 0,
              in: {
                $add: [
                  "$$value",
                  {
                    $cond: [
                      {
                        $or: [
                          {
                            $and: [
                              {
                                $gte: [
                                  "$$this.from",
                                  "$date"
                                ]
                              },
                              {
                                $lte: [
                                  "$$this.from",
                                  "$nextHour"
                                ]
                              }
                            ]
                          },
                          {
                            $and: [
                              {
                                $lte: [
                                  "$date",
                                  "$$this.to"
                                ]
                              },
                              {
                                $gte: [
                                  "$date",
                                  "$$this.from"
                                ]
                              }
                            ]
                          }
                        ]
                      },
                      1,
                      0
                    ]
                  }
                ]
              }
            }
          }
        }
      },
      {
        $match: {
          $expr: {
            $and: [
              {
                $gte: [
                  "$date",
                  "$today"
                ]
              },
              {
                $lte: [
                  "$date",
                  "$nextDay"
                ]
              },
    
            ]
          }
        }
      },
      {
        $group: {
          _id: "$date",
          visits: {
            $sum: "$visits"
          }
        }
      },
      {
        $sort: {
          _id: 1
        }
      }
    ])
    

    MongoPlayground

    对于JS设置,请单击here

    如果您想要更优雅的方式,可以尝试this解决方案

    10-05 20:52
    查看更多