Elasticsearch--group by、avg、sort等聚合分析示例

╰+哭是因爲堅強的太久メ 2021-08-26 23:40 240阅读 0赞

将文本field的fielddata属性设置为true

PUT /ecommerce/_mapping/product
    {
      "properties": {
        "tags": {
          "type": "text",
          "fielddata": true
        }
      }
    }

第一个分析需求：计算每个tag下的商品数量

GET /ecommerce/product/_search
    { 
      "aggs": { 
        "group_by_tags": { 
          "terms": {  "field": "tags" }
        }
      }
    }

{ 
      "took": 34,
      "timed_out": false,
      "_shards": { 
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
      },
      "hits": { 
        "total": 4,
        "max_score": 1,
        "hits": [
          { 
            "_index": "ecommerce",
            "_type": "product",
            "_id": "2",
            "_score": 1,
            "_source": { 
              "name": "jiajieshi yagao",
              "desc": "youxiao fangzhu",
              "price": 25,
              "producer": "jiajieshi producer",
              "tags": [
                "fangzhu"
              ]
            }
          },
          { 
            "_index": "ecommerce",
            "_type": "product",
            "_id": "4",
            "_score": 1,
            "_source": { 
              "name": "special yagao",
              "desc": "special meibai",
              "price": 50,
              "producer": "special yagao producer",
              "tags": [
                "meibai"
              ]
            }
          },
          { 
            "_index": "ecommerce",
            "_type": "product",
            "_id": "1",
            "_score": 1,
            "_source": { 
              "name": "gaolujie yagao",
              "desc": "gaoxiao meibai",
              "price": 30,
              "producer": "gaolujie producer",
              "tags": [
                "meibai",
                "fangzhu"
              ]
            }
          },
          { 
            "_index": "ecommerce",
            "_type": "product",
            "_id": "3",
            "_score": 1,
            "_source": { 
              "name": "zhonghua yagao",
              "desc": "caoben zhiwu",
              "price": 40,
              "producer": "zhonghua producer",
              "tags": [
                "qingxin"
              ]
            }
          }
        ]
      },
      "aggregations": { 
        "group_by_tags": { 
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": [
            { 
              "key": "fangzhu",
              "doc_count": 2
            },
            { 
              "key": "meibai",
              "doc_count": 2
            },
            { 
              "key": "qingxin",
              "doc_count": 1
            }
          ]
        }
      }
    }

第二个聚合分析的需求：对名称中包含yagao的商品，计算每个tag下的商品数量

GET /ecommerce/product/_search
    { 
      "size": 0,
      "query": { 
        "match": { 
          "name": "yagao"
        }
      },
      "aggs": { 
        "all_tags": { 
          "terms": { 
            "field": "tags"
          }
        }
      }
    }

{ 
      "took": 5,
      "timed_out": false,
      "_shards": { 
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
      },
      "hits": { 
        "total": 4,
        "max_score": 0,
        "hits": []
      },
      "aggregations": { 
        "all_tags": { 
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": [
            { 
              "key": "fangzhu",
              "doc_count": 2
            },
            { 
              "key": "meibai",
              "doc_count": 2
            },
            { 
              "key": "qingxin",
              "doc_count": 1
            }
          ]
        }
      }
    }

第三个聚合分析的需求：先分组，再算每组的平均值，计算每个tag下的商品的平均价格

GET /ecommerce/product/_search
    { 
        "size": 0,
        "aggs" : { 
            "group_by_tags" : { 
                "terms" : {  "field" : "tags" },
                "aggs" : { 
                    "avg_price" : { 
                        "avg" : {  "field" : "price" }
                    }
                }
            }
        }
    }

{ 
      "took": 5,
      "timed_out": false,
      "_shards": { 
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
      },
      "hits": { 
        "total": 4,
        "max_score": 0,
        "hits": []
      },
      "aggregations": { 
        "group_by_tags": { 
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": [
            { 
              "key": "fangzhu",
              "doc_count": 2,
              "avg_price": { 
                "value": 27.5
              }
            },
            { 
              "key": "meibai",
              "doc_count": 2,
              "avg_price": { 
                "value": 40
              }
            },
            { 
              "key": "qingxin",
              "doc_count": 1,
              "avg_price": { 
                "value": 40
              }
            }
          ]
        }
      }
    }

第四个数据分析需求：计算每个tag下的商品的平均价格，并且按照平均价格降序排序

GET /ecommerce/product/_search
    { 
        "size": 0,
        "aggs" : { 
            "all_tags" : { 
                "terms" : {  "field" : "tags", "order": {  "avg_price": "desc" } },
                "aggs" : { 
                    "avg_price" : { 
                        "avg" : {  "field" : "price" }
                    }
                }
            }
        }
    }

{ 
      "took": 2,
      "timed_out": false,
      "_shards": { 
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
      },
      "hits": { 
        "total": 4,
        "max_score": 0,
        "hits": []
      },
      "aggregations": { 
        "all_tags": { 
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": [
            { 
              "key": "meibai",
              "doc_count": 2,
              "avg_price": { 
                "value": 40
              }
            },
            { 
              "key": "qingxin",
              "doc_count": 1,
              "avg_price": { 
                "value": 40
              }
            },
            { 
              "key": "fangzhu",
              "doc_count": 2,
              "avg_price": { 
                "value": 27.5
              }
            }
          ]
        }
      }
    }