ElasticSearch查询之Aggregation

Elasticsearch（5.0）中的Aggregation查询大概分一下几类

具体查看：https://www.elastic.co/guide/en/elasticsearch/reference/5.0/search-aggregations.html

下面将通过java api操作来总结其使用方法：

操作之前，我们首先建一个employee索引，employee索引类型包括8个字段：编号(id), 姓名(ename)，性别(sex)，年龄(age)，籍贯(birthplace)，部门名称(deptid)，职位(job)，工资(salary)。

生成的mapping如下：

mapping创建成功后，我随机插入了100条数据

一： Metrics Aggregations   

  1：对单个字段group by

 例如要计算每个部门的男性员工数，如果使用SQL语句，应表达如下：

select  deptid, count(*) as emp_count from employee group by deptid where sex=0

es的java代码如下：

public static void groupByTest(TransportClient client) {
        SearchRequestBuilder requestBuilder = client.prepareSearch(INDEX_NAME).setTypes(MAPPING_TYPE);
        TermsAggregationBuilder termsAggregationBuilder = AggregationBuilders.terms("emp_count").field("deptid");
        requestBuilder.setQuery(QueryBuilders.termQuery("sex", 0)).addAggregation(termsAggregationBuilder);
        SearchResponse response = requestBuilder.execute().actionGet();
        Terms aggregation = response.getAggregations().get("emp_count");

        for (Terms.Bucket bucket : aggregation.getBuckets()) {
            System.out.println("部门编号=" + bucket.getKey() + ";员工数=" + bucket.getDocCount());
        }
    }

 输入结果:

部门编号=8;员工数=31
部门编号=5;员工数=25
部门编号=6;员工数=22
部门编号=7;员工数=22

 

2：group by多个field

例如要计算每个球队每个位置的球员数，如果使用SQL语句，应表达如下：

select deptid, birthplace, count(*) as emp_count from employee group by deptid, birthplace

对应的java代码如下：

    public static void groupByMutilFieldTest(TransportClient client) {
        SearchRequestBuilder requestBuilder = client.prepareSearch(INDEX_NAME).setTypes(MAPPING_TYPE);
        TermsAggregationBuilder aggregationBuilder1 = AggregationBuilders.terms("emp_count").field("deptid");
        TermsAggregationBuilder aAggregationBuilder2 = AggregationBuilders.terms("region_count").field("birthplace");
        requestBuilder.addAggregation(aggregationBuilder1.subAggregation(aAggregationBuilder2));
        SearchResponse response = requestBuilder.execute().actionGet();
        Terms terms1 = response.getAggregations().get("emp_count");

        Terms terms2;
        for (Terms.Bucket bucket : terms1.getBuckets()) {
            System.out.println("部门编号=" + bucket.getKey());
            terms2 = bucket.getAggregations().get("region_count");
            for (Terms.Bucket bucket2 : terms2.getBuckets()) {
                System.out.println("籍贯=" + bucket2.getKey() + ";员工数=" + bucket2.getDocCount());
            }
        }
    }

输入的结果：

部门编号=8
籍贯=广东佛山;员工数=9
籍贯=湖北武汉;员工数=8
籍贯=湖南长沙;员工数=5
籍贯=广东深圳;员工数=4
籍贯=广东广州;员工数=3
籍贯=湖南岳阳;员工数=2
部门编号=5
籍贯=湖北武汉;员工数=7
籍贯=广东佛山;员工数=4
籍贯=广东广州;员工数=4
籍贯=广东深圳;员工数=4
籍贯=湖南岳阳;员工数=3
籍贯=湖南长沙;员工数=3
部门编号=6
籍贯=广东广州;员工数=6
籍贯=广东佛山;员工数=4
籍贯=广东深圳;员工数=4
籍贯=湖南岳阳;员工数=4
籍贯=湖北武汉;员工数=2
籍贯=湖南长沙;员工数=2
部门编号=7
籍贯=广东广州;员工数=6
籍贯=湖南岳阳;员工数=6
籍贯=广东深圳;员工数=4
籍贯=湖北武汉;员工数=3
籍贯=广东佛山;员工数=2
籍贯=湖南长沙;员工数=1

 

