需求：把作家所有作品的总点击数加起来求和再进行排序的一个实现
1.先通过SQL语句计算出作家排行
select sum(a.clickcount) countTotal,a.author,group_concat(a.name) novel_names from db_novel.novel_test a group by author order by countTotal desc ;
2.把SQL语句转换成ES语句
语句转换地址：http://www.ischoolbar.com/EsParser/
3.格式化 ES语句
格式化语句地址：http://www.bejson.com/
4.创建索引【novel_author_countsort 】
拷贝之前创建的novel索引，直接修改author字段的类型为【 keyword 】
{ "novel_author_countsort" : { "mappings" : { "doc" : { "properties" : { "@timestamp" : { "type" : "date" }, "@version" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "author" : { "type" : "keyword" }, "category" : { "type" : "keyword" }, "clickcount" : { "type" : "long" }, "collect" : { "type" : "long" }, "count" : { "type" : "long" }, "countrecommend" : { "type" : "long" }, "detail" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "id" : { "type" : "long" }, "lastchapter" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "lastupdate" : { "type" : "date", "format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis" }, "monthclick" : { "type" : "long" }, "monthrecommend" : { "type" : "long" }, "name" : { "type" : "keyword" }, "new" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "novelinfo" : { "type" : "keyword" }, "picurl" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "status" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "weekclick" : { "type" : "long" }, "weekrecommend" : { "type" : "long" } } } } } }
5.给索引【novel_author_countsort】加载数据
# 加载数据 POST _reindex { "source": { "index": "novel" }, "dest": { "index": "novel_author_countsort" } }
6.测试
基于之前SQL语句生成的ES进行测试
# 测试 GET novel_author_countsort/_search { "size" : 0, "aggs": { "author": { "terms": { "field": "author", "size": 10, "order": { "countTotal": "DESC" } }, "aggs": { "countTotal": { "sum": { "field": "clickcount" } }, "top": { "top_hits": { "size": 1 } } } } } }
