ElasticSearch基于bucket selector实现SQL聚合后的having功能

import java.net.InetAddress;
import java.util.HashMap;
import java.util.Map;
import org.elasticsearch.action.search.SearchRequestBuilder;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.client.transport.TransportClient;
import org.elasticsearch.common.settings.Settings;
import org.elasticsearch.common.transport.InetSocketTransportAddress;
import org.elasticsearch.common.transport.TransportAddress;
import org.elasticsearch.index.query.QueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.script.Script;
import org.elasticsearch.search.aggregations.AggregationBuilder;
import org.elasticsearch.search.aggregations.AggregationBuilders;
import org.elasticsearch.search.aggregations.bucket.terms.Terms;
import org.elasticsearch.search.aggregations.metrics.avg.Avg;
import org.elasticsearch.search.aggregations.metrics.avg.AvgAggregationBuilder;
import org.elasticsearch.search.aggregations.pipeline.PipelineAggregatorBuilders;
import org.elasticsearch.search.aggregations.pipeline.bucketselector.BucketSelectorPipelineAggregationBuilder;
import org.elasticsearch.transport.client.PreBuiltTransportClient;

/**
 * 基于bucket selector实现having功能
 * <p>
 * 模拟如下SQL查询
 * </p>
 * <code>
 * select 
 *    team,
 *    avg(age) as avg_age 
 *    count(*) as docCount
 * from 
 *    a_person 
 * where 
 *    salary >=0 
 * group by
 *    team 
 * having
 *    avg(age) >30 
 * order by
 *    team asc
 * </code>
 * <p>
 *  参考教程：
 *  <i>https://www.icode9.com/content-2-60558.html</i>
 *  <i>https://blog.csdn.net/tuposky/article/details/81002526</i>
 * </p>
 */
public class AggsGroupByHaving {

	private static final String clusterName = "cluster";
	private static final String serverAddress = "192.168.0.170";
	private static final int serverPort = 9300;

	private static String ALL_INDEX = "*";

	public static void main(String[] args) throws Exception {
		Settings settings = Settings.builder()
				.put("cluster.name", clusterName)
				.put("client.transport.sniff", true)
				.build();

		TransportAddress transportAddress = new InetSocketTransportAddress(InetAddress.getByName(serverAddress), serverPort);
		TransportClient transportlient = new PreBuiltTransportClient(settings).addTransportAddress(transportAddress);
		
		// 配置 where salary>=0 条件
		QueryBuilder queryBuilder=QueryBuilders.boolQuery().must(QueryBuilders.rangeQuery("salary").from(0,true));
		
		// 配置分组 group by team
		AvgAggregationBuilder avgAggregationBuilder=AggregationBuilders.avg("avg_age").field("age");
		
		// 配置 having avg(age) > 30
		//(1) 设置脚本
		Script script = new Script("params.avgAge > 30");

		//(2) 声明BucketPath，用于后面的bucket筛选
		Map<String, String> bucketsPathsMap = new HashMap<>(2);
		bucketsPathsMap.put("avgAge", "avg_age");
		
		//(3) 构建bucket selector 实现having条件筛选过滤
		BucketSelectorPipelineAggregationBuilder bs =
		        PipelineAggregatorBuilders.bucketSelector("having", bucketsPathsMap, script);
		
		// 配置聚合 avg(age)
		AggregationBuilder aggregation = AggregationBuilders.terms("agg_team").field("team")
				.subAggregation(avgAggregationBuilder)
				.subAggregation(bs)  //注意这里的bs是紧跟在avgAggregationBuilder的后面，非嵌套
				.order(Terms.Order.term(true))
				.size(Integer.MAX_VALUE);
		
		// 构造查询请求
		SearchRequestBuilder searchRequestBuilder = transportlient.prepareSearch(ALL_INDEX)
				.setTypes("worker")
				.setQuery(queryBuilder)
				.addAggregation(aggregation)
				.setExplain(true)
				.setFrom(0)
				.setSize(2);
		System.out.println(searchRequestBuilder);

		// 发送查询请求
		SearchResponse response = searchRequestBuilder.execute().actionGet();

		// 处理查询结果
		Terms agg = response.getAggregations().get("agg_team");
		for (Terms.Bucket entry : agg.getBuckets()) {
			String key = (String) entry.getKey(); // bucket key
			long docCount = entry.getDocCount();  // Doc count
			Avg ageAvg = entry.getAggregations().get("avg_age");
			System.out.println("key="+key+",docCount="+docCount+",avg(age)="+ageAvg.getValueAsString());
		}

	}

}