【ES实战】简单的聚合-复合查询

演示一个使用复合查询的场景案例

背景：

一个索引，存放了短信发送纪录。

需求：

根据chlAccount、suppliersId字段分组。统计一下数据：

1. 根据serialNum字段统计出总记录数，
2. 根据smsStatus短信发送状态字段统计出发送短信成功数，
3. 根据receiveStatus短信接收状态统计出短信接收成功数和接收失败数，
4. 根据succTotal短信成功数字段统计出计费短信总数，
5. 根据短信时间smsTime短信耗时统计出平均耗时，
6. 根据receiveStatus短信发送状态统计出短信接收成功，接收失败，接收未知状态的总数。

主要使用的聚合：

1. Value Count Aggregation ：值计数聚合
2. Terms Aggregation：分词桶聚合
3. Filter Aggregation：过滤器桶聚合
4. Filters Aggregation: 过滤器组桶聚合
5. Avg Aggregation：平均值聚合
6. Sum Aggregation：值汇总聚合

参与排序和聚合的字段，需要开启doc_values，doc_values默认开启。不开启的会聚合语句执行时会，直接报错。
使用的的是5版本的ES，所以标注脚本语言用的是inline字段，高版本已变更。需要重新查阅。

Index的结构

PUT sms-20240423

{
    "mappings": {
        "sms": {
            "dynamic": "strict",
            "_all": {
                "enabled": false
            },
            "properties": {
                "serialNum": {
                    "type": "keyword"
                },
                "sendStatus": {
                    "type": "integer"
                },
                "receiveStatus": {
                    "type": "integer"
                },
                "succTotal": {
                    "type": "integer"
                },
                "smsTime": {
                    "type": "long"
                },
                "chlAccount": {
                    "type": "keyword"
                },
                "suppliersId": {
                    "type": "keyword"
                },
                "chlName": {
                    "type": "keyword",
                    "doc_values": false
                },
            }
        }
    },
    "settings": {
        "number_of_shards": "3",
        "number_of_replicas": "1"
    }
}

查询语句，执行时，需要去掉注释部分。

GET sms-20240423/_search
{
    "size": 0, //默认不展示hit数据记录
    "query": { // bool must查询，选择数据的sendTime时间范围
        "bool": {
            "must": [
                {
                    "range": {
                        "sendTime": {
                            "from": 1713283200000
                        }
                    }
                }
            ]
        }
    },
    "aggregations": {
        "sendDataByChannel": {
            // 分词桶聚合-使用脚本进行分组
            "terms": {
                "script": {
                    "inline": "doc['chlAccount']+'#'+doc['suppliersId']",
                    "lang": "painless"
                },
                "size": 1000,
                "min_doc_count": 1,
                "shard_min_doc_count": 0,
                "show_term_doc_count_error": false,
                "order": [
                    {
                        "_count": "desc"
                    },
                    {
                        "_term": "asc"
                    }
                ]
            },
            "aggregations": {
                "sendTotal": {
                    // 值计数聚合，聚合使用字段serialNum
                    "value_count": {
                        "field": "serialNum"
                    }
                },
                "sendSuccSum": {
                    // 过滤器聚合
                    "filter": {
                        "match": {
                            "sendStatus": {
                                "query": 1,
                                "operator": "OR",
                                "prefix_length": 0,
                                "max_expansions": 50,
                                "fuzzy_transpositions": true,
                                "lenient": false,
                                "zero_terms_query": "NONE",
                                "boost": 1.0
                            }
                        }
                    }
                },
                "receiveSuccSum": {
                     // 过滤器聚合
                    "filter": {
                        "match": {
                            "receiveStatus": {
                                "query": 1,
                                "operator": "OR",
                                "prefix_length": 0,
                                "max_expansions": 50,
                                "fuzzy_transpositions": true,
                                "lenient": false,
                                "zero_terms_query": "NONE",
                                "boost": 1.0
                            }
                        }
                    }
                },
                "receiveFailSum": {
                    // 过滤器桶聚合
                    "filter": {
                        "match": {
                            "receiveStatus": {
                                "query": -2,
                                "operator": "OR",
                                "prefix_length": 0,
                                "max_expansions": 50,
                                "fuzzy_transpositions": true,
                                "lenient": false,
                                "zero_terms_query": "NONE",
                                "boost": 1.0
                            }
                        }
                    }
                },
                "costTotalSum": {
                   // 求和值聚合
                    "sum": {
                        "field": "succTotal"
                    }
                },
                "avgReceiveTime": {‘
                    // 平均值聚合
                    "avg": {
                        "field": "smsTime"
                    }
                },
                "callbackSuccSum": {
                     // 多过滤器桶聚合
                    "filters": {
                        "filters": [
                            {
                                "match": {
                                    "receiveStatus": {
                                        "query": 1,
                                        "operator": "OR",
                                        "prefix_length": 0,
                                        "max_expansions": 50,
                                        "fuzzy_transpositions": true,
                                        "lenient": false,
                                        "zero_terms_query": "NONE",
                                        "boost": 1.0
                                    }
                                }
                            },
                            {
                                "match": {
                                    "receiveStatus": {
                                        "query": -2,
                                        "operator": "OR",
                                        "prefix_length": 0,
                                        "max_expansions": 50,
                                        "fuzzy_transpositions": true,
                                        "lenient": false,
                                        "zero_terms_query": "NONE",
                                        "boost": 1.0
                                    }
                                }
                            },
                            {
                                "match": {
                                    "receiveStatus": {
                                        "query": -3,
                                        "operator": "OR",
                                        "prefix_length": 0,
                                        "max_expansions": 50,
                                        "fuzzy_transpositions": true,
                                        "lenient": false,
                                        "zero_terms_query": "NONE",
                                        "boost": 1.0
                                    }
                                }
                            }
                        ],
                        "other_bucket": false,
                        "other_bucket_key": "_other_"
                    }
                }
            }
        }
    }
}

