ElasticSearch ( 九 ) 订单操作实例

春哥的魔法书

已于 2023-04-11 16:56:25 修改

阅读量596

点赞数 2

分类专栏： ElasticSearch 文章标签： elasticsearch 大数据搜索引擎

于 2023-04-07 16:58:19 首次发布

本文链接：https://blog.csdn.net/yuanchun05/article/details/130016393

版权

ElasticSearch 专栏收录该内容

10 篇文章 2 订阅

订阅专栏

ElasticSearch订单管理

1.准备

1.1.索引映射

PUT /order_master
{
  "mappings": {
    "properties": {
      "orderId": {
        "type": "long"
      },
      "cusId": {
        "type": "long"
      },
      "goodsId": {
        "type": "long"
      },
      "orderSn": {
        "type": "long"
      },
      "goodsName": {
        "type": "text",
        "analyzer": "ik_max_word",
        "search_analyzer": "ik_smart"
      },
      "goodsPrice": {
        "type": "double"
      },
      "goodsCount": {
        "type": "integer"
      },
      "orderChannel": {
        "type": "integer"
      },
      "status": {
        "type": "integer"
      },
      "payDate": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
      },
      "createDate": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
      }
    },
    "_meta": {
      "author": "yuan",
      "version": "1.0"
    },
    "_source": {
      "enabled": true
    },
    "dynamic": true
  }
}

1.2.实体类

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

/**
 * 
 * @TableName order_master
 */
@TableName(value ="order_master")
@Data
public class OrderMaster implements Serializable {
    /**
     * 订单主键
     */
    @TableId
    private Integer orderId;

    /**
     * 用户ID
     */
    private Integer cusId;

    /**
     * 商品订单号
     */
    private Integer orderSn;

    /**
     * 商品ID
     */
    private Integer goodsId;

    /**
     * 商品名字
     */
    private String goodsName;

    /**
     * 商品数量
     */
    private Integer goodsCount;

    /**
     * 商品价格
     */
    private BigDecimal goodsPrice;

    /**
     * 1 pc,2 android, 3 ios
     */
    private String orderChannel;

    /**
     * 订单状态，0 新建未支付，1已支付，2已发货，3已收货，4已退货，5已完成 ,6已取消
     */
    private Integer status;

