ElasticSearch中distinct、count和group by的实现

brew安装 elasticsearch

1、brew install elasticsearch
To have launchd start elasticsearch now and restart at login:
 	brew services start elasticsearch
Or, if you don't want/need a background service you can just run:
  elasticsearch
2、启动elasticsearch
	brew services start elasticsearch
	elasticsearch
3、访问：http://localhost:9200

安装elasticsearch-head插件

这个插件可以直观的看到ES运行和数据存储的情况。
1.  下载elasticsearch-head到本地的elasticsearchhead目录下，命令如下：
    git clone git://github.com/mobz/elasticsearch-head.git
2.  切换到刚刚的下载目录下
    cd ⁨⁨elasticsearch-head/
3. 安装elasticsearch-head的依赖
   npm install
4. 启动这个js插件 npm run start
5. 查看ES运行情况
   http://localhost:9100

因为在9100这个端口访问9200这个端口涉及到跨域问题，所以要先配置下ES的跨域允许设置问题

打开ES的配置文件（ /Users/sunww/Documents/JAVA/elasticsearch-6.6.2/config/elasticsearch.yml），输入下面的两句
http.cors.enabled: true
http.cors.allow-origin: "*"

参考原文地址

https://www.jianshu.com/p/62bed9cc8349
https://www.cnblogs.com/Ace-suiyuan008/p/9958552.html

1 - distinct

SELECT DISTINCT(user_id) FROM table WHERE user_id_type = 3;

{
  "query": {
    "term": {
      "user_id_type": 3
    }
  },
  "collapse": {
    "field": "user_id"
  }
}

{
  ...
  "hits": {
    "hits": [
      {
        "_index": "es_qd_mkt_visitor_packet_dev_v1_20180621",
        "_type": "ad_crowd",
        "_source": {
          "user_id": "wx2af8414b502d4ca2_oHtrD0Vxv-_8c678figJNHmtaVQQ",
          "user_id_type": 3
        },
        "fields": {
          "user_id": [
            "wx2af8414b502d4ca2_oHtrD0Vxv-_8c678figJNHmtaVQQ"
          ]
        }
      }
    ]
  }
}

总结：使用collapse字段后，查询结果中[hits]中会出现[fields]字段，其中包含了去重后的user_id

2 - count + distinct

SELECT COUNT(DISTINCT(user_id)) FROM table WHERE user_id_type = 3;

{
  "query": {
    "term": {
      "user_id_type": 3
    }
  },
  "aggs": {
    "count": {
      "cardinality": {
        "field": "user_id"
      }
    }
  }
}

{
  ...
  "hits": {
  ...
  },
  "aggregations": {
    "count": {
      "value": 121
    }
  }
}

总结：aggs中cardinality的字段代表需要distinct的字段

3 - count + group by

SELECT COUNT(user_id) FROM table GROUP BY user_id_type;

{
  "aggs": {
    "user_type": {
      "terms": {
        "field": "user_id_type"
      }
    }
  }
}

{
  ...
  "hits": {
    ...
  },
  "aggregations": {
    "user_type": {
      ...
      "buckets": [
        {
          "key": 4,
          "doc_count": 1220
        },
        {
          "key": 3,
          "doc_count": 488
        }
      ]
    }
  }
}

总结：aggs中terms的字段代表需要gruop by的字段

4 - count + distinct + group by

SELECT COUNT(DISTINCT(user_id)) FROM table GROUP BY user_id_type;

{
  "aggs": {
    "user_type": {
      "terms": {
        "field": "user_id_type"
      },
      "aggs": {
        "count": {
          "cardinality": {
            "field": "user_id"
          }
        }
      }
    }
  }
}

{
  ...
  "hits": {
    ...
  },
  "aggregations": {
    "user_type": {
      ...
      "buckets": [
        {
          "key": 4,
          "doc_count": 1220, //去重前数据1220条
          "count": {
            "value": 276 //去重后数据276条
          }
        },
        {
          "key": 3,
          "doc_count": 488, //去重前数据488条
          "count": {
            "value": 121 //去重后数据121条
          }
        }
      ]
    }
  }
}

5 - count + distinct + group by + where

SELECT COUNT(DISTINCT(user_id)) FROM table WHERE user_id_type = 2 GROUP BY user_id;

总结：对于既有group by又有distinct的查询要求，需要在aggs中嵌套子aggs

6 - 注意事项

collapse关键字

1、折叠功能ES5.3版本之后才发布的。
2、聚合&折叠只能针对keyword类型有效；