最近做了一个项目,以前是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
}
}
}
}
}
劝君须惜少年时