Elasticsearch多字段分组聚合, 并对分组聚合的count进行筛选

墨蓝 2022-04-15 05:56 1699阅读 0赞

**前提**  
假设我们的文档包括以下几个字段 : activityId, clientIp, orderNumber

**目标**  
依据activityId(策略ID) + clientIp(IP地址)分组聚合, 查找相同策略ID+相同IP下订单数目超过2的聚合结果

**实现**

{
        "request_body": {
            // 不返回具体的查询数据
            "size": 0,
            "aggs": {
                "group_by_activityId": {
                    "terms": {
                        // 多字段聚合
                        "script": "doc['activityId'].values +'#split#'+ doc['clientIp'].values",
                        // 设置聚合返回的最大数目
                        "size": 2147483647
                    },
                    "aggs": {
                        // 依据orderNumber去重统计数目
                        "orderNumber_count": {
                            "cardinality": {
                                "field": "orderNumber"
                            }
                        },
                        "orderNumber_count_filter": {
                            "bucket_selector": {
                                "buckets_path": {
                                    "orderNumberCount": "orderNumber_count"
                                },
                                // 筛选去数目>1
                                "script": "params.orderNumberCount>1"
                            }
                        }
                    }
                }
            }
        }
    }

**结果**

{
        "took": 644,
        "timed_out": false,
        "_shards": {
            "total": 2,
            "successful": 2,
            "skipped": 0,
            "failed": 0
        },
        "_clusters": {
            "total": 3,
            "successful": 3,
            "skipped": 0
        },
        "hits": {
            "total": 3742,
            "max_score": 0,
            "hits": []
        },
        "aggregations": {
            "group_by_activityId": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                    {
                        "key": "[63336]#split#[117.136.89.93]",
                        "doc_count": 3,
                        "orderNumber_count": {
                            "value": 3
                        }
                    },
                    {
                        "key": "[63336]#split#[1.180.71.158]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[101.67.161.81]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[101.67.161.83]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[112.224.1.196]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[113.56.205.98]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[117.136.13.21]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[117.136.25.182]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[117.136.40.135]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[117.136.5.147]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[117.136.5.228]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[117.136.75.132]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[117.136.79.114]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[117.136.87.51]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[124.64.16.10]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[124.88.36.156]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[183.240.195.131]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[221.11.61.224]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[221.192.179.232]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[221.212.180.24]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[221.212.180.28]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[223.104.20.79]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[223.104.250.229]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[223.104.35.228]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[223.104.64.25]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[59.173.193.152]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[59.44.235.74]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[60.15.96.98]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63336]#split#[61.158.147.112]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[63341]#split#[101.67.161.90]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    },
                    {
                        "key": "[78657]#split#[223.104.248.6]",
                        "doc_count": 2,
                        "orderNumber_count": {
                            "value": 2
                        }
                    }
                ]
            }
        }
    }