基于ES之业务数据分组求和TopN开发-阿里云开发者社区

基于ES之业务数据分组求和TopN开发

2022-05-17 551

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 需求：把作家所有作品的总点击数加起来求和再进行排序的一个实现

需求：把作家所有作品的总点击数加起来求和再进行排序的一个实现

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
          }
        }
      }
    }
  }
}