根据elastic search中过滤器的最后一个条目分组

vxqlmq5t 于 2021-06-13 发布在 ElasticSearch

关注(0)|答案(1)|浏览(359)

我有一个类似于以下的场景：
一个索引，包含商店的已购商品，其中每个商品都有一个订单id。我只需要按每个订单的最后一个商品的颜色进行分组。
数据结构：

{
    "order_id": 1,
    "product_id":235233
    "color": "Blue",
    "purchase_date": "2020-08-21T05:53:43.362Z"
},
{
    "order_id": 1,
    "product_id":2352662
    "color": "Black",
    "purchase_date": "2020-08-23T05:53:43.362Z"
},
{
    "order_id": 2,
    "product_id":855477
    "color": "Blue",
    "purchase_date": "2020-08-22T05:53:43.362Z"
},
{
    "order_id": 2,
    "product_id":322352
    "color": "Red",
    "purchase_date": "2020-08-24T05:53:43.362Z"
},
{
    "order_id": 3,
    "product_id":3225235
    "color": "Red",
    "purchase_date": "2020-08-25T05:53:43.362Z"
}

预期结果
black:1 （订单编号1的最后一个产品的颜色）
red:2 （订单号2、3的最后产品的颜色）
基于这个答案，我可以得到每个订单的最后一个项目作为整个项目，但我要找的是得到项目计数每种颜色直接

POST /items/_search?search_type=count
{
    "aggs": {
        "group": {
            "terms": {
                "field": "order_id"
            },
            "aggs": {
                "group_items": {
                    "top_hits": {
                        "size": 1,
                          "sort": [
                            {
                                "purchase_date": {
                                    "order": "desc"
                                }
                            }
                        ]
                    }
                }
            }
        }
    }
}

下面给出了所有订单的每种颜色的商品数量，而不仅仅是每个订单的最后一个。

GET /items/_search?search_type=count
{
 "size":0,
  "aggs": {
    "colors": {
       "terms": {
        "field": "color.keyword"
        }
     }
  }
}

elasticsearch elasticsearch-aggregation elasticsearch-query

来源：https://stackoverflow.com/questions/65391060/group-by-based-on-last-entry-of-filter-in-elastic-search

1条答案

按热度按时间

s4chpxco1#

你可以使用分组方式 color 按最大值排序 purchase_date 像这样：

{
  "size": 0,
  "aggs": {
    "group": {
      "terms": {
        "field": "color.keyword",
        "order": {
          "by_latest_purchase": "desc"
        }
      },
      "aggs": {
        "by_latest_purchase": {
          "max": {
            "field": "purchase_date"
          }
        }
      }
    }
  }
}

但你最终还是会 blue b/c这是一种存在于你的文件中的颜色，我不知道是否可以过滤掉。
当有疑问（或所有其他措施都失败）时，将脚本化度量聚合用于救援：

{
  "size": 0, 
  "aggs": {
    "by_color": {
      "scripted_metric": {
        "init_script": "state.by_order_id = [:]",
        "map_script": """
          def color = doc['color.keyword'].value;
          def date = doc['purchase_date'].value.millis;
          def order_id = doc['order_id'].value;

          def current_group = ['color':color, 'date': date];

          if (state.by_order_id.containsKey(order_id)) {
            def max_group = state.by_order_id[order_id];
            if (date > max_group.date) {
              // we've found a new maximum
              state.by_order_id[order_id] = current_group
            }
          } else {
            state.by_order_id[order_id] = current_group;
          }
        """,
        "combine_script": """
          def colors_vs_count = [:];

          for (def group : state.by_order_id.entrySet()) {
            def order_id = group.getKey();
            def color = group.getValue()['color'];
            if (colors_vs_count.containsKey(color)) {
              colors_vs_count[color]++;
            } else {
              colors_vs_count[color] = 1;
            }
          }

          return colors_vs_count;
        """,
        "reduce_script": "return states"
      }
    }
  }
}

顺从的：

...
"aggregations" : {
  "by_color" : {
    "value" : [
      {
        "Red" : 2,
        "Black" : 1
      }
    ]
  }
}

下面是一个json友好的精简版脚本：

{"size":0,"aggs":{"by_color":{"scripted_metric":{"init_script":"state.by_order_id = [:]","map_script":"          def color = doc['color.keyword'].value;\n          def date = doc['purchase_date'].value.millis;\n          def order_id = doc['order_id'].value;\n          \n          def current_group = ['color':color, 'date': date];\n          \n          if (state.by_order_id.containsKey(order_id)) {\n            def max_group = state.by_order_id[order_id];\n            if (date > max_group.date) {\n              state.by_order_id[order_id] = current_group\n            }\n          } else {\n            state.by_order_id[order_id] = current_group;\n          }","combine_script":"          def colors_vs_count = [:];\n          \n          for (def group : state.by_order_id.entrySet()) {\n            def order_id = group.getKey();\n            def color = group.getValue()['color'];\n            if (colors_vs_count.containsKey(color)) {\n              colors_vs_count[color]++;\n            } else {\n              colors_vs_count[color] = 1;\n            }\n          }\n          \n          return colors_vs_count;","reduce_script":"return states"}}}}

赞(0）回复(0）举报 2021-06-13

我来回答

根据elastic search中过滤器的最后一个条目分组

1条答案

相关问题

热门标签

最新问答