如何在MongoDB中按服务分组计算月度平均值？

我在MongoDB中有一组存储服务当日指标的文档，结构如下：

{
    _id: ObjectId("..."),
    name: '<some_metric_name>',
    service: '<service_name>',
    timestamp_unix: Long("1635033600000"),
    timestamp: '2021-10-24 03:00:00 +0300 EEST',
    value: 99.9810785241248
}

我需要在Redash仪表盘中查看按服务分组的月度指标平均值，期望输出格式如下：

目前我已经有一个计算每日整体平均值的查询：

{
    "collection": "metrics",
    "aggregate": [
        {
            "$group": {
                "_id": {
                    "$dateToString": {
                        "format": "%Y-%m-%d",
                        "date": {
                            "$toDate": "$timestamp_unix"
                        }
                    }
                },
                "total_average": {
                    "$avg": "$value"
                }
            }
        }
    ]
}

实现方案 #

以下聚合查询可以实现需求，同时支持生成每个服务的跨月度总平均值记录：

{
  "collection": "metrics",
  "aggregate": [
    // 转换时间戳并提取年月
    {
      "$addFields": {
        "date_obj": { "$toDate": "$timestamp_unix" },
        "year_month": { "$dateToString": { "format": "%Y-%m", "date": { "$toDate": "$timestamp_unix" } } }
      }
    },
    // 按服务+年月分组，计算月度平均值并记录当月起止日期
    {
      "$group": {
        "_id": {
          "service": "$service",
          "year_month": "$year_month"
        },
        "avg_value": { "$avg": "$value" },
        "min_date": { "$min": "$date_obj" },
        "max_date": { "$max": "$date_obj" }
      }
    },
    // 生成日期范围字符串，提取服务名称到顶层
    {
      "$addFields": {
        "date_range": {
          "$concat": [
            { "$dateToString": { "format": "%d-%m-%Y", "date": "$min_date" } },
            " to ",
            { "$dateToString": { "format": "%d-%m-%Y", "date": "$max_date" } }
          ]
        },
        "service": "$_id.service"
      }
    },
    // 整理输出字段，保留三位小数
    {
      "$project": {
        "_id": 0,
        "service": 1,
        "date": "$date_range",
        "avg": { "$round": ["$avg_value", 3] }
      }
    },
    // 按服务分组，收集月度数据并计算总平均值
    {
      "$group": {
        "_id": "$service",
        "monthly_data": { "$push": "$$ROOT" },
        "total_avg": { "$avg": "$avg" }
      }
    },
    // 展开月度数据数组
    {
      "$unwind": "$monthly_data"
    },
    // 合并月度数据和总平均值记录
    {
      "$project": {
        "data": {
          "$concatArrays": [
            ["$monthly_data"],
            [
              {
                "service": { "$concat": ["$_id", "_total"] },
                "date": {
                  "$concat": [
                    { "$dateToString": { "format": "%d-%m-%Y", "date": { "$min": "$monthly_data.min_date" } } },
                    " to ",
                    { "$dateToString": { "format": "%d-%m-%Y", "date": { "$max": "$monthly_data.max_date" } } }
                  ]
                },
                "avg": { "$round": ["$total_avg", 3] }
              }
            ]
          ]
        }
      }
    },
    // 展开合并后的数组
    {
      "$unwind": "$data"
    },
    // 将数组对象转为顶层文档
    {
      "$replaceRoot": { "newRoot": "$data" }
    },
    // 按服务和日期排序（可选）
    {
      "$sort": { "service": 1, "date": 1 }
    }
  ]
}

关键阶段说明 #

• 年月分组：通过year_month字段实现按月份聚合，避免跨月的数据被错误归组。
• 日期范围生成：利用当月的最小/最大日期拼接成示例中的dd-mm-yyyy to dd-mm-yyyy格式。
• 总平均值生成：二次分组收集服务的所有月度数据，计算跨月总平均值，并生成带_total后缀的服务名称记录。
• 小数格式化：用$round将平均值保留三位小数，和示例输出格式匹配。

如果不需要每个服务的总平均值记录，直接去掉第五到第九步的聚合阶段即可。

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