当前位置： 文江博客话题详情

用子项聚集的巢和聚合

发布于 2025-02-08 01:00:42 字数 9266 浏览 1 评论 0原文

我创建的聚合包括每个术语存储符的总和汇总的术语，并将对总和值进行排序。这很好。但是，如果我添加嵌套术语聚合 这是代码段中的第二个评论块。它会因此错误消息“ {”类型“：” gentregation_execution_exception“，”“原因”：“无效的聚合订单路径[aggcputimems]。}”，然后在我删除“顺序”行之后 聚集

如果

有子 我通过“ QueryHash”创建第二级存储桶组。

为什么我需要在此级别上订购），然后在每个“系统”存储桶中， 订单”和“ Bucketsort”之间的区别是什么

  var response = client.Search<SearchResultBucket>(search => search
                   .Size(0)
                   .RequestConfiguration(r => r.DisableDirectStreaming())
                   .Aggregations(aggContainer => aggContainer
                       .Terms("topLevelAggregation", termsAgg => termsAgg
                           .Field(new Field("System.keyword"))
                           .Size(5)
                           .Aggregations(aggContainer => aggContainer
                               .Sum("AggCPUTimeMS", sumAgg => sumAgg
                                   .Field(new Field("CpuTimeMilliseconds"))
                               )
                               //.BucketSort("sum_bucket_sort", bs => bs
                               //  .Sort(s => s
                               //      .Descending("AggCPUTimeMS")
                               //  )
                               //)
                           )
                           .Order(o => o.Descending("AggCPUTimeMS"))
                           //.Aggregations(aggContainer => aggContainer
                           //  .Terms("SubAggregation1", termsAgg => termsAgg
                           //      .Field(new Field("QueryHash.keyword"))
                           //  )
                           //)
                       )
                   )

？

{
  "idx-au2-prod-sqlcpumonitoring-prod-2022.06.17-000043" : {
    "mappings" : {
      "dynamic_templates" : [
        {
          "message_field" : {
            "path_match" : "message",
            "match_mapping_type" : "string",
            "mapping" : {
              "norms" : false,
              "type" : "text"
            }
          }
        },
        {
          "string_fields" : {
            "match" : "*",
            "match_mapping_type" : "string",
            "mapping" : {
              "fields" : {
                "keyword" : {
                  "ignore_above" : 256,
                  "type" : "keyword"
                }
              },
              "norms" : false,
              "type" : "text"
            }
          }
        }
      ],
      "properties" : {
        "@timestamp" : {
          "type" : "date"
        },
        "@version" : {
          "type" : "keyword"
        },
        "BatchResult" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "ClientAppName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "ClientHostname" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "ClientProcessId" : {
          "type" : "long"
        },
        "CollectSystemDateUtc" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "CollectSystemTimeUtc" : {
          "type" : "date"
        },
        "CpuTimeMilliseconds" : {
          "type" : "long"
        },
        "CurrentVersion" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "DatabaseName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "Duration" : {
          "type" : "long"
        },
        "EventName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "ExeDate" : {
          "type" : "date"
        },
        "IncrementalId" : {
          "type" : "long"
        },
        "LogicalReads" : {
          "type" : "long"
        },
        "Owner" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "OwnerId" : {
          "type" : "long"
        },
        "PhysicalReads" : {
          "type" : "long"
        },
        "Qty" : {
          "type" : "long"
        },
        "QueryHash" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "QueryPlanHash" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "QueryType" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "QueryTypeId" : {
          "type" : "long"
        },
        "ResourceGroupID" : {
          "type" : "long"
        },
        "ResourcePoolID" : {
          "type" : "long"
        },
        "RowCount" : {
          "type" : "long"
        },
        "ServerInstanceName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "ServerPrincipalName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "SessionID" : {
          "type" : "long"
        },
        "SourceTableName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "SqlText" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 32766
            }
          },
          "norms" : false
        },
        "System" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "SystemId" : {
          "type" : "long"
        },
        "TaskTime" : {
          "type" : "long"
        },
        "Writes" : {
          "type" : "long"
        },
        "geoip" : {
          "dynamic" : "true",
          "properties" : {
            "ip" : {
              "type" : "ip"
            },
            "latitude" : {
              "type" : "half_float"
            },
            "location" : {
              "type" : "geo_point"
            },
            "longitude" : {
              "type" : "half_float"
            }
          }
        }
      }
    }
  }
}