    /**
     * 订单创建时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createDate;

    /**
     * 支付时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date payDate;

    @TableField(exist = false)
    private static final long serialVersionUID = 1L;
}

1.3.数据库表结构

CREATE TABLE `order_master`  (
  `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单主键',
  `cus_id` int(11) NULL DEFAULT NULL COMMENT '用户ID',
  `order_sn` int(20) NULL DEFAULT NULL COMMENT '商品订单号',
  `goods_id` int(11) NULL DEFAULT NULL COMMENT '商品ID',
  `goods_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商品名字',
  `goods_count` int(11) NULL DEFAULT NULL COMMENT '商品数量',
  `goods_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '商品价格',
  `order_channel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '1 pc,2 android, 3 ios',
  `status` int(11) NULL DEFAULT NULL COMMENT '订单状态，0 新建未支付，1已支付，2已发货，3已收货，4已退货，5已完成 ,6已取消',
  `create_date` datetime NULL DEFAULT NULL COMMENT '订单创建时间',
  `pay_date` datetime NULL DEFAULT NULL COMMENT '支付时间',
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

2.基本操作

2.1.上传数据

将数据库中6000条数据, 导入到ES中

将 id 设置为记录的 orderId值

@RequestMapping("/orderMaster/query")
public  Map<String, Object> query(){
    // 调用 全查方法
    List<OrderMaster> list = orderMasterService.list(new LambdaQueryWrapper<OrderMaster>());

    // 存储到  es中
    // 1.创建Request
    BulkRequest request = new BulkRequest();
    //下面尽量控制一下一次bulk的数量，如果数据过大，条数过多可能出现同步不完全的情况
    for (OrderMaster orderMaster : list) {

        String data = JSONObject.toJSONString(orderMaster);
        // 注意 索引名 ,  id 设置成 与数据记录的 orderId 对应
        IndexRequest indexRequest = new IndexRequest("order_master").id(orderMaster.getOrderId()+"");
        indexRequest.source(data, XContentType.JSON);
        // 保存, 指明类型
        request.add(indexRequest);
    }
    try {
        // 批量操作
        BulkResponse response = client.bulk(request, RequestOptions.DEFAULT);
        System.out.println("response = " + response);
        // 如果有异常
        if (response.hasFailures()) {
            System.out.println("出现异常!");
        }
    } catch (IOException e) {
        e.printStackTrace();
    }

    // 信息返回页面
    Map<String, Object> map = new HashMap<>();
    map.put("data", list);
    return   map;
}

在 kibana中测试

GET /order_master/_search

得到结果, total 为 6000

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 6000,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {...}
    ]
  }
}

2.2.添加一条数据

这里模拟一条数据存储到ES里

/**
  * 添加一条数据到数据库的同时, 也上传到ES里
  */
@RequestMapping("/orderMaster/addSave")
public Map<String, Object> addSave() throws IOException {

    OrderMaster orderMaster = new OrderMaster();
    orderMaster.setOrderId(10000); // orderId 设置为 10000
    orderMaster.setCusId(5);
    orderMaster.setOrderSn(10000);
    orderMaster.setGoodsId(70);
    orderMaster.setGoodsName("华为 HUAWEI Mate 30 Pro 128GB 青山黛 5G 麒麟990 OLED环幕屏双4000万徕卡电影四摄");
    orderMaster.setGoodsCount(3);
    orderMaster.setGoodsPrice(new BigDecimal("6098.0"));
    orderMaster.setOrderChannel("2");
    orderMaster.setStatus(0);  // 状态为 0 
    orderMaster.setCreateDate(new Date());
    orderMaster.setPayDate(new Date());

    boolean result = orderMasterService.save(orderMaster);

    //es
    // 将对象转为json
    String data = JSONObject.toJSONString(orderMaster);

    IndexRequest indexRequest = new IndexRequest("order_master");
    indexRequest.id(orderMaster.getOrderId()+"");
    // 保存, 指明类型
    indexRequest.source(data, XContentType.JSON);
    IndexRequest timeout = indexRequest.timeout("50s");
    System.out.println("timeout = " + timeout);
    // 执行
    IndexResponse response = client.index(indexRequest, RequestOptions.DEFAULT);
    // 获取响应数据
    log.info("创建状态：{}", response.status());

    // 内部转到 添加页面  add
    Map<String, Object> map = new HashMap<>();
    map.put("type", "addSave");
    // 内部转到 列表页 list
    return   map;
}

在 kibana中测试 , 根据 id 是 10000 来查询

GET /order_master/_search
{
  "query": {
    "ids": {
      "values": [10000]
    }
  }
}

得到结果, 注意时间现在是时间戳格式

{
  "took" : 565,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "order_master",
        "_type" : "_doc",
        "_id" : "10000",
        "_score" : 1.0,
        "_source" : {
          "createDate" : 1680851891604,
          "cusId" : 5,
          "goodsCount" : 3,
          "goodsId" : 70,
          "goodsName" : "华为 HUAWEI Mate 30 Pro 128GB 青山黛 5G 麒麟990 OLED环幕屏双4000万徕卡电影四摄",
          "goodsPrice" : 6098.0,
          "orderChannel" : "2",
          "orderId" : 10000,
          "orderSn" : 10000,
          "payDate" : 1680851891604,
          "status" : 0
        }
      }
    ]
  }
}

2.3.修改数据

将 id 是 10000 的记录的状态从 0 修改为 1

/**
  * 将 id 是 10000 的记录的状态 从 0  修改为 1
  */
@RequestMapping("/orderMaster/editSave")
public Map<String, Object> editSave(){
    // 写死 测试
    Integer orderId = 10000;
    Integer status = 1;
    // 到数据库查询数据
    OrderMaster orderMaster = orderMasterService.getById(orderId);
    // 修改状态属性 
    orderMaster.setStatus(status);
    // 调用 根据id修改记录方法
    boolean result = orderMasterService.updateById(orderMaster);
    System.out.println("result = " + result);
    
    // 创建索引请求对象
    UpdateRequest request ;
    try {
        String data = JSONObject.toJSONString(orderMaster);
        request = new UpdateRequest("order_master", orderId + "").doc(data, XContentType.JSON);
        UpdateResponse response = client.update(request,  RequestOptions.DEFAULT);
        log.info("更新状态：{}", response.getResult());
    } catch (IOException e) {
        log.error("更新写入异常:{}", e.getMessage(), e);
    }
    if (log.isDebugEnabled()) {
        log.info("es更新数据完成");
    }

    Map<String, Object> map = new HashMap<>();
    map.put("type", "editSave");
    return   map;
}

