SQL to Elasticsearch java code

简介:
+关注继续查看

把Elasticsearch当成Database用,因为Elasticsearch不支持SQL,就需要把SQL转换成代码实现。

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:

复制代码
SearchRequestBuilder searchReq = client.prepareSearch("sample_index");
searchReq.setTypes("sample_types");
TermsBuilder termsb = AggregationBuilders.terms("my_fieldA").field("fieldA").size(100);

BoolFilterBuilder bf = FilterBuilders.boolFilter();
TermFilterBuilder tf_fieldC = FilterBuilders.termFilter("fieldC","hoge");
TermFilterBuilder tf_fieldD = FilterBuilders.termFilter("fieldD","huga");
bf.must(tf_fieldC);
bf.must(tf_fieldD);

RangeFilterBuilder rangefieldBFilter = FilterBuilders.rangeFilter("fieldB")
                .gt(10)
                .lt(100);

searchReq.setQuery(QueryBuilders.filteredQuery(QueryBuilders.matchAllQuery(),
                    FilterBuilders.andFilter(bf, rangefieldBFilter))).addAggregation(
                    termsb);
SearchResponse searchRes = searchReq.execute().actionGet();

Terms fieldATerms = searchRes.getAggregations().get("my_fieldA");
for (Terms.Bucket filedABucket : fieldATerms.getBuckets()) {
    //fieldA
    String fieldAValue = filedABucket.getKey();
    
    //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:

复制代码
SearchRequestBuilder searchReq = client.prepareSearch("sample_index");
searchReq.setTypes("sample_types");
DateHistogramBuilder dhb = AggregationBuilders.dateHistogram("my_datehistogram").field("create_at").interval(DateHistogram.Interval.days(1));
TermsBuilder termsb_fa = AggregationBuilders.terms("my_fieldA").field("fieldA").size(100);
termsb_fa.subAggregation(AggregationBuilders.sum("my_sum_fieldB").field("fieldB"));
dhb.subAggregation(termsb_fa)

searchReq.setQuery(QueryBuilders.matchAllQuery()).addAggregation(dhb);
SearchResponse searchRes = searchReq.execute().actionGet();

DateHistogram dateHist = searchRes.getAggregations().get("my_datehistogram");
for (DateHistogram.Bucket dateBucket : dateHist.getBuckets()) {
    //DATE(create_at)
    String create_at = dateentry.getKey();
    Terms fieldATerms = dateBucket.getAggregations().get("my_fieldA");
    for (Terms.Bucket filedABucket : fieldATerms.getBuckets()) {
        //fieldA
        String fieldAValue = filedABucket.getKey();
        
        //SUM(fieldB)
        Sum sumagg = filedABucket.getAggregations().get("my_sum_fieldB");
        long sumFieldB = (long)sumagg.getValues();
    }
}
复制代码

 

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

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

对应的java code:

复制代码
SearchRequestBuilder searchReq = client.prepareSearch("sample_index");
searchReq.setTypes("sample_types");

TermsBuilder termsb_fa = AggregationBuilders.terms("my_fieldA").field("fieldA").size(100);
TermsBuilder 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)

searchReq.setQuery(QueryBuilders.matchAllQuery()).addAggregation(termsb_fa);
SearchResponse searchRes = searchReq.execute().actionGet();

Terms fieldATerms = searchRes.getAggregations().get("my_fieldA");
for (Terms.Bucket filedABucket : fieldATerms.getBuckets()) {
    //fieldA
    String fieldAValue = filedABucket.getKey();
    Terms fieldCTerms = filedABucket.getAggregations().get("my_fieldC");
    for (Terms.Bucket filedCBucket : fieldCTerms.getBuckets()) {
        //fieldC
        String fieldCValue = filedCBucket.getKey();
        
        //SUM(fieldB)
        Sum sumagg = filedCBucket.getAggregations().get("my_sum_fieldB");
        long sumFieldB = (long)sumagg.getValues();
    }
}
复制代码

 

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

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

对应的java code:

复制代码
SearchRequestBuilder searchReq = client.prepareSearch("sample_index");
searchReq.setTypes("sample_types");

TermsBuilder 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"));

