MongoDB按IP最近访问时间24小时周期去重统计独立IP数

需求回顾 #

需要统计访问记录中，排除每个IP上次访问24小时内重复访问后的独立IP数量（或有效访问次数）。规则示例：

• IP 1.1.1.1 在01-01-2022T00:00和02-01-2022T01:00的访问，后者处于前者24小时周期内，需排除；
• 周三23:30与周四01:00的访问属于同一24小时周期，需排除。

错误查询问题分析 #

你之前的聚合查询存在以下问题：

1. 字段不匹配：文档中存储IP的字段是ipaddress、时间字段是accessDate，但查询中使用了UUID和timestamp，完全无法匹配数据；
2. 窗口逻辑错误：range: [-12,12]设置的是前后12小时的统计窗口，不符合“排除上次访问24小时内重复”的需求；
3. 分组逻辑偏离：最后按accessCount分组，无法实现按IP去重的目标。

正确聚合方案 #

方案一：使用$reduce筛选有效访问记录（适合需保留每个IP有效时间列表的场景） #

这个方案会先按IP分组，再筛选出每个IP中与上一次有效访问间隔超过24小时的记录：

[
  // 1. 按IP分组，收集所有访问时间
  {
    $group: {
      _id: "$ipaddress",
      accessDates: { $push: "$accessDate" }
    }
  },
  // 2. 对每个IP的访问时间按升序排序
  {
    $project: {
      _id: 1,
      sortedDates: { $sortArray: { input: "$accessDates", sortBy: 1 } }
    }
  },
  // 3. 筛选出符合条件的有效访问时间
  {
    $project: {
      _id: 1,
      validAccesses: {
        $reduce: {
          input: "$sortedDates",
          initialValue: { lastValid: null, validList: [] },
          in: {
            $cond: {
              if: {
                $or: [
                  // 第一个访问时间直接保留
                  { $eq: ["$$value.lastValid", null] },
                  // 当前时间与上一次有效访问间隔超过24小时（86400000毫秒）
                  { $gt: [{ $subtract: ["$$this", "$$value.lastValid"] }, 86400000] }
                ]
              },
              then: {
                lastValid: "$$this",
                validList: { $concatArrays: ["$$value.validList", ["$$this"]] }
              },
              else: "$$value"
            }
          }
        }
      }
    }
  },
  // 4. 统计独立IP数量（可选，若需统计总有效访问次数可替换为$sum）
  {
    $group: {
      _id: null,
      uniqueValidIPCount: { $addToSet: "$_id" }
    }
  },
  {
    $project: {
      uniqueValidIPCount: { $size: "$uniqueValidIPCount" },
      _id: 0
    }
  }
]

方案二：使用窗口函数$lag实现高效筛选（适合直接统计数量的场景） #

这个方案通过窗口函数获取每个IP上一次的访问时间，直接筛选出符合条件的记录：

[
  // 1. 按IP和访问时间升序排序
  {
    $sort: { ipaddress: 1, accessDate: 1 }
  },
  // 2. 按IP分区，获取上一次访问时间并计算时间差
  {
    $setWindowFields: {
      partitionBy: "$ipaddress",
      sortBy: { accessDate: 1 },
      output: {
        prevAccessDate: { $lag: "$accessDate" },
        timeDiff: { $subtract: ["$accessDate", { $lag: "$accessDate" }] }
      }
    }
  },
  // 3. 筛选出有效记录：要么是该IP第一条访问，要么与上一次间隔超24小时
  {
    $match: {
      $or: [
        { prevAccessDate: { $exists: false } },
        { timeDiff: { $gt: 86400000 } }
      ]
    }
  },
  // 4. 统计独立IP数量
  {
    $group: {
      _id: null,
      uniqueValidIPCount: { $addToSet: "$ipaddress" }
    }
  },
  {
    $project: {
      uniqueValidIPCount: { $size: "$uniqueValidIPCount" },
      _id: 0
    }
  }
]

说明 #

• 两个方案中的86400000是24小时对应的毫秒数，若你的时间戳是秒级，需改为86400；
• 若你需要统计的是总有效访问次数而非独立IP数量，可将最后两个阶段替换为：

  {
    $group: {
      _id: null,
      totalValidAccesses: { $sum: 1 }
    }
  }
  

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