ElasticSearch与Mysql对比(ElasticSearch常用方法大全，持续更新)

最新推荐文章于 2024-03-06 11:32:44 发布

追逐梦想永不停

最新推荐文章于 2024-03-06 11:32:44 发布

阅读量1.3k

点赞数 1

分类专栏： 2020.4—— Java进阶文章标签： ElasticSearch ES Java sql

本文链接：https://blog.csdn.net/BHSZZY/article/details/118079269

版权

2020.4—— 同时被 2 个专栏收录

100 篇文章 2 订阅

订阅专栏

Java进阶

44 篇文章 3 订阅

订阅专栏

ElasticSearch是一个开源的搜索引擎，它可以被下面这样准确的形容：

一个分布式的实时文档存储，每个字段可以被索引与搜索
一个分布式实时分析搜索引擎
能胜任上百个服务节点的扩展，并支持 PB 级别的结构化或者非结构化数据

个人理解：

ES的特点就是搜索快；

插入ES的每一条数据可以理解为一个json报文，每个json报文都有一个唯一的id，每次插入就是新增一条json入库；

如果插入时不指定id，则id会自动生成一个新的；

如果插入时指定id，则使用指定的id，并会覆盖之前使用这个id的数据。

ES插入数据前，需要创建索引，ES插入数据时，必须指定索引名；

如果索引存在，则会把数据插入指定索引；

如果索引不存在，则会自动新建一个索引，索引字段为json数据中的字段名。

个人理解，索引就类似数据库中的表。

在此通过对比的方式总结下ES常用方法，从创建索引(表)、插入数据(insert)、删除数据(delete)、修改数据(update)、查询数据(select)等几方面总结。

特别说明：文章所有内容基于ElasticSerch 7.6.2版本；

ES代码样例是在Kibana的Dev Tools中运行的，或者是在Java中运行的。

一、创建索引(表)

1.ES创建索引

ES创建索引，个人感觉就像sql创建数据库中的表。

下方创建一个名称叫exam_data的索引，properties中的为映射(列)名。

ES的创建索引语句例如(POST请求)：

PUT exam_data
{
  "settings":{
    "index":{
      "number_of_shards": "1",
      "number_of_replicas": "1"
    }
  },
  
  "mappings": {
    "properties": {
      "userId":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},
      "startTime": {"type":"long"},
      "commitTime": {"type":"long"},
      "duration": {"type":"double"},
      "examStartTime":{"type":"long"},
      "examEndTime":{"type":"long"},
      "examId":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},
      "examName":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},
      "examStyleId":{"type":"long"},
      "examStyleName":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},
      "isPass":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},
      "passScore": {"type":"double"},
      "score": {"type":"double"},
      "times": {"type":"long"},
      "examCreater":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},
      "examCreateTime": {"type":"long"},
      "company":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},
      "com":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},
      "branch":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},
      "brc":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},
      "aracde":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},
      "ara":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},
      "chnl":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},
      "sequence":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},
      "memo":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}}
    }
  }
}

说明：

这个ES索引(ES表)是用来记录用户考试成绩等相关信息的，用户每完成一次考试，后台就会推到ES一条数据。

字段说明：


userId:用户id

startTime:开始考试时间

commitTime:结束考试时间

duration:考试用时

examStartTime:考试开始时间(可能持续一周)

examEndTime:考试结束时间(可能持续一周)

examId:考试id

examName:考试名称

examStyleId:考试规则id（网段限制等）

examSytleName:考试规则名称（网段限制等）

isPass:是否通过考试

passScore:考试通过所需分数

score:考试得分

times:考试次数

examCreater:考试创建人

examCreateTime:考试创建时间

company:一级公司id

com:一级公司名称

branch:二级公司id

brc:二级公司名称

aracde:三级公司id

ara:三级公司名称

chnl:渠道

sequence:队列id（全局唯一id，每一条考试记录有一个id）

memo:备注

2.对应的建表sql，这里写一个简单的例子。(与上方不完全对应，主要记录下大概用法)

create table 'exam_data' (
'id' bigint(20) unsigned not null auto increment,
'sequence' bigint(20) unsigned not null comment '队列id',
'userName' varchar(60) character set utf8mb4 collate utf8mb4_general_ci null default null comment '用户名',
'type' tinyint(3) unsigned null default null,
'start_time' datetime null default null comment '开始时间',
'end_time' datetime null default null comment '结束时间',

'create_time' datetime null default current_timestamp comment '创建时间，自动更新',
'update_time' timestamp null default current_timestamp on update current_timestamp comment '更新时间，自动更新',

primary key ('id'),
index 'sequence' ('sequence') using btree
)
engine=InnoDB
default character set=ut8mb4 collate=utf8mb4_general_ci
comment='考试记录表'
auto_increment=12398
row_format=dynamic
;

备注：

create_time与update_time是自动更新的，插入数据时可以不用管这两个字段；

当数据要记录这两个时间时，比较方便；甚至可以在旧表中直接新增这两个字段，而不用管代码逻辑。

二、索引结构的修改

1.增加索引字段

(1)sql

alter table exam_data add abc varchar(256) default '123'

(2)ES

下方代码在exam_data索引中增加了一个字段abc，并设置了keyword属性。

PUT exam_data/_mappings
{
  "properties": {
    "abc": {
      "type": "text",
      "fields": {
         "keyword": {
            "type": "keyword",
            "ignore_above": 256
         }
      }
    }
  }
}

2.删除索引字段

(1)sql

alter table exam_data drop column abc

(2)ES

好像不能，需要新建一个索引，然后把数据导过去

3.修改索引字段

(1)sql

语法是有，但是自己测试发现报错：

ALTER TABLE exam_data ALTER COLUMN abc varchar(233)

(2)ES

好像不能，需要新建一个索引，然后把数据导过去

4.重新创建索引、导入旧数据的具体流程

参考：https://www.cnblogs.com/Rawls/p/10300639.html

https://www.cnblogs.com/huangxiufen/p/12461191.html

涉及到3个索引，待修改结构的旧索引exam_data、中间索引exam_data_1、新索引exam_data

(1)把旧索引连同结构与数据复制到中间索引(相当于改名字)

POST _reindex
{
   "source": {
     "index": "exam_data"
   },
   "dest": {
     "index": "exam_data_1"
   }
}

(2)删除旧索引

delete exam_data

(3)执行创建新索引exam_data的语句

(4)从中间索引把数据导入新索引

POST _reindex
{
   "source": {
     "index": "exam_data_1"
   },
   "dest": {
     "index": "exam_data"
   }
}

(5)删除中间索引

delete exam_data_1

三、插入数据

1.ES插入数据样例

例如插入数据到索引exam_data中。

(1)使用put请求，根据索引名与id插入数据

PUT exam_data/_doc/1
{
  "id123":"123",
  "examId":"abc",
  "score":"100"
}

说明：

exam_data是索引名(表名),_doc是固定写法,1是指定这条json入库后的id为1；