在 kibana中测试 , 查询 id 是 10000 的记录

GET /order_master/_search
{
  "query": {
    "match": {
      "orderId": "10000"
    }
  }
}

得到结果, 注意现在的 status 状态字段

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "order_master",
        "_type" : "_doc",
        "_id" : "10000",
        "_score" : 1.0,
        "_source" : {
          "createDate" : 1680851892000,
          "cusId" : 5,
          "goodsCount" : 3,
          "goodsId" : 70,
          "goodsName" : "华为 HUAWEI Mate 30 Pro 128GB 青山黛 5G 麒麟990 OLED环幕屏双4000万徕卡电影四摄",
          "goodsPrice" : 6098.0,
          "orderChannel" : "2",
          "orderId" : 10000,
          "orderSn" : 10000,
          "payDate" : 1680851892000,
          "status" : 1
        }
      }
    ]
  }
}

2.4.根据id 从 ES中查询

根据 id 从ES 中获取数据

/**
  * 查询 id 是 10000 的记录
  */
@RequestMapping("/orderMaster/getDocumentForId")
public Map<String, Object> getDocumentForId() throws IOException {
    // 写死 测试
    Integer orderId = 10000;

    // 创建获取请求对象
    GetRequest getRequest = new GetRequest("order_master", orderId + "" );
    GetResponse response = client.get(getRequest, RequestOptions.DEFAULT);
    // 得到 查询结果
    String data = response.getSourceAsString();
    // 将 结果转换成 实例对象
    OrderMaster orderMaster = JSONObject.parseObject(data, new TypeReference<OrderMaster>() {});

    Map<String, Object> map = new HashMap<>();
    map.put("data", orderMaster);
    return   map;
}

2.5.删除记录

将 id 为 10000 的记录删除

/**
  *  删除 id 为 10000的记录
  */
@RequestMapping("/orderMaster/delOne")
    public Map<String, Object> delOne() throws IOException {
    // 写死 测试
    Integer orderId = 10000;

    // 调用 根据id查询对象方法
    boolean result = orderMasterService.removeById(orderId);
    System.out.println("result = " + result);

    // 创建删除请求对象
    DeleteRequest deleteRequest = new DeleteRequest("order_master", orderId + "");
    // 执行删除文档
    DeleteResponse response = client.delete(deleteRequest, RequestOptions.DEFAULT);
    log.info("删除状态：{}", response.status());

    Map<String, Object> map = new HashMap<>();
    map.put("type", "delOne");
    return   map;
}

在 kibana中测试 , 查询 id 是 10000 的记录

GET /order_master/_search
{
  "query": {
    "match": {
      "orderId": "10000"
    }
  }
}

得到结果, 已经查询不到记录

{
  "took" : 45,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 0,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  }
}

2.6.无条件查询

/**
  * 无条件查询
  */
@RequestMapping("/orderMaster/queryFromEs")
//在方法的参数里 写 ModelMap
public  Map<String, Object> queryFromEs() throws IOException {

    List<OrderMaster> list = new ArrayList<>();

    SearchRequest searchRequest = new SearchRequest();
    searchRequest.indices("order_master");

    // 设置 查询条件
    SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();
    // 全查
    sourceBuilder.query(QueryBuilders.matchAllQuery());
    // 将查询条件构造器传入request
    searchRequest.source(sourceBuilder);

    // 分析结果
    SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);

    // 查询结果
    SearchHits searchHits = searchResponse.getHits();
    // 打印 总记录
    System.out.println("hits.getTotalHits() = " + searchHits.getTotalHits());

    SearchHit[] hitss = searchHits.getHits();
    for (SearchHit hit : hitss) {
        String sourceAsString = hit.getSourceAsString();
        System.out.println("sourceAsString = " + sourceAsString);
        OrderMaster orderMaster = JSONObject.parseObject(sourceAsString, new TypeReference<OrderMaster>() {});
        System.out.println("orderMaster = " + orderMaster);

        list.add(orderMaster);
    }

    Map<String, Object> map = new HashMap<>();
    map.put("data", list);
    return   map;
}

相当于 DSL

GET /order_master/_search
{
  "query": {
    "match_all": {}
  }
}

3.条件查询

3.1.时间范围

/**
  * 时间范围查询
  */
