ElasticSearch 7 搜索（11）聚合查询桶聚合

Joe.Ye • 2023-03-12 • ElasticSearch

terms aggregation 桶聚合查询 GET/POST

  • 请求
localhost:9200/nba/_search

  • 请求体
{
  // 语义: 火箭队根据年龄进行分组
  "query": {
    "term": {
      "teamNameEn": "Rockets"
    }
  },
  "aggs": {
    "aggrAge": {
      "terms": { // 因为根据年龄做桶聚合所以使用terms
        "field": "age",
        "size": 3 // 指定显示桶数
      }
    }
  },
  "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" : {
    "aggrAge" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 3,
      "buckets" : [
        {
          "key" : 21, // 指定的字段 age
          "doc_count" : 4 // 21岁的文档数有4个
        },
        {
          "key" : 25,
          "doc_count" : 3
        },
        {
          "key" : 23,
          "doc_count" : 2
        }
      ]
    }
  }
}

order 第一种分组聚合查询 GET/POST

  • 请求
localhost:9200/nba/_search
  • 请求体
{
  // 语义: 根据火箭队年龄进行分组，分组信息通过年龄从大到小排序（通过指定字段）
  "query": {
    "term": {
      "teamNameEn": "Rockets"
    }
  },
  "aggs": {
    "aggrAge": {
      "terms": {
        "field": "age",
        "size": 3,
        "order": {
          "key": "desc" //_key关键字 倒序排序
          //"doc_count": "desc"  通过文档排序
        }
      }
    }
  },
  "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" : {
    "aggrAge" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 17,
      "buckets" : [
        {
          "key" : 37,
          "doc_count" : 1
        },
        {
          "key" : 34,
          "doc_count" : 2
        },
        {
          "key" : 33,
          "doc_count" : 1
        }
      ]
    }
  }
}

order 第二种分组聚合查询 GET/POST

  • 请求
localhost:9200/nba/_search
  • 请求体
{
  // 语义: 每支球队按该队所有球员的平均年龄进行分组排序（通过分组指标值）
  "aggs": {
    "aggsTeamName": {
      "terms": { // 因为使用分组所以用terms
        "field": "teamNameEn",
        "size": 3, // 显示3条
        "order": {
          "avgAge": "desc"
        }
      }, // 因为需要通过对球队的平均年龄进行分组排序，所以再写aggs
      "aggs": {
        "avgAge": {
          "avg": {
            "field": "age"
          }
        }
      }
    }
  },
  "size": 0
}
  • 响应
{
  "took" : 3,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 566,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "aggsTeamName" : {
      "doc_count_error_upper_bound" : -1,
      "sum_other_doc_count" : 511,
      "buckets" : [
        {
          "key" : "Bucks",
          "doc_count" : 14,
          "avgAge" : {
            "value" : 28.142857142857142
          }
        },
        {
          "key" : "Mavericks",
          "doc_count" : 20,
          "avgAge" : {
            "value" : 27.85
          }
        },
        {
          "key" : "Lakers",
          "doc_count" : 21,
          "avgAge" : {
            "value" : 27.714285714285715
          }
        }
      ]
    }
  }
}

include 筛选分组聚合查询 GET/POST

  • 请求
localhost:9200/nba/_search
  • 请求体
{
  // 语义: 湖人和火箭队按球队平均年龄进行分组排序（指定值列表）
  "aggs": {
    "aggsTeamName": {
      "terms": {
        "field": "teamNameEn",
        // include指定队伍  ["Lakes","Rockets","warriors"]
        "include": ["Lakes","Rockets","warriors"], 
        // exclude去除["warriors"]
        "exclude": ["warriors"], 
        "size": 3,
        "order": {
          "avgAge": "desc"
        }
      },
      "aggs": {
        "avgAge": {
          "avg": {
            "field": "age"
          }
        }
      }
    }
  },
  "size": 0
}
  • 响应
{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 566,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "aggsTeamName" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "Rockets",
          "doc_count" : 21,
          "avgAge" : {
            "value" : 26.761904761904763
          }
        }
      ]
    }
  }
}

