如何编写MongoDB关联$lookup查询统计首小时转推最多的推文

需求说明 #

现有tweets_hurricane集合存储两类推文数据：

• 原创推文：无retweet_id字段，包含推文ID、发布时间等核心字段
• 转推推文：携带retweet_id字段，指向被转推的原创推文ID
  需要找出发布后1小时内获得转推数量最高的原创推文，输出推文ID与对应转推数。

原脚本错误分析 #

你写的脚本无法正常运行的核心原因有两个：

• 聚合管道是服务端批量运算逻辑，不能在阶段内调用findOne这类客户端同步查询方法，无法动态获取每一条转推对应的原创推文时间
• 时间对比逻辑错误：你之前计算的是转推自身发布时间+1小时，正确逻辑应该是判断转推发布时间是否在原创推文发布时间的1小时范围内

正确查询语句 #

使用$lookup关联原创推文数据，全程在聚合管道内完成运算：

db.tweets_hurricane.aggregate([
  // 第一步：过滤出所有转推数据，减少后续运算量
  {
    $match: { retweet_id: { $exists: true } }
  },
  // 第二步：关联查询每条转推对应的原创推文数据
  {
    $lookup: {
      from: "tweets_hurricane",
      localField: "retweet_id",
      foreignField: "id",
      as: "original_tweet"
    }
  },
  // 第三步：过滤关联不到原创推文的无效转推，展开关联返回的数组结构
  {
    $unwind: "$original_tweet"
  },
  // 第四步：转换时间格式，判断转推是否在原创发布后1小时范围内
  {
    $project: {
      _id: 0,
      retweet_id: 1,
      is_valid: {
        $lt: [
          { $toDate: "$created_at" },
          { $add: [ { $toDate: "$original_tweet.created_at" }, 3600000 ] } // 1小时等于3600*1000毫秒
        ]
      }
    }
  },
  // 第五步：只保留符合时间要求的有效转推
  {
    $match: { is_valid: true }
  },
  // 第六步：按原创推文ID分组，统计有效转推数量
  {
    $group: {
      _id: "$retweet_id",
      first_hour_retweet_count: { $sum: 1 }
    }
  },
  // 第七步：按转推数倒序排序，转推量最高的排在最前
  {
    $sort: { first_hour_retweet_count: -1 }
  }
])

输出说明 #

查询结果的_id字段就是原创推文ID，first_hour_retweet_count就是该推文发布后首小时的转推数，第一条结果就是转推数最高的原创推文。
你提供的样例数据运行后会得到以下结果：

{ "_id" : 1432568863334539264, "first_hour_retweet_count" : 2 }

内容的提问来源于stack exchange，提问作者何子洋