Elasticsearch 5.3 JAVA Demo集約クエリー
40000 ワード
直接コードに行きます:弟は特にはっきり書いていません!特に完璧ではありません!大神たちが気にしないでほしい!ありがとう!
package com.vdyoo.paas.answerservice.search.es.esquery;
import org.elasticsearch.action.search.SearchRequestBuilder;
import org.elasticsearch.client.transport.TransportClient;
import org.elasticsearch.common.settings.Settings;
import org.elasticsearch.common.transport.InetSocketTransportAddress;
import org.elasticsearch.transport.client.PreBuiltTransportClient;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.net.InetAddress;
import java.net.UnknownHostException;
/**
* Created by on 2017/6/20.
*/
public class EsQuery {
// ‘
TransportClient client;
@Before
public void setup() throws UnknownHostException {
System.out.println("create TransportClient...");
client = new PreBuiltTransportClient(Settings.EMPTY)
.addTransportAddress(new InetSocketTransportAddress(InetAddress.getByName(""), 9300));
}
@After
public void clearup() {
System.out.println("close TransportClient...");
if (client != null) {
client.close();
}
}
@Test
public void doQuery(){
// ( )
// QueryBuilder qb = matchAllQuery();
// System.out.println(qb);
/* QueryBuilder qb = termQuery(
"city",
"Crumpler"
);
System.out.println(qb);*/
//**********************************************************************************
// ID
/**
+ * ID 。
+ * @param id ID。
+ * @return json 。
+ */
// String index="classsstudentexam";
// String type="answer";
// String id="120";
// GetResponse getResponse = client
// .prepareGet() // get , get 。( get )
// .setIndex(index) //
// .setType(type)
// .setId(id)
// .get();
// System.out.println(getResponse);
//*********************************************************************************************************
//
//
//
/* SearchResponse response = client.prepareSearch("store")
.setTypes("book")
.setSearchType(SearchType.DFS_QUERY_THEN_FETCH)
//.setQuery(QueryBuilders.termQuery("author", "Kathy Sierra")) // // Query
.setPostFilter(QueryBuilders.rangeQuery("price").from(50).to(100)) // Filter
.setFrom(0).setSize(60).setExplain(true)
.get();
System.out.println(response);*/
// {
// "took": 9,
// "timed_out": false,
// "_shards": {
// "total": 5,
// "successful": 5,
// "failed": 0
// },
// "hits": {
// "total": 2,
// "max_score": 1,
// "hits": [
// {
// "_shard": "[store][1]",
// "_node": "qTaK0CmRQ3iY4j8MOLWcXA",
// "_index": "store",
// "_type": "book",
// "_id": "12",
// "_score": 1,
// "_source": {
// "title": "Head First Java",
// "origin": " ",
// "description": "java ",
// "author": "Kathy Sierra",
// "price": 54
// },
// "_explanation": {
// "value": 1,
// "description": "ConstantScore(*:*), product of:",
// "details": [
// {
// "value": 1,
// "description": "boost",
// "details": []
// },
// {
// "value": 1,
// "description": "queryNorm",
// "details": []
// }
// ]
// }
// },
// {
// "_shard": "[store][1]",
// "_node": "qTaK0CmRQ3iY4j8MOLWcXA",
// "_index": "store",
// "_type": "book",
// "_id": "21",
// "_score": 1,
// "_source": {
// "title": "Design Pattern",
// "origin": " ",
// "description": " ",
// "author": "Kathy Sierra",
// "price": 89
// },
// "_explanation": {
// "value": 1,
// "description": "ConstantScore(*:*), product of:",
// "details": [
// {
// "value": 1,
// "description": "boost",
// "details": []
// },
// {
// "value": 1,
// "description": "queryNorm",
// "details": []
// }
// ]
// }
// }
// ]
// }
// }
//
//************************************************************************************************************
//
// Builder
// Builder
SearchRequestBuilder searchRequestBuilder = client.prepareSearch("vdyoo").setTypes("desktop");
// group by
//select Examnum, count(*) as Examnum_count from studentexamtest group by Examnum;
/* TermsAggregationBuilder field = AggregationBuilders.terms("CourseClass_count").field("orgkey");
searchRequestBuilder.addAggregation(field);
SearchResponse searchResponse = searchRequestBuilder.execute().actionGet();
System.out.println(searchResponse);*/
//{"took":16,"timed_out":false,"_shards":{"total":5,"successful":5,"failed":0},"hits":{"total":7174,"max_score":1.0,"hits":[{"_index":"classssstudentexamtest","_type":"answer","_id":"253","_score":1.0,"_source":{"Updatetime":1493374495149,"Student":2634,"Classs":101,"Answer":"D","Score":0,"CourseplanId":"CUP20170427025735619nixjd","CourseClass":35,"Createtime":1493374495149,"Examnum":28,"id":253,"Ok":0,"Room":101,"CourseClassExam":113}},{"_index":"classssstudentexamtest","_type":"answer","_id":"254","_score":1.0,"_source":{"Updatetime":1493374653157,"Student":2630,"Classs":101,"Answer":"B","Score":0,"CourseplanId":"CUP20170427025735619nixjd","CourseClass":35,"Createtime":1493374653157,"Examnum":28,"id":254,"Ok":0,"Room":101,"CourseClassExam":113}},{"_index":"classssstudentexamtest","_type":"answer","_id":"256","_score":1.0,"_source":{"Updatetime":1493721800274,"Student":5200,"Classs":227,"Answer":"A","Score":5,"CourseplanId":"CUP20170502040118596ldang","CourseClass":109,"Createtime":1493721800274,"Examnum":2,"id":256,"Ok":1,"Room":239,"CourseClassExam":121}},{"_index":"classssstudentexamtest","_type":"answer","_id":"259","_score":1.0,"_source":{"Updatetime":1493722363517,"Student":5200,"Classs":227,"Answer":"A","Score":5,"CourseplanId":"CUP20170502040118596ldang","CourseClass":109,"Createtime":1493722363517,"Examnum":6,"id":259,"Ok":1,"Room":239,"CourseClassExam":125}},{"_index":"classssstudentexamtest","_type":"answer","_id":"260","_score":1.0,"_source":{"Updatetime":1493722386247,"Student":5200,"Classs":227,"Answer":"0","Score":5,"CourseplanId":"CUP20170502040118596ldang","CourseClass":109,"Createtime":1493722386247,"Examnum":7,"id":260,"Ok":1,"Room":239,"CourseClassExam":126}},{"_index":"classssstudentexamtest","_type":"answer","_id":"264","_score":1.0,"_source":{"Updatetime":1493805813556,"Student":5199,"Classs":227,"Answer":"1","Score":5,"CourseplanId":"CUP20170503060043676boale","CourseClass":109,"Createtime":1493805813556,"Examnum":2,"id":264,"Ok":1,"Room":239,"CourseClassExam":130}},{"_index":"classssstudentexamtest","_type":"answer","_id":"265","_score":1.0,"_source":{"Updatetime":1493805922316,"Student":5199,"Classs":227,"Answer":"0","Score":5,"CourseplanId":"CUP20170503060043676boale","CourseClass":109,"Createtime":1493805922316,"Examnum":3,"id":265,"Ok":1,"Room":239,"CourseClassExam":131}},{"_index":"classssstudentexamtest","_type":"answer","_id":"270","_score":1.0,"_source":{"Updatetime":1493807772746,"Student":5200,"Classs":227,"Answer":"0","Score":5,"CourseplanId":"CUP20170503060043676boale","CourseClass":109,"Createtime":1493807772746,"Examnum":9,"id":270,"Ok":1,"Room":239,"CourseClassExam":137}},{"_index":"classssstudentexamtest","_type":"answer","_id":"108","_score":1.0,"_source":{"Updatetime":1493214489825,"Student":2574,"Classs":100,"Answer":"D","Score":0,"CourseplanId":"CUP20170423020722752xqkol","CourseClass":31,"Createtime":1493214489825,"Examnum":14,"id":108,"Ok":0,"Room":100,"CourseClassExam":58}},{"_index":"classssstudentexamtest","_type":"answer","_id":"110","_score":1.0,"_source":{"Updatetime":1493214495486,"Student":2578,"Classs":100,"Answer":"B","Score":0,"CourseplanId":"CUP20170423020722752xqkol","CourseClass":31,"Createtime":1493214495486,"Examnum":14,"id":110,"Ok":0,"Room":100,"CourseClassExam":58}}]},"aggregations":{"classssstudentexamtest_count":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":"CUP20170603100856501tgjws","doc_count":765},{"key":"CUP20170519090117735ugmzw","doc_count":531},{"key":"CUP20170603120737619gfxsa","doc_count":312},{"key":"CUP20170516083240398evrik","doc_count":308},{"key":"CUP20170618082126408psszj","doc_count":291},{"key":"CUP20170612095749865ljusl","doc_count":254},{"key":"CUP20170510064759160qckgw","doc_count":244},{"key":"CUP20170505101018686pbrkj","doc_count":219},{"key":"CUP20170628061714049iqzvj","doc_count":214},{"key":"CUP20170619030802390hynnv","doc_count":187},{"key":"CUP20170630113637873lbgtd","doc_count":178},{"key":"CUP20170627062050796qtpxj","doc_count":174},{"key":"CUP20170618045038072qvuvu","doc_count":165},{"key":"CUP20170602013009158szyqm","doc_count":130},{"key":"CUP20170522031911505okfvr","doc_count":125},{"key":"CUP20170613071602678cmgfj","doc_count":124},{"key":"CUP20170627062050800ybegt","doc_count":122},{"key":"CUP20170512094236918zkimg","doc_count":120},{"key":"CUP20170618062945995lahuf","doc_count":118},{"key":"CUP20170609110408973ocnkv","doc_count":113},{"key":"CUP20170515041217245ybplo","doc_count":110},{"key":"CUP20170517105057782okfkc","doc_count":99},{"key":"CUP20170619120325390ssyuk","doc_count":89},{"key":"CUP20170608125228438dibwg","doc_count":80},{"key":"CUP20170606105546790zbvji","doc_count":78},{"key":"CUP20170427025735619nixjd","doc_count":77},{"key":"CUP20170423020722752xqkol","doc_count":76},{"key":"CUP20170524013221969zswck","doc_count":76},{"key":"CUP20170509031004381zdxnq","doc_count":70},{"key":"CUP20170511074722091ctntn","doc_count":70},{"key":"CUP20170607015250657xddtk","doc_count":69},{"key":"CUP20170618082126520pqkoj","doc_count":69},{"key":"CUP20170625034524849jkusz","doc_count":69},{"key":"CUP20170618045038036fwdhv","doc_count":64},{"key":"CUP20170517121724060thbda","doc_count":61},{"key":"CUP20170618082126503urfpr","doc_count":59},{"key":"CUP20170605061237610gwzyg","doc_count":58},{"key":"CUP20170605100718730uxypg","doc_count":56},{"key":"CUP20170618082126354svunr","doc_count":56},{"key":"CUP20170423020722210lpxsw","doc_count":54},{"key":"CUP20170618062945967cbwmk","doc_count":52},{"key":"CUP20170625034524857tgshy","doc_count":50},{"key":"CUP20170522104220414aocyz","doc_count":49},{"key":"CUP20170522104220368xrwaq","doc_count":45},{"key":"CUP20170526121528243lmavt","doc_count":44},{"key":"CUP20170517105057700oixho","doc_count":43},{"key":"CUP20170517121346221voixt","doc_count":38},{"key":"CUP20170618082126485gykgc","doc_count":35},{"key":"CUP20170601121138642lkizq","doc_count":33},{"key":"CUP20170508050859926vaoal","doc_count":29},{"key":"CUP20170522111238154sopdg","doc_count":29},{"key":"CUP20170604102956055kcmvj","doc_count":28},{"key":"CUP20170522110923817qxuqv","doc_count":27},{"key":"CUP20170507014855690psysc","doc_count":26},{"key":"CUP20170603014855467woaic","doc_count":26},{"key":"CUP20170618062945980mpfiz","doc_count":23},{"key":"CUP20170627062050804dwkgh","doc_count":23},{"key":"CUP20170505101018695ndpdg","doc_count":22},{"key":"CUP20170602123137983czrys","doc_count":22},{"key":"CUP20170427025735442wrubf","doc_count":21},{"key":"CUP20170522104220456gplnq","doc_count":21},{"key":"CUP20170605032618233ksnab","doc_count":16},{"key":"CUP20170522031911501omlii","doc_count":15},{"key":"CUP20170515104323842mhkro","doc_count":14},{"key":"CUP20170522031911496vnfil","doc_count":14},{"key":"CUP20170603100856541gynud","doc_count":14},{"key":"CUP20170427103703112nlnof","doc_count":13},{"key":"CUP20170517105057728arbsc","doc_count":13},{"key":"CUP20170601100240495zbdaw","doc_count":13},{"key":"CUP20170618035821590idvcn","doc_count":13},{"key":"CUP20170625053525057pgyap","doc_count":13},{"key":"CUP20170613071602758gmtbd","doc_count":12},{"key":"CUP20170613071602770usdho","doc_count":11},{"key":"CUP20170421041000946okgou","doc_count":10},{"key":"CUP20170613071602745ojqzs","doc_count":10},{"key":"CUP20170618045038000uxhwl","doc_count":10},{"key":"CUP20170510012210242dudkc","doc_count":9},{"key":"CUP20170531083632225kcvui","doc_count":9},{"key":"CUP20170608115502218knund","doc_count":9},{"key":"CUP20170502040118596ldang","doc_count":8},{"key":"CUP20170503060043676boale","doc_count":8},{"key":"CUP20170505101018706vtkmq","doc_count":8},{"key":"CUP20170522031911480sfoqz","doc_count":8},{"key":"CUP20170618045037867klmvi","doc_count":8},{"key":"CUP20170517105057836ygmgr","doc_count":7},{"key":"CUP20170612095749881lyrcc","doc_count":7},{"key":"CUP20170511074726059tugym","doc_count":6},{"key":"CUP20170515104322151fbgid","doc_count":5},{"key":"CUP20170531022335744ifmli","doc_count":5},{"key":"CUP20170607124724116skwsw","doc_count":5},{"key":"CUP20170507014855712lzyry","doc_count":4},{"key":"CUP20170508050851786dnozp","doc_count":4},{"key":"CUP20170515104320399bcezj","doc_count":4},{"key":"CUP20170613071602732tivcv","doc_count":4},{"key":"CUP20170626120529505ntvbd","doc_count":4},{"key":"CUP20170423020722401vnxmc","doc_count":3},{"key":"CUP20170505101018643doomo","doc_count":3},{"key":"CUP20170522044733528gbnva","doc_count":3},{"key":"CUP20170522090223205hbewv","doc_count":3},{"key":"CUP20170601053401975ecmlj","doc_count":3},{"key":"CUP20170603034121549ejnix","doc_count":3},{"key":"CUP20170612095749909bmkse","doc_count":3},{"key":"CUP20170612095749922nogwa","doc_count":3},{"key":"CUP20170509031124516xrwks","doc_count":2},{"key":"CUP20170531023841869mxyuh","doc_count":2},{"key":"CUP20170531025220343ideaq","doc_count":2},{"key":"CUP20170603101018582hfeol","doc_count":2},{"key":"CUP20170605104822204mipkr","doc_count":2},{"key":"CUP20170511041909555jlrat","doc_count":1},{"key":"CUP20170512053313354tianx","doc_count":1},{"key":"CUP20170515104321302ujepd","doc_count":1},{"key":"CUP20170606102915104chhfl","doc_count":1},{"key":"CUP20170618045038019hvyqe","doc_count":1},{"key":"CUP20170625053334210vkpxl","doc_count":1},{"key":"CUP20170629074013434rabrr","doc_count":1}]}}}
//group by field
//select CourseplanId,CourseClass count(*) as class_count from group by CourseplanId,CourseClass;
// A B 。。。。
/* TermsAggregationBuilder CourseplanIdAgg = AggregationBuilders.terms("Room_count").field("Room");
TermsAggregationBuilder CourseClassAgg = AggregationBuilders.terms("CourseClass_count").field("Answer");
searchRequestBuilder.addAggregation(CourseplanIdAgg.subAggregation(CourseClassAgg));
SearchResponse searchResponse1 = searchRequestBuilder.execute().actionGet();
System.out.println(searchResponse1);*/
// {"took":18,"timed_out":false,"_shards":{"total":5,"successful":5,"failed":0},"hits":{"total":7174,"max_score":1.0,"hits":[{"_index":"classssstudentexamtest","_type":"answer","_id":"24","_score":1.0,"_source":{"Updatetime":1492930865258,"Student":2701,"Classs":102,"Answer":"A","Score":1,"CourseplanId":"CUP20170423020722210lpxsw","CourseClass":31,"Createtime":1492930865258,"Examnum":4,"id":24,"Ok":1,"Room":102,"CourseClassExam":27}},{"_index":"classssstudentexamtest","_type":"answer","_id":"25","_score":1.0,"_source":{"Updatetime":1492930865877,"Student":2704,"Classs":102,"Answer":"A","Score":1,"CourseplanId":"CUP20170423020722210lpxsw","CourseClass":31,"Createtime":1492930865877,"Examnum":4,"id":25,"Ok":1,"Room":102,"CourseClassExam":27}},{"_index":"classssstudentexamtest","_type":"answer","_id":"26","_score":1.0,"_source":{"Updatetime":1492930867462,"Student":2698,"Classs":102,"Answer":"A","Score":1,"CourseplanId":"CUP20170423020722210lpxsw","CourseClass":31,"Createtime":1492930867462,"Examnum":4,"id":26,"Ok":1,"Room":102,"CourseClassExam":27}},{"_index":"classssstudentexamtest","_type":"answer","_id":"29","_score":1.0,"_source":{"Updatetime":1492930874709,"Student":2635,"Classs":101,"Answer":"B","Score":0,"CourseplanId":"CUP20170423020722210lpxsw","CourseClass":31,"Createtime":1492930874710,"Examnum":4,"id":29,"Ok":0,"Room":101,"CourseClassExam":27}},{"_index":"classssstudentexamtest","_type":"answer","_id":"40","_score":1.0,"_source":{"Updatetime":1492934512558,"Student":2582,"Classs":100,"Answer":"55","Score":5,"CourseplanId":"CUP20170423020722210lpxsw","CourseClass":31,"Createtime":1492934512558,"Examnum":8,"id":40,"Ok":1,"Room":100,"CourseClassExam":30}},{"_index":"classssstudentexamtest","_type":"answer","_id":"41","_score":1.0,"_source":{"Updatetime":1492934523940,"Student":5192,"Classs":99,"Answer":"66","Score":0,"CourseplanId":"CUP20170423020722210lpxsw","CourseClass":31,"Createtime":1492934523940,"Examnum":8,"id":41,"Ok":0,"Room":99,"CourseClassExam":30}},{"_index":"classssstudentexamtest","_type":"answer","_id":"44","_score":1.0,"_source":{"Updatetime":1492934535161,"Student":5190,"Classs":100,"Answer":"33","Score":0,"CourseplanId":"CUP20170423020722210lpxsw","CourseClass":31,"Createtime":1492934535161,"Examnum":8,"id":44,"Ok":0,"Room":100,"CourseClassExam":30}},{"_index":"classssstudentexamtest","_type":"answer","_id":"48","_score":1.0,"_source":{"Updatetime":1492934678950,"Student":2632,"Classs":101,"Answer":"12","Score":0,"CourseplanId":"CUP20170423020722210lpxsw","CourseClass":31,"Createtime":1492934678950,"Examnum":8,"id":48,"Ok":0,"Room":101,"CourseClassExam":30}},{"_index":"classssstudentexamtest","_type":"answer","_id":"52","_score":1.0,"_source":{"Updatetime":1492934832480,"Student":2638,"Classs":101,"Answer":"12","Score":0,"CourseplanId":"CUP20170423020722210lpxsw","CourseClass":31,"Createtime":1492934832480,"Examnum":8,"id":52,"Ok":0,"Room":101,"CourseClassExam":30}},{"_index":"classssstudentexamtest","_type":"answer","_id":"253","_score":1.0,"_source":{"Updatetime":1493374495149,"Student":2634,"Classs":101,"Answer":"D","Score":0,"CourseplanId":"CUP20170427025735619nixjd","CourseClass":35,"Createtime":1493374495149,"Examnum":28,"id":253,"Ok":0,"Room":101,"CourseClassExam":113}}]},"aggregations":{"CourseplanId_count":{"doc_count_error_upper_bound":89,"sum_other_doc_count":3849,"buckets":[{"key":"CUP20170603100856501tgjws","doc_count":765,"CourseClass_count":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":124,"doc_count":765}]}},{"key":"CUP20170519090117735ugmzw","doc_count":531,"CourseClass_count":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":123,"doc_count":531}]}},{"key":"CUP20170603120737619gfxsa","doc_count":312,"CourseClass_count":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":124,"doc_count":312}]}},{"key":"CUP20170516083240398evrik","doc_count":308,"CourseClass_count":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":106,"doc_count":308}]}},{"key":"CUP20170618082126408psszj","doc_count":291,"CourseClass_count":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":31,"doc_count":291}]}},{"key":"CUP20170612095749865ljusl","doc_count":254,"CourseClass_count":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":30,"doc_count":254}]}},{"key":"CUP20170510064759160qckgw","doc_count":244,"CourseClass_count":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":118,"doc_count":244}]}},{"key":"CUP20170505101018686pbrkj","doc_count":219,"CourseClass_count":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":109,"doc_count":219}]}},{"key":"CUP20170628061714049iqzvj","doc_count":214,"CourseClass_count":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":8,"doc_count":214}]}},{"key":"CUP20170619030802390hynnv","doc_count":187,"CourseClass_count":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":131,"doc_count":187}]}}]}}}
//**************************************************************************************************************
//max/min/sum/avg
// / / / , SQL , :
//select age, max(salary) as max_salary from player group by age;
// age
/* TermsAggregationBuilder classAgg = AggregationBuilders.terms("CourseplanId_count").field("CourseplanId");
MaxAggregationBuilder maxAggregationBuilder = AggregationBuilders.max("max_salary").field("salary");
searchRequestBuilder.addAggregation(classAgg.subAggregation(maxAggregationBuilder));
SearchResponse searchResponse = searchRequestBuilder.execute().actionGet();
System.out.println(searchResponse);*/
// field max/min/sum/avg , , SQL , :
//select team, avg(age)as avg_age, sum(salary) as total_salary from player group by team;
/*TermsAggregationBuilder terms = AggregationBuilders.terms("team");
AvgAggregationBuilder field = AggregationBuilders.avg("avg_age").field("age");
AvgAggregationBuilder field1 = AggregationBuilders.avg("avg_salary").field("salary");
searchRequestBuilder.addAggregation(terms.subAggregation(field).subAggregation(field1));
SearchResponse searchResponse1 = searchRequestBuilder.execute().actionGet();
System.out.println(searchResponse1);*/
}
//
//*********************************************************************************************************
}
mapping