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

布满荆棘的人生 2022-10-29 07:56 35阅读 0赞

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

**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 ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70_pic_center]  
**2.把SQL语句转换成ES语句**  
语句转换地址：[http://www.ischoolbar.com/EsParser/][http_www.ischoolbar.com_EsParser]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70_pic_center 1]  
**3.格式化 ES语句**  
格式化语句地址：[http://www.bejson.com/][http_www.bejson.com]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70_pic_center 2]  
**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
    					}
    				}
    			}
    		}
    	}
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70_pic_center 3]  
**7.测试数据分组页面效果**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70_pic_center 4]

--------------------

以上内容仅供参考学习，如有侵权请联系我删除！  
如果这篇文章对您有帮助，左下角的大拇指就是对博主最大的鼓励。  
您的鼓励就是博主最大的动力！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221024/47b0a0d444534d919235e816ab4bc938.png
[http_www.ischoolbar.com_EsParser]: http://www.ischoolbar.com/EsParser/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221024/42c0a79d56714a9dbaaac226dd900a5d.png
[http_www.bejson.com]: http://www.bejson.com/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221024/1cb09dd4f87342588c2a01e05e52bbaf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221024/97a85521a75f48179285c5d7be303700.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221024/f559d67e63c940b1ac2ab9394a45a6ca.png