ElacsticSearch中集合了分组、排序，求和，group by sort sum sum(A*B)

最新推荐文章于 2023-05-14 19:24:45 发布

TianmingQi94

最新推荐文章于 2023-05-14 19:24:45 发布

阅读量504

点赞数

分类专栏： elasticsearch 文章标签： java 开发语言

本文链接：https://blog.csdn.net/TianmingQi94/article/details/126873306

版权

elasticsearch 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

最近做了一个项目，以前是mysql中，现在使用es进行查询

mysql的查询是返回的结果是

1、字段求和的数据

2、字段相乘再求和在除数据（这个问题卡了一天）

3、根据某个字段分组

4、根据某个字段排序

根据思路因为是求和，所以需要用到聚合，其中又进行分组，所以会用到terms

{
    "aggregations":
    {
        "group_by_price": --这个是起的别名，贯穿整个聚合信息
        {
            "terms":
            {
                "field": "manage_channel.keyword"
            },
"其他需要的数据"
        }
    }
}

求单个字段的和

{
    "aggs":
    {
        "sum_premium_sum":--别名
        {
            "sum":
            {
                "field": "premium_sum"
            }
        }
    }
}

两个字段乘积的和

{
    "sum_premium_sum1":--别名
    {
        "sum":--求和
        {
            "script":--必须是这个
            {
                "lang": "expression",--必须是这个
                "source": "doc['premium_sum'] *doc['expect_compensate_rate']" 格式必须是doc[***]
            }
        }
    }
}

计算两个数据乘积的和去除以另一个字段累计的和(很绕口)

{
    "expect_compensate_rate":--别名
    {
        "bucket_script":
        {
            "buckets_path":
            {
                "tmpexpect_compensate1": "12312312312",--重新定义字段名称,这个数据是两个字段相乘的合计
                "tmppremium_sum": "sum_premium_sum"--重新定义字段名称，单个字段相乘的合计
            },
            "script":
            {
                "source": "params.tmpexpect_compensate1/params.tmppremium_sum"--必须前面加上params.
            }
        }
    }
}

这个是整个查询的一个kibana格式看到的。

GET /自己的索引/_search/
{
  "size": 0,
  "query": {
    "bool": { 
      "must": [
        {
          "match": {
            "second_org_code": "213700000000"
          }
        },
        {
          "match": {
            "select_type": "D"
          }
        }
      ]
    }
  },
  "aggregations": {
    "group_by_price": {
      "terms": {
        "field": "manage_channel.keyword"
      },
      "aggs": {
        "sum_premium_sum": {
          "sum": {
            "field": "premium_sum"
          }
        },
        "expect_compensate_rate1": {
          "sum": {
            "script": {
              "lang": "expression",
              "source": "doc['premium_sum'] *doc['expect_compensate_rate']"
            }
          }
        },
        "expect_compensate_rate": {
          "bucket_script": {
            "buckets_path": {
              "tmpexpect_compensate1": "12312312312",
              "tmppremium_sum": "sum_premium_sum"
            },
            "script": {
              "source": "params.tmpexpect_compensate1/params.tmppremium_sum"
            }
          }
        }
      }
    }
  },
  "sort": [
    {
      "manage_channel.keyword": {
        "order": "asc"
      }
    }
  ]
}

为方便大家查看，这个是创建索引的语句，依次执行，不会报错

DELETE /monitor_report_nw

PUT /monitor_report_nw

PUT /monitor_report_nw/_mapping
{
    "properties":
    {
        "BPRM_NCD_NEW":
        {
            "type": "float"
        },
        "MTT_all":
        {
            "type": "text",
            "fields":
            {
                "keyword":
                {
                    "type": "keyword",
                    "ignore_above": 256
                }
            }
        },
        "NETFLAG":
        {
            "type": "text",
            "fields":
            {
                "keyword":
                {
                    "type": "keyword",
                    "ignore_above": 256
                }
            }
        },
        "PREMIUMTAX":
        {
            "type": "text",
            "fields":
            {
                "keyword":
                {
                    "type": "keyword",
                    "ignore_above": 256
                }
            }
        },
        "boundary_cost_rate":
        {
            "type": "float"
        },
        "bprmNcdNew":
        {
            "type": "double"
        },
        "client_risk_level":
        {
            "type": "text",
            "fields":
            {
                "keyword":
                {
                    "type": "keyword",
                    "ignore_above": 256
                }
            }
        },
        "commercial_autonomy_coefficient":
        {
            "type": "float"
        },
        "documentary_expense_rate":
        {
            "type": "float"
        },
        "expect_compensate_rate":
        {
            "type": "double"
        },
        "four_org_code":
        {
            "type": "text",
            "fields":
            {
                "keyword":
                {
                    "type": "keyword",
                    "ignore_above": 256
                }
            }
        },
        "grp_newpower_ind":
        {
            "type": "text",
            "fields":
            {
                "keyword":
                {
                    "type": "keyword",
                    "ignore_above": 256
                }
            }
        },
        "id":
        {
            "type": "keyword",
            "fields":
            {
                "keyword":
                {
                    "type": "keyword",
                    "ignore_above": 256
                }
            }
        },
        "manage_channel":
        {
            "type": "text",
            "fields":
            {
                "keyword":
                {
                    "type": "keyword",
                    "ignore_above": 256
                }
            }
        },
        "mttAll":
        {
            "type": "double"
        },
        "premium_sum":
        {
            "type": "double"
        },
        "premiumtax":
        {
            "type": "double"
        },
        "renewal_years":
        {
            "type": "text",
            "fields":
            {
                "keyword":
                {
                    "type": "keyword",
                    "ignore_above": 256
                }
            }
        },
        "second_org_code":
        {
            "type": "text",
            "fields":
            {
                "keyword":
                {
                    "type": "keyword",
                    "ignore_above": 256
                }
            }
        },
        "select_type":
        {
            "type": "text"
        },
        "total_expected_loss":
        {
            "type": "text",
            "fields":
            {
                "keyword":
                {
                    "type": "keyword",
                    "ignore_above": 256
                }
            }
        },
        "vehicle_use_nature":
        {
            "type": "text",
            "fields":
            {
                "keyword":
                {
                    "type": "keyword",
                    "ignore_above": 256
                }
            }
        }
    }
}

劝君须惜少年时