MongoDB 3.2.15复杂集合URL统计问题求助

Hey there! Since you're a MongoDB newbie on version 3.2.15 with a large collection (1M+ docs), let's skip MapReduce first—it's slower for these straightforward stats. The Aggregation Framework is more efficient and easier to follow once you get the hang of it. Let's break down both of your requirements:

1. 统计去重后的完整URL（Path+QueryString）总数 #

We need to flatten the Pages array, combine Path and QueryString into a full URL, then count unique values. Here's the aggregation pipeline:

db.yourCollection.aggregate([
  // 把每个Pages数组元素拆成独立文档
  { $unwind: "$Pages" },
  // 拼接Path和QueryString成完整URL（处理QueryString为空的情况）
  {
    $project: {
      fullUrl: {
        $concat: [
          "$Pages.Path",
          { $cond: [ { $ne: [ "$Pages.QueryString", "" ] }, "?", "" ] },
          "$Pages.QueryString"
        ]
      }
    }
  },
  // 按完整URL分组去重
  { $group: { _id: "$fullUrl" } },
  // 统计去重后的总数（MongoDB 3.2没有$count，用$group求和）
  { $group: { _id: null, totalUniqueUrls: { $sum: 1 } } }
])

解释： #

• $unwind: Takes each element in the Pages array and turns it into its own document—this lets us process each URL entry individually.
• $project: Builds the full URL by concatenating Path and QueryString, adding a ? only if QueryString isn't empty.
• First $group: Groups by the full URL to eliminate duplicates.
• Second $group: Counts how many unique groups there are (our total unique URLs).

2. 按UserId分组，统计每个用户的去重Path数量 #

This time we need to group by UserId first, then count unique Path values per user:

db.yourCollection.aggregate([
  // 拆分数组（跳过空Pages的话可以加{ $unwind: { path: "$Pages", preserveNullAndEmptyArrays: false } }）
  { $unwind: "$Pages" },
  // 按UserId和Path分组，去重每个用户的Path
  { $group: { _id: { userId: "$UserId", path: "$Pages.Path" } } },
  // 再按UserId分组，统计去重后的Path数量
  {
    $group: {
      _id: "$_id.userId",
      uniquePathCount: { $sum: 1 }
    }
  },
  // 可选：重命名_id为UserId，让结果更清晰
  { $project: { UserId: "$_id", uniquePathCount: 1, _id: 0 } }
])

解释： #

• First $group: Groups by both UserId and Path—this ensures we only count each Path once per user.
• Second $group: Aggregates those unique (UserId, Path) pairs to get the count per user.
• The final $project cleans up the output to use UserId instead of _id.

如果你一定要用MapReduce（不推荐，性能较差） #

MapReduce is more flexible but slower for these simple aggregations, especially on large datasets. Here's how you'd implement both stats:

1. 统计去重完整URL总数 #

db.yourCollection.mapReduce(
  // Map函数：遍历每个Pages元素，输出完整URL作为key
  function() {
    if (this.Pages && this.Pages.length > 0) {
      this.Pages.forEach(page => {
        let fullUrl = page.Path;
        if (page.QueryString && page.QueryString !== "") {
          fullUrl += "?" + page.QueryString;
        }
        emit(fullUrl, 1);
      });
    }
  },
  // Reduce函数：对相同key的value求和（这里其实只是标记存在）
  function(key, values) {
    return 1;
  },
  // 最终处理：统计所有唯一key的数量
  {
    out: { inline: 1 },
    finalize: function(key, reducedValue) {
      return reducedValue;
    }
  }
).count

2. 按UserId统计去重Path数量 #

db.yourCollection.mapReduce(
  // Map函数：遍历每个Pages元素，输出(UserId, Path)作为key
  function() {
    if (this.Pages && this.Pages.length > 0) {
      this.Pages.forEach(page => {
        emit({ userId: this.UserId, path: page.Path }, 1);
      });
    }
  },
  // Reduce函数：标记唯一的(UserId, Path)对
  function(key, values) {
    return 1;
  },
  // 最终处理：按UserId重新分组统计数量
  {
    out: "temp_results",
    finalize: function(key, reducedValue) {
      return reducedValue;
    }
  }
);

// 然后从临时集合中统计每个用户的数量
db.temp_results.aggregate([
  {
    $group: {
      _id: "$_id.userId",
      uniquePathCount: { $sum: 1 }
    }
  },
  { $project: { UserId: "$_id", uniquePathCount: 1, _id: 0 } }
])

// 记得用完删除临时集合
db.temp_results.drop()

重要提醒： #

• For large collections (1M+ docs), the Aggregation Framework will be significantly faster than MapReduce—stick with that if possible.
• If your Pages array has a lot of empty entries, add preserveNullAndEmptyArrays: false to the $unwind stage to skip those and save processing time.

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