Kibana SQL转DSL逻辑咨询：排序不符与动态命名疑问

首先，先明确你的原始SQL请求和生成的DSL结果：

原始SQL请求 #

POST _sql/translate
{
  "query": "SELECT day_of_week, avg(taxful_total_price) FROM kibana_sample_data_ecommerce WHERE customer_id = 52 GROUP BY day_of_week ORDER BY avg(taxful_total_price) DESC LIMIT 2"
}

生成的DSL结果 #

{
  "size" : 0,
  "query" : {
    "term" : {
      "customer_id" : {
        "value" : 52,
        "boost" : 1.0
      }
    }
  },
  "_source" : false,
  "stored_fields" : "_none_",
  "aggregations" : {
    "groupby" : {
      "composite" : {
        "size" : 1000,
        "sources" : [
          {
            "10985" : {
              "terms" : {
                "field" : "day_of_week",
                "missing_bucket" : true,
                "order" : "asc"
              }
            }
          }
        ]
      },
      "aggregations" : {
        "11030" : {
          "avg" : {
            "field" : "taxful_total_price"
          }
        }
      }
    }
  }
}

整体查询逻辑 #

先梳理一下这个SQL的核心意图：

• 过滤出customer_id=52的所有电商订单
• 按day_of_week（星期几）分组
• 计算每组订单的taxful_total_price（含税总价）平均值
• 按这个平均值降序排序，只返回前2个分组

对应的DSL逻辑是：

1. size: 0：告诉Elasticsearch不需要返回原始文档，只需要聚合结果
2. term查询：精准匹配customer_id=52的文档，完成WHERE条件过滤
3. composite聚合：实现SQL的GROUP BY day_of_week，missing_bucket: true表示如果某个星期几没有匹配的订单，也要生成一个空桶
4. 子聚合avg：计算每个分组的taxful_total_price平均值，对应SQL里的avg(taxful_total_price)
5. _source: false和stored_fields: "_none_"：进一步优化性能，不返回任何原始字段数据

解答你的两个疑问 #

1. 为什么SQL指定降序，但DSL里是order: "asc"？ #

这个是最容易误解的点，原因在于：

• DSL里composite聚合的order参数，控制的是分组键（这里是day_of_week）的遍历顺序，而不是最终按聚合值（平均值）的排序顺序。
• 你的SQL要求ORDER BY avg(taxful_total_price) DESC，但Elasticsearch的composite聚合本身不支持直接按子聚合的结果排序。
• Kibana SQL翻译器的处理逻辑是：先让Elasticsearch按day_of_week升序返回所有分组的聚合结果，然后在Kibana的内存中对这些结果按平均值做降序排序，最后应用LIMIT 2返回前2个结果。
• 所以DSL里的asc只是Elasticsearch返回分组的顺序，不影响你最终看到的按平均值降序的结果——排序是在Kibana端完成的，不是ES端。

2. 为什么会出现10985、11030这类随机命名？ #

这些是Kibana SQL翻译器生成的临时内部别名，原因如下：

• 在你的SQL中，day_of_week作为分组键没有显式指定别名，avg(taxful_total_price)这个聚合函数也没有指定别名（比如SELECT day_of_week, avg(taxful_total_price) AS avg_price ...）。
• 翻译器需要给每个聚合和分组键分配一个唯一的键名，用来在DSL中标识它们，但又不会和你的字段名或其他聚合冲突。
• 这些随机数字是每次翻译时生成的唯一字符串，只是内部使用——最终Kibana会把这些别名映射回你SQL中的原始字段/聚合名称，展示给你友好的结果，完全不影响查询的正确性。

如果想让这些别名更清晰，你可以在SQL中给聚合和分组键指定别名，比如：

SELECT day_of_week AS weekday, avg(taxful_total_price) AS avg_total FROM kibana_sample_data_ecommerce WHERE customer_id = 52 GROUP BY weekday ORDER BY avg_total DESC LIMIT 2

这样翻译后的DSL就会用weekday和avg_total作为聚合键名，而不是随机数字了。

内容的提问来源于stack exchange，提问作者cat_on_the_mat