searchReq.setQuery(QueryBuilders.matchAllQuery()).addAggregation(termsb);
SearchResponse searchRes = searchReq.execute().actionGet();
Terms fieldATerms = searchRes.getAggregations().get("my_fieldA");
for (Terms.Bucket filedABucket : fieldATerms.getBuckets()) {
    //fieldA
    String fieldAValue = filedABucket.getKey();
    
    //COUNT(fieldA)
    long fieldACount = filedABucket.getDocCount();
    
    //SUM(fieldB)
    Sum sumagg = filedABucket.getAggregations().get("my_sum_fieldB");
    long sumFieldB = (long)sumagg.getValues();
    
    //AVG(fieldB)
    Avg avgagg = filedABucket.getAggregations().get("my_avg_fieldB");
    double avgFieldB = avgagg.getValues();
}
复制代码

 

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:

复制代码
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(Order.aggregation("my_sum_fieldB", false));
SearchResponse searchResponse = client.prepareSearch("sample_index").setQuery(queryBuilder).addAggregation(aggregationBuilder).execute().actionGet();
Terms terms = searchResponse.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();
}
复制代码

 

代码在GitHub上:https://github.com/luxiaoxun/Code4Java

 


    本文转自阿凡卢博客园博客,原文链接:http://www.cnblogs.com/luxiaoxun/p/6826211.html,如需转载请自行联系原作者


相关实践学习
使用阿里云Elasticsearch体验信息检索加速
通过创建登录阿里云Elasticsearch集群,使用DataWorks将MySQL数据同步至Elasticsearch,体验多条件检索效果,简单展示数据同步和信息检索加速的过程和操作。
ElasticSearch 入门精讲
ElasticSearch是一个开源的、基于Lucene的、分布式、高扩展、高实时的搜索与数据分析引擎。根据DB-Engines的排名显示,Elasticsearch是最受欢迎的企业搜索引擎,其次是Apache Solr(也是基于Lucene)。 ElasticSearch的实现原理主要分为以下几个步骤: 用户将数据提交到Elastic Search 数据库中 通过分词控制器去将对应的语句分词,将其权重和分词结果一并存入数据 当用户搜索数据时候,再根据权重将结果排名、打分 将返回结果呈现给用户 Elasticsearch可以用于搜索各种文档。它提供可扩展的搜索,具有接近实时的搜索,并支持多租户。
相关文章
|
1月前
|
存储 Java 索引
|
2月前
|
存储 Java 索引
五.全文检索ElasticSearch经典入门-ElasticSearch Java实战
五.全文检索ElasticSearch经典入门-ElasticSearch Java实战
|
3月前
|
JSON 自然语言处理 网络协议
【ElasticSearch从入门到放弃系列 六】Java客户端操作ElasticSearch
【ElasticSearch从入门到放弃系列 六】Java客户端操作ElasticSearch
124 0
|
4月前
|
JSON 自然语言处理 Java
来聊一聊 ElasticSearch 最新版的 Java 客户端
来聊一聊 ElasticSearch 最新版的 Java 客户端
|
5月前
|
SQL 消息中间件 JavaScript
ElasticSearch进阶:一文全览各种ES查询在Java中的实现(下)
ElasticSearch进阶:一文全览各种ES查询在Java中的实现(下)
|
5月前
|
SQL 缓存 JavaScript
ElasticSearch进阶:一文全览各种ES查询在Java中的实现(上)
ElasticSearch进阶:一文全览各种ES查询在Java中的实现(上)
|
7月前
|
Java API Maven
ElasticSearch学习(三):Java API操作ElasticSearch
ElasticSearch学习(三):Java API操作ElasticSearch
163 0
ElasticSearch学习(三):Java API操作ElasticSearch
|
7月前
|
JavaScript Java
十、.net core(.NET 6)搭建ElasticSearch(ES)系列之Java环境搭建和Node.js环境搭建
安装java jdk环境:我此处使用的是jdk16版本。下载地址:https://www.oracle.com/java/technologies/javase-jdk16-downloads.html然后运行,直接默认都是下一步进行安装:
98 0
十、.net core(.NET 6)搭建ElasticSearch(ES)系列之Java环境搭建和Node.js环境搭建
|
7月前
|
Java API Maven
Elasticsearch连续剧之实战篇Java操作es
大家好!我是狮子,在上几篇文章中,详细介绍了在kibana中操作es,现在我们来看看真实开发中,如何使用Java操作es。
Elasticsearch连续剧之实战篇Java操作es
|
8月前
|
JSON Java API
全文检索工具elasticsearch:第三章: Java程序中的应用
全文检索工具elasticsearch:第三章: Java程序中的应用
148 0
全文检索工具elasticsearch:第三章: Java程序中的应用
推荐文章
更多