最终dsl
{
"query": {
"bool": {
"filter": [
{
"range": {
"taskCreatedDate": { // es 存储时用的utc
"gte": "2019-09-30T16:00:00Z",
"lte": "2019-11-30T16:00:00Z"
}
}
}
]
}
},
"aggs": {
"sales_per_month": {
"date_histogram": { //es 聚合显示时候,主要添加时区装换,否则容易出现跨月显示问题
"field": "taskCreatedDate",
"interval": "month",
"time_zone": "+08:00",
"format": "yyyy-MM"
},
"aggs": {
"domain_report": {
"terms": {
"script": {
"source": "def email = doc['email'].value;def beginIndex =email.indexOf('@');def domain = email.substring(beginIndex+1);return domain"
},
"size": 10, // 设置条目数量
"order": {
"_count": "desc"
}
},
"aggs": {
"domain_report_count": {
"value_count": {
"field": "_index"
}
},
"hard_bounce": {
"filter": {
"term": {
"actionStatus": "HARD_BOUNCE"
}
},
"aggs": {
"hard_bounce_count": {
"value_count": {
"field": "_index"
}
}
}
},
"system_bounce": {
"filter": {
"term": {
"actionStatus": "SYSTEM_BOUNCE"
}
},
"aggs": {
"system_bounce_count": {
"value_count": {
"field": "_index"
}
}
}
},
"hard_percentage": {
"bucket_script": {
"buckets_path": {
"hard_bounce_count": "hard_bounce>hard_bounce_count",
"delivery_count": "domain_report_count"
},
"script": "params.hard_bounce_count/ params.delivery_count* 100"
}
},
"system_hard_percentage": {
"bucket_script": {
"buckets_path": {
"system_bounce_count": "system_bounce>system_bounce_count",
"delivery_count": "domain_report_count"
},
"script": "params.system_bounce_count/ params.delivery_count* 100"
}
}
}
}
}
}
}
}
原始需求
对所有退信邮箱的域名进行terms聚合,email 采用keword 存储.一般情况terms 只对特定确定字段进行聚合操作。本需求需要对这个特定字段进行部分截取,然后对这个截取后得到的域名进行聚合统计。在关系型数据库中一般可以通过视图来解决。经过一定的官方文档阅读,es terms agg api居然提供了terms agg script 功能 https://www.elastic.co/guide/en/elasticsearch/reference/5.6/search-aggregations-bucket-terms-aggregation.html
#script 章节,采用painless 的语法进行脚本定制.
遇到的坑
- 获取 email 值一定要加
.value
方法,不然会报Unable to find dynamic method [substring] with [1] arguments for class [org.elasticsearch.index.fielddata.ScriptDocValues.Strings]
异常,明明 email的mapping 设置的是keyword,莫非和String不是一个东西? - substring 的 第二个
s
要小写; - 跨月聚合时,注意添加时区偏移;
- bucket_script 的参数变量一定要是value类型的,一般使用count,sum等聚合