创建索引及映射
PUT /tvs
PUT /tvs/_mapping
{
"properties":{
"price":{
"type":"long"
},
"color":{
"type":"keyword"
},
"brand":{
"type":"keyword"
},
"sold_date":{
"type":"date"
}
}
}
插入数据
POST /tvs/_bulk
{"index":{}}
{"price":1000,"color":"红色","brand":"长虹","sold_date":"2019-10-28"}
{"index":{}}
{"price":2000,"color":"红色","brand":"长虹","sold_date":"2019-11-05"}
{"index":{}}
{"price":3000,"color":"绿色","brand":"小米","sold_date":"2019-05-18"}
{"index":{}}
{"price":1500,"color":"蓝色","brand":"TCL","sold_date":"2019-07-02"}
{"index":{}}
{"price":1200,"color":"绿色","brand":"TCL","sold_date":"2019-08-19"}
{"index":{}}
{"price":2000,"color":"红色","brand":"长虹","sold_date":"2019-11-05"}
{"index":{}}
{"price":5000,"color":"红色","brand":"三星","sold_date":"2020-01-01"}
{"index":{}}
{"price":2500,"color":"蓝色","brand":"小米","sold_date":"2020-02-12"}
{"index":{}}
{"price":6000,"color":"黑色","brand":"海信","sold_date":"2020-02-18"}
{"index":{}}
{"price":4000,"color":"黑色","brand":"海尔","sold_date":"2020-02-28"}
{"index":{}}
{"price":5000,"color":"白色","brand":"海尔","sold_date":"2020-03-28"}
{"index":{}}
{"price":3500,"color":"黑色","brand":"创维","sold_date":"2020-03-18"}
1、统计哪种种电视的销量最高
GET /tvs/_search
{
"size": 0,
"aggs": {
"popular_color": {
"terms": {
"field": "color"
}
}
}
}
返回结果：
{
"took" : 1031,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 12,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"popular_color" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "红色",
"doc_count" : 4
},
{
"key" : "黑色",
"doc_count" : 3
},
{
"key" : "绿色",
"doc_count" : 2
},
{
"key" : "蓝色",
"doc_count" : 2
},
{
"key" : "白色",
"doc_count" : 1
}
]
}
}
}
返回结果解析：
- hits.hits：指定了size为0，所以hits.hits为空
- aggregations：聚合结果
- popular_color：指定某个聚合的名称
- buckets：根据指定的field划分出bucket
- key：每个bucket对应的值
- doc_count：bucket分组内，有多少数据数量
每种颜色对应的bucket中的数据默认的排序规则：按照doc_count降序排序
2、统计每种颜色电视的平均价格
GET /tvs/_search
{
"size": 0,
"aggs": {
"popular_color": {
"terms": {
"field": "color"
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
}
}
}
}
}
3、每个颜色下的平均价格以及每个颜色下每个品牌的平均价格
GET /tvs/_search
{
"size": 0,
"aggs": {
"group_by_color": {
"terms": {
"field": "color"
},
"aggs": {
"color_avg_price": {
"avg": {
"field": "price"
}
},
"group_by_brand":{
"terms": {
"field": "brand"
},
"aggs": {
"brand_avg_brand": {
"avg": {
"field": "price"
}
}
}
}
}
}
}
}
4、更多的metric
count：bucket，terms，自动就会有一个doc_count，就相当于count。
avg：avg aggs，求平均值。
max：求一个bucket内，指定field值最大的那个数据。
min：求一个bucket内，指定field值最小的那个数据。
sum：求一个bucket内，指定field值的总和。
求出每个颜色的销售数量、平均价格、最大价格、最小价格、价格总和
GET /tvs/_search
{
"size": 0,
"aggs": {
"group_by_color": {
"terms": {
"field": "color"
},
"aggs": {
"color_avg": {
"avg": {
"field": "price"
}
},
"max_price":{
"max": {
"field": "price"
}
},
"min_price":{
"min": {
"field": "price"
}
},
"sum_price":{
"sum": {
"field": "price"
}
}
}
}
}
}
5、划分范围 histogram，求出价格每2000为一个区间，每个区间的销售总额
GET /tvs/_search
{
"size": 0,
"aggs": {
"price_histogram": {
"histogram": {
"field": "price",
"interval": 2000
},
"aggs": {
"income": {
"sum": {
"field": "price"
}
}
}
}
}
}
histogram类似于terms，也是进行bucket分组操作，接收一个field，按照这个field的值的各个范围区间，进行bucket分组操作。
"histogram": {
"field": "price",
"interval": 2000
}
interval：2000，划分范围，0-2000、2000-4000、4000-6000、6000-8000，buckets
bucket有了之后，对每个bucket进行avg、count、sum、max、min等各种metric操作，聚合分析。
6、按照日期聚合分组，求出每个月销售个数
date_histogram：按照我们指定的某个date类型的日期field，以及日期interval，按照一定的日期间隔，去划分bucket。
min_doc_count：即使某个日期interval，如2019-01-01~2019-01-31中一条数据也没有，那么这个区间也是要返回的，不然默认是会过滤掉这个区间。
extended_bounds、min、max：划分bucket的时候，会限定在这个起始日期和结束日期内。
GET /tvs/_search
{
"size": 0,
"aggs": {
"date_sales": {
"date_histogram": {
"field": "sold_date",
"calendar_interval": "month",
"format": "yyyy-MM-dd",
"min_doc_count": 0,
"extended_bounds": {
"min": "2019-01-01",
"max": "2019-12-31"
}
}
}
}
}
7、统计每个季度每个品牌的销售额，及每个季度销售总额
GET /tvs/_search
{
"size": 0,
"aggs": {
"date_sales": {
"date_histogram": {
"field": "sold_date",
"calendar_interval": "quarter",
"format": "yyyy-MM-dd",
"min_doc_count": 0,
"extended_bounds": {
"min": "2019-01-01",
"max": "2020-12-31"
}
},
"aggs": {
"group_by_brand": {
"terms": {
"field": "brand"
},
"aggs": {
"sum_price": {
"sum": {
"field": "price"
}
}
}
},
"total_sum_price":{
"sum":{
"field": "price"
}
}
}
}
}
}
8、搜索与聚合结合，查询某个品牌按颜色销量
aggregation，scope，任何的聚合，都必须在搜索出来的结果数据之中，搜索结果就是聚合分析操作的scope
GET /tvs/_search
{
"size": 0,
"query": {
"term": {
"brand": {
"value": "小米"
}
}
},
"aggs": {
"group_by_color": {
"terms": {
"field": "color"
}
}
}
}
9、global bucket、单个品牌与所有品牌均价对比
aggregation，scope，一个聚合操作，必须在query的搜索结果范围内执行。
出来两个结果，一个结果是基于query搜索结果来聚合的，一个结果是对所有数据执行聚合的。
GET /tvs/_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"
}
}
}
}
}
}
10、过滤+聚合：统计价格大于1200的电视平均价格
搜索+聚合
过滤+聚合
GET /tvs/_search
{
"size": 0,
"query": {
"constant_score": {
"filter": {
"range": {
"price": {
"gte": 1200
}
}
},
"boost": 1.2
}
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
}
}
}
11、bucket filter：统计品牌最近一个月的平均价格
GET /tvs/_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_60d": {
"filter": {
"range": {
"sold_date": {
"gte": "now-60d"
}
}
},
"aggs": {
"recent_60d_avg_price": {
"avg": {
"field": "price"
}
}
}
},
"recent_30d": {
"filter": {
"range": {
"sold_date": {
"gte": "now-30d"
}
}
},
"aggs": {
"recent_30d_avg_price": {
"avg": {
"field": "price"
}
}
}
}
}
}
aggs.filter、针对的是聚合去做的。
如果放query里面的filter是全局的，会对所有的数据都有影响。
但是如果要统计海信最近30天，60天，最近3个月，最近6个月的平均值。
bucket filter：对不同的bucket下的aggs，进行filter。
12、排序、按每种颜色的平均销售额降序排序
GET /tvs/_search
{
"size": 0,
"aggs": {
"group_by_color": {
"terms": {
"field": "color",
"order": {
"avg_price": "desc"
}
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
}
}
}
}
}
13、排序、按每种颜色的每种品牌平均销售额降序排序
GET /tvs/_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"
}
}
}
}
}
}
}
}