@RequestMapping("/orderMaster/getDocumentRangeTime")
public Map<String, Object> getDocumentRangeTime( ) throws IOException {

    SearchRequest request = new SearchRequest();
    request.indices("order_master");

    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();

    BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();

    boolQueryBuilder.must(QueryBuilders.rangeQuery("createDate").gte("2023-04-01").lte("2023-04-02"));

    searchSourceBuilder.query(boolQueryBuilder);

    request.source(searchSourceBuilder);


    // 查询 得到结果
    SearchResponse searchResponse = client.search(request, RequestOptions.DEFAULT);

    List<OrderMaster> list = new ArrayList<>();
    // 查询结果
    SearchHits searchHits = searchResponse.getHits();
    // 查询 总记录数
    TotalHits totalHits = searchHits.getTotalHits();
    System.out.println("totalHits.value = " + totalHits.value + ",totalHits.relation = " + totalHits.relation);


    SearchHit[] hitss = searchHits.getHits();
    for (SearchHit hit : hitss) {
        String sourceAsString = hit.getSourceAsString();
        OrderMaster orderMaster = JSONObject.parseObject(sourceAsString, new TypeReference<OrderMaster>() {});

        list.add(orderMaster);
    }

    Map<String, Object> map = new HashMap<>();
    map.put("count", count++);
    map.put("data", totalHits.value );
    return   map;
}

相当于 DSL, 时间范围

GET /order_master/_search
{
  "query":{
    "range": {
      "createDate": {
        "from": "2023-04-01 00:00:00",
        "to": "2023-04-02 23:59:59",
        "include_lower": true,
        "include_upper": true
      }
    }
  }
}

得到结果

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1701,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {... 查询结果}
    ]
  }
}

3.2.灵活的时间查询

3.2.1.日期的计算表达式

表达式以一个给定日期开始，可以是now(当前时间)，也可以是以 || 结尾的日期字符串。

此给定日期可以选择后跟一个或多个数学表达式：

+1h : 加一个小时
-1d : 减一天
/d : 向下舍入到最近的一天

支持的单位有：

单位	描述
y	Years
M	Months
w	Weeks
d	Days
h/H	Hours
m	Minutes
s	Seconds

举例说明:

假设now是 2001-01-01 12:00:00，请看以下例子：
1 now+1h : now以毫秒为单位加一小时. 解析为: 2001-01-01 13:00:00
2 now-1h : now以毫秒为单位减一小时. 解析为: 2001-01-01 11:00:00
3 now-1h/d : now以毫秒为单位减一小时，向下舍入到天，解析为: 2001-01-01 00:00:00
4 2001.02.01||+1M/d : 2001-02-01以毫秒为单位加一个月，向下舍入到天，解析为: 2001-03-01 00:00:00

3.2.2.关于时间的四舍五入

对日期中的日、月、小时等进行四舍五入时, 取决于范围的结尾是包含(include)还是排除(exclude).

gt lt 向上舍入: 移动到舍入范围的最后一毫秒;

gte lte 向下舍入: 一定到舍入范围的第一毫秒.

举例说明:

1 “gt”: “2018-12-18||/M” 大于日期, 需要向上舍入, 结果是2018-12-31T23:59:59.999, 也就是不包含整个12月.

2 “gte”: “2018-12-18||/M” 大于或等于日期, 需要向下舍入, 结果是 2018-12-01, 也就是包含整个12月.

3 “lt”: “2018-12-18||/M” 小于日期, 需要向上舍入, 结果是2018-12-01, 也就是不包含整个12月.

4 “lte”: “2018-12-18||/M” 小于或等于日期, 需要向下舍入, 结果是2018-12-31T23:59:59.999, 也就是包含整个12月.

3.2.3.日期格式化范围查询(format)

格式化日期查询时, 将默认使用日期field中指定的格式进行解析, 当然也可以通过format参数来覆盖默认配置.

示例:

GET website/_search
{
    "query": {
        "range": {
            "post_date": {
                "gte": "2/1/2018",
                "lte": "2019",
                "format": "dd/MM/yyyy||yyyy"
            }
        }
    }
}

注意: 如果日期中缺失了部分年、月、日, 缺失的部分将被填充为unix系统的初始值, 也就是1970年1月1日.

比如, 将dd指定为format, 像"gte": 10将转换为1970-01-10T00:00:00.000Z.

3.2.3.时区范围查询(time_zone)

如果日期field的格式允许, 也可以通过在日期值本身中指定时区, 从而将日期从另一个时区的时间转换为UTC时间, 或者为其指定特定的time_zone参数.

示例:

