1 准备数据
参考上一篇博客在kibana里造好数据
数据结果如下:
2 AVG(平均值)
2.1 不带where条件求平均
2.1.1 SQL描述
#=======求平均值=======
POST /_xpack/sql?format=txt
{
"query": "SELECT AVG(level) AS avg_level FROM dept"
}
2.1.2 DSL描述
POST /dept/_doc/_search?size=0
{
"aggs":{
"avg_level": {
"avg":{
"field": "level"
}
}
}
}
2.2 带where条件求平均
2.2.1 SQL描述
POST /_xpack/sql?format=txt
{
"query": "SELECT AVG(level) AS avg_level FROM dept WHERE parentId = 3"
}
2.2.2 DSL描述
POST /dept/_doc/_search
{
"size":0,
"query":{
"term":{
"parentId.keyword": "3"
}
},
"aggs":{
"avg_level": {
"avg":{
"field": "level"
}
}
}
}
2.2.3 Java API 描述
public static Double getAvg(QueryBuilder queryBuilder, String field, String... indexs) throws IOException {
getClient();
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
searchSourceBuilder.query(queryBuilder);
searchSourceBuilder.size(0);
AvgAggregationBuilder aggregation = AggregationBuilders.avg("agg").field(field);
searchSourceBuilder.aggregation(aggregation);
SearchRequest searchRequest = new SearchRequest(indexs).types(type);
searchRequest.source(searchSourceBuilder);
SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
Avg agg = searchResponse.getAggregations().get("agg");
return agg.getValue();
}
3 MAX(最大值)
3.1 SQL描述
POST /_xpack/sql?format=txt
{
"query": "SELECT MAX(level) AS max_level FROM dept"
}
3.2 DSL描述
POST dept/_doc/_search?size=0
{
"aggs": {
"max_level": {
"max": {
"field": "level"
}
}
}
}
3.3 Java API 描述
public static Double getMax(QueryBuilder queryBuilder, String field, String... indexs) throws IOException {
getClient();
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
searchSourceBuilder.query(queryBuilder);
searchSourceBuilder.size(0);
AggregationBuilder aggregationBuilder = AggregationBuilders.max("agg").field(field);
searchSourceBuilder.aggregation(aggregationBuilder);
SearchRequest searchRequest = new SearchRequest(indexs).types(type);
searchRequest.source(searchSourceBuilder);
SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
Max agg = searchResponse.getAggregations().get("agg");
return agg.getValue();
}
4 MIN(最小值)
4.1 SQL描述
POST /_xpack/sql?format=txt
{
"query": "SELECT MIN(level) AS min_level FROM dept"
}
4.2 DSL描述
POST dept/_doc/_search?size=0
{
"aggs": {
"min_level": {
"min": {
"field": "level"
}
}
}
}
4.3 Java API 描述
public static Double getMin(QueryBuilder queryBuilder, String field, String... indexs) throws IOException {
getClient();
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
searchSourceBuilder.query(queryBuilder);
searchSourceBuilder.size(0);
MinAggregationBuilder aggregation = AggregationBuilders.min("agg").field(field);
searchSourceBuilder.aggregation(aggregation);
SearchRequest searchRequest = new SearchRequest(indexs).types(type);
searchRequest.source(searchSourceBuilder);
SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
Min agg = searchResponse.getAggregations().get("agg");
return agg.getValue();
}
5 SUM(求和)
5.1 SQL描述
POST /_xpack/sql?format=txt
{
"query": "SELECT SUM(level) AS sum_level FROM dept"
}
5.2 DSL描述
POST dept/_doc/_search?size=0
{
"aggs": {
"sum_level": {
"sum": {
"field": "level"
}
}
}
}
5.3 Java API 描述
public static Double getSum(QueryBuilder queryBuilder, String field, String... indexs) throws IOException {
getClient();
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
searchSourceBuilder.query(queryBuilder);
searchSourceBuilder.size(0);
SumAggregationBuilder aggregation = AggregationBuilders.sum("agg").field(field);
searchSourceBuilder.aggregation(aggregation);
SearchRequest searchRequest = new SearchRequest(indexs).types(type);
searchRequest.source(searchSourceBuilder);
SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
Sum agg = searchResponse.getAggregations().get("agg");
return agg.getValue();
}
6 COUNT(个数)
6.1 SQL描述
6.1.1 COUNT(*)
COUNT(*),包括null和丢失的字段
#=======COUNT(*),包括null和丢失的字段=======
POST /_xpack/sql?format=txt
{
"query": "SELECT COUNT(*) AS all_count FROM dept"
}
上一篇博客有这一类count的java api写法
6.1.2 COUNT(ALL field_name),只统计非null的值
#=======COUNT(ALL field_name),只统计非null的值=======
POST /_xpack/sql?format=txt
{
"query": "SELECT COUNT(ALL status) AS status_count FROM dept"
}
因为有一条数据的status=null,所以不统计它
6.1.3 COUNT(field_name),只统计非null的值
COUNT(field_name)和上面COUNT(ALL field_name)等效
#=======COUNT(field_name),只统计非null的值=======
POST /_xpack/sql?format=txt
{
"query": "SELECT COUNT(status) AS status_count FROM dept"
}
6.1.4 COUNT(DISTINCT field_name),只统计非null的值
#=======COUNT(DISTINCT field_name),只统计非null的值=======
POST /_xpack/sql?format=txt
{
"query": "SELECT COUNT(DISTINCT status) AS status_count FROM dept"
}
status的有效值只有0或者1
6.2 DSL描述
统计包含某一字段(比如status)的文档数,只统计非null的值
POST dept/_doc/_search?size=0
{
"aggs" : {
"status_count" : {
"value_count" : { "field" : "status" } }
}
}
6.3 Java API 描述
统计包含某一字段(比如status)的文档数,只统计非null的值
public static long getCount(QueryBuilder queryBuilder, String field, String... indexs) throws IOException {
getClient();
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
searchSourceBuilder.query(queryBuilder);
searchSourceBuilder.size(0);
ValueCountAggregationBuilder aggregation = AggregationBuilders.count("agg").field(field);
searchSourceBuilder.aggregation(aggregation);
SearchRequest searchRequest = new SearchRequest(indexs).types(type);
searchRequest.source(searchSourceBuilder);
SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
ValueCount agg = searchResponse.getAggregations().get("agg");
return agg.getValue();
}
7 STATS(统计)
7.1 DSL描述
POST /dept/_doc/_search
{
"size":0,
"aggs":{
"stats_level": {
"stats":{
"field": "level"
}
}
}
}
7.2 Java API 描述
public static Stats getStats(QueryBuilder queryBuilder, String field, String... indexs) throws IOException {
getClient();
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
searchSourceBuilder.query(queryBuilder);
searchSourceBuilder.size(0);
StatsAggregationBuilder aggregation = AggregationBuilders.stats("agg").field(field);
searchSourceBuilder.aggregation(aggregation);
SearchRequest searchRequest = new SearchRequest(indexs).types(type);
searchRequest.source(searchSourceBuilder);
SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
Stats stats = searchResponse.getAggregations().get("agg");
return stats;
}