Es elasticsearch 十七 Java api 实现聚合几个聚合示例 sql 开启许可新特效 java 实现es7 sql 功能

最新推荐文章于 2025-04-02 08:03:06 发布

宇神城主_蒋浩宇

最新推荐文章于 2025-04-02 08:03:06 发布

阅读量1k

点赞数 2

分类专栏： elasticsearch es 文章标签： java elasticsearch 开发语言

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/weixin_42749765/article/details/131091976

版权

elasticsearch es 专栏收录该内容

21 篇文章

订阅专栏

目录

Java api 实现聚合

简单聚合按照颜色分组获取每个卖出数量

聚合每个颜色卖出数量，及平均价格（每个分桶子聚合）

按照颜色分组，获取销售数量，avg min max sum

按照600区间价格范围分组，算出每个区间销售总额

每个季度销售总额

其他展示方式 csv tsv json yaml

翻译实现 translate sql 怎么转成rest 请求

Java 实现 es7 sql功能

许可管理开启白金版试用或购买

引入依赖pom

Java api 实现聚合

依赖


import lombok.extern.slf4j.Slf4j;
import org.apache.http.HttpHost;
import org.elasticsearch.action.ActionListener;
import org.elasticsearch.action.search.SearchRequest;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.client.RequestOptions;
import org.elasticsearch.client.RestClient;
import org.elasticsearch.client.RestHighLevelClient;
import org.elasticsearch.client.indices.CreateIndexRequest;
import org.elasticsearch.client.indices.CreateIndexResponse;
import org.elasticsearch.common.xcontent.ToXContent;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.MultiMatchQueryBuilder;
import org.elasticsearch.index.query.QueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.search.SearchHit;
import org.elasticsearch.search.SearchHits;
import org.elasticsearch.search.aggregations.Aggregation;
import org.elasticsearch.search.aggregations.AggregationBuilder;
import org.elasticsearch.search.aggregations.AggregationBuilders;
import org.elasticsearch.search.aggregations.Aggregations;
import org.elasticsearch.search.aggregations.bucket.terms.Terms;
import org.elasticsearch.search.aggregations.bucket.terms.TermsAggregationBuilder;
import org.elasticsearch.search.builder.SearchSourceBuilder;
import org.elasticsearch.search.sort.SortOrder;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

简单聚合按照颜色分组获取每个卖出数量

//private RestHighLevelClient client  = new RestHighLevelClient(RestClient.builder(HttpHost.create("http://localhost:9200")));

@Test
public void jh1() throws IOException {
    SearchRequest searchRequest = new SearchRequest("tvs");
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    searchSourceBuilder.size(0);
    searchSourceBuilder.query(QueryBuilders.matchAllQuery());
    TermsAggregationBuilder termsAggregationBuilder = AggregationBuilders.terms("group_color").field("color");
    searchSourceBuilder.aggregation(termsAggregationBuilder);

    searchRequest.source(searchSourceBuilder);

    SearchResponse response = client.search(searchRequest,RequestOptions.DEFAULT);

    Aggregations aggregations = response.getAggregations();
    Terms group_color = aggregations.get("group_color");
    List<?extends Terms.Bucket> buckets = group_color.getBuckets();
    for (Terms.Bucket bucKey : buckets){
        System.out.println(bucKey.getKeyAsString()+"  == "+ bucKey.getDocCount());
    }
}

获取关注 buckets 获取所有分桶 -=分组

聚合每个颜色卖出数量，及平均价格（每个分桶子聚合）