GET website/_search
{
    "query": {
        "range": {
            "post_date": {
                "gte": "2018-01-01 00:00:00",
                "lte": "now",
                "format": "yyyy-MM-dd hh:mm:ss",
                "time_zone": "+1:00"
            }
        }
    }
}

ES中的日期类型必须按照UTC时间格式存储, 所以, 上述的2018-01-01 00:00:00将被转换为2017-12-31T23:00:00 UTC.

另外需要注意的是, now是不受time_zone影响的.

3.3.查询昨天到现在的记录数

在 kibana 中 DSL 修改前面的查询

GET /order_master/_search
{
  "query":{
    "range": {
      "createDate": {
        "from": "now-1d/d",
        "to": "now"
      }
    }
  }
}

java 中实现

/**
  * 时间范围查询
  */
@RequestMapping("/orderMaster/getDocumentRangeTime2")
public Map<String, Object> getDocumentRangeTime2( ) throws IOException {

    SearchRequest request = new SearchRequest();
    request.indices("order_master");

    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();

    BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();

    boolQueryBuilder.must(QueryBuilders.rangeQuery("createDate").from("now-1d/d").to("now"));

    searchSourceBuilder.query(boolQueryBuilder);

    request.source(searchSourceBuilder);


    // 查询 得到结果
    SearchResponse searchResponse = client.search(request, RequestOptions.DEFAULT);

    List<OrderMaster> list = new ArrayList<>();
    // 查询结果
    SearchHits searchHits = searchResponse.getHits();
    // 查询 总记录数
    TotalHits totalHits = searchHits.getTotalHits();
    System.out.println("totalHits.value = " + totalHits.value + ",totalHits.relation = " + totalHits.relation);


    SearchHit[] hitss = searchHits.getHits();
    for (SearchHit hit : hitss) {
        String sourceAsString = hit.getSourceAsString();
        OrderMaster orderMaster = JSONObject.parseObject(sourceAsString, new TypeReference<OrderMaster>() {});

        list.add(orderMaster);
    }

    Map<String, Object> map = new HashMap<>();
    map.put("count", count++);
    map.put("data", totalHits.value );
    return   map;
}

3.4.综合查询

在 kibana 中 DSL 进行查询

goodsName : “Mate 华为 HUAWEI 5G 4000万”

status : 1

createDate : 从 2023-04-05 00:00:00 到 2023-04-06 23:59:59

从 0 到 10 条( 分页)

goodsPrice : 降序 desc 排序

GET /order_master/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match":{
             "goodsName": "Mate  华为 HUAWEI  5G  4000万"
          }
        },{
          "term": {
            "status": {
              "value": "1"
            }
          }
        },{
          "range": {
            "createDate": {
              "gte": "2023-04-05 00:00:00",
              "lte": "2023-04-06 23:59:59"
            }
          }
        }
      ]
    }
  },
  "from": 0,
  "size": 10,
  "sort": {
    "goodsPrice": "desc"
  }
}

java 实现

/**
  * 综合查询
  *
  * @return
  * @throws IOException
  */
@RequestMapping("/orderMaster/getDocumentForMany2")
public Map<String, Object> getDocumentForMany2( ) throws IOException {

    Integer status = 1;
    Date createDate = new Date();

    String goodsName = "Mate  华为 HUAWEI  5G  4000万";

    Integer pageNum = 1;
    Integer pageSize = 10;

    String field = "goodsPrice";
    String order = "desc";


    SearchRequest request = new SearchRequest();
    request.indices("order_master");

    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();

    BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
    boolQueryBuilder.must(QueryBuilders.matchQuery("goodsName", goodsName));
    boolQueryBuilder.filter(QueryBuilders.termQuery("status", status));
    boolQueryBuilder.must(QueryBuilders.rangeQuery("createDate").gte("2023-04-05").lte("2023-04-06"));

    searchSourceBuilder.query(boolQueryBuilder);

    searchSourceBuilder.sort(field, "desc".equals(order)?SortOrder.DESC:SortOrder.ASC);
    searchSourceBuilder.from((pageNum - 1) * pageSize).size(pageSize);
    searchSourceBuilder.size(pageSize);

    request.source(searchSourceBuilder);

    // 3.发送请求
    SearchResponse searchResponse = client.search(request, RequestOptions.DEFAULT);

    List<OrderMaster> list = new ArrayList<>();
    // 查询结果
    SearchHits searchHits = searchResponse.getHits();
    // 查询 总记录数
    TotalHits totalHits = searchHits.getTotalHits();
    System.out.println("totalHits.value = " + totalHits.value + ",totalHits.relation = " + totalHits.relation);


    SearchHit[] hitss = searchHits.getHits();
    for (SearchHit hit : hitss) {
        String sourceAsString = hit.getSourceAsString();
        OrderMaster orderMaster = JSONObject.parseObject(sourceAsString, new TypeReference<OrderMaster>() {});

        list.add(orderMaster);
    }

    Map<String, Object> map = new HashMap<>();
    map.put("data", list);
    return   map;
}