include 正则筛选分组聚合查询 GET/POST

  • 请求
localhost:9200/nba/_search
  • 请求体
{
  // 语义: 湖人和火箭队按球队平均年龄进行分组排序（指定值列表 ）
  "aggs": {
    "aggsTeamName": {
      "terms": {
        "field": "teamNameEn",
        // 使用正则表达式
        "include": "Lakers|Ro.*|Warriors.*",
        "exclude": "warriors",
        "size": 3,
        "order": {
          "avgAge": "desc"
        }
      },
      "aggs": {
        "avgAge": {
          "avg": {
            "field": "age"
          }
        }
      }
    }
  },
  "size": 0
}
  • 响应
{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 566,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "aggsTeamName" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "Lakers",
          "doc_count" : 21,
          "avgAge" : {
            "value" : 27.714285714285715
          }
        },
        {
          "key" : "Rockets",
          "doc_count" : 21,
          "avgAge" : {
            "value" : 26.761904761904763
          }
        },
        {
          "key" : "Warriors",
          "doc_count" : 20,
          "avgAge" : {
            "value" : 26.25
          }
        }
      ]
    }
  }
}

range aggregation 范围分组聚合查询 GET/POST

  • 请求
localhost:9200/nba/_search
  • 请求体
{
  // 语义: 球员年龄按20、20-35、35 这样分组
  "aggs": {
    "ageRange": {
      "range": {
        "field": "age",
        "ranges": [
          {
            "to": 20,  // 小于等于 20
            "key": "A" //别名 A
          },
          {
            "from": 20, // 大于等于20
            "to": 35, //  小于等于 35
            "key": "B"  //别名 B
          },
          {
            "from": 35, // 大于等于35 
            "key": "C" //别名 C
          }
        ]
      }
    }
  }, 
  "size": 0
}
  • 响应
{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 566,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "ageRange" : {
      "buckets" : [
        {
          "key" : "A",
          "to" : 20.0,
          "doc_count" : 15
        },
        {
          "key" : "B",
          "from" : 20.0,
          "to" : 35.0,
          "doc_count" : 531
        },
        {
          "key" : "C",
          "from" : 35.0,
          "doc_count" : 20
        }
      ]
    }
  }
}

date_range aggregation 时间范围分组聚合查询 GET/POST

  • 请求
localhost:9200/nba/_search
  • 请求体
{
  // 语义: 球员按出生年月分组
  "aggs": {
    "birthDayRange": {
      "date_range": {   // 时间类型
        "field": "birthDay", // 生日 生日是时间类型
        "format": "MM-yyyy", //时间格式 月份年份
        "ranges": [
          {
            "from": "01-1989" // 小于1989年1月出生
          },
          {
            "from": "01-1989", // 1989年1月到1999年1月
            "to": "01-1999"
          },
          {
            "from": "01-1999", // 1999年1月到2009年1月
            "to": "01-2009"
          },
          {
            "from": "01-2009"
          }
        ]
      }
    }
  }, 
  "size": 0
}
  • 响应
{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 566,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "birthDayRange" : {
      "buckets" : [
        {
          "key" : "01-1989-01-1999",
          "from" : 5.99616E11,
          "from_as_string" : "01-1989",
          "to" : 9.151488E11,
          "to_as_string" : "01-1999",
          "doc_count" : 426
        },
        {
          "key" : "01-1989-*",
          "from" : 5.99616E11,
          "from_as_string" : "01-1989",
          "doc_count" : 469
        },
        {
          "key" : "01-1999-01-2009",
          "from" : 9.151488E11,
          "from_as_string" : "01-1999",
          "to" : 1.230768E12,
          "to_as_string" : "01-2009",
          "doc_count" : 43
        },
        {
          "key" : "01-2009-*",
          "from" : 1.230768E12,
          "from_as_string" : "01-2009",
          "doc_count" : 0
        }
      ]
    }
  }
}

