mongodb中的$count在dates和$group by字段之间

nhaq1z21 于 12个月前发布在 Go

关注(0)|答案(1)|浏览(92)

这是一个示例，其中存储了processes、processCompletedBy和processCompletedTime。目标是确定每个员工的绩效及其完成的流程，这些流程应在两个日期之间。

[
    {
        processA: {
            processCompletedBy: "001",
            processCompletedTime: ISODate("2023-09-03T7:23:57.701Z"),
        },
        processB: {
            processCompletedTime: ISODate("2023-09-05T6:47:57.701Z"),
            processCompletedBy: "002",
        },
        processC: {
            processCompletedTime: ISODate("2023-09-05T4:47:57.701Z"),
            processCompletedBy: "003"
        }

    },
    {
        processA: {
            processCompletedBy: "003",
            processCompletedTime: ISODate("2023-09-05T00:47:57.701Z"),
        },

        processB: {
            processCompletedTime: ISODate("2023-09-06T00:47:57.701Z"),
            processCompletedBy: "001",
        },

        processC: {
            processCompletedTime: ISODate("2023-09-05T00:47:57.701Z"),
            processCompletedBy: "002"
        }
    },
    {
        processA: {
            processCompletedBy: "002",
            processCompletedTime: ISODate("2023-09-07T00:47:57.701Z"),
        },
        processB: {
            processCompletedTime: ISODate("2023-09-06T00:47:57.701Z"),
            processCompletedBy: "003",
        },

        processC: {
            processCompletedTime: ISODate("2023-09-08T00:47:57.701Z"),
            processCompletedBy: "001"
        }

    },
]

对于预期输出，日期范围为“2023-09-03”至“2023-09-08”。但应排除不符合daterange标准的进程。
预期产出，

[
        {
            processCompletedBy: "001",
            countOfProcesses: 3, //The no of activity done by him
            summary: [
                {
                    process: "processA",
                    processCompletedTime: ISODate("2023-09-03T7:23:57.701Z"),
                },
                {
                    process: "processB",
                    processCompletedTime: ISODate("2023-09-06T00:47:57.701Z"),
                },
                {
                    process: "processC",
                    processCompletedTime: ISODate("2023-09-08T00:47:57.701Z"),
                },
            ]

        },
        {
            processCompletedBy: "002",
            countOfProcesses: 3,
            summary: [
                {
                    process: "processB",
                    processCompletedTime: ISODate("2023-09-05T6:47:57.701Z"),
                },
                {
                    process: "processC",
                    processCompletedTime: ISODate("2023-09-05T00:47:57.701Z"),
                },
                {
                    process: "processA",
                    processCompletedTime: ISODate("2023-09-07T00:47:57.701Z"),
                },
            ]

        },
        {
            processCompletedBy: "003",
            countOfProcesses: 3,
            summary: [
                {
                    process: "processC",
                    processCompletedTime: ISODate("2023-09-05T4:47:57.701Z"),
                },
                {
                    process: "processA",
                    processCompletedTime: ISODate("2023-09-06T00:47:57.701Z"),
                },
                {
                    process: "processB",
                    processCompletedTime: ISODate("2023-09-07T00:47:57.701Z"),
                },
            ]

        }
    ]

如果你能帮忙的话，我将不胜感激。纠正我，如果我的努力，使这个问题可以理解是不达标。

mongodb

来源：https://stackoverflow.com/questions/77150093/count-in-between-dates-and-group-by-field-in-mongodb

1条答案

按热度按时间

gxwragnw1#

正如评论中提到的，文档模式设计很差。这要求您至少有3个阶段，以便将文档转换为易于查询的（理想文档）格式。
你应该关注文档的 * 可扩展性 *，例如如果有“processD”，“processE”等，你必须再次修改查询，这会使查询性能变差。
理想的文档模式应该是这样的：

{
  "type": "processA",
  "process": "dishwashing",
  "processCompletedBy": "001",
  "processCompletedTime": ISODate("2023-09-07T00:47:57.701Z")
}

一旦您能够将文档转换为理想的文档格式，这使得一切，只是一个简单的通过$match过滤文档，分组文档执行计数，并通过$group添加对象到summary数组。
此外，我看不出有任何理由认为应用动态键（如“processA”，“processB”）将是输出文档中的键。

db.collection.aggregate([
  {
    $project: {
      _id: 0,
      A: {
        processName: "processA",
        process: "$processA",
        processCompletedTime: "$processACompletedTime",
        processCompletedBy: "$processACompletedBy"
      },
      B: {
        processName: "processB",
        process: "$processB",
        processCompletedTime: "$processBCompletedTime",
        processCompletedBy: "$processBCompletedBy"
      },
      C: {
        processName: "processC",
        process: "$processC",
        processCompletedTime: "$processCCompletedTime",
        processCompletedBy: "$processCCompletedBy"
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        processes: {
          $objectToArray: "$$ROOT"
        }
      }
    }
  },
  {
    $unwind: "$processes"
  },
  {
    $replaceWith: "$processes.v"
  },
  {
    $match: {
      processCompletedTime: {
        $gte: ISODate("2023-09-03"),
        $lte: ISODate("2023-09-08")
      }
    }
  },
  {
    $group: {
      _id: "$processCompletedBy",
      countOfProcesses: {
        $count: {}
      },
      summary: {
        $push: {
          process: [
            {
              k: "$processName",
              v: "$process"
            }
          ],
          processCompletedTime: "$processCompletedTime"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      processCompletedBy: "$_id",
      countOfProcesses: "$countOfProcesses",
      summary: {
        $map: {
          input: "$summary",
          in: {
            $mergeObjects: [
              {
                $arrayToObject: "$$this.process"
              },
              {
                processCompletedTime: "$$this.processCompletedTime"
              }
            ]
          }
        }
      }
    }
  }
])

Demo @ Mongo Playground

赞(0）回复(0）举报 12个月前

我来回答

mongodb中的$count在dates和$group by字段之间

1条答案

相关问题

热门标签

最新问答