按日期计算记录数 MongoDB

Question

这是客户收藏记录

 {
    name: xyz,
    .
    .
    .
    createdAt: Sun Nov 20 2016 00:00:00 GMT+0530 (IST)
    lastModified: Sat Dec 10 2016 00:00:00 GMT+0530 (IST)
 }

我需要根据日期获取修改客户数量的计数

eg: Date                Count(lastModified)
    11-20-2016           10
    12-20-2016            7
    13-20-2016            9

我希望结果以类似这样的方式分组。

- Syed Is Saqlain

---

可能是 MongoDB在每日分组内聚合 的重复问题。 - felix

这个回答解决了你的问题吗？在MongoDB中按日期分组 - Xavier Guihot

Answer 1

如果您想按lastModified的日期和时间进行计数，那么可以像这样使用：

db.getCollection('collectionName').aggregate({$group:{_id: "$lastModified", count:{$sum:1}}})

如果您想只按lastModified日期计数，则可以像这样使用：

db.getCollection('collectionName').aggregate(
[
    {
        $group:
        {
            _id:
            {
                day: { $dayOfMonth: "$lastModified" },
                month: { $month: "$lastModified" }, 
                year: { $year: "$lastModified" }
            }, 
            count: { $sum:1 },
            date: { $first: "$lastModified" }
        }
    },
    {
        $project:
        {
            date:
            {
                $dateToString: { format: "%Y-%m-%d", date: "$date" }
            },
            count: 1,
            _id: 0
        }
    }
])

- Shaishab Roy

Answer 2

这更简单:

db.getCollection('collectionName').aggregate(
[
    {
        $group:
        {
            _id: {
                $dateToString: {
                    "date": "$lastModified",
                    "format": "%Y-%m-%d"
                }
            }, 
            count: { $sum:1 }
        }
    }
])

- Ali Jafari

Answer 3

我已经按照以下方式计算了我的日期

示例数据：

{
    "_id" : ObjectId("5f703ef9db38661df02d3a77"),
    "color" : "black",
    "icon" : "grav",
    "name" : "viewed count",
    "created_date" : ISODate("2020-09-27T07:27:53.816Z"),
    "updated_date" : ISODate("2021-05-11T19:11:14.415Z"),
    "read" : false,
    "graph" : [ 
        {
            "_id" : ObjectId("5f704286db38661df02d3a81"),
            "count" : 1,
            "date" : ISODate("2020-09-27T07:43:02.231Z")
        }, 
        {
            "_id" : ObjectId("5f7043ebdb38661df02d3a88"),
            "date" : ISODate("2020-09-27T07:48:59.383Z"),
            "count" : 2
        }]
}

我的问题是：

db.test.aggregate(
  [{$match:{"name" : "viewed count"}},
   {$unwind:'$graph'},
    {
      $match: {
        'graph.date': {
          $gt: ISODate("2018-09-27"),
          $lt: ISODate("2021-09-27")
        }
      }
    },
    {
      $project: {
        date: {$dateToString: {format: '%Y-%m-%d', date: '$graph.date'}}
      },
    },
    {
      $group: {
        _id: "$date",
        count: {$sum: 1}
      }
    },
    {
      $sort: {
        _id: 1
      }
    }
  ]
)

输出：

[{
    "_id" : "2020-09-27",
    "count" : 58.0
},
{
    "_id" : "2020-09-29",
    "count" : 50.0
}]

- KARTHIKEYAN.A

Answer 4

Mongo自5.0.0版本起实际上已经有一个名为dateTrunc的函数可以直接完成这个操作。

db.getCollection('collectionName').aggregate([
        {
            $group: {
                _id: {
                    $dateTrunc: {
                        date: "$lastModified", unit: "day"
                    }
                },
                count: {$sum: 1}
            }
        }
    ])

- Dzone64

Answer 5

我在Yii2中通过以下方式解决了这个问题

$aggregateResult = Yii::$app->mongodb->getCollection('patient')->aggregate([
            [
                '$match' => [
                    '$and' => [
                        ['deleted_at' => ['$exists' =>  false]],
                        ['registration_date' => ['$gte' =>  $startDateStamp, '$lte' => $endDateStamp]],
                    ]
                ]
            ],
            [
                '$group' => [
                    '_id' =>  [
                        'day' => ['$dayOfMonth' =>  ['$toDate' => ['$multiply' => ['$registration_date', 1000]]]],
                        'month' => ['$month' => ['$toDate' => ['$multiply' => ['$registration_date', 1000]]]],
                        'year' => ['$year' => ['$toDate' => ['$multiply' => ['$registration_date', 1000]]]]
                    ],
                    'count' => ['$sum' => 1],
                    'date' => ['$first' => ['$toDate' => ['$multiply' => ['$registration_date', 1000]]]]
                ]
            ],

            [
                '$project' => [
                    'date' => ['$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$date']],
                    'count' => 1,
                    '_id' => 0
                ]
            ],
            ['$sort' => ['date' => 1]],

        ]);

- danish ali