Elasticsearch中SQL COUNT(DISTINCT)等价实现方法咨询

现有查询的结果偏差原因 #

• 你使用的value_count聚合的逻辑是统计指定字段存在非空值的总文档数，不具备去重能力，和SQL中普通的count(unique_field)逻辑等价，完全没有覆盖DISTINCT的语义。
• 你搭配的terms聚合虽然会对unique_field的值做去重分组，但默认仅返回前10个分组结果，既不能覆盖全量唯一值，也不是专门的计数聚合，根本无法用来计算准确的去重总数，这是你之前结果和SQL不匹配的核心原因。

对齐SQL逻辑的可运行查询 #

完全匹配你给出的过滤条件的正确查询结构如下，核心替换成专门的去重计数聚合：

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "city": {
              "value": "bratislava"
            }
          }
        },
        {
          "range": {
            "db_date": {
              "gte": "2020-05-15T00:00:00.000Z",
              "lte": "2020-05-16T00:00:00.000Z"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "distinct_unique_field_count": {
      "cardinality": {
        "field": "unique_field",
        "precision_threshold": 20000
      }
    }
  }
}

逻辑对齐说明 #

• 查询阶段的过滤逻辑完全和你的SQL保持一致：先筛选出城市为bratislava、db_date落在指定时间区间的所有文档，不会出现过滤范围的偏差。
• cardinality聚合就是ES中等价SQL去重计数的原生实现，基于HyperLogLog++算法做近似去重统计，搭配precision_threshold参数可以把误差控制在和SQL结果基本无差的水平。

关键注意点 #

• 如果unique_field字段在索引映射中是text类型，聚合时需要指定对应的keyword子字段（比如"field": "unique_field.keyword"），否则会因为分词拆分导致计数结果完全偏离预期。
• precision_threshold的最大可配置值为100000，值越高统计精度越高，但内存开销也会同步上升，日常场景下配置10000~40000的区间，精度就完全可以满足和SQL结果对齐的需求。
• 如果业务要求完全零误差的去重计数，且unique_field的总基数较低，可以将terms聚合的size参数调整到匹配预估基数的数值，通过统计返回的分组数量得到准确结果，但大基数场景下这种方式的性能和内存开销远高于cardinality聚合，不推荐使用。

内容的提问来源于stack exchange，提问作者Orkun