ElasticSearch聚合+搜索语法学习
目录
- 搜索+聚合：统计指定品牌下每个颜色的销量
- global bucket：单个品牌与所有品牌销量对比
- 过滤+聚合：统计价格大于1200的电视平均价格
- bucket filter：统计牌品最近一个月的平均价格
- 排序：按每种颜色的平均销售额降序排序
- 颜色+品牌下钻分析时按最深层metric进行排序
- cardinality去重算法以及每月销售品牌数量统计
- cardinality算法之优化内存开销以及HLL算法
1. 搜索+聚合：统计指定品牌下每个颜色的销量
- 实际上来说，我们之前学习的搜索相关的知识，完全可以和聚合组合起来使用
select count(*)
from tvs.sales
where brand like "%长%"
group by price- es aggregation，scope，任何的聚合，都必须在搜索出来的结果数据中之行，搜索结果，就是聚合分析操作的scope
GET /tvs/sales/_search
{
"size": 0,
"query": {
"term": {
"brand": {
"value": "小米"
}
}
},
"aggs": {
"group_by_color": {
"terms": {
"field": "color"
}
}
}
}{
"took": 5,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"failed": 0
},
"hits": {
"total": 2,
"max_score": 0,
"hits": []
},
"aggregations": {
"group_by_color": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "绿色",
"doc_count": 1
},
{
"key": "蓝色",
"doc_count": 1
}
]
}
}
}2. global bucket：单个品牌与所有品牌销量对比
- aggregation，scope，一个聚合操作，必须在query的搜索结果范围内执行
GET /tvs/sales/_search
{
"size": 0,
"query": {
"term": {
"brand": {
"value": "长虹"
}
}
},
"aggs": {
"single_brand_avg_price": {
"avg": {
"field": "price"
}
},
"all": {
"global": {},
"aggs": {
"all_brand_avg_price": {
"avg": {
"field": "price"
}
}
}
}
}
}- global：就是global bucket，就是将所有数据纳入聚合的scope，而不管之前的query
- 出来两个结果，一个结果，是基于query搜索结果来聚合的; 一个结果，是对所有数据执行聚合的
{
"took": 4,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"failed": 0
},
"hits": {
"total": 3,
"max_score": 0,
"hits": []
},
"aggregations": {
"all": {
"doc_count": 8,
"all_brand_avg_price": {
"value": 2650
}
},
"single_brand_avg_price": {
"value": 1666.6666666666667
}
}
}- single_brand_avg_price：就是针对query搜索结果，执行的，拿到的，就是长虹品牌的平均价格
- all.all_brand_avg_price：拿到所有品牌的平均价格
3. 过滤+聚合：统计价格大于1200的电视平均价格
GET /tvs/sales/_search
{
"size": 0,
"query": {
"constant_score": {
"filter": {
"range": {
"price": {
"gte": 1200
}
}
}
}
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
}
}
}{
"took": 41,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"failed": 0
},
"hits": {
"total": 7,
"max_score": 0,
"hits": []
},
"aggregations": {
"avg_price": {
"value": 2885.714285714286
}
}
}4. bucket filter：统计牌品最近一个月的平均价格
GET /tvs/sales/_search
{
"size": 0,
"query": {
"term": {
"brand": {
"value": "长虹"
}
}
},
"aggs": {
"recent_150d": {
"filter": {
"range": {
"sold_date": {
"gte": "now-150d"
}
}
},
"aggs": {
"recent_150d_avg_price": {
"avg": {
"field": "price"
}
}
}
},
"recent_140d": {
"filter": {
"range": {
"sold_date": {
"gte": "now-140d"
}
}
},
"aggs": {
"recent_140d_avg_price": {
"avg": {
"field": "price"
}
}
}
},
"recent_130d": {
"filter": {
"range": {
"sold_date": {
"gte": "now-130d"
}
}
},
"aggs": {
"recent_130d_avg_price": {
"avg": {
"field": "price"
}
}
}
}
}
}- aggs.filter，针对的是聚合去做的
- 如果放query里面的filter，是全局的，会对所有的数据都有影响
- 但是，如果，比如说，你要统计，长虹电视，最近1个月的平均值; 最近3个月的平均值; 最近6个月的平均值
- bucket filter：对不同的bucket下的aggs，进行filter
5. 排序：按每种颜色的平均销售额降序排序
- 之前的话，排序，是按照每个bucket的doc_count降序来排的
- 但是假如说，我们现在统计出来每个颜色的电视的销售额，需要按照销售额降序排序？？？？
GET /tvs/sales/_search
{
"size": 0,
"aggs": {
"group_by_color": {
"terms": {
"field": "color"
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
}
}
}
}
}{
"took": 2,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"failed": 0
},
"hits": {
"total": 8,
"max_score": 0,
"hits": []
},
"aggregations": {
"group_by_color": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "红色",
"doc_count": 4,
"avg_price": {
"value": 3250
}
},
{
"key": "绿色",
"doc_count": 2,
"avg_price": {
"value": 2100
}
},
{
"key": "蓝色",
"doc_count": 2,
"avg_price": {
"value": 2000
}
}
]
}
}
}GET /tvs/sales/_search
{
"size": 0,
"aggs": {
"group_by_color": {
"terms": {
"field": "color",
"order": {
"avg_price": "asc"
}
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
}
}
}
}
}6. 颜色+品牌下钻分析时按最深层metric进行排序
GET /tvs/sales/_search
{
"size": 0,
"aggs": {
"group_by_color": {
"terms": {
"field": "color"
},
"aggs": {
"group_by_brand": {
"terms": {
"field": "brand",
"order": {
"avg_price": "desc"
}
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
}
}
}
}
}
}
}7. cardinality去重算法以及每月销售品牌数量统计
- 去重，cartinality metric，对每个bucket中的指定的field进行去重，取去重后的count，类似于count(distcint)
GET /tvs/sales/_search
{
"size" : 0,
"aggs" : {
"months" : {
"date_histogram": {
"field": "sold_date",
"interval": "month"
},
"aggs": {
"distinct_colors" : {
"cardinality" : {
"field" : "brand"
}
}
}
}
}
}{
"took": 70,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"failed": 0
},
"hits": {
"total": 8,
"max_score": 0,
"hits": []
},
"aggregations": {
"group_by_sold_date": {
"buckets": [
{
"key_as_string": "2016-05-01T00:00:00.000Z",
"key": 1462060800000,
"doc_count": 1,
"distinct_brand_cnt": {
"value": 1
}
},
{
"key_as_string": "2016-06-01T00:00:00.000Z",
"key": 1464739200000,
"doc_count": 0,
"distinct_brand_cnt": {
"value": 0
}
},
{
"key_as_string": "2016-07-01T00:00:00.000Z",
"key": 1467331200000,
"doc_count": 1,
"distinct_brand_cnt": {
"value": 1
}
},
{
"key_as_string": "2016-08-01T00:00:00.000Z",
"key": 1470009600000,
"doc_count": 1,
"distinct_brand_cnt": {
"value": 1
}
},
{
"key_as_string": "2016-09-01T00:00:00.000Z",
"key": 1472688000000,
"doc_count": 0,
"distinct_brand_cnt": {
"value": 0
}
},
{
"key_as_string": "2016-10-01T00:00:00.000Z",
"key": 1475280000000,
"doc_count": 1,
"distinct_brand_cnt": {
"value": 1
}
},
{
"key_as_string": "2016-11-01T00:00:00.000Z",
"key": 1477958400000,
"doc_count": 2,
"distinct_brand_cnt": {
"value": 1
}
},
{
"key_as_string": "2016-12-01T00:00:00.000Z",
"key": 1480550400000,
"doc_count": 0,
"distinct_brand_cnt": {
"value": 0
}
},
{
"key_as_string": "2017-01-01T00:00:00.000Z",
"key": 1483228800000,
"doc_count": 1,
"distinct_brand_cnt": {
"value": 1
}
},
{
"key_as_string": "2017-02-01T00:00:00.000Z",
"key": 1485907200000,
"doc_count": 1,
"distinct_brand_cnt": {
"value": 1
}
}
]
}
}
}8. cardinality算法之优化内存开销以及HLL算法
1. cardinality解析
- cardinality，count(distinct)，5%的错误率，性能在100ms左右
- precision_threshold优化准确率和内存开销
GET /tvs/sales/_search
{
"size" : 0,
"aggs" : {
"distinct_brand" : {
"cardinality" : {
"field" : "brand",
"precision_threshold" : 100
}
}
}
}- brand去重，如果brand的unique value，在100个以内，小米，长虹，三星，TCL，HTL…
- 在多少个unique value以内，cardinality，几乎保证100%准确
- cardinality算法，会占用precision_threshold * 8 byte 内存消耗，100 * 8 = 800个字节，占用内存很小，而且unique value如果的确在值以内，那么可以确保100%准确
- 100，数百万的unique value，错误率在5%以内
- precision_threshold，值设置的越大，占用内存越大，1000 * 8 = 8000 / 1000 = 8KB，可以确保更多unique value的场景下，100%的准确
- field，去重，count，这时候，unique value，10000，precision_threshold=10000，10000 * 8 = 80000个byte，80KB
2. HyperLogLog++ (HLL)算法性能优化
- cardinality底层算法：HLL算法，HLL算法的性能
- 会对所有的uqniue value取hash值，通过hash值近似去求distcint count，误差
- 默认情况下，发送一个cardinality请求的时候，会动态地对所有的field value，取hash值; 将取hash值的操作，前移到建立索引的时候
PUT /tvs/
{
"mappings": {
"sales": {
"properties": {
"brand": {
"type": "text",
"fields": {
"hash": {
"type": "murmur3"
}
}
}
}
}
}
}GET /tvs/sales/_search
{
"size" : 0,
"aggs" : {
"distinct_brand" : {
"cardinality" : {
"field" : "brand.hash",
"precision_threshold" : 100
}
}
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
