修改Metabase生成的MongoDB聚合查询：按周统计达标唯一ID数

你需要的是仅统计每周内至少有X条记录的唯一用户ID数量，核心思路是拆分分组逻辑：先按「周+用户ID」统计每个用户每周的记录数，筛选出达标的用户后，再按周统计这些用户的数量。修改后的完整聚合查询如下：

[
  {
    "$match": {
      "$and": [
        {
          (some filters)
        },
        {
          "$expr": {
            "$gte": [
              "$createdAt", { "$subtract": [ISODate(), { "$multiply": [3600000, 24, 7, 8] }] }
            ]
          }
        },
        {
          "$expr": {
            "$lt": [
              "$createdAt", ISODate() 
            ]
          }
        }
      ]
    }
  },
  // 第一步：按周+用户ID分组，统计每个用户每周的记录数
  {
    "$group": {
      "_id": {
        "week": {
          "$let": {
            "vars": {
              "parts": {
                "$dateToParts": {
                  "date": {
                    "$subtract": [
                      "$createdAt",
                      {
                        "$multiply": [
                          {
                            "$subtract": [
                              {
                                "$let": {
                                  "vars": {
                                    "day_of_week": {
                                      "$mod": [
                                        {
                                          "$add": [
                                            {
                                              "$dayOfWeek": "$createdAt"
                                            },
                                            6
                                          ]
                                        },
                                        7
                                      ]
                                    }
                                  },
                                  "in": {
                                    "$cond": {
                                      "if": {
                                        "$eq": [
                                          "$$day_of_week",
                                          0
                                        ]
                                      },
                                      "then": 7,
                                      "else": "$$day_of_week"
                                    }
                                  }
                                }
                              },
                              1
                            ]
                          },
                          86400000
                        ]
                      }
                    ]
                  }
                }
              }
            },
            "in": {
              "$dateFromParts": {
                "year": "$$parts.year",
                "month": "$$parts.month",
                "day": "$$parts.day"
              }
            }
          }
        },
        "userId": "$user._id"
      },
      "recordCount": { "$sum": 1 }
    }
  },
  // 第二步：筛选出每周记录数≥X的用户（把X换成你需要的数值）
  {
    "$match": {
      "recordCount": { "$gte": X }
    }
  },
  // 第三步：按周分组，统计符合条件的唯一用户数量
  {
    "$group": {
      "_id": "$_id.week",
      "count": { "$addToSet": "$_id.userId" }
    }
  },
  // 最后调整输出格式，和原查询一致
  {
    "$project": {
      "_id": false,
      "createdAt~~~week": "$_id",
      "count": { "$size": "$count" }
    }
  }
]

关键调整说明： #

1. 新增第一个$group阶段：同时按周和用户ID分组，用$sum:1统计每个用户在对应周内的记录总数recordCount。
2. 新增$match阶段：过滤掉recordCount < X的分组，只保留每周记录数达标的用户。
3. 第二个$group阶段：回到按周分组，用$addToSet收集符合条件的用户ID，最后用$size计算数量，得到每周达标的唯一用户数。

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