@Test
public void jh2() throws IOException {
    SearchRequest searchRequest = new SearchRequest("tvs");
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    searchSourceBuilder.size(0);
    searchSourceBuilder.query(QueryBuilders.matchAllQuery());
    TermsAggregationBuilder termsAggregationBuilder = AggregationBuilders.terms("group_color").field("color");

    //增加子聚合
    AvgAggregationBuilder avgAggregationBuilder = AggregationBuilders.avg("avg_price").field("price");
    termsAggregationBuilder.subAggregation(avgAggregationBuilder);

    searchSourceBuilder.aggregation(termsAggregationBuilder);

    searchRequest.source(searchSourceBuilder);

    SearchResponse response = client.search(searchRequest,RequestOptions.DEFAULT);

    Aggregations aggregations = response.getAggregations();
    Terms group_color = aggregations.get("group_color");
    List<?extends Terms.Bucket> buckets = group_color.getBuckets();
    for (Terms.Bucket bucKey : buckets){

        //获取子聚合
        Aggregations aggregations9= bucKey.getAggregations();
        Avg avg_price = aggregations9.get("avg_price");

        System.out.println(bucKey.getKeyAsString()+"  == "+ bucKey.getDocCount() +"  ;  avg = "+avg_price.getValue());
    }
}

查询增加子聚合返回通过子聚合获取值

按照颜色分组，获取销售数量，avg min max sum

@Test
public void jh3() throws IOException {
    SearchRequest searchRequest = new SearchRequest("tvs");
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    searchSourceBuilder.size(0);
    searchSourceBuilder.query(QueryBuilders.matchAllQuery());
    TermsAggregationBuilder termsAggregationBuilder = AggregationBuilders.terms("group_color").field("color");

    //增加子聚合
    AvgAggregationBuilder avgAggregationBuilder = AggregationBuilders.avg("avg_price").field("price");
    termsAggregationBuilder.subAggregation(avgAggregationBuilder);

    SumAggregationBuilder sumAggregationBuilder = AggregationBuilders.sum("sum_price").field("price");
    termsAggregationBuilder.subAggregation(sumAggregationBuilder);

    MaxAggregationBuilder maxAggregationBuilder = AggregationBuilders.max("max_price").field("price");
    termsAggregationBuilder.subAggregation(maxAggregationBuilder);

    MinAggregationBuilder minAggregationBuilder = AggregationBuilders.min("min_price").field("price");
    termsAggregationBuilder.subAggregation(minAggregationBuilder);

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

    SearchResponse response = client.search(searchRequest,RequestOptions.DEFAULT);

    Aggregations aggregations = response.getAggregations();
    Terms group_color = aggregations.get("group_color");
    List<?extends Terms.Bucket> buckets = group_color.getBuckets();
    for (Terms.Bucket bucKey : buckets){

        //获取子聚合
        Aggregations aggregations9= bucKey.getAggregations();
        Avg avg_price = aggregations9.get("avg_price");
        Sum sum_price = aggregations9.get("sum_price");
        Min min_price = aggregations9.get("min_price");
        Max max_price = aggregations9.get("max_price");

        System.out.println(bucKey.getKeyAsString()+"  == "+ bucKey.getDocCount() +"  ;  avg = "+avg_price.getValue()
                +"  ;  sum_price = "+sum_price.getValue()
                +"  ;  min_price = "+min_price.getValue()
                +"  ;  max_price = "+max_price.getValue());
    }
}

按照600区间价格范围分组，算出每个区间销售总额

@Test
public void jh4() throws IOException {
    SearchRequest searchRequest = new SearchRequest("tvs");
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    searchSourceBuilder.size(0);
    searchSourceBuilder.query(QueryBuilders.matchAllQuery());
    HistogramAggregationBuilder termsAggregationBuilder =  AggregationBuilders.histogram("group_histogram").field("price").interval(600);

    //增加子聚合
    SumAggregationBuilder sumAggregationBuilder = AggregationBuilders.sum("sum_price").field("price");
    termsAggregationBuilder.subAggregation(sumAggregationBuilder);

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

    SearchResponse response = client.search(searchRequest,RequestOptions.DEFAULT);

    Aggregations aggregations = response.getAggregations();
    Histogram group_color = aggregations.get("group_histogram");
    List<?extends Histogram.Bucket> buckets = group_color.getBuckets();
    for (Histogram.Bucket bucKey : buckets){
        //获取子聚合
        Aggregations aggregations9= bucKey.getAggregations();
        Sum sum_price = aggregations9.get("sum_price");

        System.out.println(bucKey.getKeyAsString()+"  == "+ bucKey.getDocCount()
                +"  ;  sum_price = "+sum_price.getValue());
    }
}

