Elasticsearch教程(32) ES 聚合查询后过滤 Distinct Group By Having功能
2023-09-27 14:26:50 时间
一、之前写的关于ES聚合的博客
Elasticsearch教程(4) High Level REST Client API 查询 聚合 分组
Elasticsearch教程(5) 指标聚合 SQL DSL JavaAPI
Elasticsearch教程(6) 桶聚合Query DSL-Terms Aggregation
Elasticsearch教程(10) ES term terms prefix 搜索 聚合查询 详细总结
Elasticsearch教程(11) elasticsearch 桶聚合 Query DSL
二、测试数据
PUT /pigg/_doc/1
{
"name": "老亚瑟",
"age": 30,
"sex": "男",
"group": "日落圣殿",
"tag":["战士", "坦克"],
"date": "2019-12-26",
"friend": "安琪拉"
}
PUT /pigg/_doc/2
{
"name": "安琪拉",
"age": 16,
"sex": "女",
"group": "日落圣殿",
"tag":["法师"],
"date": "2019-01-01",
"friend": ""
}
PUT /pigg/_doc/3
{
"name": "凯",
"age": 28,
"sex": "男",
"group": "长城守卫军",
"tag":["战士"],
"date": "2020-01-01"
}
PUT /pigg/_doc/4
{
"name": "盾山",
"age": 38,
"sex": "男",
"group": "长城守卫军",
"tag":["辅助", "坦克"],
"date": "2020-02-02"
}
PUT /pigg/_doc/5
{
"name": "百里守约",
"age": 18,
"sex": "男",
"group": "长城守卫军",
"tag":["射手"],
"date": "2020-03-03"
}
PUT /pigg/_doc/6
{
"name": "李元芳",
"age": 15,
"sex": "男",
"group": "长安",
"tag":["刺客"],
"date": "2020-03-23"
}
PUT /pigg/_doc/7
{
"name": "陈咬金",
"age": 40,
"sex": "男",
"group": "长安",
"tag":["战士", "坦克"]
}
三、分组聚合
- select count(1) from table group by…
GET /pigg/_search
{
"size": 0,
"aggs": {
"count_of_group": {
"terms": {
"field": "group.keyword"
}
}
}
}
返回如下:
"aggregations" : {
"count_of_group" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "长城守卫军",
"doc_count" : 3
},
{
"key" : "日落圣殿",
"doc_count" : 2
},
{
"key" : "长安",
"doc_count" : 2
}
]
}
}
四、先过滤,后聚合
- select count(1) from table where … group by…
先过滤查询数据的范围,然后再聚合,hits是过滤后的数据
GET /pigg/_search
{
"size": 10,
"query": {
"bool": {
"filter": [
{
"term": {
"sex.keyword": "男"
}
}
]
}
},
"aggs": {
"count_of_group": {
"terms": {
"field": "group.keyword"
}
}
}
}
五、先filter聚合,再内嵌terms聚合
这个聚合结果和上面一样,不同的是这个hits里面是filter之前的全部数据
GET /pigg/_search
{
"size": 10,
"aggs": {
"group_of_man": {
"filter": {
"term": {
"sex.keyword": "男"
}
},
"aggs": {
"count_of_group": {
"terms": {
"field": "group.keyword",
"size": 10
}
}
}
}
}
}
六、先filter聚合,再内嵌terms聚合,再内嵌avg聚合
select count(1) avg(age) from table where … group by…
GET /pigg/_search
{
"size": 10,
"aggs": {
"group_of_man": {
"filter": {
"term": {
"sex.keyword": "男"
}
},
"aggs": {
"count_of_group": {
"terms": {
"field": "group.keyword",
"size": 10
},
"aggs": {
"avg_age": {
"avg": {
"field": "age"
}
}
}
}
}
}
}
}
"buckets" : [
{
"key" : "长城守卫军",
"doc_count" : 3,
"avg_age" : {
"value" : 28.0
}
},
{
"key" : "长安",
"doc_count" : 2,
"avg_age" : {
"value" : 27.5
}
},
{
"key" : "日落圣殿",
"doc_count" : 1,
"avg_age" : {
"value" : 30.0
}
}
]
七、先filter聚合,再内嵌terms聚合,再内嵌avg聚合,再根据平均年龄排序
select count(1) avg(age) from table where … group by… order by avg_age asc
GET /pigg/_search
{
"size": 10,
"aggs": {
"group_of_man": {
"filter": {
"term": {
"sex.keyword": "男"
}
},
"aggs": {
"count_of_group": {
"terms": {
"field": "group.keyword",
"size": 10,
"order": {
"avg_age": "asc"
}
},
"aggs": {
"avg_age": {
"avg": {
"field": "age"
}
}
}
}
}
}
}
}
八、实现Having功能 having count
select count(1) avg(age) from table where … group by… having count > 1
GET /pigg/_search
{
"size": 10,
"aggs": {
"group_of_man": {
"filter": {
"term": {
"sex.keyword": "男"
}
},
"aggs": {
"count_of_group": {
"terms": {
"field": "group.keyword",
"size": 10
},
"aggs": {
"having": {
"bucket_selector": {
"buckets_path": {
"count_of_group": "_count"
},
"script": {
"source": "params.count_of_group > 1"
}
}
}
}
}
}
}
}
}
九、实现Having功能 having avg
select count(1) avg(age) from table where … group by… having avg_age > 28
GET /pigg/_search
{
"size":10,
"aggs":{
"group_of_man":{
"filter":{
"term":{
"sex.keyword":"男"
}
},
"aggs":{
"count_of_group":{
"terms":{
"field":"group.keyword",
"size":10
},
"aggs":{
"avg_age":{
"avg":{
"field":"age"
}
},
"avg_age_filter":{
"bucket_selector":{
"buckets_path":{
"avg_age":"avg_age"
},
"script":{
"source":"params.avg_age > 28"
}
}
}
}
}
}
}
}
}
十、实现distinct去重
select count(distinct(group)) as count_of_distinct_group from pigg group by sex
GET /pigg/_search
{
"size":10,
"aggs":{
"count_of_group":{
"terms":{
"field":"sex.keyword"
},
"aggs":{
"count_of_distinct_group":{
"cardinality":{
"field":"group.keyword"
}
}
}
}
}
}
相关文章
- ElasticSearch(ES)和solr的关系和区别
- Flink-Sink(Kafka、Redis、ES、JDBC)
- Python操作ElasticSearch(Python操作ES)
- python中elasticsearch_dsl查询语句转换成es查询语句
- 使用python,将es数据写入mongo数据库中
- Node.js 4.0 中的 ES 6 特性介绍
- Es索引优化
- Es分析
- ES
- ElasticSearch入门之es使用shell命令操作索引,curl操作索引 08
- ES——使用Postman连接Elasticsearch
- ElasticSearch第十八讲 ES-Master节点职责和ES是如何做到数据实时性的
- ElasticSearch第二讲 ES配置环境参数/调优参数讲解
- ES常用维护命令
- ElasticSearch系列二 ES基本使用及文档
- 《OpenGL ES 3.x游戏开发(上卷)》一2.1 游戏中的音效
- 《OpenGL ES 3.x游戏开发(上卷)》一2.2 简单数据的存储——Preferences
- ES (ElasticSearch) 简易解读(四)Docker环境下安装和配置;非常简单的方式
- ES (ElasticSearch) 简易解读(三)企业级日志分析ELK架构的搭建与使用
- 【OpenGL ES】绘制立方体
- (1.5)es集群部署运维【最佳实践】
- ES核心概念(第四更)
- ES 聚合查询
- 精选 ES(elasticsearch)添加、修改、查询、删除详细使用语法