猫鼬日期比较没有时间和按createdAt和staffId分组，每周、每月和每年按聚合计算的员工总数? [英] mongoose Date comparing without time and Group by createdAt and staffId with Weekly, monthly and Yearly total of staff count by aggregation?

问题描述

可能问题看起来像重复但为此道歉.

Might be question is look like duplicate but apologize for it.

我想聚合 createdAt 属性的每周、每月、每年的结果基础，没有时间和 staffId.

I want to aggregate the weekly, Monthly, Yearly result basis of createdAt property without time and staffId.

模型如下:

   {
    "_id" : ObjectId("5f351f3d9d90b1281c44c5dp"),
    "staffId" : 12345,
    "category" : "trend",
    "page_route" : "http://example.com/rer",
    "expireAt" : ISODate("2020-08-13T11:08:45.196Z"),
    "createdAt" : ISODate("2020-08-13T11:08:45.199Z"),
    "updatedAt" : ISODate("2020-08-13T11:08:45.199Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("5f351f3d9d90b1281c44c5de"),
    "staffId" : 12346,
    "category" : "incident",
    "page_route" : "http://example.com/rergfhfhf",
    "expireAt" : ISODate("2020-08-14T11:08:45.196Z"),
    "createdAt" : ISODate("2020-08-08T11:08:45.199Z"),
    "updatedAt" : ISODate("2020-08-12T11:08:45.199Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("5f351f3d9d90b1281c44c5dc"),
    "staffId" : 12347,
    "category" : "trend",
    "page_route" : "http://example.com/rerrwe",
    "expireAt" : ISODate("2020-08-13T11:08:45.196Z"),
    "createdAt" : ISODate("2020-08-13T11:08:45.199Z"),
    "updatedAt" : ISODate("2020-08-13T11:08:45.199Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("5f351f3d9d90b1281c44c5dr"),
    "staffId" : 12348,
    "category" : "trend",
    "page_route" : "http://example.com/rerrwe",
    "expireAt" : ISODate("2020-08-12T11:08:45.196Z"),
    "createdAt" : ISODate("2020-08-08T11:08:45.199Z"),
    "updatedAt" : ISODate("2020-08-12T11:08:45.199Z"),
    "__v" : 0
}

预期结果:示例 1) 每周

[
{_id: "2020-11-13", total: 2},
{_id: "2020-11-8", total: 2},


]

示例 2) 每月

[
{_id: "2020-11-8", total: 4},

]

每年类似...

我正在使用 nodejs 和 mongoose 来实现 API.

I am using nodejs and mongoose to implement the API.

我很挣扎，但我无法达到预期的结果.

I am struggle lot but I am unable to achieve the expected result.

如果有人帮助我，那将是很大的帮助.

if anyone help me then it will be great help.

感谢所有专家.

我尝试过这样的事情:

[
        {
          $match: {
            createdAt: { $gte: new Date(currentDate), $lt: new Date(nextDate) }
          }
        },
        {
          $project: {
            _id: 1,
            staffId: 1,
            day: {
              $dayOfMonth: "$createdAt"
            },
            month: {
              $month: "$createdAt"
            },
            year: {
              $year: "$createdAt"
            }
          }
        },
        {
          $project: {
            _id: 1,
            staffId: 1,
            datetime: {
              $concat: [
                {
                  $substr: ["$year", 0, 4]
                },
                "-",
                {
                  $substr: ["$month", 0, 2]
                },
                "-",
                {
                  $substr: ["$day", 0, 2]
                }
              ]
            }
          }
        },
        {
          $group: {
            _id: {
              createdAt: "$datetime",
              staffId: "$staffId"
            }
          }
        },
        {
          $group: {
            _id: {$week:"$_id.createdAt"},
            total: {
              $sum: 1
            }
          }
        },
        { $sort: { _id: 1 } }
      ];

推荐答案

你可以试试，

• 按周分组

db.collection.aggregate([
  {
    $group: {
      _id: {
        year: { $year: "$createdAt" },
        week: { $week: "$createdAt" }
      },
      createdAt: { $first: "$createdAt" },
      count: { $sum: 1 }
    }
  }
])

游乐场

• 按月分组

db.collection.aggregate([
  {
    $group: {
      _id: {
        year: { $year: "$createdAt" },
        month: { $month: "$createdAt" }
      },
      createdAt: { $first: "$createdAt" },
      count: { $sum: 1 }
    }
  }
])

游乐场

• 按年份分组

db.collection.aggregate([
  {
    $group: {
      _id: { $year: "$createdAt" },
      createdAt: { $first: "$createdAt" },
      count: { $sum: 1 }
    }
  }
])

游乐场

您可以使用 createdAt 字段从客户端语言更改日期格式！

You can change date format from your client side language using createdAt field!

这篇关于猫鼬日期比较没有时间和按createdAt和staffId分组，每周、每月和每年按聚合计算的员工总数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！