块： “ ：

{
  "_index": "idx-au2-prod-sqlcpumonitoring-prod-2022.06.17-000043",
  "_id": "lDoxiYEBssAtKosd_uKJM",
  "_version": 1,
  "_score": 1,
  "_source": {
    "QueryType": "Regular",
    "SqlText": "BACKUP LOG @DbName TO DISK = @FilePath WITH INIT, NOSKIP, RETAINDAYS=1, NAME = 'LogBackup', NO_COMPRESSION;\n\t\t\t",
    "LogicalReads": 417,
    "QueryTypeId": 1,
    "QueryHash": "0",
    "CollectSystemDateUtc": "2022.06.22",
    "DatabaseName": "FO8PRD",
    "ResourceGroupID": 280,
    "System": "Unknown",
    "SessionID": 3027,
    "@version": "1",
    "Writes": 8,
    "ClientAppName": "ServiceRunner",
    "ClientProcessId": 40792,
    "Owner": "CORE",
    "CollectSystemTimeUtc": "2022-06-22T02:02:50.293",
    "QueryPlanHash": "0",
    "Duration": 1133,
    "SystemId": 14,
    "Qty": 1,
    "@timestamp": "2022-06-22T02:15:45.711Z",
    "CpuTimeMilliseconds": 16,
    "EventName": "sp_statement_completed",
    "PhysicalReads": 99,
    "ResourcePoolID": 257,
    "TaskTime": 43216568,
    "CurrentVersion": "2.17.41",
    "RowCount": 8,
    "IncrementalId": 18894454167,
    "ClientHostname": "SPRC-015",
    "ServerInstanceName": "INSTANCE1",
    "ExeDate": "2022-06-16T23:00:00+10",
    "OwnerId": 3,
    "ServerPrincipalName": "Admin"
  }
原文

I created aggregations includes a terms aggregate with a sum aggregate for each term bucket and will sort on the sum value. This works fine. However if I add a nested terms aggregation
which is the second commented block in the code snippet. It fails with this error message "{"type":"aggregation_execution_exception","reason":"Invalid aggregation order path [AggCPUTimeMS].}" and after I remove the "Order" line it works just fine. Is it not possible to sort the bucket by a sum if there is sub aggregation?

To Clarify what I need:

I want top level bucket group by "System" and I pick top n (this is why I need to order on this level) then inside each "System" bucket I create second level buckets group by "QueryHash". So it is not 2 groups at the same level, it is nested groups.