4.聚合查询

4.1.聚合查询一

根据 orderChannel 渠道分组, 统计数量

全部 goodsPrice 价格平均值

全部 goodsCount 总数

在 java 中实现

/**
  * 聚合查询
  */
@RequestMapping("/orderMaster/getDocumentByChannel")
public Map<String, Object> getDocumentByChannel( ) throws IOException {

    SearchRequest searchRequest = new SearchRequest();
    searchRequest.indices("order_master");

    // 设置 查询条件
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    searchSourceBuilder.query(QueryBuilders.matchAllQuery());

    // 分组统计
    TermsAggregationBuilder channelAggTerm = AggregationBuilders.terms("channelAgg");
    channelAggTerm.field("orderChannel").size(10);
    searchSourceBuilder.aggregation(channelAggTerm);
    // 平均值统计
    AvgAggregationBuilder priceAvgAgg = AggregationBuilders.avg("priceAvg");
    priceAvgAgg.field("goodsPrice");
    searchSourceBuilder.aggregation(priceAvgAgg);
    // 合计统计
    SumAggregationBuilder countSumAgg = AggregationBuilders.sum("countSum");
    countSumAgg.field("goodsCount");
    searchSourceBuilder.aggregation(countSumAgg);

    searchRequest.source(searchSourceBuilder);
    // 查询 得到结果
    SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);


    Map<String, Object> mm = new HashMap<>();

    // 分析聚合信息
    Aggregations aggregations = searchResponse.getAggregations();
    System.out.println("aggregations = " + aggregations);
    // 分析 分组信息
    Terms channelAgg = aggregations.get("channelAgg");
    List<Map<String, Object>> keys = new ArrayList<>();
    for (Terms.Bucket bucket : channelAgg.getBuckets()) {
        Map<String, Object> m = new HashMap<>();
        m.put("keyName", bucket.getKeyAsString());
        m.put("countNumber", bucket.getDocCount());

        keys.add(m);
    }
    mm.put("channelAgg", keys);
    // 分析 平均值
    Avg priceAvg = aggregations.get("priceAvg");
    mm.put("priceAvg", priceAvg.getValue());

    // 分析 合计值
    Sum countSum = aggregations.get("countSum");
    mm.put("countSum", countSum.getValue());

    Map<String, Object> map = new HashMap<>();
    map.put("count", count++);
    map.put("data", mm);
    return   map;
}

相当于在 kibana 中 DSL 进行查询

GET /order_master/_search
{
  "query": {
    "match_all": {}
  },
   "aggs": {
    "channelAgg": {
      "terms": {
        "field": "orderChannel",
        "size": 10
      }
    },
    "priceAvg":{
      "avg": {
        "field": "goodsPrice"
      }
    },
    "countSum":{
      "sum": {
        "field": "goodsCount"
      }
    }
  }
}

得到结果, total 为 6000

{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 6000,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {...}
    ]
  },
  "aggregations" : {
    "priceAvg" : {
      "value" : 3224.312775
    },
    "channelAgg" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : 1,
          "doc_count" : 2030
        },
        {
          "key" : 3,
          "doc_count" : 2013
        },
        {
          "key" : 2,
          "doc_count" : 1957
        }
      ]
    },
    "countSum" : {
      "value" : 32778.0
    }
  }
}

4.2.聚合查询二:子聚合

根据 orderChannel 渠道分组, 统计数量

对每组信息再查询 goodsPrice 价格平均值

对每组信息再查询 goodsCount 总数

在 java 中实现


/**
  * 聚合查询, 子聚合查询
  */
