SQL查询转换成Elasticsearch查询

Elasticsearch虽然定位为Search Engine，但是因其可以持久化数据，很多时候，我们把Elasticsearch当成Database用，但是Elasticsearch不支持SQL，就需要把SQL逻辑转换成代码实现对应的功能。

以下列举了一些常用的SQL转换成对应的Java代码。

1.按某个field group by查询count

SELECT  
fieldA, COUNT(fieldA)
from table   
WHERE fieldC = "hoge" 
AND fieldD = "huga" 
AND fieldB > 10
AND fieldB < 100 
group by fieldA;

对应的java code:

SearchRequest searchRequest = new SearchRequest("sample_index");
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
TermsAggregationBuilder termsAggregationBuilder = AggregationBuilders.terms("my_fieldA").field("fieldA").size(100);

BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
TermQueryBuilder tf_fieldC = QueryBuilders.termQuery("fieldC", "hoge");
TermQueryBuilder tf_fieldD = QueryBuilders.termQuery("fieldD", "huga");
boolQueryBuilder.must(tf_fieldC);
boolQueryBuilder.must(tf_fieldD);

RangeQueryBuilder rangeQueryBuilder = QueryBuilders.rangeQuery("fieldB").gt(10).lt(100);
boolQueryBuilder.must(rangeQueryBuilder);

searchSourceBuilder.query(boolQueryBuilder).aggregation(termsAggregationBuilder);
searchRequest.source(searchSourceBuilder);

SearchResponse searchRes = client.search(searchRequest);
Terms fieldATerms = searchRes.getAggregations().get("my_fieldA");
for (Terms.Bucket filedABucket : fieldATerms.getBuckets()) {
    //fieldA
    String fieldAValue = filedABucket.getKeyAsString();

    //COUNT(fieldA)
    long fieldACount = filedABucket.getDocCount();
}

 

2. 按某个field 和 date group by 并查询另一个filed的sum，时间统计图，时间间隔是1天。

SELECT  
DATE(create_at), fieldA, SUM(fieldB) 
from table   
group by DATE(create_at), fieldA;

对应的java code:

SearchRequest searchRequest = new SearchRequest("sample_index");
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();

DateHistogramAggregationBuilder dateHistogramAgg = AggregationBuilders.dateHistogram("my_datehistogram")
        .field("create_at").dateHistogramInterval(DateHistogramInterval.DAY);
TermsAggregationBuilder fieldA_Agg = AggregationBuilders.terms("my_fieldA").field("fieldA").size(100);
fieldA_Agg.subAggregation(AggregationBuilders.sum("my_sum_fieldB").field("fieldB"));
dateHistogramAgg.subAggregation(fieldA_Agg);

searchSourceBuilder.query(QueryBuilders.matchAllQuery()).aggregation(dateHistogramAgg);
searchRequest.source(searchSourceBuilder);

SearchResponse searchRes = client.search(searchRequest);
ParsedDateHistogram dateHist = searchRes.getAggregations().get("my_datehistogram");
for (Histogram.Bucket dateBucket : dateHist.getBuckets()) {
    //DATE(create_at)
    String create_at = dateBucket.getKeyAsString();
    Terms fieldATerms = dateBucket.getAggregations().get("my_fieldA");
    for (Terms.Bucket filedABucket : fieldATerms.getBuckets()) {
        //fieldA
        String fieldAValue = filedABucket.getKeyAsString();

        //SUM(fieldB)
        Sum sumagg = filedABucket.getAggregations().get("my_sum_fieldB");
        double sumFieldB = sumagg.getValue();
    }
}

 

3. 按两个field group by并查询第三个filed的sum

SELECT  
fieldA, fieldC, SUM(fieldB)
from table   
group by fieldA, fieldC;

对应的java code:

SearchRequest searchRequest = new SearchRequest("sample_index");
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();

TermsAggregationBuilder termsb_fa = AggregationBuilders.terms("my_fieldA").field("fieldA").size(100);
TermsAggregationBuilder termsb_fc = AggregationBuilders.terms("my_fieldC").field("fieldC").size(50);
termsb_fc.subAggregation(AggregationBuilders.sum("my_sum_fieldB").field("fieldB"));
termsb_fa.subAggregation(termsb_fc);

searchSourceBuilder.query(QueryBuilders.matchAllQuery()).aggregation(termsb_fa);
searchRequest.source(searchSourceBuilder);

