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

问题描述

我有一个包含日期的文档，我想按季度对文档进行分组. 如何在MongoDB中执行此操作?
我的架构是:

I have a document which contains a date and I want to group document according to quarterly basis. How can I do it in MongoDB ?
My schema is:

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 contain months 1, 2, 3. 2nd quarter contain months 4, 5, 6 and so on up-to 4th quarter. My final answer should be :

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

推荐答案

您可以使用 $cond 运算符，检查是否:

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

• $month 是<= 3，项目a名为quarter的字段，其中 值是一个".
• $month是<= 6，使用以下项投影一个名为quarter的字段 值是两个".
• $month是<= 9，使用以下项投影一个名为quarter的字段 值为三".
• 否则，字段quarter的值为第四".
• 然后在quarter字段中输入$group.

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

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