这条json的字段可以不按照索引中设置的映射字段，例如id123并不在创建索引时索引的映射字段(列)中，也不影响之后根据id123搜索出该条数据；

不过这条json会算在索引exam_data下，当删除该索引时，这条json也会被删除。

(2)使用post请求，根据索引名插入数据

POST exam_data/_doc
{
  "id123":"123",
  "examId":"abc",
  "score":"100"
}

说明：

使用post请求插入数据，不用指定id，插入后会自动生成一个唯一id。

2.sql插入数据样例，简单对比用

insert into mytable(id,name) values('1','abc')

3.Java使用ES插入数据样例

(1)插入数据操作

//注入esclient对象
@Autowired
@Qualifier("restHighLevelClient")
private RestHighLevelClient esClient;

public void insertTOES() {
  //待插入数据
  String data = "{\"id\":\"abc\",\"name\":\"abc\"}";
  //这里指定ES的索引名
  IndexRequest request = new IndexRequest("exam_data");
  //这里指定插入ES的数据的id，也可以不写
  //request.id("1");
  //填充数据
  request.source(data,XContentType.JSON);
  
  //发送请求并获取返回值，esclient是注入的javabean，见下个代码块的创建代码
  IndexResponse index = esclient.index(request,RequestOptions.DEFAULT);
  //如果插入成功，会返回id；如果指定了id，则值与上方指定的id相同
  System.out.println(index.getId());
}

(2)esclient对象初始化方法

@Configuration
public class ElasticsearchRestClientConfig {
  
  //从yml中获取配置信息
  @Value("${es.host}")
  private String host;
  @Value("${es.port}")
  private String port;
  @Value("${es.connTimeout}")
  private String connTimeout;
  @Value("${es.socketTimeout}")
  private String socketTimeout;
  @Value("${es.connectionRequestTimeout}")
  private String connectionRequestTimeout;
  @Value("${es.username}")
  private String username;
  @Value("${es.password}")
  private String password;

  //把这个bean转入spring容器，之后就能@Autowired获取到了
  @Bean
  public RestHighLevelClient restHighLevelClient(){
    BasicCredentialsProvider credentialsProvider = new BasicCredentialsProvider();
    credentialsProvider.setCredentials(AuthScope.ANY,new UsernamePasswordCredentials(username,password));

    RestClientBuilder builder = RestClient.builder(new HttpHost(host,port))
      .setRequestConfigCallback(requestconfigBuilder -> requestConfigBuilder
        .setConnectTimeout(connTimeout)
        .setSocketTimeout(socketTimeout)
        .setConnectionRequestTimeout(connectionRequestTimeout)
      );
    return new RestHighLevelClient(builder);
  }
}

(3)相关application.yml

#es相关参数，名称不固定，自己用的
es: 
  host: 10.123.123.123
  port: 9200
  scheme: http
  connTimeout: 5000000
  socketTimeout: 3000000
  connectionRequestTimeout: 500
  #用户名与密码，ES允许不设置用户名密码登录，不过不安全；所以最好还是设置一个
  username: abc
  password: abc

四、删除数据

1.ES删除数据

(1)根据查询条件删除

POST exam_data/_delete_by_query
{
  "query": {
    "term": {
      "examId": "abc"
    }
  }
}

说明：

exam_data是索引名，_delete_by_query是固定写法；

该语句删除所有examId等于abc的数据。

(2)根据id删除

DELETE exam_data/_doc/1

该语句根据每条数据的唯一id删除数据。

删除了id为1的数据。

2.sql删除数据样例

delete from exam_data where id = '1'

五、修改数据

1.ES修改数据

(1)先找到目标数据的id，然后新增一条相同id的数据，即可覆盖掉旧数据

(2)先找到目标数据的id，然后进行修改

POST exam_data/_update/1
{
  "doc": {
    "examId":"abcdefg"
  }
}

这个语句将id为1的数据的examId修改为abcdefg.

(3)按照查询条件进行修改

POST exam_data/_update_by_query
{
  "query":{
    "term":{
      "examId":"abcdefg"
    }
  },
  "script":{
    "source": "ctx._source['concact']=\"ccc\""
  }
}

这个语句，将把examId为abcdefg的所有数据的concact字段修改为ccc；如果没有concact字段，则会新增该字段并设置为ccc；

_update_by_query为固定写法(更新by查询)，ctx._source为固定写法(指向当前要被更新的文档)。

2.sql修改数据

update exam_data e set e.concact = 'ccc' where e.examId = 'abcdefg'

六、查询数据

1.ES查询数据

