先按字段聚合，然后按另一个字段的合计值
先按字段聚合，然后按另一个字段的合计值
提问于 2019-06-11 00:10:55
我需要按一个字段进行聚合，然后在同一聚合中，用另一个字段值计算sum。但是在执行查询时，第一次聚合是可以的，但总和始终为0。
示例索引：
{
"mappings": {
"transaction": {
"dynamic": "strict",
"properties": {
"transaction": {
"properties": {
"amount": {
"type": "double"
}
}
},
"infrastructureElement": {
"type": "nested",
"properties": {
"infrastructureElementSubType": {
"type": "keyword"
}
}
}
}
}
}
}在下面的查询中，按infrastructureElement.infrastructureElementSubType聚合，然后将值transactionPurchase.amount在另一个aggs中求和：
{
"aggs": {
"group_by_infrastructure_element": {
"nested": {
"path": "infrastructureElement"
},
"aggs": {
"group_by_ie_subtype": {
"terms": {
"field": "infrastructureElement.infrastructureElementSubType"
},
"aggs": {
"revenue": {
"sum": {
"field": "transactionPurchase.amount"
}
}
}
}
}
}
}
}当前结果：
{
"took": 6,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
...
},
"aggregations": {
"group_by_infrastructure_element": {
"doc_count": 365,
"group_by_ie_subtype": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "MOBILE",
"doc_count": 1,
"revenue": {
"value": 0
}
}
]
}
}
}
}提前感谢！
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-06-11 05:59:20
您需要使用Reverse Nested Aggregation，然后在Sum Aggregation中链接来计算您要查找的内容：
聚合查询：
POST <your_index_name>/_search
{
"size":0,
"aggs":{
"myterms":{
"nested":{
"path":"infrastructureElement"
},
"aggs":{
"myterms":{
"terms":{
"field":"infrastructureElement.infrastructureElementSubType",
"size":10
},
"aggs":{
"reverse":{
"reverse_nested":{},
"aggs":{
"revenue":{
"sum":{
"field":"transaction.amount"
}
}
}
}
}
}
}
}
}
}还要注意映射的结构，字段transaction不是Nested Type，而是一个简单的Object Type。现在，如果您在嵌套聚合中，则需要遍历回根，然后执行度量聚合，例如sum，以便计算amount
请注意下面对我创建的示例文档的响应。
POST someaggregation/_doc/1
{
"transaction":{
"amount": 100
},
"infrastructureElement": [
{
"infrastructureElementSubType": "type1"
},
{
"infrastructureElementSubType": "type2"
}
]
}
POST someaggregation/_doc/2
{
"transaction":{
"amount": 100
},
"infrastructureElement": [
{
"infrastructureElementSubType": "type1"
},
{
"infrastructureElementSubType": "type2"
}
]
}
POST someaggregation/_doc/3
{
"transaction":{
"amount": 100
},
"infrastructureElement": [
{
"infrastructureElementSubType": "type3"
},
{
"infrastructureElementSubType": "type4"
}
]
}响应：
{
"took" : 519,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 3,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"myterms" : {
"doc_count" : 6,
"myterms" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "type1",
"doc_count" : 2,
"reverse" : {
"doc_count" : 2,
"revenue" : {
"value" : 200.0
}
}
},
{
"key" : "type2",
"doc_count" : 2,
"reverse" : {
"doc_count" : 2,
"revenue" : {
"value" : 200.0
}
}
},
{
"key" : "type3",
"doc_count" : 1,
"reverse" : {
"doc_count" : 1,
"revenue" : {
"value" : 100.0
}
}
},
{
"key" : "type4",
"doc_count" : 1,
"reverse" : {
"doc_count" : 1,
"revenue" : {
"value" : 100.0
}
}
}
]
}
}
}
}希望这能有所帮助！
如果您认为这解决了您的问题，请随意投票和/或接受此答案:)
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56529910
复制相关文章
相似问题