Elasticsearch 类似 Mysql distinct 功能实现

一时失言乱红尘 2021-12-11 10:43 389阅读 0赞

## 实现方式 ##

#### 使用聚合方式实现 ####

按 `floor` distinct，并取出 num 最大的一条记录

{ 
      "size": 0,
      "aggregations": { 
        "field1": { 
          "terms": { 
            "field": "floor",
            "size": 20
          },
          "aggs": { 
            "num_top": { 
              "top_hits": { 
                "_source": true,
                "size":1,
                "sort": { "num":"asc"}
              }
            }
          }
        }
      }
    }

注：不可以分页

##### 使用 `collapse` 折叠功能 #####

例如，下面的查询检索 `message` 中有 elasticsearch 值的 twitter，按喜好数量对它们进行排序并按用户 distinct（取排序后每个用户的第一条数据）。

GET /twitter/_search
    { 
        "query": { 
            "match": { 
                "message": "elasticsearch"
            }
        },
        "collapse" : { 
            "field" : "user" 
        },
        "sort": ["likes"], 
        "from": 10 
    }

inner\_hits

curl -X GET "localhost:9200/twitter/_search" -H 'Content-Type: application/json' -d' { "query": { "match": { "message": "elasticsearch" } }, "collapse" : { "field" : "user", "inner_hits": { "name": "last_tweets", "size": 5, "sort": [{ "date": "asc" }] }, "max_concurrent_group_searches": 4 }, "sort": ["likes"] } '

注：仅仅适用于 keyword 和数字类型

## 统计总数 ##

按 `floor` distinct 后总数，使用到了[数值度量聚合][Link 1]

{ 
      "aggs": { 
        "distinct": {  //该参数 key 随意
          "cardinality": { 
            "field": "floor"
          }
        }
      }
    }
    // 相应
    ...
      "aggregations" : { 
        "distinct" : { 
          "value" : 20
        }
      }

#### 参考 ####

1.  collapse：[https://www.elastic.co/guide/en/elasticsearch/reference/6.4/search-request-collapse.html][https_www.elastic.co_guide_en_elasticsearch_reference_6.4_search-request-collapse.html]
2.  top\_hits：[https://www.elastic.co/guide/en/elasticsearch/reference/6.4/search-aggregations-metrics-top-hits-aggregation.html][https_www.elastic.co_guide_en_elasticsearch_reference_6.4_search-aggregations-metrics-top-hits-aggregation.html]
3.  cardinality：[https://www.elastic.co/guide/en/elasticsearch/reference/6.4/search-aggregations-metrics-cardinality-aggregation.html][https_www.elastic.co_guide_en_elasticsearch_reference_6.4_search-aggregations-metrics-cardinality-aggregation.html]

[Link 1]: https://blog.csdn.net/qq_35958788/article/details/94412852
[https_www.elastic.co_guide_en_elasticsearch_reference_6.4_search-request-collapse.html]: https://www.elastic.co/guide/en/elasticsearch/reference/6.4/search-request-collapse.html
[https_www.elastic.co_guide_en_elasticsearch_reference_6.4_search-aggregations-metrics-top-hits-aggregation.html]: https://www.elastic.co/guide/en/elasticsearch/reference/6.4/search-aggregations-metrics-top-hits-aggregation.html
[https_www.elastic.co_guide_en_elasticsearch_reference_6.4_search-aggregations-metrics-cardinality-aggregation.html]: https://www.elastic.co/guide/en/elasticsearch/reference/6.4/search-aggregations-metrics-cardinality-aggregation.html