每个季度销售总额

@Test
public void jh5() throws IOException {
    SearchRequest searchRequest = new SearchRequest("tvs");
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    searchSourceBuilder.size(0);
    searchSourceBuilder.query(QueryBuilders.matchAllQuery());

    DateHistogramAggregationBuilder termsAggregationBuilder =AggregationBuilders.dateHistogram("group_histogram")
            .field("sold_date").format("yyyy-MM-dd").minDocCount(0).calendarInterval(DateHistogramInterval.QUARTER)
            .extendedBounds(new ExtendedBounds("2019-01-01","2023-01-01"));

    //增加子聚合
    SumAggregationBuilder sumAggregationBuilder = AggregationBuilders.sum("sum_price").field("price");
    termsAggregationBuilder.subAggregation(sumAggregationBuilder);

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

    SearchResponse response = client.search(searchRequest,RequestOptions.DEFAULT);

    Aggregations aggregations = response.getAggregations();
    ParsedDateHistogram group_color = aggregations.get("group_histogram");

    List<?extends Histogram.Bucket> buckets = group_color.getBuckets();
    for (Histogram.Bucket bucKey : buckets){
        //获取子聚合
        Aggregations aggregations9= bucKey.getAggregations();
        Sum sum_price = aggregations9.get("sum_price");

        System.out.println(bucKey.getKeyAsString()+"  == "+ bucKey.getDocCount()
                +"  ;  sum_price = "+sum_price.getValue());
    }
}

Sql新特性

简单sql

post /_sql?format=txt
{
  "query":" select * from tvs "
}

聚合sql

post /_sql?format=txt
{
  "query":" select color,avg(price),sum(price) from tvs group by color "
}

启动方式

elasticsearch-sql-cli.bat

其他展示方式 csv tsv json yaml

post /_sql?format=csv
{
  "query":" select * from tvs "
}

post /_sql?format=tsv
{
  "query":" select * from tvs "
}

post /_sql?format=json
{
  "query":" select * from tvs "
}

post /_sql?format=yaml
{
  "query":" select * from tvs "
}

翻译实现 translate sql 怎么转成rest 请求


post /_sql/translate
{
  "query":" select * from tvs "
}

混合使用


post /_sql?format=txt
{
  "query":" select * from tvs ",
  "filter":{
    "range": {
      "price": {
        "gte": 1110,
        "lte": 2220
      }
    }
  }
}



     brand     |     color     |     price     |       sold_date        
---------------+---------------+---------------+------------------------
天虹2            |红色2            |2000           |2019-12-06T00:00:00.000Z
天虹             |红色             |1800           |2022-12-06T00:00:00.000Z

Java 实现 es7 sql功能

许可管理开启白金版试用或购买

引入依赖pom


    <dependency>
        <groupId>org.elasticsearch.plugin</groupId>
        <artifactId>x-pack-sql-jdbc</artifactId>
        <version>7.3.0</version>
    </dependency>

</dependencies>
<repositories>
    <repository>
        <id>elastic.co</id>
        <url>https://artifacts.elastic.co/maven</url>
    </repository>
</repositories>

Java 代码

package com.esbase.fintech.ais;

import java.sql.*;

public class TestJdbc {

    public static void main(String[] args) throws SQLException {
        //链接
       Connection connection =  DriverManager.getConnection("jdbc:es://http://localhost:9200");
        //创建 statement
        Statement statement = connection.createStatement();
        //执行sql
        ResultSet resultSet = statement.executeQuery("select * from tvs");
        //获取结果
        while (resultSet.next()){
            System.out.println(resultSet.getString(1));
            System.out.println(resultSet.getString(2));
            System.out.println(resultSet.getString(3));
            System.out.println(resultSet.getString(4));
            System.out.println("==========");
        }

    }
}

ok

持续更新

评论

被折叠的条评论为什么被折叠?

到【灌水乐园】发言

查看更多评论

添加红包

成就一亿技术人!

hope_wisdom

发出的红包

实付元

使用余额支付

点击重新获取

扫码支付

钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。