3：max/min/sum/avg

例如 计算每个部门最高的工资，如果使用SQL语句，应表达如下：

select deptid, max(salary) as max_salary from employee group by deptid

对应的java代码如下：

public static void maxTest(TransportClient client) {
        SearchRequestBuilder requestBuilder = client.prepareSearch(INDEX_NAME).setTypes(MAPPING_TYPE);

        TermsAggregationBuilder aggregationBuilder1 = AggregationBuilders.terms("deptid").field("deptid");
        MaxAggregationBuilder aggregationBuilder2 = AggregationBuilders.max("maxsalary").field("salary");
        requestBuilder.addAggregation(aggregationBuilder1.subAggregation(aggregationBuilder2));
        SearchResponse response = requestBuilder.execute().actionGet();

        Terms aggregation = response.getAggregations().get("deptid");
        Max terms2 = null;
        for (Terms.Bucket bucket : aggregation.getBuckets()) {
            terms2 = bucket.getAggregations().get("maxsalary");  //class org.elasticsearch.search.aggregations.metrics.max.InternalMax
            System.out.println("部门编号=" + bucket.getKey() + ";最高工资=" + terms2.getValue());
        }
    }

输出结果：

部门编号=8;最高工资=8000.0
部门编号=5;最高工资=8000.0
部门编号=6;最高工资=8000.0
部门编号=7;最高工资=8000.0

 

4：对多个field求max/min/sum/avg

例如要计算每个部门的平均年龄，同时又要计算总薪资，最后按平均年龄升序排序，如果使用SQL语句，应表达如下：

select deptid, avg(age) as avg_age, sum(salary) as max_salary from employee group by deptid order by avg_age asc

对应的java代码如下：

    public static void methodTest1(TransportClient client) {
        SearchRequestBuilder requestBuilder = client.prepareSearch(INDEX_NAME).setTypes(MAPPING_TYPE);

        TermsAggregationBuilder aggregationBuilder1 = AggregationBuilders.terms("deptid").field("deptid") //
                .order(Order.aggregation("avg_age", true)); //按平均年龄升序排序,
        AggregationBuilder aggregationBuilder2 = AggregationBuilders.avg("avg_age").field("age");
        AggregationBuilder aggregationBuilder3 = AggregationBuilders.sum("sum_salary").field("salary");
        requestBuilder.addAggregation(aggregationBuilder1.subAggregation(aggregationBuilder2).subAggregation(aggregationBuilder3));
        SearchResponse response = requestBuilder.execute().actionGet();

        Terms aggregation = response.getAggregations().get("deptid");
        Avg terms2 = null;
        Sum term3 = null;
        for (Terms.Bucket bucket : aggregation.getBuckets()) {
            terms2 = bucket.getAggregations().get("avg_age"); // org.elasticsearch.search.aggregations.metrics.avg.InternalAvg
            term3 = bucket.getAggregations().get("sum_salary"); // org.elasticsearch.search.aggregations.metrics.sum.InternalSum
            System.out.println("部门编号=" + bucket.getKey() + ";平均年龄=" + terms2.getValue() + ";总工资=" + term3.getValue());
        }
    }

输入结果：

部门编号=7;平均年龄=31.954545454545453;总工资=129000.0
部门编号=5;平均年龄=33.36;总工资=121000.0
部门编号=8;平均年龄=33.54838709677419;总工资=171000.0
部门编号=6;平均年龄=33.59090909090909;总工资=121000.0

 

总结 

从实现上来讲，SearchRequestBuilder在内部保持了一个私有的 SearchSourceBuilder实例， SearchSourceBuilder内部包含一个List<AbstractAggregationBuilder>，
每次调用addAggregation时会调用 SearchSourceBuilder实例，添加一个AggregationBuilder。

同样的，TermsAggregationBuilder也在内部保持了一个List<AbstractAggregationBuilder>，调用addAggregation方法（来自父类addAggregation）时会添加一个AggregationBuilder。

 

其他例子，补充中。。。