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
