带聚合的elasticsearch交叉索引查询

我使用：elasticsearch 7.7，kibana 7.7
例如，让我们取两个索引：
具有简单Map的用户索引：

PUT /user_index
{
  "mappings": {
    "properties": {
      "user_id":    { "type": "text" },
      "user_phone":    { "type": "text" },
      "name":   { "type": "text"  }     
    }
  }
}

使用简单Map检查：

PUT /check_index
{
  "mappings": {
    "properties": {
      "user_id":    { "type": "text" },  
      "price":   { "type": "integer"  },
      "goods_count":  {"type": "integer"}
    }
  }
}

我想构建这样的表可视化：

________________________________________________________________________
  user_id  |   user_phone  | average_price       |    sum_goods_count  |
___________|_______________|_____________________|______________________
     1     |       123     |       512           |         64          |
___________|_______________|_____________________|______________________
     2     |       456     |       256           |         16          | 
___________|_______________|_____________________|______________________

所以我的问题是：
是真的吗？
我是否正确地理解了我需要查询这两个索引，得到一个用户列表，然后在一个循环中创建带有支票的购物车？

首先，您应该尽可能地去规范化es中的数据，以获得它所提供的最佳性能和功能，我查看了您提供的示例和问题中的注解，似乎在您的用例中很容易实现，下面的示例中显示了通过组合 user 以及 check 索引为单个索引。
索引Map

{
    "mappings": {
        "properties": {
            "user_id": {
                "type": "text",
                "fielddata": "true"
            },
            "price": {
                "type": "integer"
            },
            "goods_count": {
                "type": "integer"
            }
        }
    }
}

索引数据：
使用上面定义的索引Map，索引这三个文档，其中一个文档具有 "user_id":"1" 有2份文件 "user_id":"2" ```
{
"user_id":"1",
"price":500,
"goods_count":100
}
{
"user_id":"2",
"price":500,
"goods_count":100
}
{
"user_id":"2",
"price":100,
"goods_count":200
}

搜索查询：
请参阅es官方文档中有关术语聚合、热门搜索聚合、总和聚合和平均聚合的详细说明。

{
"size": 0,
"aggs": {
"user": {
"terms": {
"field": "user_id"
},
"aggs": {
"top_user_hits": {
"top_hits": {
"_source": {
"includes": [
"user_id"
]
}
}
},
"avg_price": {
"avg": {
"field": "price"
}
},
"goods_count": {
"sum": {
"field": "goods_count"
}
}
}
}
}
}

搜索结果：

{
"took": 10,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 3,
"relation": "eq"
},
"max_score": null,
"hits": [

},
"aggregations": {
"user": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "2",
"doc_count": 2,
"top_user_hits": {
"hits": {
"total": {
"value": 2,
"relation": "eq"
},
"max_score": 1.0,
"hits": [
{
"_index": "stof_63925596",
"_type": "_doc",
"_id": "2",
"_score": 1.0,
"_source": {
"user_id": "2"
}
},
{
"_index": "stof_63925596",
"_type": "_doc",
"_id": "3",
"_score": 1.0,
"_source": {
"user_id": "2"
}
}
]
}
},
"avg_price": {
"value": 300.0
},
"goods_count": {
"value": 300.0
}
},
{
"key": "1",
"doc_count": 1,
"top_user_hits": {
"hits": {
"total": {
"value": 1,
"relation": "eq"
},
"max_score": 1.0,
"hits": [
{
"_index": "stof_63925596",
"_type": "_doc",
"_id": "1",
"_score": 1.0,
"_source": {
"user_id": "1"
}
}
]
}
},
"avg_price": {
"value": 500.0
},
"goods_count": {
"value": 100.0
}
}
]
}
}
}

正如您在上面的搜索结果中看到的，对于 `"user_id":"2"` 平均价格是 `(500+100)/2 = 300` 和 `goods_count` 是 `100+200 = 300` .
同样适用于 `"user_id":"1"` 平均价格是 `500/1 = 500` 和 `goods_count` 是 `100` .

带聚合的elasticsearch交叉索引查询

1条答案

相关问题

热门标签

最新问答