Mongodb 如何按季度分组日期

发布于02月03日

我有包含日期的文件，我想知道如何按季度对它们进行分组？

我的模式是:

var ekgsanswermodel = new mongoose.Schema({
    userId: {type: Schema.Types.ObjectId},
    topicId : {type: Schema.Types.ObjectId},
    ekgId : {type: Schema.Types.ObjectId},
    answerSubmitted :{type: Number},
    dateAttempted : { type: Date},
    title : {type: String},
    submissionSessionId : {type: String}  
});

第一季度包括第1、2、3个月.第二季度包括第4、5、6个月，以此类推，直至第四季度.

我的最终结果应该是:

 "result" : [ 
   {
     _id: {
        quater:
     },
     _id: {
        quater:
     },
    _id: {
        quater:
     },
     _id: {
        quater:
     }
  }

推荐答案

您可以使用$cond操作员判断:

$month是<= 3，用
$month是<= 6，用
$month是<= 9，用
否则字段quarter的值将为"第四".
然后是quarter区的$group.

代码:

db.collection.aggregate([
  {
    $project: {
      date: 1,
      quarter: {
        $cond: [
          { $lte: [{ $month: "$date" }, 3] },
          "first",
          {
            $cond: [
              { $lte: [{ $month: "$date" }, 6] },
              "second",
              {
                $cond: [{ $lte: [{ $month: "$date" }, 9] }, "third", "fourth"],
              },
            ],
          },
        ],
      },
    },
  },
  { $group: { _id: { quarter: "$quarter" }, results: { $push: "$date" } } },
]);

特定于您的模式:

db.collection.aggregate([
  {
    $project: {
      dateAttempted: 1,
      userId: 1,
      topicId: 1,
      ekgId: 1,
      title: 1,
      quarter: {
        $cond: [
          { $lte: [{ $month: "$dateAttempted" }, 3] },
          "first",
          {
            $cond: [
              { $lte: [{ $month: "$dateAttempted" }, 6] },
              "second",
              {
                $cond: [
                  { $lte: [{ $month: "$dateAttempted" }, 9] },
                  "third",
                  "fourth",
                ],
              },
            ],
          },
        ],
      },
    },
  },
  { $group: { _id: { quarter: "$quarter" }, results: { $push: "$$ROOT" } } },
]);