Another question is about the first commented block: what is the difference between "Order" and "BucketSort"? My guess is "Order" will sort the buckets and pick the top n while "BucketSort" will sort the buckets already picked, randomly or not.

  var response = client.Search<SearchResultBucket>(search => search
                   .Size(0)
                   .RequestConfiguration(r => r.DisableDirectStreaming())
                   .Aggregations(aggContainer => aggContainer
                       .Terms("topLevelAggregation", termsAgg => termsAgg
                           .Field(new Field("System.keyword"))
                           .Size(5)
                           .Aggregations(aggContainer => aggContainer
                               .Sum("AggCPUTimeMS", sumAgg => sumAgg
                                   .Field(new Field("CpuTimeMilliseconds"))
                               )
                               //.BucketSort("sum_bucket_sort", bs => bs
                               //  .Sort(s => s
                               //      .Descending("AggCPUTimeMS")
                               //  )
                               //)
                           )
                           .Order(o => o.Descending("AggCPUTimeMS"))
                           //.Aggregations(aggContainer => aggContainer
                           //  .Terms("SubAggregation1", termsAgg => termsAgg
                           //      .Field(new Field("QueryHash.keyword"))
                           //  )
                           //)
                       )
                   )

Mapping here:

{
  "idx-au2-prod-sqlcpumonitoring-prod-2022.06.17-000043" : {
    "mappings" : {
      "dynamic_templates" : [
        {
          "message_field" : {
            "path_match" : "message",
            "match_mapping_type" : "string",
            "mapping" : {
              "norms" : false,
              "type" : "text"
            }
          }
        },
        {
          "string_fields" : {
            "match" : "*",
            "match_mapping_type" : "string",
            "mapping" : {
              "fields" : {
                "keyword" : {
                  "ignore_above" : 256,
                  "type" : "keyword"
                }
              },
              "norms" : false,
              "type" : "text"
            }
          }
        }
      ],
      "properties" : {
        "@timestamp" : {
          "type" : "date"
        },
        "@version" : {
          "type" : "keyword"
        },
        "BatchResult" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "ClientAppName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "ClientHostname" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "ClientProcessId" : {
          "type" : "long"
        },
        "CollectSystemDateUtc" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "CollectSystemTimeUtc" : {
          "type" : "date"
        },
        "CpuTimeMilliseconds" : {
          "type" : "long"
        },
        "CurrentVersion" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "DatabaseName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "Duration" : {
          "type" : "long"
        },
        "EventName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "ExeDate" : {
          "type" : "date"
        },
        "IncrementalId" : {
          "type" : "long"
        },
        "LogicalReads" : {
          "type" : "long"
        },
        "Owner" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "OwnerId" : {
          "type" : "long"
        },
        "PhysicalReads" : {
          "type" : "long"
        },
        "Qty" : {
          "type" : "long"
        },
        "QueryHash" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "QueryPlanHash" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "QueryType" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "QueryTypeId" : {
          "type" : "long"
        },
        "ResourceGroupID" : {
          "type" : "long"
        },
        "ResourcePoolID" : {
          "type" : "long"
        },
        "RowCount" : {
          "type" : "long"
        },
        "ServerInstanceName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "ServerPrincipalName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "SessionID" : {
          "type" : "long"
        },
        "SourceTableName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "SqlText" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 32766
            }
          },
          "norms" : false
        },
        "System" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "SystemId" : {
          "type" : "long"
        },
        "TaskTime" : {
          "type" : "long"
        },
        "Writes" : {
          "type" : "long"
        },
        "geoip" : {
          "dynamic" : "true",
          "properties" : {
            "ip" : {
              "type" : "ip"
            },
            "latitude" : {
              "type" : "half_float"
            },
            "location" : {
              "type" : "geo_point"
            },
            "longitude" : {
              "type" : "half_float"
            }
          }
        }
      }
    }
  }
}

document example:

{
  "_index": "idx-au2-prod-sqlcpumonitoring-prod-2022.06.17-000043",
  "_id": "lDoxiYEBssAtKosd_uKJM",
  "_version": 1,
  "_score": 1,
  "_source": {
    "QueryType": "Regular",
    "SqlText": "BACKUP LOG @DbName TO DISK = @FilePath WITH INIT, NOSKIP, RETAINDAYS=1, NAME = 'LogBackup', NO_COMPRESSION;\n\t\t\t",
    "LogicalReads": 417,
    "QueryTypeId": 1,
    "QueryHash": "0",
    "CollectSystemDateUtc": "2022.06.22",
    "DatabaseName": "FO8PRD",
    "ResourceGroupID": 280,
    "System": "Unknown",
    "SessionID": 3027,
    "@version": "1",
    "Writes": 8,
    "ClientAppName": "ServiceRunner",
    "ClientProcessId": 40792,
    "Owner": "CORE",
    "CollectSystemTimeUtc": "2022-06-22T02:02:50.293",
    "QueryPlanHash": "0",
    "Duration": 1133,
    "SystemId": 14,
    "Qty": 1,
    "@timestamp": "2022-06-22T02:15:45.711Z",
    "CpuTimeMilliseconds": 16,
    "EventName": "sp_statement_completed",
    "PhysicalReads": 99,
    "ResourcePoolID": 257,
    "TaskTime": 43216568,
    "CurrentVersion": "2.17.41",
    "RowCount": 8,
    "IncrementalId": 18894454167,
    "ClientHostname": "SPRC-015",
    "ServerInstanceName": "INSTANCE1",
    "ExeDate": "2022-06-16T23:00:00+10",
    "OwnerId": 3,
    "ServerPrincipalName": "Admin"
  }

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。
列表为空，暂无数据

关于作者

メ斷腸人バ

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

alipaysp_snBf0MSZIv

文章 0 评论 0

梦断已成空

文章 0 评论 0

瞎闹

文章 0 评论 0

凯凯我们等你回来

文章 0 评论 0

寄意

文章 0 评论 0

似梦非梦

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文