ElasticSearch 搜索引擎入门到实战 17-- 聚合查询之桶聚合

ES聚合分析

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

 

Terms Aggregation 根据字段项分组聚合

• ⽕箭队根据年龄进⾏分组

POST /nba/_search
{
  "query": {
    "term": {
      "teamNameEn": {
        "value": "Rockets"
      }
    }
  },
  "aggs": {
    "aggsAge别名": {
      "terms": {
        "field": "age",
        "size": 10
      }
    }
  },
  "size": 0
}

 

order 分组聚合排序

• ⽕箭队根据年龄进⾏分组，分组信息通过年龄从⼤到⼩排序 (通过指定字段)

POST /nba/_search
{
  "query": {
    "term": {
      "teamNameEn": {
        "value": "Rockets"
      }
    }
  },
  "aggs": {
    "aggsAge别名": {
      "terms": {
        "field": "age",
        "size": 10,
        "order": {
          "_key": "desc"
        }
      }
    }
  },
  "size": 0
}

• ⽕箭队根据年龄进⾏分组，分组信息通过⽂档数从⼤到⼩排序 (通过⽂档数)

POST /nba/_search
{
  "query": {
    "term": {
      "teamNameEn": {
        "value": "Rockets"
      }
    }
  },
  "aggs": {
    "aggsAge别名": {
      "terms": {
        "field": "age",
        "size": 10,
        "order": {
          "_count": "desc"
        }
      }
    }
  },
  "size": 0
}

• 每⽀球队按该队所有球员的平均年龄进⾏分组排序 (通过分组指标值)

POST /nba/_search
{
  "aggs": {
    "aggsTeamName别名1": {
      "terms": {
        "field": "teamNameEn",
        "size": 30,
        "order": {
          "avgAge别名2": "desc"
        }
      },
      "aggs": {
        "avgAge别名2": {
          "avg": {
            "field": "age"
          }
        }
      }
    }
  },
  "size": 0
}

 

筛选分组聚合

• 湖⼈和⽕箭队按球队平均年龄进⾏分组排序 (指定值列表)

POST /nba/_search
{
  "aggs": {
    "aggsTeamName别名1": {
      "terms": {
        "field": "teamNameEn",
        "include": ["Lakers","Rockets","Warriors"],
        "exclude": ["Warriors"],
        "size": 30,
        "order": {
          "avgAge别名2": "desc"
        }
      },
      "aggs": {
        "avgAge别名2": {
          "avg": {
            "field": "age"
          }
        }
      }
    }
  },
  "size": 0
}

• 湖⼈和⽕箭队按球队平均年龄进⾏分组排序 (正则表达式匹配值)

POST /nba/_search
{
  "aggs": {
    "aggsTeamName别名1": {
      "terms": {
        "field": "teamNameEn",
        "include": "Lakers|Ro.*|Warriors.*",
        "exclude": "Warriors",
        "size": 30,
        "order": {
          "avgAge别名2": "desc"
        }
      },
      "aggs": {
        "avgAge别名2": {
          "avg": {
            "field": "age"
          }
        }
      }
    }
  },
  "size": 0
}

 

Range Aggregation 范围分组聚合

• NBA球员年龄按20,20-35,35这样分组

POST /nba/_search
{
  "aggs": {
    "ageRange别名": {
      "range": {
        "field": "age",
        "ranges": [
          {
            "to": 20,
            "key": "A"
          },
          {
            "from": 20,
            "to": 35,
            "key": "B"
          },
          {
            "from": 35,
            "key": "C"
          }
        ]
      }
    }
  },
  "size": 0
}

 
 

Date Range Aggregation 时间范围分组聚合

• NBA球员按出⽣年⽉分组

POST /nba/_search
{
  "aggs": {
    "birthDayRange别名": {
      "date_range": {
        "field": "birthDay",
        "format": "MM-yyy",
        "ranges": [
          {
            "to": "01-1989"
          },
          {
            "from": "01-1989",
            "to": "01-1999"
          },
          {
            "from": "01-1999",
            "to": "01-2009"
          },
          {
            "from": "01-2009"
          }
        ]
      }
    }
  },
  "size": 0
}

 

Date Histogram Aggregation 时间柱状图聚合

• 按天、⽉、年等进⾏聚合统计。可按 year (1y), quarter (1q), month (1M), week (1w), day(1d), hour (1h), minute (1m), second (1s) 间隔聚合
• NBA球员按出⽣年分组

POST /nba/_search
{
	"aggs": {
		"birthday_aggs别名": {
			"date_histogram": {
				"field": "birthDay",
				"format": "yyyy",
				"interval": "year"
			}
		}
	},
	"size": 0
}