白话Elasticsearch38深入聚合数据分析之案例实战下钻分析之统计每季度每个品牌的销售额

概述

继续跟中华石杉老师学习ES，第38篇

课程地址： https://www.roncoo.com/view/55

案例

需求： 统计每季度每个品牌的销售额

原始数据：

Step1. 先按照季度进行bucket分组

首先按照季度 bucket分组 ，使用 date_histogram ， 季度interval->quarter

GET /tvs/sales/_search
{"size": 0,"aggs": {"group_by_sold_date": {"date_histogram": {"field": "sold_date","interval": "quarter","min_doc_count": 0,"format": "yyyy-MM-dd","extended_bounds": {"min": "2016-04-01","max": "2017-03-31"}}}}
}


返回：

Step2.然后对每个季度bucket中，下钻 ，按照品牌继续分组,对每个品牌求销售额

GET /tvs/sales/_search
{"size": 0,"aggs": {"group_by_sold_date": {"date_histogram": {"field": "sold_date","interval": "quarter","min_doc_count": 0,"format": "yyyy-MM-dd","extended_bounds": {"min": "2016-04-01","max": "2017-03-31"}},"aggs": {"group_by_brand": {"terms": {"field": "brand"},"aggs": {"brand_sum_price": {"sum": {"field": "price"}}}}}}}
}


返回：

{"took": 9,"timed_out": false,"_shards": {"total": 5,"successful": 5,"skipped": 0,"failed": 0},"hits": {"total": 8,"max_score": 0,"hits": []},"aggregations": {"group_by_sold_date": {"buckets": [{"key_as_string": "2016-04-01","key": 1459468800000,"doc_count": 1,"group_by_brand": {"doc_count_error_upper_bound": 0,"sum_other_doc_count": 0,"buckets": [{"key": "小米","doc_count": 1,"brand_sum_price": {"value": 3000}}]}},{"key_as_string": "2016-07-01","key": 1467331200000,"doc_count": 2,"group_by_brand": {"doc_count_error_upper_bound": 0,"sum_other_doc_count": 0,"buckets": [{"key": "TCL","doc_count": 2,"brand_sum_price": {"value": 2700}}]}},{"key_as_string": "2016-10-01","key": 1475280000000,"doc_count": 3,"group_by_brand": {"doc_count_error_upper_bound": 0,"sum_other_doc_count": 0,"buckets": [{"key": "长虹","doc_count": 3,"brand_sum_price": {"value": 5000}}]}},{"key_as_string": "2017-01-01","key": 1483228800000,"doc_count": 2,"group_by_brand": {"doc_count_error_upper_bound": 0,"sum_other_doc_count": 0,"buckets": [{"key": "三星","doc_count": 1,"brand_sum_price": {"value": 8000}},{"key": "小米","doc_count": 1,"brand_sum_price": {"value": 2500}}]}}]}}
}


找个季度来验证下结果

原始数据：

计算结果中的一部分：

Step3. 其实也还可以计算每个季度所有品牌的 总销售额

GET /tvs/sales/_search
{"size": 0,"aggs": {"group_by_sold_date": {"date_histogram": {"field": "sold_date","interval": "quarter","min_doc_count": 0,"format": "yyyy-MM-dd","extended_bounds": {"min": "2016-04-01","max": "2017-03-31"}},"aggs": {"group_by_brand": {"terms": {"field": "brand"},"aggs": {"brand_sum_price": {"sum": {"field": "price"}}}},"total_sum_price": {"sum": {"field": "price"}}}}}
}

返回：

GET /tvs/sales/_search
{"size": 0,"aggs": {"group_by_sold_date": {"date_histogram": {"field": "sold_date","interval": "quarter","min_doc_count": 0,"format": "yyyy-MM-dd","extended_bounds": {"min": "2016-04-01","max": "2017-03-31"}},"aggs": {"group_by_brand": {"terms": {"field": "brand"},"aggs": {"brand_sum_price": {"sum": {"field": "price"}}}},"total_sum_price": {"sum": {"field": "price"}}}}}
}

继续用2017第一季度来验证下