@RequestMapping("/orderMaster/getDocumentByChannel2")
public Map<String, Object> getDocumentByChannel2( ) throws IOException {

    SearchRequest request = new SearchRequest();
    request.indices("order_master");

    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();

    BoolQueryBuilder boolQuery = QueryBuilders.boolQuery();
    searchSourceBuilder.query(boolQuery);


    TermsAggregationBuilder channelAgg = AggregationBuilders.terms("channelAgg");
    channelAgg.field("orderChannel").size(10);
    // 子 聚合
    channelAgg.subAggregation(AggregationBuilders.avg("priceAvg").field("goodsPrice"));
    channelAgg.subAggregation(AggregationBuilders.sum("countSum").field("goodsCount"));

    searchSourceBuilder.aggregation(channelAgg);

    request.source(searchSourceBuilder);

    // 查询得到结果
    SearchResponse searchResponse = client.search(request, RequestOptions.DEFAULT);

    Map<String, Object> mm = new HashMap<>();

    // 分析聚合结果
    ParsedLongTerms parsedLongTerms = searchResponse.getAggregations().get("channelAgg");
    List<Map<String, Object>> keys = new ArrayList<>();
    for (Terms.Bucket bucket : parsedLongTerms.getBuckets()) {
        Map<String, Object> m = new HashMap<>();
        m.put("keyName", bucket.getKeyAsString());
        m.put("countNumber", bucket.getDocCount());

        Avg priceAvg = bucket.getAggregations().get("priceAvg");
        m.put("priceAvg", priceAvg.getValue());

        Sum countSum = bucket.getAggregations().get("countSum");
        m.put("countSum", countSum.getValue());

        keys.add(m);
    }

    mm.put("channelAgg", keys);

    Map<String, Object> map = new HashMap<>();
    map.put("count", count++);
    map.put("data", mm);
    return   map;
}

相当于在 kibana 中 DSL 进行查询

GET /order_master/_search
{
  "query": {
    "match_all": {}
  },
   "aggs": {
    "channelAgg": {
      "terms": {
        "field": "orderChannel",
        "size": 10
      },
      "aggs" :{
          "priceAvg":{
            "avg": {
              "field": "goodsPrice"
            }
          },
          "countSum":{
            "sum": {
              "field": "goodsCount"
            }
          }
      }
    }
  
  }
}

得到结果, total 为 6000

{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 6000,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {...}
    ]
  },
  "aggregations" : {
    "channelAgg" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : 1,
          "doc_count" : 2030,
          "priceAvg" : {
            "value" : 3151.7016502463052
          },
          "countSum" : {
            "value" : 11065.0
          }
        },
        {
          "key" : 3,
          "doc_count" : 2013,
          "priceAvg" : {
            "value" : 3227.9908792846495
          },
          "countSum" : {
            "value" : 10988.0
          }
        },
        {
          "key" : 2,
          "doc_count" : 1957,
          "priceAvg" : {
            "value" : 3295.849085334696
          },
          "countSum" : {
            "value" : 10725.0
          }
        }
      ]
    }
  }
}

4.3.聚合查询三:时间分组聚合

按 day 日进行分组, 统计每个用户的订单

    /**
     * 聚合查询 : 按天 day 统计, 每个用户的订单数量
     *
     * @return
     * @throws IOException
     */
    @RequestMapping("/orderMaster/getDocumentByDay")
    public Map<String, Object> getDocumentByDay( ) throws IOException {

        SearchRequest request = new SearchRequest();
        request.indices("order_master");

        SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();

        BoolQueryBuilder boolQuery = QueryBuilders.boolQuery();
        searchSourceBuilder.query(boolQuery);

        // 时间函数 聚合
        DateHistogramAggregationBuilder groupByDayAgg = AggregationBuilders.dateHistogram("group_by_day");
        groupByDayAgg.field("createDate");
        groupByDayAgg.calendarInterval(DateHistogramInterval.DAY);
        groupByDayAgg.timeZone(ZoneId.of("Asia/Shanghai"));
        groupByDayAgg.format("yyyy-MM-dd");
        groupByDayAgg.minDocCount(0);
        groupByDayAgg.extendedBounds(new ExtendedBounds("now-7d/d","now"));  // 查询的时间 范围

        groupByDayAgg.subAggregation(AggregationBuilders.terms("group_by_cusIds").field("cusId").size(10));;
        
        searchSourceBuilder.aggregation(groupByDayAgg);

        request.source(searchSourceBuilder);

        // 查询 得到结果
        SearchResponse searchResponse = client.search(request, RequestOptions.DEFAULT);

        Map<String, Object> mm = new HashMap<>();

        // 分析聚合结果
        ParsedDateHistogram groupByDay = searchResponse.getAggregations().get("group_by_day");
        System.out.println("groupByDay = " + groupByDay);

        List<Map<String, Object>> keys = new ArrayList<>();
        for (Histogram.Bucket bucket : groupByDay.getBuckets()) {
            Map<String, Object> m = new HashMap<>();
            m.put("keyName", bucket.getKeyAsString());
            m.put("countNumber", bucket.getDocCount());

            ParsedLongTerms groupByUserIds   = bucket.getAggregations().get("group_by_cusIds");
            List<Map<String, Object>> keys2 = new ArrayList<>();
            for (Terms.Bucket userIdBucket : groupByUserIds.getBuckets()) {
                Map<String, Object> m2 = new HashMap<>();
                m2.put("keyName", userIdBucket.getKeyAsString());
                m2.put("countNumber", userIdBucket.getDocCount());
                keys2.add(m2);
            }
            m.put("groupByUserIds", keys2);

            keys.add(m);
        }

        mm.put("group_by_day", keys);

        Map<String, Object> map = new HashMap<>();
        map.put("data", mm);
        return   map;
    }

