es 聚合函数Expected numeric type on field , but got [keyword]

生产做个平均统计：

POST /system_info/_search?size=10
{
    "query": {
      "bool": {
        "must_not": [
          {
            "term": {
              "runtime": {
                "value": ""
              }
            }
          }
        ],
        "must": [
          {
            "range": {
              "createDate": {
                "gte": 1623686400000,
                "lt": 1642176000000
              }
            }
          },
          {
            "terms": {
              "status": [
                "03",
                "04"
              ]
            }
          }
        ]
      }
      
    }, 
    "aggs" : {
        "avg_runtime" : { 
          "avg" : 
            {
              "field": "runtime",
              "missing": 0
          }
      }
    }
}Expected numeric type on field [runtime], but got [keyword]
{
  "error" : {
    "root_cause" : [
      {
        "type" : "illegal_argument_exception",
        "reason" : "Expected numeric type on field [runtime], but got [keyword]"
      }
    ],
    "type" : "search_phase_execution_exception",
    "reason" : "all shards failed",
    "phase" : "query",
    "grouped" : true,
    "failed_shards" : [
      {
        "shard" : 0,
        "index" : "system_info",
        "node" : "kDAxe6qoQF6QQEIx78PYyA",
        "reason" : {
          "type" : "illegal_argument_exception",
          "reason" : "Expected numeric type on field [runtime], but got [keyword]"
        }
      }
    ],
    "caused_by" : {
      "type" : "illegal_argument_exception",
      "reason" : "Expected numeric type on field [runtime], but got [keyword]",
      "caused_by" : {
        "type" : "illegal_argument_exception",
        "reason" : "Expected numeric type on field [runtime], but got [keyword]"
      }
    }
  },
  "status" : 400
}

//部分索引配置
{
    "system_info":{
        "mappings":{
            "properties":{
               //...
                "runStartTime":{
                    "type":"date"
                },
                "runEndTime":{
                    "type":"date"
                },
                "status":{
                    "type":"keyword"
                },
                "runtime":{//运行的毫秒数
                    "type":"keyword"
                }
                 //...
            }
        }
    }
}

解决办法：

1 修改索引数据类型，显然生产环境不支持，数据量大，线上生产分库分表设计，统计麻烦，ps所有数据都进入了es索引。

2 修改索引配置

这是在进行avg和sum时，字段类型只能是数字类型的，但是我们可以进行数字探测，默认是关闭的。开启方式如下：

PUT system_info/_mapping
{
    "numeric_detection":true
}

3 针对实际场景 利用脚本进行支持，比较开启数字探测有性能损耗。

//size=0 表示只看聚合部分
POST /system_info/_search?size=0
{
    "query": {
      "bool": {
        "must_not": [
          {
            "term": {
              "runtime": {
                "value": ""
              }
            }
          }
        ],
        "must": [
          {
            "range": {
              "createDate": {
                "gte": 1623686400000,
                "lt": 1642176000000
              }
            }
          },
          {
            "terms": {
              "status": [
                "03",
                "04"
              ]
            }
          }
        ]
      }
      
    }, 
    "aggs" : {
        "avg_runtime" : { 
          "avg" : 
            { 
               "script": {
               "lang":   "expression",
               "source": "(doc['ccStartTime'] - doc['ccStartTime'])/1000"//计算秒数
            },
            "missing": 0//缺失用0表示
          }
      }
    }
}