Elasticsearch：多病房区域周平均人口统计异常及查询优化需求

你当前的查询在计算区域人口时，错误地累加了所有文档中的population字段——如果同一病房在一周内有多条记录，这个字段会被重复计算，导致区域总人口偏高。我们需要先按病房聚合获取每个病房的人口（取周内平均值或固定值），再将这些病房人口求和得到区域的总人口，同时结合按周的时间分组来完成准确统计。

修改后的查询语句 #

GET development_benchmark/_search
{
  "size": 0, // 无需返回原始文档，仅关注聚合结果
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "date": {
              "gte": "2017-12-18", // 可调整为目标周的起始日期
              "lte": "2018-01-15"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "weekly_stats": {
      "date_histogram": {
        "field": "date",
        "calendar_interval": "week", // 按自然周自动分组
        "format": "yyyy-MM-dd" // 输出周起始日期的可读格式
      },
      "aggs": {
        "benchmarks": {
          "terms": {
            "field": "benchmark_id",
            "size": 100
          },
          "aggs": {
            "cluster_filter": {
              "filter": {
                "term": {
                  "cluster_id": 72997
                }
              },
              "aggs": {
                "total_amount": {
                  "sum": {
                    "field": "amount"
                  }
                },
                "regions": {
                  "terms": {
                    "field": "location_id",
                    "size": 1000
                  },
                  "aggs": {
                    "region_total_amount": {
                      "sum": {
                        "field": "amount"
                      }
                    },
                    "wards": {
                      "terms": {
                        "field": "ward.id", // 先按病房维度分组，避免重复计算
                        "size": 1000
                      },
                      "aggs": {
                        "ward_avg_population": {
                          "avg": {
                            "field": "population" // 获取该病房本周的平均人口
                          }
                        }
                      }
                    },
                    "region_avg_population": {
                      "sum_bucket": { // 将区域内所有病房的平均人口求和，得到区域总人口
                        "buckets_path": "wards>ward_avg_population"
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

关键修改点说明 #

• 按周分组：新增weekly_stats聚合，用calendar_interval: week自动按自然周划分统计区间，精准匹配你按周统计的需求。
• 避免人口重复计算：在区域（location_id）聚合下，先按ward.id分组，计算每个病房本周的平均人口（如果病房人口是固定值，用max/min/first替代avg结果一致），从根源避免同一病房多条记录导致的人口重复累加。
• 区域总人口计算：使用sum_bucket聚合，将区域内所有病房的平均人口求和，得到该区域本周的真实总人口。
• 语义化命名：修改聚合名称为region_total_amount、region_avg_population等，让结果更易读易懂。

结果说明 #

最终你会得到层级清晰的统计结果：

• 按周区间、benchmark、cluster划分的总用药量
• 每个区域的总用药量，以及该区域本周的总人口（下辖所有病房的平均人口之和）

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