創(chuàng)建索引及映射
PUT /tvs
PUT /tvs/_mapping
{
"properties":{
"price":{
"type":"long"
},
"color":{
"type":"keyword"
},
"brand":{
"type":"keyword"
},
"sold_date":{
"type":"date"
}
}
}
插入數(shù)據(jù)
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":"創(chuàng)維","sold_date":"2020-03-18"}
1、統(tǒng)計哪種種電視的銷量最高
GET /tvs/_search
{
"size": 0,
"aggs": {
"popular_color": {
"terms": {
"field": "color"
}
}
}
}
返回結(jié)果：
{
"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
}
]
}
}
}
返回結(jié)果解析：
- hits.hits：指定了size為0捐康，所以hits.hits為空
- aggregations：聚合結(jié)果
- popular_color：指定某個聚合的名稱
- buckets：根據(jù)指定的field劃分出bucket
- key：每個bucket對應(yīng)的值
- doc_count：bucket分組內(nèi)，有多少數(shù)據(jù)數(shù)量
每種顏色對應(yīng)的bucket中的數(shù)據(jù)默認的排序規(guī)則：按照doc_count降序排序
2懂算、統(tǒng)計每種顏色電視的平均價格
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猪落，就相當(dāng)于count昨凡。
avg：avg aggs，求平均值晌该。
max：求一個bucket內(nèi)，指定field值最大的那個數(shù)據(jù)。
min：求一個bucket內(nèi)憋肖，指定field值最小的那個數(shù)據(jù)因痛。
sum：求一個bucket內(nèi)，指定field值的總和岸更。
求出每個顏色的銷售數(shù)量鸵膏、平均價格、最大價格怎炊、最小價格谭企、價格總和
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為一個區(qū)間债查，每個區(qū)間的銷售總額
GET /tvs/_search
{
"size": 0,
"aggs": {
"price_histogram": {
"histogram": {
"field": "price",
"interval": 2000
},
"aggs": {
"income": {
"sum": {
"field": "price"
}
}
}
}
}
}
histogram類似于terms，也是進行bucket分組操作瓜挽，接收一個field攀操，按照這個field的值的各個范圍區(qū)間，進行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、按照日期聚合分組凄鼻，求出每個月銷售個數(shù)
date_histogram：按照我們指定的某個date類型的日期field腊瑟，以及日期interval，按照一定的日期間隔块蚌，去劃分bucket闰非。
min_doc_count：即使某個日期interval，如2019-01-01~2019-01-31中一條數(shù)據(jù)也沒有峭范，那么這個區(qū)間也是要返回的财松，不然默認是會過濾掉這個區(qū)間。
extended_bounds纱控、min辆毡、max：劃分bucket的時候政敢，會限定在這個起始日期和結(jié)束日期內(nèi)。
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胚迫、統(tǒng)計每個季度每個品牌的銷售額喷户，及每個季度銷售總額
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、搜索與聚合結(jié)合访锻，查詢某個品牌按顏色銷量
aggregation褪尝，scope，任何的聚合期犬，都必須在搜索出來的結(jié)果數(shù)據(jù)之中河哑，搜索結(jié)果就是聚合分析操作的scope
GET /tvs/_search
{
"size": 0,
"query": {
"term": {
"brand": {
"value": "小米"
}
}
},
"aggs": {
"group_by_color": {
"terms": {
"field": "color"
}
}
}
}
9、global bucket龟虎、單個品牌與所有品牌均價對比
aggregation璃谨，scope，一個聚合操作鲤妥，必須在query的搜索結(jié)果范圍內(nèi)執(zhí)行佳吞。
出來兩個結(jié)果，一個結(jié)果是基于query搜索結(jié)果來聚合的棉安，一個結(jié)果是對所有數(shù)據(jù)執(zhí)行聚合的底扳。
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、過濾+聚合：統(tǒng)計價格大于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：統(tǒng)計品牌最近一個月的平均價格
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是全局的蒲赂，會對所有的數(shù)據(jù)都有影響阱冶。
但是如果要統(tǒng)計海信最近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"
}
}
}
}
}
}
}
}
