Elasticsearch 复杂查询

需求

• 数据内容：id、name、age、subject、score
• 求：
  • 语文的最大分数是多少
  • 所有人的平均分
  • 每个科目有多少人
  • 分数大于80以上的有多少人
  • 每个科目平均分多少

1. 造数据

@Test
public void testMakeData() throws IOException {
    Random random = new Random();
    String[] strings = {"语文", "数据", "英语"};
    for (int i = 0; i <= 100; i++) {
        Map<String, Object> jsonMap = new HashMap<>();
        jsonMap.put("name", "spark" + i);
        jsonMap.put("age", random.nextInt(5) + 10);
        jsonMap.put("subject", strings[random.nextInt(3)]);
        jsonMap.put("score", random.nextInt(40) + 60);
        IndexRequest request = new IndexRequest(INDEX)
                .id(i + "").source(jsonMap);
        System.out.println(jsonMap);
        IndexResponse response = ElasticsaerchUtils.put(request);
    }
}

2. 使用 ES 命令查询

• 建索引语句

PUT bigdata
{
  "mappings": {
    "properties": {
      "age":{
        "type": "long"
      },
      "name":{
        "type": "text"
      },
      "score":{
        "type": "long"
      },
      "subject":{
        "type": "text",
        "fielddata" : true,
        "analyzer": "ik_smart",
        "search_analyzer": "ik_smart"
      }
    }
  }
}

2.1 语文的最大分数是多少

GET bigdata/_search
{
  "query": {
    "match": {
      "subject": "语文"
    }
  },
  "aggs": {
    "stats_balance": {
      "stats": {
        "field": "score"
      }
    }
  },
  "size": 0
}

{
  "took" : 38,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 68,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "stats_balance" : {
      "count" : 68,
      "min" : 61.0,
      "max" : 99.0,
      "avg" : 80.79411764705883,
      "sum" : 5494.0
    }
  }
}

2.2 所有人的平均分

GET bigdata/_search
{
  "aggs": {
    "stats_balance": {
      "stats": {
        "field": "score"
      }
    }
  },
  "size": 0
}

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 101,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "stats_balance" : {
      "count" : 101,
      "min" : 60.0,
      "max" : 99.0,
      "avg" : 81.03960396039604,
      "sum" : 8185.0
    }
  }
}

2.3 每个科目有多少人

GET bigdata/_search
{
  "size": 0,
  "aggs": {
    "group_by_state": {
      "terms": {
        "field": "subject.keyword"
      }
    }
  }
}

{
  "took" : 55,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 101,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "group_by_state" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "语文",
          "doc_count" : 35
        },
        {
          "key" : "数据",
          "doc_count" : 33
        },
        {
          "key" : "英语",
          "doc_count" : 33
        }
      ]
    }
  }
}

2.4 分数大于80以上的有多少人

GET bigdata/_search
{
  "query": {
    "range": {
      "score": {
        "gte": 80
      }
    }
  },
  "aggs": {
    "score": {
      "value_count": {
        "field": "score"
      }
    }
  },
  "size": 0
}

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 53,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "score" : {
      "value" : 53
    }
  }
}

2.5 每个科目平均分多少

GET bigdata/_search
{
	"size": 0,
	"aggs": {
		"group_by_subject": {
			"terms": {
				"field": "subject"
			},
			"aggs": {
				"avg_score": {
					"avg": {
						"field": "score"
					}
				}
			}
		}
	}
}

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 101,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "group_by_subject" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "数学",
          "doc_count" : 36,
          "avg_score" : {
            "value" : 78.72222222222223
          }
        },
        {
          "key" : "英语",
          "doc_count" : 35,
          "avg_score" : {
            "value" : 77.94285714285714
          }
        },
        {
          "key" : "语文",
          "doc_count" : 30,
          "avg_score" : {
            "value" : 79.96666666666667
          }
        }
      ]
    }
  }
}