ES实现nested类型聚合以及子聚合排序
ES实现nested类型聚合以及子聚合排序
需求业务说明
针对日销量索引，需要分析品牌、品类等日销量趋势，以及近7天，近30天等销量靠前的品牌、品类等。
涉及到 nested 聚合，以及聚合排序。
指定日期范围价格趋势，销量趋势查询排序
品牌近7天销量
//品类近7天销量
POST /goods_sku_sales2_v20200109/_search
{
"size": 0,
"query": {
"match_all": {
}
},
"aggs": {
"group_by_brand": {
"terms": {
"field": "standardCategoryParentName",
"size": 10,
"order" : { "7days>filter_type>sum_sale" : "desc" }
},
"aggs": {
"7days": {
"nested": {
"path": "salesInfo"
},
"aggs": {
"filter_type": {
"filter": {
"range": {
"salesInfo.saleDt": {
"gte": "2020-01-12",
"lte": "2020-01-18",
"format": "yyyy-MM-dd"
}
}
},
"aggs": {
"sum_sale": {
"sum": {
"field": "salesInfo.salesVolume"
}
}
}
}
}
}
}
}
}
}
品牌近7日销量趋势
//品牌日销量趋势
POST /goods_sku_sales2_v20200615/_search
{
"size": 0,
"query": {
"match": {
"standardBrandName": "完美日记"
}
},
"aggs": {
"by_day": {
"nested": {
"path": "salesInfo"
},
"aggs": {
"filter_type": {
"filter": {
"range": {
"salesInfo.saleDt": {
"gte": "2020-06-10",
"lte": "2020-06-18",
"format": "yyyy-MM-dd"
}
}
},
"aggs": {
"salesInfo_dt": {
"terms": {
"field": "salesInfo.saleDt",
"size": 90
},
"aggs": {
"sum_sale": {
"sum": {
"field": "salesInfo.salesVolume"
}
}
}
}
}
}
}
}
}
}
指定某个sku日期范围销量趋势和价格趋势
GET /goods_sku_sales2_v20200305/_search?pretty
{
"_source": "",
"query": {
"bool": {
"must": [
{
"nested": {
"path": "salesInfo",
"query": {
"bool": {
"must": [
{
"range": {
"salesInfo.saleDt": {
"gte": "2020-01-12",
"lte": "2020-01-18",
"format": "yyyy-MM-dd"
}
}
}
]
}
},
"inner_hits": {
"sort": {
"salesInfo.saleDt": {
"order": "asc"
}
},
"size": 10
}
}
},
{
"match_phrase": {
"skuPk": "00_592671059999_0"
}
}
]
}
}
}
电商商品日销量索引mapping
{
"mapping": {
"_doc": {
"dynamic": "true",
"dynamic_templates": [
{
"integers": {
"match": "salesVolume*",
"mapping": {
"type": "long"
}
}
},
{
"doubles": {
"match": "sales*",
"mapping": {
"type": "double"
}
}
},
{
"keywords": {
"match": "saleDt",
"mapping": {
"type": "keyword"
}
}
}
],
"properties": {
"channel": {
"type": "keyword"
},
"declareIngredientNames": {
"type": "text",
"analyzer": "simple_comma_analyzer",
"fielddata": true
},
"goodsName": {
"type": "text",
"term_vector": "with_positions_offsets",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
},
"analyzer": "ik_max_word",
"search_analyzer": "ik_smart_synonym",
"fielddata": true
},
"goodsPk": {
"type": "keyword"
},
"salesInfo": {
"type": "nested",
"properties": {
"saleDt": {
"type": "keyword"
},
"sales": {
"type": "double"
},
"salesVolume": {
"type": "long"
}
}
},
"shopPk": {
"type": "keyword"
},
"skuPk": {
"type": "keyword"
},
"standardBrandName": {
"type": "keyword",
"normalizer": "lc_normalizer"
},
"standardCategoryNames": {
"type": "keyword"
},
"standardCategoryParentName": {
"type": "keyword"
},
"standardConceptNames": {
"type": "text",
"analyzer": "simple_comma_analyzer",
"fielddata": true
},
"standardEfficacyNames": {
"type": "text",
"analyzer": "simple_comma_analyzer",
"fielddata": true
},
"standardEfficacyParentNames": {
"type": "text",
"analyzer": "simple_comma_analyzer",
"fielddata": true
},
"standardGoodsName": {
"type": "text",
"term_vector": "with_positions_offsets",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
},
"analyzer": "ik_max_word",
"search_analyzer": "ik_smart_synonym",
"fielddata": true
},
"standardIngredientNames": {
"type": "text",
"analyzer": "simple_comma_analyzer",
"fielddata": true
},
"tagNames": {
"type": "text",
"analyzer": "simple_comma_analyzer",
"fielddata": true
}
}
}
}
}
电商商品sku销量索引数据
{
"took": 50,
"timed_out": false,
"_shards": {
"total": 3,
"successful": 3,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 1,
"max_score": 12.854391,
"hits": [
{
"_index": "goods_sku_sales2_v20200109",
"_type": "_doc",
"_id": "01_534144856955_3917259493034",
"_score": 12.854391,
"_source": {
"goodsPk": "01_534144856955",
"channel": "01",
"standardBrandName": "1",
"standardEfficacyNames": "3",
"tagNames": "2",
"standardEfficacyParentNames": "1",
"standardCategoryNames": "1",
"standardCategoryParentName": "1",
"shopPk": "01_151815633",
"salesInfo": [
{
"saleDt": "2019-10-23",
"salesVolume": "12",
"sales": "4440.0"
},
{
"saleDt": "2019-12-30",
"salesVolume": "13",
"sales": "5460.0"
},
{
"saleDt": "2020-01-03",
"salesVolume": "3",
"sales": "1260.0"
},
{
"saleDt": "2019-10-21",
"salesVolume": "231",
"sales": "85470.0"
},
{
"saleDt": "2019-11-06",
"salesVolume": "15",
"sales": "5550.0"
},
{
"saleDt": "2019-11-30",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2020-01-13",
"salesVolume": "3",
"sales": "1260"
},
{
"saleDt": "2019-11-20",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2020-01-19",
"salesVolume": "1",
"sales": "420.0"
},
{
"saleDt": "2019-12-09",
"salesVolume": "3",
"sales": "910.0"
},
{
"saleDt": "2019-11-09",
"salesVolume": "43",
"sales": "15910.0"
},
{
"saleDt": "2020-01-18",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2020-01-14",
"salesVolume": "5",
"sales": "2100"
},
{
"saleDt": "2020-01-05",
"salesVolume": "9",
"sales": "3780.0"
},
{
"saleDt": "2019-10-18",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-12-16",
"salesVolume": "1",
"sales": "420"
},
{
"saleDt": "2019-10-08",
"salesVolume": "4",
"sales": "1760.0"
},
{
"saleDt": "2020-01-02",
"salesVolume": "7",
"sales": "2940.0"
},
{
"saleDt": "2020-01-11",
"salesVolume": "7",
"sales": "2940.0"
},
{
"saleDt": "2019-10-15",
"salesVolume": "1",
"sales": "440.0"
},
{
"saleDt": "2019-11-22",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-10-28",
"salesVolume": "13",
"sales": "4810.0"
},
{
"saleDt": "2020-01-10",
"salesVolume": "7",
"sales": "3048.39"
},
{
"saleDt": "2019-12-21",
"salesVolume": "8",
"sales": "3360"
},
{
"saleDt": "2019-11-11",
"salesVolume": "49",
"sales": "18515"
},
{
"saleDt": "2020-01-12",
"salesVolume": "6",
"sales": "2520"
},
{
"saleDt": "2019-12-19",
"salesVolume": "17",
"sales": "7140"
},
{
"saleDt": "2019-12-29",
"salesVolume": "3",
"sales": "1260.0"
},
{
"saleDt": "2019-11-24",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2020-01-06",
"salesVolume": "11",
"sales": "4620"
},
{
"saleDt": "2019-12-27",
"salesVolume": "9",
"sales": "3780.0"
},
{
"saleDt": "2019-11-27",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-11-12",
"salesVolume": "6",
"sales": "2640"
},
{
"saleDt": "2019-12-18",
"salesVolume": "21",
"sales": "8568.0"
},
{
"saleDt": "2019-11-16",
"salesVolume": "1",
"sales": "440"
},
{
"saleDt": "2019-12-01",
"salesVolume": "1",
"sales": "420.0"
},
{
"saleDt": "2019-12-23",
"salesVolume": "8",
"sales": "3360"
},
{
"saleDt": "2019-12-24",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-10-12",
"salesVolume": "1",
"sales": "440.0"
},
{
"saleDt": "2019-12-12",
"salesVolume": "145",
"sales": "61090.91"
},
{
"saleDt": "2019-11-28",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-12-08",
"salesVolume": "1",
"sales": "420"
},
{
"saleDt": "2019-11-10",
"salesVolume": "29",
"sales": "10730.0"
},
{
"saleDt": "2019-10-27",
"salesVolume": "9",
"sales": "3330.0"
},
{
"saleDt": "2019-10-25",
"salesVolume": "14",
"sales": "5180.0"
},
{
"saleDt": "2019-12-28",
"salesVolume": "5",
"sales": "2100.0"
},
{
"saleDt": "2020-01-08",
"salesVolume": "11",
"sales": "4620"
},
{
"saleDt": "2019-12-26",
"salesVolume": "1",
"sales": "420.0"
},
{
"saleDt": "2019-10-24",
"salesVolume": "7",
"sales": "2590.0"
},
{
"saleDt": "2020-01-04",
"salesVolume": "5",
"sales": "2100.0"
},
{
"saleDt": "2019-12-06",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-10-30",
"salesVolume": "11",
"sales": "3885.0"
},
{
"saleDt": "2019-12-02",
"salesVolume": "3",
"sales": "1260.0"
},
{
"saleDt": "2019-11-29",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-12-04",
"salesVolume": "2",
"sales": "840.0"
},
{
"saleDt": "2019-10-22",
"salesVolume": "25",
"sales": "9250.0"
},
{
"saleDt": "2020-01-17",
"salesVolume": "4",
"sales": "1680.0"
},
{
"saleDt": "2019-10-10",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-12-20",
"salesVolume": "9",
"sales": "3780"
},
{
"saleDt": "2019-10-17",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-10-19",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-11-02",
"salesVolume": "9",
"sales": "3330.0"
},
{
"saleDt": "2019-10-20",
"salesVolume": "1",
"sales": "405"
},
{
"saleDt": "2019-12-15",
"salesVolume": "1",
"sales": "420"
},
{
"saleDt": "2019-10-26",
"salesVolume": "13",
"sales": "4810.0"
},
{
"saleDt": "2019-11-17",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-11-01",
"salesVolume": "14",
"sales": "5180.0"
},
{
"saleDt": "2020-01-15",
"salesVolume": "13",
"sales": "5460"
},
{
"saleDt": "2019-11-26",
"salesVolume": "1",
"sales": "440.0"
},
{
"saleDt": "2019-11-08",
"salesVolume": "109",
"sales": "40330.0"
},
{
"saleDt": "2019-12-07",
"salesVolume": "1",
"sales": "420.0"
},
{
"saleDt": "2019-10-16",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-12-11",
"salesVolume": "4",
"sales": "1400.0"
},
{
"saleDt": "2019-12-17",
"salesVolume": "3",
"sales": "980.0"
},
{
"saleDt": "2019-11-13",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-12-25",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-12-03",
"salesVolume": "1",
"sales": "420.0"
},
{
"saleDt": "2019-11-21",
"salesVolume": "1",
"sales": "440.0"
},
{
"saleDt": "2019-10-31",
"salesVolume": "12",
"sales": "4440.0"
},
{
"saleDt": "2019-10-29",
"salesVolume": "10",
"sales": "3515.0"
},
{
"saleDt": "2019-10-11",
"salesVolume": "2",
"sales": "880.0"
},
{
"saleDt": "2019-12-22",
"salesVolume": "3",
"sales": "1260"
},
{
"saleDt": "2019-10-13",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-12-14",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-11-25",
"salesVolume": "2",
"sales": "880.0"
},
{
"saleDt": "2019-11-15",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-12-10",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-10-14",
"salesVolume": "1",
"sales": "440.0"
},
{
"saleDt": "2019-12-05",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2020-01-16",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-11-03",
"salesVolume": "9",
"sales": "3330.0"
},
{
"saleDt": "2019-11-05",
"salesVolume": "15",
"sales": "5550"
},
{
"saleDt": "2019-11-19",
"salesVolume": "1",
"sales": "440.0"
},
{
"saleDt": "2019-11-18",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2020-01-09",
"salesVolume": "6",
"sales": "2520"
},
{
"saleDt": "2019-11-23",
"salesVolume": "0",
"sales": "0"
},
{
"saleDt": "2019-12-13",
"salesVolume": "7",
"sales": "2940"
},
{
"saleDt": "2020-01-01",
"salesVolume": "8",
"sales": "3360.0"
},
{
"saleDt": "2019-12-31",
"salesVolume": "10",
"sales": "4200.0"
},
{
"saleDt": "2020-01-07",
"salesVolume": "18",
"sales": "7560"
},
{
"saleDt": "2019-10-09",
"salesVolume": "4",
"sales": "1540"
},
{
"saleDt": "2019-11-07",
"salesVolume": "11",
"sales": "4070.0"
},
{
"saleDt": "2019-11-14",
"salesVolume": "2",
"sales": "880"
},
{
"saleDt": "2019-11-04",
"salesVolume": "13",
"sales": "4810"
}
],
"goodsName": "iope天才水青春焕妍精华液补水保湿滋润神仙水官方正品 非亦博",
"skuPk": "01_534144856955_3917259493034"
}
}
]
}
}
1328
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