SearchResponse searchRes = client.search(searchRequest);
Terms fieldATerms = searchRes.getAggregations().get("my_fieldA");
for (Terms.Bucket filedABucket : fieldATerms.getBuckets()) {
    //fieldA
    String fieldAValue = filedABucket.getKeyAsString();
    Terms fieldCTerms = filedABucket.getAggregations().get("my_fieldC");
    for (Terms.Bucket filedCBucket : fieldCTerms.getBuckets()) {
        //fieldC
        String fieldCValue = filedCBucket.getKeyAsString();

        //SUM(fieldB)
        Sum sumagg = filedCBucket.getAggregations().get("my_sum_fieldB");
        double sumFieldB = sumagg.getValue();
    }
}

 

4. 按某个filed group by 并查询count、sum 和 average

SELECT  
fieldA, COUNT(fieldA), SUM(fieldB), AVG(fieldB) 
from table   
group by fieldA;

对应的java code:

SearchRequest searchRequest = new SearchRequest("sample_index");
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();

TermsAggregationBuilder termsb = AggregationBuilders.terms("my_fieldA").field("fieldA").size(100);
termsb.subAggregation(AggregationBuilders.sum("my_sum_fieldB").field("fieldB"));
termsb.subAggregation(AggregationBuilders.avg("my_avg_fieldB").field("fieldB"));

searchSourceBuilder.query(QueryBuilders.matchAllQuery()).aggregation(termsb);
searchRequest.source(searchSourceBuilder);

SearchResponse searchRes = client.search(searchRequest);
Terms fieldATerms = searchRes.getAggregations().get("my_fieldA");
for (Terms.Bucket filedABucket : fieldATerms.getBuckets()) {
    //fieldA
    String fieldAValue = filedABucket.getKeyAsString();

    //COUNT(fieldA)
    long fieldACount = filedABucket.getDocCount();

    //SUM(fieldB)
    Sum sumagg = filedABucket.getAggregations().get("my_sum_fieldB");
    double sumFieldB = sumagg.getValue();

    //AVG(fieldB)
    Avg avgagg = filedABucket.getAggregations().get("my_avg_fieldB");
    double avgFieldB = avgagg.getValue();
}

 

5. 按某个field group by 并按另一个filed的Sum排序，获取前10

SELECT  
fieldA, SUM(fieldB)
from table
WHERE fieldC = "hoge" 
group by fieldA
order by SUM(fieldB) DESC
limit 10;

对应的java code:

SearchRequest searchRequest = new SearchRequest("sample_index");
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();

QueryBuilder termsc = QueryBuilders.termQuery("fieldC","hoge");
QueryBuilder queryBuilder = QueryBuilders.boolQuery().must(termsc);

TermsAggregationBuilder aggregationBuilder = AggregationBuilders.terms("my_fieldA").field("fieldA").size(10);
aggregationBuilder.subAggregation(AggregationBuilders.sum("my_sum_fieldB").field("fieldB"));
aggregationBuilder.order(BucketOrder.aggregation("my_sum_fieldB", false));

searchSourceBuilder.query(queryBuilder).aggregation(aggregationBuilder);
searchRequest.source(searchSourceBuilder);

SearchResponse searchRes = client.search(searchRequest);
Terms terms = searchRes.getAggregations().get("my_fieldA");
for (Terms.Bucket entry : terms.getBuckets()) {
    String fieldAValue = entry.getKey().toString();

    Sum sumagg = entry.getAggregations().get("my_sum_fieldB");
    double fieldValue = sumagg.getValue();
}

 

6. 按2个field group by 并按总体的doc count，获取前10

SELECT  
fieldA, fieldB, COUNT(fieldA,fieldB)
from table
WHERE fieldC = "hoge"
group by fieldA,fieldB
order by COUNT(fieldA, fieldB) DESC
limit 10;

使用multi-terms-aggregation。

官方文档：https://www.elastic.co/guide/en/elasticsearch/reference/7.16/search-aggregations-bucket-multi-terms-aggregation.html#search-aggregations-bucket-multi-terms-aggregation

但是在ES 7中的 Java Rest High Level Client 中没有对应的multi-terms-aggregation API

这个issue地址：https://github.com/elastic/elasticsearch/issues/75030

ES官方在java 8的API中实现了multi-terms-aggregation，但是在ES 7的Java Rest High Level Client 中没有。

网上github上的一个实现：https://github.com/zimmj/elastic-multi-term-aggre

 

代码参考： 

GitHub：https://github.com/luxiaoxun/Code4Java

Gitee：https://gitee.com/luxiaoxun001/Code4Java