MongoDB聚合实现：基于选定数组值计算课程单元完成率

Let's break down how to compute the completion rate (ratio of completed units to total course units) based on your existing aggregation output. Here's a step-by-step pipeline that builds on your current results:

Full Aggregation Pipeline #

db.yourCollection.aggregate([
  // Step 1: Group modules by user and course to combine all course content
  {
    $group: {
      _id: {
        userId: "$_id",
        courseId: "$learningPath.historyCourses.course._id"
      },
      learnedUnits: { $first: "$learningPath.historyCourses.learnedUnits" },
      courseTitle: { $first: "$learningPath.historyCourses.course.title" },
      modules: { $push: "$learningPath.historyCourses.course.cmodules" }
    }
  },
  // Step 2: Calculate total number of units in the course
  {
    $addFields: {
      totalUnits: {
        $sum: {
          $map: {
            input: "$modules",
            as: "module",
            in: {
              $sum: {
                $map: {
                  input: "$$module.topics",
                  as: "topic",
                  in: { $size: "$$topic.units" }
                }
              }
            }
          }
        }
      }
    }
  },
  // Step 3: Collect all course units and count how many are in learnedUnits
  {
    $addFields: {
      allCourseUnits: {
        $reduce: {
          input: "$modules",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              {
                $reduce: {
                  input: "$$this.topics",
                  initialValue: [],
                  in: { $concatArrays: ["$$value", "$$this.units"] }
                }
              }
            ]
          }
        }
      },
      completedUnits: {
        $size: {
          $setIntersection: ["$learnedUnits", "$allCourseUnits"]
        }
      }
    }
  },
  // Step 4: Compute completion rate (handle division by zero)
  {
    $addFields: {
      completionRate: {
        $cond: {
          if: { $eq: ["$totalUnits", 0] },
          then: 0,
          else: { $divide: ["$completedUnits", "$totalUnits"] }
        }
      },
      completionPercent: {
        $round: [
          {
            $multiply: [
              {
                $cond: {
                  if: { $eq: ["$totalUnits", 0] },
                  then: 0,
                  else: { $divide: ["$completedUnits", "$totalUnits"] }
                }
              },
              100
            ]
          },
          2
        ]
      }
    }
  },
  // Step 5: Clean up output fields
  {
    $project: {
      _id: 0,
      userId: "$_id.userId",
      courseId: "$_id.courseId",
      courseTitle: 1,
      totalUnits: 1,
      completedUnits: 1,
      completionRate: 1,
      completionPercent: 1
    }
  }
])

Explanation of Each Step #

1. Group by User & Course: Your current output has one document per course module. We first group these by user ID and course ID to combine all modules into a single document per user-course pair, preserving the learnedUnits list and course metadata.
2. Count Total Units: We use nested $map and $sum operations to iterate through each module's topics, count the units in each topic, and sum all those counts to get the total number of units in the course.
3. Count Completed Units: First, we use nested $reduce and $concatArrays to flatten all course units into a single list. Then, $setIntersection finds the overlap between this list and the user's learnedUnits, and $size counts those overlapping items to get completed units.
4. Compute Completion Ratio: We calculate the ratio of completed units to total units, adding a safeguard with $cond to avoid division by zero if the course has no units. We also convert this ratio to a rounded percentage for readability.
5. Clean Output: We reshape the document to present the results in a clear, user-friendly format.

Example Output #

You'll get a result like this for each user-course pair:

{
  "userId": "5fd5c105694d122f4837fa0f",
  "courseId": "612f5605dc3813a3717663fe",
  "courseTitle": "C++ Complete Course",
  "totalUnits": 12,
  "completedUnits": 4,
  "completionRate": 0.3333333333333333,
  "completionPercent": 33.33
}

内容的提问来源于stack exchange，提问作者Smith Dwayne