GET exam_data/_search
{
  "query":{
    "bool": {
      "must" : [
        {
          "term": {
             "examId.keyword": {
                "value": "abcdefg"
             }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  }
}

这个语句可以匹配到所有examId为abcdefg的数据，返回结果默认返回10条(设置方法不在此处讲解)，返回的hits字段是匹配数量，可以知道究竟有多少条数据符合此条件。

2.sql查询数据

select * from exam_data where examId = 'abcdefg'

七、ES复杂场景查询样例

首先，用到了自己封装的2个公共方法，如下：

●CommonUtil.getCommonBoolQueryBuilder()，这个方法是获取查询条件对象BoolQueryBuilder用的；其中有一些设置条件的方法，可以参考。

public static BoolQueryBuilder getCommonBoolQueryBuilder(RestHighLevelClient esclient, BoolSearchVo data, String startTime, String endTime) throws Exception{
  //所有查询条件都设置在这个bean中
  BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();

  //设置查询考试时长小于2小时的数据；大于2小时的舍弃，为错误数据
  //数据已转为分钟，所以查询duration小于120的
  boolQueryBuilder.must(QueryBuilders.rangeQuery("duration").lt(2*60));
  
  //根据考试名称或ID，模糊查询，如果有重复，则选取最新的一场考试的数据
  String examNameOrId = data.getExamNameOrId();
  if(StringUtils.isNotEmpty(examNameOrId)){
    //先根据考试名称或ID，模糊查询出最新一场考试的ID
    //getLatestByIdOrName在下面的代码块中，可以页面搜索找到
    String examId = getLatestByIdOrName(esclient, examNameOrId);
    if(StringUtils.isNotEmpty(examId)){
      boolQueryBuilder.must(QueryBuilders.termQuery("examId.keyword",examId));
    }else{
      boolQueryBuilder.must(QueryBuilders.termQuery("examId.keyword",examNameOrId));
    }
  }

  //根据机构填写查询条件
  //机构有3级,用_分割,多选时逗号拼接,机构可能的形式为
  //1,2_1,3_4_5
  String instituteId = data.getInstituteId();
  if(StringUtils.isNotEmpty(instituteId)){
    String instituteIds = instituteId.split(",");
    //这个if也许不用写,多余了...
    if(instituteIds.length == 1){
      if(instituteIds.split("_").length == 1){
        boolQueryBuilder.must(QueryBuilders.termQuery("company.keyword",instituteId));
      }else if(instituteIds.split("_").length == 2){
        String company = instituteIds.split("_")[0];
        String branch = instituteIds.split("_")[1];
        boolQueryBuilder.must(QueryBuilders.termQuery("company.keyword",company));
        boolQueryBuilder.must(QueryBuilders.termQuery("branch.keyword",branch));
      }else if(instituteIds.split("_").length == 3){
        //本来应该是拆开查询company,branch,aracde的
        //不过数据中有一个字段是这样类型的，如3_4_5
        //如果某些人只属于一级公司或二级公司,会是1_*_*,*_*_*
        boolQueryBuilder.must(QueryBuilders.termQuery("instituteId.keyword",instituteId));
      }
    }
    else{
      //如果是多选查询，用bool嵌套的方法
      //注意，必须嵌套，直接用should查的结果有问题，不行
      BoolQueryBuilder boolShould = QueryBuilders.boolQuery();
      //1,a_1,_b_1_2
      for(int i=0; i<instituteIds.length; i++){
        //company,branch,aracde
        String[] cba = instituteIds[i].split("_");
        if(cba.length == 1 || cba.length == 2){
          //ES的正则，查询开头为某格式的，例如开头为1_的或2_3_的
          boolShould.should(QueryBuilders.wildcardQuery("instituteId.keyword",instituteIds[i]+"_*"));
        }
        else if(cba.length == 3){
          boolShould.should(QueryBuilders.termQuery("instituteId.keyword",instituteIds[i]));
        }
      }
      //放入查询bean,实现嵌套bool
      boolQeuryBuilder.must(boolShould);
    }
  }

  //根据渠道筛选，除了机构，考生还可能属于某个渠道
  //渠道为1-9,a-z,单个字符,逗号拼接
  //1,2,a,b,3
  //使用filter与termsQuery实现
  String chnl = data.getChnl();
  if(StringUtils.isNotEmpty(chnl)){
    String[] chnls=chnl.spilt(",");
    boolQueryBuilder.filter(QueryBuilders.termsQuery("chnl.keyword",chnls));
  }

  //根据考生提交日期筛选
  if(StringUtils.isNotEmpty(startTime) && StringUtils.isNotEmpty(endTime) {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    long st = sdf.parse(startTime).getTme();
    //结束日期是当日0时0分0秒，因此要加成23时59分59秒
    long et = sdf.parse(endTime).getTime()+(24*60*60*1000-1);
    
    boolQueryBuilder.must(QueryBuilders.rangeQuery("commitTime").from(st).to(et));
  }

  return boolQueryBuilder;
}

●CommonUtil.getSearchResponse()，用来获取返回结果：

//公用搜索方法
public static SearchResponse getSearchResponse(RestHighLevelClient esclient, BoolQueryBuilder boolQueryBuilder, AggregationBuilder aggregationBulder) throws Exception {
  //要存入的索引名(可以理解为表名)
  SearchRequest request = new SearchRequest("exam_data");
  
  SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
  //放入查询条件对象，这个一般都有值，因此没有判空
  searchSourceBuilder.query(boolQueryBuilder);
  //放入聚合函数，进行了判空
  if(aggregationBuilder != null) {
    searchSourceBuilder.aggregation(aggregationBuilder);
  }
  
  request.source(searchSourceBuilder);

  //这个日志中打印出来的语句可以放到kibana运行
  log.debug("ES请求报文："+ request.toString());

  SearchResponse response = esclient.search(request, RequestOptions.DEFAULT);

  //这个可以看到ES返回的json报文
  log.debug("ES响应："+ response.toString());

  return response;
}

1.根据id或名称，模糊查询数据，如果有多条重复，选取最新的一条。

分两步，先模糊查询，获得最新的id，然后根据此id获取其余数据。(ES一步到位较难，因此使用这个方法)

ES使用嵌套bool、aggregations聚合实现。

(1)对应sql：

select id from exam_data where (examName like '%考试%' or examId like "%考试%") order by create_time desc limit 1

select * from exam_data where id = '查询得到的id'

(2)对应ES：分两步，第一步，根据传入的值模糊查询，得到最新一条的id。

GET exam_data/_search
{
  "query": {
    "bool": {
      "must": [
        "bool": {
          "should": [
             {
               "match_phrase_prefix":{
                  "examName": {
                     "query": "考试",
                     "slop": 0,
                     "max_expansions": 50,
                     "boost": 1
                  }
               }
             },
             {
               "match_phrase_prefix":{
                  "examId": {
                     "query": "考试",
                     "slop": 0,
                     "max_expansions": 50,
                     "boost": 1
                  }
               }
             }
          ],
          "adjust_pure_negative": true,
          "boost": 1
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  },
  "aggregations": {
     "examStartTime": {
       "terms": {
         "field": "examStartTime",
         "size": 1,
         "min_doc_count": 1,
         "shard_min_doc_count": 0,
         "show_term_doc_count_error": false,
         "order":{
            "_key": "desc"
         }
       },
       "aggregations": {
          "examId": {
            "terms": {
              "field":"examId.keyword",
              "size":10,
              "min_doc_count": 1,
              "shard_min_doc_count": 0,
              "show_term_doc_count_error": false,
              "order":[
                {
                  "_count": "desc"
                },
                {
                  "_key": "asc"
                }
              ]
            }
          }
       }

     }
  }
}

说明：

只用2个should不能实现需求，必须使用bool嵌套should才行，如上方使用了2个bool。

order中的_key:desc可以按key的降序排序，例如examStartTime(long)的降序；再有size:1，就是最大的了。

第一次聚合是为了按时间聚合、降序、得到时间最新的数据；第二次聚合是为了得到examId；只用第一次聚合的话，ES返回的内容中只有examStartTime和聚合数，得不到examId，所以要用第二次聚合。

ES第二步，根据id查询目标数据，如果为空也没关系，查询到0条就可以：

GET exam_data/_search
{
  "query": {
    "bool": {
      "must": [
        {
           "term": {
             "examId.keyword":{
                 "value": "得到的id",
                 "boost": 1
             }
           }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  },
  "aggregations": {
          "zrs": {
            "cardinality": {
              "field":"userId.keyword"
           }
         }
   }
}

说明：ES中，如果要获取某条数据的某个信息，最好还是使用聚合aggregations，聚合某字段，然后从桶里找值，而不是从hits中找，因为hits返回的有时候不准确。

上方的语句，筛选条件为某个指定的examId，然后用cardinality对结果的userId去重，可以从返回的结果桶中得到考试人数。

(3)对应java，功能为根据考试id或考试名称，获取到最新的考试的考试人数：

先获取最新的考试ID：

//获取最新的考试ID
private static String getLatestIdByIdOrName(String examNameOrId, RestHighLevelClient esclient) throws Exception{

String examNameOrId = "考试";

//模糊查询ES，获取最新的考试id
String[] result = new String[1];
BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
BoolQueryBuilder boolShould = QueryBuilders.boolQuery();
boolShould.should(QueryBuilders.matchPhrasePrefixQuery("examName",examNameOrId));
boolShould.should(QueryBuilders.matchPhrasePrefixQuery("examId",examNameOrId));
boolQueryBuilder.must(boolShould);

//聚合，按时间逆序，选1条就够
TermsAggregationBuilder termsTime = AggregationBuilder.terms("examStartTime").field("examStartTime").order(InternalOrder.key(false).size(1));
//聚合，用来获取examId的聚合
TermsAggregationBuilder termsId = AggregationBuilder.terms("examId").field("examId.keyword");
termsTime.subAggregation(termsId);

//从结果找到examId, getSearchResponse是自己封装的方法，见下
SearchResponse searchResponse = getSearchResponse(esclient, boolQueryBuilder, termsTime);
ParsedLongTerms parsedExamStartTime = searchResponse.getAggregations().get("examStartTime");
parsedExamStartTime.getBuckets().forEach( bucket -> {
  ParsedLongTerms parseExamId = bucket.getAggregations().get("examId");
  parseExamId.getBuckets().forEach( bucket2 -> {
    //因为设置了size为1，所以正常情况下只循环一次
    result[0] = bucket2.getKeyAsString();
  });
});
return result[0];
}

然后根据id，按userId聚合，查询最新一场考试的考试人数：

//返回最新一场考试的考试总人数
public String searchZrs(BoolSearchVo data) throws Exception {
  //使用cardinality按userId去重
  //zrs是自己起的桶名
  //userId是数据的字段，需要加keyword
  CardinalityAggregationBuilder aggregationBuilder = AggregationBuilders.cardinality("zrs").field("userId.keyword");
  //获取公共查询的参数
  BoolQueryBuilder bqb = CommonUtil.getCommonBoolQueryBuilder(esclient, data, data.getStartTime(), data.getEndTime());
  //获取返回结果
  SearchResponse searchResponse = CommonUtil.getSearchResponse(esclient, bqb, aggregationBuilder);

  //获取总人数
  Cardinality valueCount = searchResponse.getAggregations().get("zrs");
  String count = String.valueOf(valueCount.getValue());

  return count;
}

2.查询某个数字字段的和

(1)sql样例

select sum(a.duration) from exam_data a where DATE_FORMAT(a.commit_time,'%Y-%m-%d') between '2021-01-01' and '2021-02-01'

(2)ES样例，使用聚合sum

GET exam_data/_search
{
  "query": {
    "bool": {
      "must": [
        {
           "range": {
             "commitTime":{
                 "from": "1624896000000",
                 "to": "1624996000000",
                 "include_lower":true,
                 "include_upper":true,
                 "boost": 1
             }
           }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  },
  "aggregations": {
          "kssc": {
            "sum": {
              "field":"duration"
           }
         }
   }
}

(3)java样例

//考试时长
public String searchkssc(BoolSearchVo data) throws Exception {
  //聚合sum
  SumAggregationBuilder aggregationBuilder = AggregationBuilders.sum("kssc").field("duration");
  //获取公共查询的参数
  BoolQueryBuilder bqb = CommonUtil.getCommonBoolQueryBuilder(esclient, data, data.getStartTime(), data.getEndTime());
  //获取返回结果
  SearchResponse searchResponse = CommonUtil.getSearchResponse(esclient, bqb, aggregationBuilder);

  //获取考试时长
  Sum valueCount = searchResponse.getAggregations().get("kssc");
  //从分钟转为小时
  double value = valueCount.getValue();
  //保留2位小数，四舍五入
  String duration = new BigDecimal(vaule/60).setScale(2,RoundingMode.HALF_UP).toString();

  return duration;
}

3.按某个字段排序、查询前三名数据

具体功能：按company分组，查询考试次数，取前三名；并查询对应的考试人数(按userId去重后的数量)。

(1)sql样例：

按company分组，查询考试次数，取前三名：

select company,count(*) from exam_data group by company order by count(*) desc limit 0,3

limit 3或limit 0,3

得到company后，再查询对应的考试人数(按userId去重)

select count(*) from exam_data group by user_id where company = 'A';
select count(*) from exam_data group by user_id where company = 'B';
select count(*) from exam_data group by user_id where company = 'C';

然后就能得到A,B,C的考试次数与考试人数。

(2)ES样例：

注意，与sql查询的顺序不太一样，一个ES就够：

按company分组，按桶的数量逆序取前3名，得到了前三名的company，在第一层桶的key中；

然后从第一层桶的doc_count中，就能获得考试次数；

然后按userId分组，是第二层桶，获得第二层桶buckets[]数组的长度，就是考试人数(相当于按userId去重了)。

GET exam_data/_search
{
  "query": {
    "bool": {
      "must": [
        {
           "range": {
             "commitTime":{
                 "from": "1624896000000",
                 "to": "1624996000000",
                 "include_lower":true,
                 "include_upper":true,
                 "boost": 1
             }
           }
        }
      ],
      "must_not": [
        {
          "term": {
            "company.keyword" : {
              "value": "*",
              "boost": 1
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  },
  "aggregations": {
          "fgsGroup": {
            "terms": {
              "field":"company.keyword",
              "size": 3,
              "min_doc_count": 1,
              "shard_min_doc_count": 0,
              "show_term_doc_count_error": false,
              "order": [
                {
                  "_count": "desc"
                },{
                  "_key": "asc"
                }
              ]
           },
           "aggregations": {
             "userIdGroup": {
                "terms": {
                  "field":"userId.keyword",
                  "size": 10,
                  "min_doc_count": 1,
                  "shard_min_doc_count": 0,
                  "show_term_doc_count_error": false,
                  "order": [
                    {
                      "_count": "desc"
                    },{
                      "_key": "asc"
                    }
                  ]
               }
             }
           }

         }
   }
}

(3)java样例：

//考试排行，返回考试次数前三名list，每个list的bean中有公司名、考试次数、考试人数
public ArrayList<RankVo> searchkszph(BoolSearchVo data) throws Exception {
  //首先根据分公司分组，取前三名，就是按考试次数排名的前三名
  TermsAggregationBuilder tb1 = AggregationBuilders.terms("fgsGroup").field("company.keyword").order(InternalOrder.count(false)).size(3);
  //然后按userId分组
  TermsAggregationBuilder tb2 = AggregationBuilders.terms("userIdGroup").field("userId.keyword");
  //tb2为tb1的子桶
  tb1.subAggregation(tb2);
  //获取公共查询的参数
  BoolQueryBuilder bqb = CommonUtil.getCommonBoolQueryBuilder(esclient, data, data.getStartTime(), data.getEndTime());
  //排除分公司为*的数据
  bqb.mustNot(QueryBuilders.termQuery("company.keyword","*"));

  //获取返回结果
  SearchResponse searchResponse = CommonUtil.getSearchResponse(esclient, bqb, tb1);

  ArrayList<RankVo> returnList = new ArrayList<>();

  //以下省略，从searchResponse中遍历桶获得结果即可
  //可以打断点查看返回的string是什么样的
  //第一层桶buckets[]的key是company
  //第一层桶buckets[]的doc_count是考试次数
  //第二层桶buckets[]的数组长度是考试人数(人数的意思就是次数按userId去重)
  
  return returnList;
}

4.filter与terms实现匹配多个条件

例如，匹配渠道chnl是1或2或3的

(1)sql样例

select count(*) from exam_data where chnl in ('1','2','3')

(2)ES样例

GET exam_data/_search
{
  "query": {
    "bool": {
      "filter": [
        {
           "terms": {
             "chnl.keyword":[
                 "1",
                 "2",
                 "3"
             ],
             "boost": 1
           }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  }

}

(3)java样例(详情见上方类似的代码块)：

  //根据渠道筛选，除了机构，考生还可能属于某个渠道
  //渠道为1-9,a-z,单个字符,逗号拼接
  //1,2,a,b,3

  //使用filter与termsQuery实现,注意是terms Query

  String chnl = data.getChnl();
  if(StringUtils.isNotEmpty(chnl)){
    String[] chnls=chnl.spilt(",");
    boolQueryBuilder.filter(QueryBuilders.termsQuery("chnl.keyword",chnls));
  }

5.bool嵌套实现匹配多个条件

例如，查询

(company是a的) 或 (company是b、branch是1的) 或 (company是c、branch是2、aracde是3的)

这样一组数据。

(1)sql样例

select * from exam_data where  (company in (a,b,c)) or 
( concat(company,'_',branch) in ('d_1','e_2') ) or
( concat(company,'_',branch,'_',aracde) in ('f_2_3','g_4_5') )

(2)ES样例

说明：

注意ES的wildcard正则表达式与普通的不太一样；

a_*的意思是匹配以a_开头的所有数据；

b_1_*是匹配以b_1_开头的所有数据；

term就是匹配完全相等的数据了；

报文结构：bool->must->bool->should，即为bool嵌套。

可以匹配company为

GET exam_data/_search
{
  "query": {
    "bool": {
      "must": [
       {
        "bool": {
          "should": [
             {
               "wildcard":{
                 "instituteId.keyword": {
                    "wildcard": "a_*",
                    "boost": 1
                 }
               }
             },
             {
               "wildcard":{
                 "instituteId.keyword": {
                    "wildcard": "b_1_*",
                    "boost": 1
                 }
               }
             },
             {
               "term":{
                 "instituteId.keyword": {
                    "value": "E_3_5",
                    "boost": 1
                 }
               }
             },
             {
               "term":{
                 "instituteId.keyword": {
                    "value": "F_5_6",
                    "boost": 1
                 }
               }
             }
             
          ],
          "adjust_pure_negative": true,
          "boost": 1
         }
       }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  }

}

(3)java样例（详情见上方相似代码块）

//根据机构填写查询条件
  //机构有3级,用_分割,多选时逗号拼接,机构可能的形式为
  //1,2_1,3_4_5
  String instituteId = data.getInstituteId();
  if(StringUtils.isNotEmpty(instituteId)){
    String instituteIds = instituteId.split(",");
    //这个if也许不用写,多余了...
    if(instituteIds.length == 1){
      if(instituteIds.split("_").length == 1){
        boolQueryBuilder.must(QueryBuilders.termQuery("company.keyword",instituteId));
      }else if(instituteIds.split("_").length == 2){
        String company = instituteIds.split("_")[0];
        String branch = instituteIds.split("_")[1];
        boolQueryBuilder.must(QueryBuilders.termQuery("company.keyword",company));
        boolQueryBuilder.must(QueryBuilders.termQuery("branch.keyword",branch));
      }else if(instituteIds.split("_").length == 3){
        //本来应该是拆开查询company,branch,aracde的
        //不过数据中有一个字段是这样类型的，如3_4_5
        //如果某些人只属于一级公司或二级公司,会是1_*_*,*_*_*
        boolQueryBuilder.must(QueryBuilders.termQuery("instituteId.keyword",instituteId));
      }
    }
    else{
      //如果是多选查询，用bool嵌套的方法
      //注意，必须嵌套，直接用should查的结果有问题，不行
      BoolQueryBuilder boolShould = QueryBuilders.boolQuery();
      //1,a_1,_b_1_2
      for(int i=0; i<instituteIds.length; i++){
        //company,branch,aracde
        String[] cba = instituteIds[i].split("_");
        if(cba.length == 1 || cba.length == 2){
          //ES的正则，查询开头为某格式的，例如开头为1_的或2_3_的
          boolShould.should(QueryBuilders.wildcardQuery("instituteId.keyword",instituteIds[i]+"_*"));
        }
        else if(cba.length == 3){
          boolShould.should(QueryBuilders.termQuery("instituteId.keyword",instituteIds[i]));
        }
      }
      //放入查询bean,实现嵌套bool
      boolQeuryBuilder.must(boolShould);
    }
  }

6.聚合嵌套实现查询多个字段的值

例如查询公司ID company与公司中文名称com;

在ES中，一个聚合只能查询一个字段，因此要聚合嵌套，才能查询其余字段

(1)sql样例：

select company,com from exam_data group by company,com

(2)ES样例:

GET exam_data/_search
{
  "query": {
    "bool": {
      "must": [
        {
           "range": {
             "commitTime":{
                 "from": "1624896000000",
                 "to": "1624996000000",
                 "include_lower":true,
                 "include_upper":true,
                 "boost": 1
             }
           }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  },
  "aggregations": {
          "fgsGroup": {
            "terms": {
              "field":"company.keyword",
              "size": 99999,
              "min_doc_count": 1,
              "shard_min_doc_count": 0,
              "show_term_doc_count_error": false,
              "order": [
                {
                  "_count": "desc"
                },{
                  "_key": "asc"
                }
              ]
           },
           "aggregations": {
             "userIdGroup": {
                "terms": {
                  "field":"com.keyword",
                  "size": 99999,
                  "min_doc_count": 1,
                  "shard_min_doc_count": 0,
                  "show_term_doc_count_error": false,
                  "order": [
                    {
                      "_count": "desc"
                    },{
                      "_key": "asc"
                    }
                  ]
               }
             }
           }

         }
   }
}

这样得到的返回结果，是先按company聚合、第一个桶的key即是company的值；

然后按com聚合，第二个桶的key即是com的值；

因为一个company对应一个com，因此每个company桶中的子桶、com的数量正常情况下也是一个；

然后java中遍历、获取返回结果，就能得到这两个字段的值了。

(3)java样例：

  //准备获取company的值，首先根据company分组
  TermsAggregationBuilder tb1 = AggregationBuilders.terms("fgsGroup").field("company.keyword").order(InternalOrder.count(false)).size(3);
  //准备获取com的值，然后根据com分组
  TermsAggregationBuilder tb2 = AggregationBuilders.terms("userIdGroup").field("com.keyword");
  //tb2为tb1的子桶
  tb1.subAggregation(tb2);

  //返回结果遍历桶即可，可参考上方代码，此处省略
  ......

八、其它ES常用操作

1.清空数据及索引

(1)类似sql的drop table:

drop table exam_data

(2)清空数据及索引的ES命令如下：

delete 索引名称

2.只清空数据

(1)类似sql的delete命令，不加条件即是所有数据：

delete from exam_data

(2)ES样例：

POST /exam_data/_delete_by_query?pretty
{
    "query": {
        "match_all": {
        }
    }
}

3.复制索引及数据

(1)sql样例

CREATE TABLE 新表 SELECT * FROM 旧表

(2)ES样例

POST _reindex{
        "source": {
            "index": "exam_data" //待复制索引
        },
        "dest": {
            "index": "new_exam_data"//目标索引
        }
    }

4.仅复制索引

(1)sql样例

CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2

(2)ES样例，暂时没有找到；

下方的代码可以把_id为1的数据与索引结构复制到new_exam_data；

只有1条数据，复制完了后手动清空索引数据吧。

如果没有_id为1的数据，则复制失败，索引也不会被创建，所以应该选择一个存在的id。

POST _reindex{
        "source": {
            "index": "exam_data", //待复制索引
            "query": {
              "match": {
                 "_id": "1"
              }
            }
        },
        "dest": {
            "index": "new_exam_data"//目标索引
        }
    }

九、其它相关笔记，ES条件查询、ES聚合等样例

1.需要注意的点

●matchPhrasePrefixQuery查询text类型字段时，不能加keyword，否则会报错

●termQuery、termsQuery、wildcardQuery查询text类型字段时，应该加keyword，不加的话查询结果会不准确，会漏查部分数据

●rangeQuery查询时，不能加keyword，加上的话会查询不到数据

●创建索引时，如果是text类型的字段，最好加上keyword，如下：

"examId":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above": 256}}}

●查询或聚合text字段类型时，应该加keyword，不加的话返回结果会不准确

●查询或聚合非text字段类型时，不用加keyword

2.多条件查询三级公司的sql写法

●需求：公司有三个级别，company，branch，aracde

要求前端使用一个字段查询，可以多选，因此实际参数可能的类型是：

a,b_1,c_2_3

●误区：之前认为sql太复杂，因此使用了$进行sql拼接，结果被查出来sql注入的问题了；后来又想了想，还是可以用#实现的；也是在java中处理、然后sql中拼接字段。

看来，前端传入参数决定sql的时候，一定不能用$，必须想办法用#，否则会sql注入的。

●实现：

@Select({

  "<script>",
  "select commit_time as group_date, count(*) as exam_count  ",
  
  "where 1=1 ",
  "<if test='instituteId !=null and instituteId != \"\" and instituteId!=undefined '> ",
  "and ( ",
  "1=2 ",
  
  "<if test='instituteId1 !=null and instituteId1 != \"\" and instituteId1!=undefined '> ",
  "or e.company in ",
  "<foreach collection='instituteId1' item='iid1' open='(' separator=',' close=')' >",
  "#{iid1}",
  "</foreach>",
  "</if>", 

    "<if test='instituteId2 !=null and instituteId2 != \"\" and instituteId2!=undefined '> ",
  "or CONCAT(e.company,'_',e.branch) in ",
  "<foreach collection='instituteId2' item='iid2' open='(' separator=',' close=')' >",
  "#{iid2}",
  "</foreach>",
  "</if>", 

    "<if test='instituteId3 !=null and instituteId3 != \"\" and instituteId3!=undefined '> ",
  "or CONCAT(e.company,'_',e.branch,'_',e.aracde) in ",
  "<foreach collection='instituteId3' item='iid3' open='(' separator=',' close=')' >",
  "#{iid3}",
  "</foreach>",
  "</if>", 

  ")</if>",

  "<if test='flag == 1'>"
  "and CONCAT(e.company,'_',e.branch)!='A_60' ",
  "</if>",

  "group by DATE_FORMAT(e.commit_time,'%Y-%m-%d') ",
  "order by group_date ",
  "</script>"
})
@Results({
@Result(column = "group_date",property="date",jdbcType=JdbcType.VARCHAR),
@Result(column = "exam_count",property="value",jdbcType=JdbcType.VARCHAR)
})
List<Map> getExamCountByParamGroupByDay(Map map);

说明:

●1=1是为了之后拼接and不会报错

●1=2是为了之后拼接or不会报错

●test是一个=，test内的条件是==，或!=

●前端传来的instituteId可能是a,b_1,c_2_3，所以需要java中进行分类（java代码省略），没有_的放入instituteId1，有一个_的放入instituteId2，有2个_的放入instituteId3，再对应外部and、内部or、有concat的sql即可实现

●注意foreach用的参数需要是数组类型

3.ES的两种排序：

(1)按照桶中数据的数量排序，相当于group by后，按count排序，如下：

//根据company分组，取前三名，就是按考试次数排名的前三名
//count是桶中数据的数量，false是逆序
  TermsAggregationBuilder tb1 = AggregationBuilders.terms("fgsGroup").field("company.keyword").order(InternalOrder.count(false)).size(3);

(2)按桶字段值排序，例如时间字段，可以选择最新/旧的数据，如下：

//按时间逆序，获取最新一条数据;examStartTime是long类型，不加keyword
//false是逆序
  TermsAggregationBuilder tbTime = AggregationBuilders.terms("mytime").field("examStartTime").order(InternalOrder.key(false)).size(1);

4.ES查询返回数据不准确问题与解决方法

ES在使用terms聚合查询时，由于多分片取数据的关系，返回数据可能不准确。

因此，在对数据准确性要求高、对响应速度要求不高的情况下，可以调高请求报文中的shard_size与size参数，让返回数据更准确；

需要注意的是shard_size与size都需要调高，只调高一个是不够的。

ES代码如下(仅展示aggregations的terms部分)：

"aggregations": {
  "my_group_name": {
    "terms": {
      "field": "company.keyword",
      "size": 99999,
      "shard_size": 99999,
      "min_doc_count": 1,
      "shard_min_doc_count": 0,
      "show_term_doc_count_error": false,
      "order": [
        {
          "_count": "desc"
        },{
          "_key": "asc"
        }
      ]
    }
  }
}

java代码如下：

//递归设置terms聚合的size与shard_size
public static void useMaxSize(AggregationBuilder ab){
  if(ab != null) {
    if(ab instanceof TermsAggregationBuilder) {
      ab.size(Integer.MAX_VALUE);
      ab.shardSize(Integer.MAX_VALUE);
    }
    Collection<AggregationBuilder> subs = ab.getSubAggregations();
    for(AggregationBuilder subAb : subs) {
      useMaxSize(subAb);
    }
  }
}

十、开发中遇到的问题与解决方法

本次开发了一个数据统计系统(test-cockpit)，用到了ES；每个考生考完一次考试，就会把消息推送给ES；然后有一个前端页面可以展示按要求从ES查询到的数据，例如考试数、考试人数、考试次数、考试时长等。

遇到的问题与解决方法如下：

1.swagger，在后端使用这个jar包后，可以访问指定网页，得到后端现有的controller的url与出参入参，便于前端查看；

springboot项目中，默认访问的网页是：http://localhost:8080/swagger-ui.html

(或：http://localhost:8080/swagger/index.html)

2.kafka不用在控制台手动创建队列，直接使用即可；但是使用新队列时，需要生产者先向该队列发送一条消息，然后消费者才能启动成功；如果消费者先启动并监听新队列，会报错(队列不存在导致)：

nested exception is java.lang.IllegalStateException: Topic(s) [test-my_kafka_Topic] is/are not present and missingTopicFatal is true

如果必须先启动消费者，那要在yml的kafka配置中增加：

missing-topics-fatal: false

3.将父类bean转换为子类bean的方法

(1)利用json

String jsonStr = JSONObject.toJSONString(fatherBean);
ChildBean cb = JSONObject.parseObject(jsonStr, ChildBean.class);

(2)使用BeanUtils

BeanUtils.copyProperties(fatherBean, childBean);

4.输出流输出的数据不正确问题

使用输出流输出excel文件时，用到了ServletOutputStream，发现使用该对象输出时，必须使用3个参数的输出方法，输出内容才正确；不能使用1个参数的输出方法，输出的内容打不开。如下：

FileInputStream inputStream = null;
ServletOutputStream outputStream = null;

try{

//假设从硬盘读excel，准备返回给用户
inputStream = new FileInputStream("D://abc.xls");
int l;
//jar包中就是1024，所以这个没问题
byte[]i = new byte[1024];
//这个response是controller中的ServletHttpResponse
outputStream = response.getOutputStream();
while((l = inputStream.read(i)) != -1){
  //这里必须是三个参数，输出才正确；否则输出的excel打不开
  outputStream.write(i,0,l);
  
  //这个不行
  //outputStream.write(i);
}
outputStream.flush();
logger.info("导出成功");
return "导出成功";

}catch(Exception e){
  logger.error("导出失败！",e);
  return "导出失败，请稍后再试！";
}

//标准关流方法
finally {

if(outputStream != null){
  try{
    outputStream.close();
  }catch(IOException e){
    logger.error("outputStream关闭失败");
  }
}
if(inputStream != null){
  try{
    inputStream.close();
  }catch(IOException e){
    logger.error("inputStream关闭失败");
  }
}

}

5.获得机构树的一种方法

(1)数据库表中，存储着三级的机构树，例如：

id	company	com	branch	brc	aracde	ara
1	9	北京分公司		NULL	NULL	NULL
23	9	北京分公司	15	朝阳	NULL	NULL
24	9	北京分公司	16	昌平	NULL	NULL
33	9	北京分公司	16	昌平	102	阳高中路支公司
34	9	北京分公司	16	昌平	103	曹路支公司

想要获得机构树层级的map，首先要在sql中按规则查询，然后java里处理；

(2)sql如下：

select * from 
(select * from company where branch ="" and aracde ="NULL" order by company asc) a union
(select * from company where branch !="" and aracde ="NULL" order by branch asc) b union
(select * from company where branch !="" and aracde !="NULL" order by aracde asc) c

这个sql可以查询出一级分公司、二级分公司、三级分公司，然后用union，把结果集按顺序拼起来，最后返回一个List<Company>对象。(Company是javabean，对应数据库的company表)

(3)java如下：

public List selectCompanyTree(){
  //通过respository，执行sql，获取结果
  List<Company> companys = companyRespository.findCompanyTree();
  //准备返回的机构树
  ArrayList<InstituteVo> treeList = new ArrayList<>();
  //准备一个map
  HashMap<String,InstituteVo> instituteVoMap = new HashMap<>();

  //开始处理返回结果，返回结果有序，第一部分为一级，第二部分为二级，第三部分为三级
  for(Company c : companys){
    //如果是一级
    if(StringUtils.isEmpty(String.valueOf(c.getBranch()).trim()) && "NULL".equals(c.getAracde())){
      //先判断map中是否存在，如果不存在才处理，如果存在，说明处理过了，不做处理
      if(instituteVoMap.get(c.getCompany()) == null){
        instituteVo level1Vo = new instituteVo(c.getCompany(),c.getCom(),new ArrayList<>());
        //存入map
        instituteVoMap.put(c.getCompany(),level1Vo);
        //存入返回的list
        treeList.add(level1Vo);
      }
    }
    //如果是二级
    else if(StringUtils.isNotEmpty(String.valueOf(c.getBranch()).trim()) && "NULL".equals(c.getAracde())){
      //先判断map中是否存在，如果不存在才处理，如果存在，说明处理过了，不做处理
      if(instituteVoMap.get(c.getCompany()+"_"+c.getBranch()) == null){
        instituteVo level2Vo = new instituteVo(c.getCompany()+"_"+c.getBranch(),c.getBrc(),new ArrayList<>());
        //获取一级的对象
        instituteVo level1Vo = instituteVoMap.get(c.getCompany());
        //如果一级对象还没有处理，那就特殊处理，创建一个
        if(level1Vo == null){
          instituteVo level1Vo = new instituteVo(c.getCompany(),c.getCom(),new ArrayList<>());
          //存入map
          instituteVoMap.put(c.getCompany(),level1Vo);
          //存入返回的list
          treeList.add(level1Vo);
        }
        //装入sublist
        level1Vo.getChildren().add(level2Vo);
        //存入map
        instituteVoMap.put(c.getCompany()+"_"+c.getBranch(),level2Vo);
      }
    }
    //如果是三级
    else if(StringUtils.isNotEmpty(String.valueOf(c.getBranch()).trim()) && !"NULL".equals(c.getAracde())){
      instituteVo level3Vo = new instituteVo(c.getCompany()+"_"+c.getBranch()+"_"+c.getAracde(),c.getAra(),null);
      //找到二级的对象
      instituteVo level2Vo = instituteVoMap.get(c.getCompany()+"_"c.getBranch());
      //如果二级对象还没有处理，就特殊处理
      if(level2Vo == null){
          level2Vo = new instituteVo(c.getCompany()+"_"+c.getBranch(),c.getBrc(),new ArrayList<>());
          //找到一级的对象
          instituteVo level1Vo = instituteVoMap.get(c.getCompany());
          //如果一级对象还没有处理，那就特殊处理，创建一个
        if(level1Vo == null){
          instituteVo level1Vo = new instituteVo(c.getCompany(),c.getCom(),new ArrayList<>());
          //存入map
          instituteVoMap.put(c.getCompany(),level1Vo);
          //存入返回的list
          treeList.add(level1Vo);
        }
        //存入map
        instituteVoMap.put(c.getCompany()+"_"+c.getBranch(),level2Vo);
        //把二级存入一级的子list
        level1Vo.getChildren().add(level2Vo);
      }
      //把三级存入二级的子list
      level2Vo.getChildren().add(level3Vo);

      }else {
        logger.error("未知级别的机构"+c.toString());
      }
    }
    //写一个全部，当做最高级别的树
    ArrayList<InstituteVo> resultList = new ArrayList<>();
    InstituteVo masterVo = new InstituteVo("","全部",treeList);
    resultList.add(masterVo);
    
    return resultList;
  }
}

总结：

通过sql的union，把一级、二级、三级机构树按顺序排好；然后在java中，使用一个map缓存机构对象，把一级、二级、三级对象都存入map中，然后把三级对象存入对应的二级对象的子链中(list)、把二级对象存入对应的一级对象的子链中(list)；

然后把一级对象装入一个list返回，或者在外层再套一个"全部"机构后、装入list返回。

6.sql按天导出数据，没有的补充0

如果sql直接按天导出数据，【group by DATE_FORMAT(t.create_time,'%Y-%m-%d')】，当某天有数据时，没有问题；当某天没有数据时，就没有那行；不符合要求。

可以用以下方式实现：

(1)使用sql创建一个包含每天的结果集，结果集只有一列，为每天的数据，如

date

2021-07-01

2021-07-02

......

sql如下：

select date from (
  select ADDDATE('1970-01-01', t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) date from
  (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
  (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
  (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
  (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
  (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
) v
where date between '2021-07-01' and '2021-07-30'

这句sql可以查询出列名为date、数据为2021-07-01到2021-07-30的结果集；

这句sql中用逗号连接t0,t1,t2,t3,t4，得到笛卡尔积，然后用ADDDATE方法，算出每行的日期并转为【年-月-日】格式（每行的日期差1天，adddate('1970-01-01',1)会得到1970-01-02）；

最后使用where得到所要的范围。

(2)使用上方的sql，left join 查询出数据的结果，使用ifnull为没有的赋值为0，即可得到所需的数据；如下：

select date_table.date as final_date, ifnull(data_table.group_count, 0) as final_count 
from

(
select date from (
  select ADDDATE('1970-01-01', t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) date from
  (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
  (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
  (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
  (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
  (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
) v
where date between '2021-07-01' and '2021-07-30'
) date_table

left join 

(
select date_format(a.create_time,'%Y-%m-%d') as group_date, count(*) as group_count 
from exam_data a 
group by date_format(a.create_time,'%Y-%m-%d')
) data_table

on date_table.date = data_table.group_date
order by final_date

这句sql先查询出日期列结果集、数据按天分组后的结果集，然后用left join关联、没有的补0，最后得到想要的结果。

最后的order by是必要的，为了让结果有序，如果不加，有时候顺序会乱。

7.java按天导出数据，没有的补充0

以下是java代码：

//某天没有数据的，补0
public static void fillDate(String startTime, String endTime, ArrayList<MyBean> result) throws Exception{

  //假设传入参数的样式
  startTime = "2021-07-01";
  endTime = "2022-08-08";
  MyBean my1 = new MyBean("2021-07-01","5");
  MyBean my2 = new MyBean("2021-07-03","3");
  MyBean my3 = new MyBean("2021-07-05","1");
  //该list中的数据需要是有序的，日期从小到大
  result = new ArrayList<>();
  result.add(my1);
  result.add(my2);
  result.add(my3);

  //准备补从开始日期到结束日期、没有数据的为0
  //例如new MyBean("2021-07-02","0")然后装入list等

  if(StringUtils.isNotEmpty(startTime) && StringUtils.isNotEmpty(endTime)){
    //准备返回的list
    ArrayList<MyBean> newResult = new ArrayList<>();
    
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    Date startDate = sdf.parse(startTime);
    Date endDate = sdf.parse(endTime);

    //借助calendar，从开始日期，每次加1，进行补
    Calendar c = Calendar.getInstance();
    c.setTime(startDate);
    
    Iterator<MyBean> iterator = result.iterator();
    MyBean fromListBean = null;
    if(iterator.hasNext()){
      fromListBean = iterator.next();
    }
    //当没有到最后一天时
    while(c.getTime().getTime() <= endDate.getTime()){
      //如果list为空
      if(fromListBean == null){
        MyBean bean = new MyBean();
        bean.setDate(sdf.format(c.getTime()));
        bean.setValue("0");
        newResult.add(bean);
      }
      else{
        String date = fromListBean.getDate();
        String compareDate = sdf.format(c.getTime());
        //如果日期相等
        if(StringUtils.equals(compareDate,date)){
          newResult.add(fromListVo);
          //然后指向下一个元素,如果没有了，设为null
          if(iterator.hasNext()){
            fromListBean = iterator.next();
          }else{
            fromListBean = null;
          }
        }
        //如果不相等，newBean，补0
        else{
          MyBean bean = new MyBean();
          bean.setDate(sdf.format(c.getTime()));
          bean.setValue("0");
          newResult.add(bean);
        }
      }
      //加一天
      c.add(Calendar.DAY_OF_MONTH, 1);
    }
    //使用新的list; 没有写return，所以这样写
    result.clear();
    result.addAll(newResult);

  }
}

8.sql条件查询!=的问题

sql中，使用where a.flag !='0'，期望得到flag不是0的所有结果，但是并不行，flag=null也不是0，却被去掉了；所以应该使用where ifnull(a.flag,1)!='0'，才能得到flag不是0的所有结果；

或者使用where if(a.flag is null,'1',a.flag) != '0'，也可以得到flag不是0的所有结果。

9.sql时间相减问题

sql中的dateTime等时间类型不能直接相减，结果不对；需要使用TIME_TO_SEC()转为秒然后再减。

10.ES匹配*的问题

如果某个字段的值恰好为*，那么以下查询会有问题：

GET exam_data/_search
{
  "query":{
    "bool":{
      "must_not": [
        {
          "term": {
            "examId": {
              "value": "*"
            }
          }
        }
      ]
    }
  }
}

这条语句想查询所有examId不为*的数据，但是会不起作用，查询出所有数据。

同样，以下查询也有问题：

GET exam_data/_search
{
  "query":{
    "bool":{
      "must": [
        {
          "term": {
            "examId": {
              "value": "*"
            }
          }
        }
      ]
    }
  }
}

该语句想查询所有examId为*的数据，但是也有问题，会查询到0条数据。

解决方法：

将examId替换为examId.keyword，才能实现想要的效果。

追逐梦想永不停

关注

1
点赞
踩
11

收藏

觉得还不错? 一键收藏
打赏
0
评论
ElasticSearch与Mysql对比(ElasticSearch常用方法大全，持续更新)

ElasticSearch是一个开源的搜索引擎，可以它可以被下面这样准确的形容：一个分布式的实时文档存储，每个字段可以被索引与搜索一个分布式实时分析搜索引擎能胜任上百个服务节点的扩展，并支持 PB 级别的结构化或者非结构化数据...
复制链接

扫一扫