背景:
用户查看推广的店铺。并展示每个店铺下展示3条用户有权推广的商品,并展示推广佣金率。
关键字段
item_id商品ID,shop_id店铺ID,promoter_id用户ID(0代表所有用户都可以推广),rate佣金率。
实现思路:
- 查出所有店铺集合。
- 根据店铺ID集合查出商品,每个店铺查3条,根据佣金率倒序。
下面展示查商品的ES脚本。
# 根据shop_id分组,再根据item_id分组,每个shop_id取3条商品,商品根据rate倒序。
GET /cdc_promoter/_search
{
"query": {
"bool": {
"should": [
{"term": {
"promoter_id": 0
}},
{"term": {
"promoter_id": 305060
}}
],
"minimum_should_match": 1,
"must": [
{
"term": {
"delete_mark": 0
}
}
]
}
},
"size": 0,
"aggs": {
"group_by_shop_id": {
"terms": {
"field": "shop_id",
"size": 2
},
"aggs": {
"group_by_item_id": {
"terms": {
"field": "item_id",
"size": 3
},
"aggs": {
"top_hits_promoter_id": {
"top_hits": {
"size": 1,
"sort": [{
"promoter_id": {
"order": "desc"
}
}]
}
},
"max_rate" : {
"max": {
"field": "rate"
}
},
"sort_page": {
"bucket_sort": {
"sort": [
{
"max_rate": {
"order": "desc"
}
}
],
"from": 0,
"size": 3
}
}
}
}
}
}
}
}
结果
{
"took" : 5,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : 240,
"max_score" : 0.0,
"hits" : [ ]
},
"aggregations" : {
"group_by_shop_id" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 202,
"buckets" : [
{
"key" : 10008700,
"doc_count" : 19,
"group_by_item_id" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 16,
"buckets" : [
{
"key" : 347943,
"doc_count" : 1,
"top_hits_promoter_id" : {
"hits" : {
"total" : 1,
"max_score" : null,
"hits" : [
{
"_index" : "cdc_promoter_202206081400",
"_type" : "_doc",
"_id" : "193",
"_score" : null,
"_source" : {
"id" : 193,
"shop_id" : 10008700,
"item_id" : 347943,
"promoter_id" : 0,
"rate_type" : 0,
"rate" : 5000,
"gmt_created" : "2022-06-08T13:53:06+08:00",
"gmt_modified" : "2022-06-08T15:45:34+08:00",
"delete_mark" : 0,
"delete_timestamp" : 0,
"audit_item_id" : null,
"__op" : "u",
"__null_ts_ms" : 1654674334449,
"seller_user_status" : 5,
"seller_user_delete_mark" : 0,
"seller_expire_time" : "2021-09-15T10:00:27+08:00",
"sales_volume" : 0
},
"sort" : [
0
]
}
]
}
},
"max_rate" : {
"value" : 5000.0
}
},
{
"key" : 348241,
"doc_count" : 1,
"top_hits_promoter_id" : {
"hits" : {
"total" : 1,
"max_score" : null,
"hits" : [
{
"_index" : "cdc_promoter_202206081400",
"_type" : "_doc",
"_id" : "204",
"_score" : null,
"_source" : {
"id" : 204,
"shop_id" : 10008700,
"item_id" : 348241,
"promoter_id" : 0,
"rate_type" : 0,
"rate" : 5000,
"gmt_created" : "2022-06-08T13:53:06+08:00",
"gmt_modified" : "2022-06-08T15:45:35+08:00",
"delete_mark" : 0,
"delete_timestamp" : 0,
"audit_item_id" : null,
"__op" : "u",
"__null_ts_ms" : 1654674335354,
"seller_user_status" : 5,
"seller_user_delete_mark" : 0,
"seller_expire_time" : "2021-11-30T16:54:01+08:00",
"sales_volume" : 0
},
"sort" : [
0
]
}
]
}
},
"max_rate" : {
"value" : 5000.0
}
},
{
"key" : 347899,
"doc_count" : 1,
"top_hits_promoter_id" : {
"hits" : {
"total" : 1,
"max_score" : null,
"hits" : [
{
"_index" : "cdc_promoter_202206081400",
"_type" : "_doc",
"_id" : "205",
"_score" : null,
"_source" : {
"id" : 205,
"shop_id" : 10008700,
"item_id" : 347899,
"promoter_id" : 0,
"rate_type" : 0,
"rate" : 1000,
"gmt_created" : "2022-06-08T13:53:06+08:00",
"gmt_modified" : "2022-06-08T15:45:35+08:00",
"delete_mark" : 0,
"delete_timestamp" : 0,
"audit_item_id" : null,
"__op" : "u",
"__null_ts_ms" : 1654674335418,
"seller_user_status" : 5,
"seller_user_delete_mark" : 0,
"seller_expire_time" : "2021-10-13T11:08:27+08:00",
"sales_volume" : 0
},
"sort" : [
0
]
}
]
}
},
"max_rate" : {
"value" : 1000.0
}
}
]
}
},
{
"key" : 10008984,
"doc_count" : 19,
"group_by_item_id" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 16,
"buckets" : [
{
"key" : 351263,
"doc_count" : 1,
"top_hits_promoter_id" : {
"hits" : {
"total" : 1,
"max_score" : null,
"hits" : [
{
"_index" : "cdc_promoter_202206081400",
"_type" : "_doc",
"_id" : "321",
"_score" : null,
"_source" : {
"id" : 134,
"seller_user_delete_mark" : 0,
"shop_id" : 10008984,
"seller_expire_time" : "2023-04-14T17:40:53+08:00",
"seller_user_status" : 2,
"__op" : "u",
"__null_ts_ms" : 1654739723673,
"item_id" : 351263,
"sales_volume" : 0,
"gmt_modified" : "2022-06-08T15:45:42+08:00",
"rate_type" : 0,
"promoter_id" : 0,
"delete_timestamp" : 0,
"rate" : 2000,
"audit_item_id" : null,
"gmt_created" : "2022-06-08T15:45:42+08:00",
"delete_mark" : 0
},
"sort" : [
0
]
}
]
}
},
"max_rate" : {
"value" : 2000.0
}
},
{
"key" : 351305,
"doc_count" : 1,
"top_hits_promoter_id" : {
"hits" : {
"total" : 1,
"max_score" : null,
"hits" : [
{
"_index" : "cdc_promoter_202206081400",
"_type" : "_doc",
"_id" : "322",
"_score" : null,
"_source" : {
"id" : 134,
"seller_user_delete_mark" : 0,
"shop_id" : 10008984,
"seller_expire_time" : "2023-04-14T17:40:53+08:00",
"seller_user_status" : 2,
"__op" : "u",
"__null_ts_ms" : 1654739723673,
"item_id" : 351305,
"sales_volume" : 0,
"gmt_modified" : "2022-06-08T15:45:42+08:00",
"rate_type" : 0,
"promoter_id" : 0,
"delete_timestamp" : 0,
"rate" : 2000,
"audit_item_id" : null,
"gmt_created" : "2022-06-08T15:45:42+08:00",
"delete_mark" : 0
},
"sort" : [
0
]
}
]
}
},
"max_rate" : {
"value" : 2000.0
}
},
{
"key" : 349943,
"doc_count" : 1,
"top_hits_promoter_id" : {
"hits" : {
"total" : 1,
"max_score" : null,
"hits" : [
{
"_index" : "cdc_promoter_202206081400",
"_type" : "_doc",
"_id" : "325",
"_score" : null,
"_source" : {
"id" : 134,
"seller_user_delete_mark" : 0,
"shop_id" : 10008984,
"seller_expire_time" : "2023-04-14T17:40:53+08:00",
"seller_user_status" : 2,
"__op" : "u",
"__null_ts_ms" : 1654739723673,
"item_id" : 349943,
"sales_volume" : 0,
"gmt_modified" : "2022-06-08T15:45:42+08:00",
"rate_type" : 0,
"promoter_id" : 0,
"delete_timestamp" : 0,
"rate" : 1000,
"audit_item_id" : null,
"gmt_created" : "2022-06-08T15:45:42+08:00",
"delete_mark" : 0
},
"sort" : [
0
]
}
]
}
},
"max_rate" : {
"value" : 1000.0
}
}
]
}
}
]
}
}
}