在MongoDB中再次聚合+ $ project + $ group + $ sum + $ count + $ group

Jan 发表于 Dev

一月

考虑架构：

const mongoose = require("mongoose");
const Schema = mongoose.Schema;
const EightWeekGamePlanSchema = new Schema({
  Week: {
    type: Number,
    required: true
  },
  LeadId: {
    type: String,
    required: true
  },
  SupplierGeneralId: {
    type: String,
    required: true
  },


  // ... More properties


  TotalClaimsLeftToBeClaimedByClientType: {
    // incresed by 1 every time it's claimed
    type: Number,
    required: true
  },



  InsertDate: {
    type: Date
    // default: Date.now
  }
});

module.exports = EightWeekGamePlan = mongoose.model(
  "eightweekgameplan",
  EightWeekGamePlanSchema
);

考虑一下Mongo查询：

   EightWeekGamePlan.aggregate(
      [
        // 1.Group by supplier
        // 2.Within each supplier group by month & year
        // Use count & sum
        {
          $project: {
            month: { $month: "$InsertDate" },
            year: { $year: "$InsertDate" }
          }
        },
        {
          $group: {
            _id: {
              SupplierGeneralId: "$SupplierGeneralId",
              month: "$month",
              year: "$year"
            },
            ClaimsSummary : { $sum: "$TotalClaimsLeftToBeClaimedByClientType" } , 
            // TotalLeadsPerSupplierAndDate :  ... 

            // Here I want to group again , by LeadID and count all the 
            // unique LeadID's 
          }
        }       
       ]

我想GROUP BYSupplierGeneralId和Month + Year的InsertDate，
总结每个月 TotalClaimsLeftToBeClaimedByClientType
再次分组，但是这次按LeadID进行分组，并计算每个供应商的所有唯一LeadId（以前按SupplierGeneralId，Month，Year分组）。

但是我越来越

[{_id：{月份：1，年份：2020}，ClaimsSummary：0}]

...即使有数据

管道出了什么问题？如何重新分组以获取每个供应商的唯一LeadId？

谢谢

编辑：

我向中添加了更多字段，Project但现在我在中得到了空数组$push：

   EightWeekGamePlan.aggregate(
      [
        // 1.Group by supplier
        // 2.Within each supplier group by month & year
        // Use count & sum
        { $sort: { SupplierGeneralId: 1 } },
        {
          $project: {
            month: { $month: "$InsertDate" },
            year: { $year: "$InsertDate" },
            SupplierGeneralId: "$SupplierGeneralId",
            TotalClaimsLeftToBeClaimedByClientType:
              "$TotalClaimsLeftToBeClaimedByClientType"
          }
        },
        {
          $group: {
            _id: {
              SupplierGeneralId: "$SupplierGeneralId",
              month: "$month",
              year: "$year"
            },
            LeadsCollection: {
              $push: {
                LeadId: "$LeadId"
              }
            },
            ClaimsSummary: { $sum: "$TotalClaimsLeftToBeClaimedByClientType" }
          }
        }
        ]

输出：

[
[0]   {
[0]     _id: {
[0]       SupplierGeneralId: 'qCwHWFD1cBvrfPp5hdBL6M',
[0]       month: 1,
[0]       year: 2020
[0]     },
[0]     LeadsCollection: [
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {}
[0]     ],
[0]     ClaimsSummary: 4
[0]   }
[0] ]

第二编辑：

  EightWeekGamePlan.aggregate(
      [
        { $sort: { SupplierGeneralId: 1 } },
        {
          $group: {
            _id: {
              SupplierGeneralId: "$SupplierGeneralId",
              month: { $month: "$InsertDate" },
              year: { $year: "$InsertDate" }
            },
            LeadsUploaded: {
              $push: {
                LeadId: "$LeadId"
              }
            },
            Sells: { $sum: "$TotalClaimsLeftToBeClaimedByClientType" }
          }
        },
        {
          $project: {
            Sells: "$Sells",
            LeadsUploaded: {
              $reduce: {
                input: { $setUnion: "$LeadsUploaded.LeadId" },
                initialValue: [],
                in: {
                  $concatArrays: [
                    "$$value",
                    [
                      {
                        Lead_ID: "$$this"
                      }
                    ]
                  ]
                }
              }
            }
          }
        }
      ]

汤姆·斯拉伯特

您应该放下$project舞台，然后进行分组，这样就没有真正意义了，添加它只会使管道效率降低。

将管道重写为：

EightWeekGamePlan.aggregate(
      [
        // 1.Group by supplier
        // 2.Within each supplier group by month & year
        // Use count & sum
        { $sort: { SupplierGeneralId: 1 } },
        {
          $group: {
            _id: {
              SupplierGeneralId: "$SupplierGeneralId",
              month: {"$month" : "$InsertDate"},
              year: { $year: "$InsertDate" },
            },
            LeadsCollection: {
              $push: {
                LeadId: "$LeadId"
              }
            },
            ClaimsSummary: { $sum: "$TotalClaimsLeftToBeClaimedByClientType" }
          }
        }
        ]

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。