ElasticSearch 7 搜索（10）聚合查询指标聚合

ES聚合分析是什么

1. 聚合分析是数据库中重要的功能特性，完成对一个查询的数据集中数据的聚合计算，如：找出某字段（或计算表达式的结果）的最大值、最小值，计算和、平均值等。ES作为搜索引擎兼数据库，同样提供了强⼤的聚合分析能力
2. 对一个数据集求最大、最小、和、平均值等指标的聚合，在ES中称为指标聚合
3. 而关系型数据库中除了有聚合函数外，还可以对查询出的数据行分组group by，再在组上进行指标聚合。在ES中称为桶聚合

max min sum avg 指标聚合查询 GET/POST

• 请求

localhost:9200/nba/_search

• 请求体

{
  // 语义: 查询火箭队球员平均年龄
  "query": {
    "term": {
      "teamNameEn": "Rockets"
    }
  },
  "aggs": { // aggs 代表使用聚合函数
    "avgAge": { // avgAge是自定义的，因为是查年年龄平均所以起名avgAge
      "avg": { // avg 平均
        "field": "age"
      }
    }
  }, 
  "size": 0 // 不看数据只看指标聚合
}

• 响应

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 21,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "avgAge" : {
      "value" : 26.761904761904763 // 平均年龄
    }
  }
}

value_count 指标聚合查询 GET/POST

统计非空字段的文档数

• 请求

localhost:9200/nba/_search

• 请求体

{
  // 语义: 查询火箭队中球员打球时间不为空的数量
  "query": {
    "term": {
      "teamNameEn": "Rockets"
    }
  },
  "aggs": {
    "countPlayerYear": {
      "value_count": {
        "field": "playYear"
      }
    }
  }, 
  "size": 0
}

• 响应

{
  "took" : 4,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 21,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "countPlayerYear" : {
      "value" : 21
    }
  }
}

_count 指标聚合查询（严格来说不属于聚合） GET/POST

查询火箭队有多少文档，也就是有多少球员

• 请求

localhost:9200/nba/_count

• 请求体

{
  "query": {
    "term": {
      "teamNameEn": "Rockets"
    }
  }
}

• 响应

{
    "count": 21,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    }
}

cardinality 指标聚合查询 GET/POST

• Cardinality 值去重计算

• 请求

localhost:9200/nba/_search

• 请求体

{
  // 语义: 查询火箭队中年龄不同的数量
  "query": {
    "term": {
      "teamNameEn": "Rockets"
    }
  },
  "aggs": {
    "countAge": { // 自定义名字
      "cardinality": { // 去掉相同的只保留一个值
        "field": "age"
      }
    }
  },
  "size": 0
}

• 响应

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 21,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "countAge" : {
      "value" : 13
    }
  }
}

stats 5种指标聚合查询 GET/POST

stats统计count max min avg sum5个值

• 请求

localhost:9200/nba/_search

• 请求体

{
  // 语义: 查询火箭球员的年龄status
  "query": {
    "term": {
      "teamNameEn": "Rockets"
    }
  },
  "aggs": {
    "status": {
      "stats": {
        "field": "age"
      }
    }
  },
  "size": 0
}

• 响应

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 21,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "status" : {
      "count" : 21,
      "min" : 21.0,
      "max" : 37.0,
      "avg" : 26.761904761904763,
      "sum" : 562.0
    }
  }
}

extended status 种指标聚合查询 GET/POST

Extended stats比stats多4个统计结果：平方和、方差、标准差、平均值加减两个标准差的区间

• 请求

localhost:9200/nba/_search

• 请求体

{
  // 查出火箭队球员的年龄Extend stats
  "query": {
    "term": {
      "teamNameEn": "Rockets"
    }
  },
  "aggs": {
    "extendedStatsAge": {
      "extended_stats": {
        "field": "age"
      }
    }
  },
  "size": 0
}

• 响应

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 21,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "extendedStatsAge" : {
      "count" : 21,
      "min" : 21.0,
      "max" : 37.0,
      "avg" : 26.761904761904763,
      "sum" : 562.0,
      "sum_of_squares" : 15534.0,
      "variance" : 23.5147392290249,
      "std_deviation" : 4.84919985451465,
      "std_deviation_bounds" : {
        "upper" : 36.46030447093406,
        "lower" : 17.063505052875463
      }
    }
  }
}

percentiles 种指标聚合查询 GET/POST

Percentiles占比百分位对应的值统计，默认返回[ 1, 5, 25, 50, 75, 95, 99 ]分位上的值

• 请求

localhost:9200/nba/_search

• 请求体

{
    // 语义: 查出火箭的球员的年龄占比
  "query": {
    "term": {
      "teamNameEn": "Rockets"
    }
  },
  "aggs": {
    "percentilesAge": {
      "percentiles": {
        "field": "age",
        // 这里可以指定，不使用默认
        // "percents": [
        //   25,
        //   50,
        //   75
        // ]
      }
    }
  },
  "size": 0
}

• 响应

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 21,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "percentilesAge" : {
      "values" : {
        "1.0" : 21.0,
        "5.0" : 21.0,
        "25.0" : 22.75, // 小于22.75岁的 占25%
        "50.0" : 25.0,
        "75.0" : 30.25,// 小于30.25岁的 占75%
        "95.0" : 35.349999999999994,
        "99.0" : 37.0
      }
    }
  }
}