如何按季度对日期进行分组? [英] How to group date quarterly wise?

问题描述

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

I have documents which contain a date and I'm wondering how to group them according to quarterly basis?

我的架构是:

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 个月等，直到第 4 季度.

1st quarter contains months 1, 2, 3. 2nd quarter contains months 4, 5, 6 and so on up-to 4th quarter.

我的最终结果应该是:

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

推荐答案

你可以使用 $cond 运算符来检查:

You could make use of the $cond operator to check if:

• $month是 <=3，投影一个名为 quarter 的字段值为一个".
• $month 是 <=6，投影一个名为 quarter 的字段值为二".
• $month 是 <= 9，投影一个名为 quarter 的字段值为三".
• 否则字段 quarter 的值将是第四个".
• 然后是 $group 通过 quarter 字段.

• The $month is <= 3, project a field named quarter with value as "one".
• The $month is <= 6, project a field named quarter with value as "two".
• The $month is <= 9, project a field named quarter with value as "three".
• else the value of the field quarter would be "fourth".
• Then $group by the quarter field.

代码:

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" } } },
]);

这篇关于如何按季度对日期进行分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！