查询结果示例：

{
    "took": 4,
    "timed_out": false,
    "_shards": {
        "total": 3,
        "successful": 3,
        "failed": 0
    },
    "hits": {
        "total": 301,
        "max_score": 0,
        "hits": []
    },
    "aggregations": {
        "sendDataByChannel": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
                {
                    "key": "[chlAccount]#[surereid]",
                    "doc_count": 101,
                    "sendSuccSum": {
                        "doc_count": 101
                    },
                    "receiveFailSum": {
                        "doc_count": 0
                    },
                    "sendTotal": {
                        "value": 101
                    },
                    "receiveSuccSum": {
                        "doc_count": 100
                    },
                    "costTotalSum": {
                        "value": 202
                    },
                    "callbackSuccSum": {
                        "buckets": [
                            {
                                "doc_count": 100
                            },
                            {
                                "doc_count": 0
                            },
                            {
                                "doc_count": 0
                            }
                        ]
                    },
                    "avgReceiveTime": {
                        "value": 550.6237623762377
                    }
                },
                {
                    "key": "[chlAccount2]#[surereid]",
                    "doc_count": 100,
                    "sendSuccSum": {
                        "doc_count": 100
                    },
                    "receiveFailSum": {
                        "doc_count": 0
                    },
                    "sendTotal": {
                        "value": 100
                    },
                    "receiveSuccSum": {
                        "doc_count": 0
                    },
                    "costTotalSum": {
                        "value": 200
                    },
                    "callbackSuccSum": {
                        "buckets": [
                            {
                                "doc_count": 0
                            },
                            {
                                "doc_count": 0
                            },
                            {
                                "doc_count": 0
                            }
                        ]
                    },
                    "avgReceiveTime": {
                        "value": 549.87
                    }
                },
                {
                    "key": "[chlAccount3]#[surereid]",
                    "doc_count": 100,
                    "sendSuccSum": {
                        "doc_count": 100
                    },
                    "receiveFailSum": {
                        "doc_count": 0
                    },
                    "sendTotal": {
                        "value": 100
                    },
                    "receiveSuccSum": {
                        "doc_count": 0
                    },
                    "costTotalSum": {
                        "value": 200
                    },
                    "callbackSuccSum": {
                        "buckets": [
                            {
                                "doc_count": 0
                            },
                            {
                                "doc_count": 0
                            },
                            {
                                "doc_count": 0
                            }
                        ]
                    },
                    "avgReceiveTime": {
                        "value": 607.38
                    }
                }
            ]
        }
    }
}