相当于在 kibana 中 DSL 进行查询

GET /order_master/_search
{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "group_by_grabTime": {
      "date_histogram": {
        "field": "createDate",
        "calendar_interval": "day",
        "time_zone": "+08:00",
        "format": "yyyy-MM-dd",
        "min_doc_count": 0,
        "extended_bounds": {
          "min": "now-7d/d",
          "max": "now"
        }
      },
      "aggs": {
        "group_by_cusIds": {
          "terms": {
            "field": "cusId"
          }
        }
      }
    }
  }
}

注释说明版本, 但在 kibana中没有办法执行

GET /order_master/_search
{
  "query": {
    "match_all": {}
  },
  // 聚合
  "aggs": {
    // 自己取的聚合名字
    "group_by_day": {
      // es提供的时间处理函数
      "date_histogram": {
        // 需要聚合分组的字段名称, 类型需要为date, 格式没有要求
        "field": "createDate",
        // 按什么时间段聚合,
        "calendar_interval": "day",
        // 设置时区, 这样就相当于东八区的时间  "Asia/Shanghai"
        "time_zone": "+08:00",
        // 返回值格式化，HH大写，不然不能区分上午、下午
        "format": "yyyy-MM-dd",
        // 为空的话则填充0
        "min_doc_count": 0,
        // 需要填充0的范围, 这里使用时间戳
        "extended_bounds": {
          "min": 1663570845000,
          "max": 1664175645000
        }
      },
      // 聚合
      "aggs": {
        // 自己取的名称
        "group_by_cusIds": {
          // es提供
          "terms": {
            // 聚合字段名
            "field": "cusId"
          }
        }
      }
    }
  }
}

得到结果, total 为 6000

{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 6000,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {...}
    ]
  },
  "aggregations" : {
    "group_by_grabTime" : {
      "buckets" : [
        {
          "key_as_string" : "2023-03-31",
          "key" : 1680192000000,
          "doc_count" : 846,
          "group_by_userIds" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        },
        {
          "key_as_string" : "2023-04-01",
          "key" : 1680278400000,
          "doc_count" : 835,
          "group_by_userIds" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        },
        {
          "key_as_string" : "2023-04-02",
          "key" : 1680364800000,
          "doc_count" : 864,
          "group_by_userIds" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        },
        {
          "key_as_string" : "2023-04-03",
          "key" : 1680451200000,
          "doc_count" : 848,
          "group_by_userIds" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        },
        {
          "key_as_string" : "2023-04-04",
          "key" : 1680537600000,
          "doc_count" : 860,
          "group_by_userIds" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        },
        {
          "key_as_string" : "2023-04-05",
          "key" : 1680624000000,
          "doc_count" : 878,
          "group_by_userIds" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        },
        {
          "key_as_string" : "2023-04-06",
          "key" : 1680710400000,
          "doc_count" : 869,
          "group_by_userIds" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        },
        {
          "key_as_string" : "2023-04-07",
          "key" : 1680796800000,
          "doc_count" : 0,
          "group_by_userIds" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      ]
    }
  }
}

春哥的魔法书

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
ElasticSearch ( 九 ) 订单操作实例

3 now-1h/d : now以毫秒为单位减一小时，向下舍入到天，解析为: 2001-01-01 00:00:00。4 2001.02.01||+1M/d : 2001-02-01以毫秒为单位加一个月，向下舍入到天，解析为: 2001-03-01 00:00:00。2 now-1h : now以毫秒为单位减一小时. 解析为: 2001-01-01 11:00:00。
复制链接

扫一扫

专栏目录