date_histogram aggregation 时间范围分组聚合查询 GET/POST

按天、月、年等进行聚合统计。可按year (1y), quarter (1q), month (1M), week (1w), day (1d), hour (1h), minute (1m), second (1s)间隔聚合

  • 请求
localhost:9200/nba/_search
  • 请求体
{
    // 语义: 球员按出生年分组
  "aggs": {
    "birthday_aggs": {
      "date_histogram": {
        "field": "birthDay", //生日字段
        "format": "yyyy",  // 时间格式
        "calendar_interval": "year" // 根据年
      }
    }
  }, 
  "size": 0
}
  • 响应
{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 566,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "birthday_aggs" : {
      "buckets" : [
        {
          "key_as_string" : "1977",
          "key" : 220924800000,
          "doc_count" : 1
        },
        {
          "key_as_string" : "1978",
          "key" : 252460800000,
          "doc_count" : 1
        },
        {
          "key_as_string" : "1979",
          "key" : 283996800000,
          "doc_count" : 0
        },
        {
          "key_as_string" : "1980",
          "key" : 315532800000,
          "doc_count" : 3
        },
        {
          "key_as_string" : "1981",
          "key" : 347155200000,
          "doc_count" : 2
        },
        {
          "key_as_string" : "1982",
          "key" : 378691200000,
          "doc_count" : 3
        },
        {
          "key_as_string" : "1983",
          "key" : 410227200000,
          "doc_count" : 2
        },
        {
          "key_as_string" : "1984",
          "key" : 441763200000,
          "doc_count" : 8
        },
        {
          "key_as_string" : "1985",
          "key" : 473385600000,
          "doc_count" : 15
        },
        {
          "key_as_string" : "1986",
          "key" : 504921600000,
          "doc_count" : 19
        },
        {
          "key_as_string" : "1987",
          "key" : 536457600000,
          "doc_count" : 16
        },
        {
          "key_as_string" : "1988",
          "key" : 567993600000,
          "doc_count" : 27
        },
        {
          "key_as_string" : "1989",
          "key" : 599616000000,
          "doc_count" : 24
        },
        {
          "key_as_string" : "1990",
          "key" : 631152000000,
          "doc_count" : 35
        },
        {
          "key_as_string" : "1991",
          "key" : 662688000000,
          "doc_count" : 31
        },
        {
          "key_as_string" : "1992",
          "key" : 694224000000,
          "doc_count" : 36
        },
        {
          "key_as_string" : "1993",
          "key" : 725846400000,
          "doc_count" : 46
        },
        {
          "key_as_string" : "1994",
          "key" : 757382400000,
          "doc_count" : 45
        },
        {
          "key_as_string" : "1995",
          "key" : 788918400000,
          "doc_count" : 57
        },
        {
          "key_as_string" : "1996",
          "key" : 820454400000,
          "doc_count" : 56
        },
        {
          "key_as_string" : "1997",
          "key" : 852076800000,
          "doc_count" : 57
        },
        {
          "key_as_string" : "1998",
          "key" : 883612800000,
          "doc_count" : 39
        },
        {
          "key_as_string" : "1999",
          "key" : 915148800000,
          "doc_count" : 28
        },
        {
          "key_as_string" : "2000",
          "key" : 946684800000,
          "doc_count" : 15
        }
      ]
    }
  }
}

版权声明：
作者：Joe.Ye
链接：https://www.appblog.cn/index.php/2023/03/12/elasticsearch-7-search-aggregation-query-bucket-aggregation/
来源：APP全栈技术分享
文章版权归作者所有，未经允许请勿转载。

THE END
分享
二维码
打赏
海报
ElasticSearch 7 搜索（11）聚合查询桶聚合
terms aggregation 桶聚合查询 GET/POST 请求 localhost:9200/nba/_search 请求体 { // 语义: 火箭队根据年龄进行分组 "query": { &qu……
<<上一篇
下一篇>>
文章目录
关闭
目 录