package com.yihu.quota.service.view; import com.fasterxml.jackson.core.type.TypeReference; import com.fasterxml.jackson.databind.ObjectMapper; import com.yihu.ehr.elasticsearch.ElasticSearchPool; import com.yihu.ehr.exception.ApiException; import com.yihu.ehr.query.BaseJpaService; import com.yihu.quota.contants.ViewConstant; import com.yihu.quota.dao.view.ViewDao; import com.yihu.quota.dao.view.ViewDimensionDao; import com.yihu.quota.dao.view.ViewQuotaDao; import com.yihu.quota.dao.view.ViewQuotaFilterDao; import com.yihu.quota.model.cube.Cube; import com.yihu.quota.model.view.View; import com.yihu.quota.model.view.ViewDimension; import com.yihu.quota.model.view.ViewQuota; import com.yihu.quota.model.view.ViewQuotaFilter; import com.yihu.quota.service.cube.CubeService; import com.yihu.quota.vo.ViewQuotaFilterModel; import io.searchbox.core.search.aggregation.DateHistogramAggregation; import io.searchbox.core.search.aggregation.FilterAggregation; import io.searchbox.core.search.aggregation.ValueCountAggregation; import org.apache.commons.lang3.StringUtils; import org.apache.log4j.Logger; import org.elasticsearch.action.search.SearchRequestBuilder; import org.elasticsearch.action.search.SearchResponse; import org.elasticsearch.client.transport.TransportClient; import org.elasticsearch.index.query.BoolQueryBuilder; import org.elasticsearch.index.query.QueryBuilder; import org.elasticsearch.index.query.QueryBuilders; import org.elasticsearch.search.aggregations.AbstractAggregationBuilder; import org.elasticsearch.search.aggregations.AggregationBuilder; import org.elasticsearch.search.aggregations.AggregationBuilders; import org.elasticsearch.search.aggregations.Aggregations; import org.elasticsearch.search.aggregations.bucket.filter.FilterAggregationBuilder; import org.elasticsearch.search.aggregations.bucket.histogram.DateHistogramBuilder; import org.elasticsearch.search.aggregations.bucket.histogram.DateHistogramInterval; import org.elasticsearch.search.aggregations.bucket.histogram.Histogram; import org.elasticsearch.search.aggregations.bucket.terms.LongTerms; import org.elasticsearch.search.aggregations.bucket.terms.Terms; import org.elasticsearch.search.aggregations.bucket.terms.TermsBuilder; import org.elasticsearch.search.aggregations.metrics.MetricsAggregationBuilder; import org.elasticsearch.search.aggregations.metrics.avg.AvgBuilder; import org.elasticsearch.search.aggregations.metrics.cardinality.Cardinality; import org.elasticsearch.search.aggregations.metrics.cardinality.CardinalityBuilder; import org.elasticsearch.search.aggregations.metrics.max.MaxBuilder; import org.elasticsearch.search.aggregations.metrics.min.MinBuilder; import org.elasticsearch.search.aggregations.metrics.sum.SumBuilder; import org.elasticsearch.search.aggregations.metrics.valuecount.ValueCountBuilder; import org.elasticsearch.search.sort.SortBuilder; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.io.IOException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @author janseny * @date 2018年9月26日 */ @Service public class ViewService extends BaseJpaService { private static final Logger logger = Logger.getLogger(ViewService.class); @Autowired private ViewDao viewDao; @Autowired private ViewDimensionDao viewDimensionDao; @Autowired private ViewQuotaDao viewQuotaDao; @Autowired private ViewQuotaFilterDao viewQuotaFilterDao; @Autowired private ElasticSearchPool elasticSearchPool; @Autowired private ObjectMapper objectMapper; @Autowired private CubeService cubeService; public View findOne(String id) { return viewDao.findOne(id); } public View findByCode(String viewCode){ return viewDao.findByCode(viewCode); } /** * 获取视图完整规则 * * @param viewCode 视图编码 * @return 视图规则 * @author 张进军 */ public View getViewRule(String viewCode) { View view = viewDao.findByCode(viewCode); // 视图默认过滤条件 view.setViewFilterList(viewQuotaFilterDao.findByRelationId(view.getId())); // 视图各组组内顶层行维度（升序） List groupTopRowDimensionList = viewDimensionDao.getGroupTopRowDimensionList(view.getId()); view.setGroupTopRowDimensionList(groupTopRowDimensionList); // 视图各组组内其他行维度（升序） Map> groupOtherRowDimensionsMap = new HashMap<>(16); for (ViewDimension viewDimension : groupTopRowDimensionList) { List otherRowDimensionList = viewDimensionDao.getGroupOtherRowDimensionList(view.getId(), viewDimension.getGroupRow()); groupOtherRowDimensionsMap.put(viewDimension.getGroupRow(), otherRowDimensionList); } view.setGroupOtherRowDimensionsMap(groupOtherRowDimensionsMap); // 视图列维度（升序） view.setColDimensionList(viewDimensionDao.getColDimensionList(view.getId())); // 视图指标 view.setViewQuotaList(viewQuotaDao.findByViewId(view.getId())); for (ViewQuota viewQuota : view.getViewQuotaList()) { List quotaFilterList = viewQuotaFilterDao.findByRelationId(viewQuota.getId()); viewQuota.setQuotaFilterList(quotaFilterList); } return view; } /** * 获取视图统计结果 * * @param viewCode 视图编码 * @param filterModelList 页面的过滤条件模型集合 * @return ES聚合对象 * @author 张进军 */ public Aggregations statViewResult(String viewCode, List filterModelList) throws Exception { // 获取视图规则 View view = this.getViewRule(viewCode); String[] esIndices = view.getEsIndex().split(","); String[] esTypes = view.getEsType().split(","); int indexCount = esIndices.length; TransportClient transportClient = elasticSearchPool.getClient(); SearchRequestBuilder searchRequestBuilder = transportClient.prepareSearch(esIndices).setTypes(esTypes).setSize(0); // 组装页面传入的过滤条件 BoolQueryBuilder viewBoolQueryBuilder = QueryBuilders.boolQuery(); this.joinFilters(viewBoolQueryBuilder, filterModelList); // 组装视图默认过滤条件 String viewFilterStr = objectMapper.writeValueAsString(view.getViewFilterList()); List viewFilterModelList = objectMapper.readValue(viewFilterStr, new TypeReference>() { }); this.joinFilters(viewBoolQueryBuilder, viewFilterModelList); searchRequestBuilder.setQuery(viewBoolQueryBuilder); // 视图的顶层行维度 List groupTopRowDimensionList = view.getGroupTopRowDimensionList(); // 视图的组内其他行维度 Map> groupOtherRowDimensionsMap = view.getGroupOtherRowDimensionsMap(); // 视图的列维度 List colDimensionList = view.getColDimensionList(); // 视图的指标 List viewQuotaList = view.getViewQuotaList(); //region 聚合组装 if (groupTopRowDimensionList.size() == 0) { if (!"1".equals(view.getDisplayType())) { // 视图展示为非数值类型场合 throw new ApiException("请为视图至少配置一个行维度。"); } else if (viewQuotaList == null || viewQuotaList.size() == 0) { throw new ApiException("请为视图至少配置一个指标。"); } else { // 视图展示为数值类型的场合 // 如果有多个多维数据集时，添加指标的过滤条件，指定其数据来自哪个多维数据集。 if (indexCount > 1) { for (ViewQuota quota : viewQuotaList) { ViewQuotaFilter indexFilter = new ViewQuotaFilter(); indexFilter.setDimensionCode("_index"); indexFilter.setCompareType("and"); indexFilter.setCompareType("belong"); indexFilter.setFilterValue(quota.getEsIndex()); quota.getQuotaFilterList().add(indexFilter); ViewQuotaFilter typeFilter = new ViewQuotaFilter(); typeFilter.setDimensionCode("_type"); typeFilter.setCompareType("and"); typeFilter.setCompareType("belong"); typeFilter.setFilterValue(quota.getEsType()); quota.getQuotaFilterList().add(typeFilter); } } // 因为该场合没有行/列维度，直接对指标聚合。 List quotaAggList = this.gatherQuotaMetricsAgg(viewQuotaList, null); for (AbstractAggregationBuilder quotaAgg : quotaAggList) { searchRequestBuilder.addAggregation(quotaAgg); } } } else { // 遍历顶层行维度 for (ViewDimension topRowDimension : groupTopRowDimensionList) { // 有多个多维度数据集时，设定顶层行维度聚合的数据来自哪个多维数据集。 FilterAggregationBuilder topFilterAgg = null; if (indexCount > 1) { String aggName = topRowDimension.getDimensionCode() + "-filter"; QueryBuilder indexQueryBuilder = QueryBuilders.termQuery("_index", topRowDimension.getEsIndex()); QueryBuilder typeQueryBuilder = QueryBuilders.termQuery("_type", topRowDimension.getEsType()); BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); boolQueryBuilder.must(indexQueryBuilder); boolQueryBuilder.must(typeQueryBuilder); topFilterAgg = AggregationBuilders.filter(aggName).filter(boolQueryBuilder); } // 顶层行维度聚合 AggregationBuilder topAgg = this.joinDimensionAgg(topRowDimension); // 遍历同组其他行维度，其聚合按顺序嵌套于同组上层行维度聚合下。 // 倒序遍历的上一个行维度聚合 AggregationBuilder preRowAgg = null; List otherRowDimensionList = groupOtherRowDimensionsMap.get(topRowDimension.getGroupRow()); if (otherRowDimensionList != null && otherRowDimensionList.size() > 0) { int otherRowDimensionCount = otherRowDimensionList.size(); for (int i = otherRowDimensionCount - 1; i > 0; i--) { ViewDimension otherRowDimension = otherRowDimensionList.get(i); AggregationBuilder rowAgg = this.joinDimensionAgg(otherRowDimension); if (i == otherRowDimensionCount - 1) { // 末端行维度场合 // 汇总列维度的聚合及嵌套其指标聚合，并列嵌套于末端行维度的聚合。 this.gatherColDimensionsAndQuotasAgg(rowAgg, otherRowDimension, colDimensionList, viewQuotaList); preRowAgg = rowAgg; } else { rowAgg.subAggregation(preRowAgg); preRowAgg = rowAgg; } topAgg.subAggregation(rowAgg); } } else { // 行维度组内只有一个行维度的场合 // 汇总列维度的聚合及嵌套其指标聚合，并列嵌套于顶层行维度的聚合。 this.gatherColDimensionsAndQuotasAgg(topAgg, topRowDimension, colDimensionList, viewQuotaList); } if (topFilterAgg != null) { topFilterAgg.subAggregation(topAgg); searchRequestBuilder.addAggregation(topFilterAgg); } else { searchRequestBuilder.addAggregation(topAgg); } } } //endregion 聚合组装 SearchResponse searchResponse = searchRequestBuilder.get(); Aggregations aggregations = searchResponse.getAggregations(); transportClient.close(); return aggregations; } /** * 拼接视图/指标的过滤条件 * * @param boolQueryBuilder ES布尔查询构建器 * @param filterModelList 视图/指标的过滤条件模型集合 */ private void joinFilters(BoolQueryBuilder boolQueryBuilder, List filterModelList) { if (filterModelList != null && filterModelList.size() != 0) { for (ViewQuotaFilterModel filterModel : filterModelList) { String field = filterModel.getDimensionCode(); Object value = filterModel.getFilterValue(); // 属于与否 if ("belong".equals(filterModel.getCompareType())) { QueryBuilder queryBuilder = QueryBuilders.termQuery(field, value); joinBoolQuery(boolQueryBuilder, queryBuilder, filterModel.getRelationType()); } else if ("not_belong".equals(filterModel.getCompareType())) { QueryBuilder queryBuilder = QueryBuilders.termQuery(field, value); joinBoolQuery(boolQueryBuilder, queryBuilder, "not"); } // 为空与否 else if ("null".equals(filterModel.getCompareType())) { QueryBuilder queryBuilder = QueryBuilders.missingQuery(field).nullValue(true); joinBoolQuery(boolQueryBuilder, queryBuilder, filterModel.getRelationType()); } else if ("not_null".equals(filterModel.getCompareType())) { QueryBuilder queryBuilder = QueryBuilders.missingQuery(field).nullValue(true); joinBoolQuery(boolQueryBuilder, queryBuilder, "not"); } // 包含与否 else if ("null".equals(filterModel.getCompareType())) { QueryBuilder queryBuilder = QueryBuilders.matchQuery(field, value); joinBoolQuery(boolQueryBuilder, queryBuilder, filterModel.getRelationType()); } else if ("not_null".equals(filterModel.getCompareType())) { QueryBuilder queryBuilder = QueryBuilders.matchQuery(field, value); joinBoolQuery(boolQueryBuilder, queryBuilder, "not"); } // 范围 else if ("gt".equals(filterModel.getCompareType())) { QueryBuilder queryBuilder = QueryBuilders.rangeQuery(field).gt(value); joinBoolQuery(boolQueryBuilder, queryBuilder, filterModel.getRelationType()); } else if ("gte".equals(filterModel.getCompareType())) { QueryBuilder queryBuilder = QueryBuilders.rangeQuery(field).gte(value); joinBoolQuery(boolQueryBuilder, queryBuilder, filterModel.getRelationType()); } else if ("lt".equals(filterModel.getCompareType())) { QueryBuilder queryBuilder = QueryBuilders.rangeQuery(field).lt(value); joinBoolQuery(boolQueryBuilder, queryBuilder, filterModel.getRelationType()); } else if ("lte".equals(filterModel.getCompareType())) { QueryBuilder queryBuilder = QueryBuilders.rangeQuery(field).lte(value); joinBoolQuery(boolQueryBuilder, queryBuilder, filterModel.getRelationType()); } } } } /** * 组装且/或/否查询 * * @param boolQueryBuilder ES布尔查询构建器 * @param queryBuilder ES查询构建器 * @param relationType 且/或/非关系类型 */ private void joinBoolQuery(BoolQueryBuilder boolQueryBuilder, QueryBuilder queryBuilder, String relationType) { if ("and".equals(relationType)) { boolQueryBuilder.must(queryBuilder); } else if ("or".equals(relationType)) { boolQueryBuilder.should(queryBuilder); } else if ("not".equals(relationType)) { boolQueryBuilder.mustNot(queryBuilder); } } /** * 组装行/列维度的聚合 * * @param viewDimension 视图行/列维度 * @return 行/列维度的聚合构建器 */ private AggregationBuilder joinDimensionAgg(ViewDimension viewDimension) { AggregationBuilder dimensionAgg = null; String dimensionCode = viewDimension.getDimensionCode(); String orderType = viewDimension.getMemberOrderType(); String orderStrategy = viewDimension.getMemberOrderStrategy(); boolean isAsc = StringUtils.isEmpty(orderStrategy) || "asc".equals(orderStrategy) ? true : false; String memberOrderQuotaId = viewDimension.getMemberOrderQuotaId(); Integer memberCount = viewDimension.getMemberCount(); String fieldType = cubeService.findDimensionDataType(viewDimension.getCubeCode(), dimensionCode); if (!"date".equals(fieldType)) { String aggName = dimensionCode + "-terms"; dimensionAgg = AggregationBuilders.terms(aggName).field(dimensionCode); // 设置排序 if (StringUtils.isEmpty(orderType) || "name".equals(orderType)) { ((TermsBuilder) dimensionAgg).order(Terms.Order.term(isAsc)); } else { // 按子聚合结果排序，拼接子聚合路径 if (StringUtils.isNotEmpty(memberOrderQuotaId)) { ViewQuota viewQuota = viewQuotaDao.findOne(memberOrderQuotaId); if ("basic".equals(viewQuota.getFormulaMode())) { String subAggPath = viewQuota.getBasicFormulaType() + "-result"; ((TermsBuilder) dimensionAgg).order(Terms.Order.aggregation(subAggPath, isAsc)); } else { ((TermsBuilder) dimensionAgg).order(Terms.Order.term(isAsc)); } } } // 设置分组个数 if (memberCount != null && memberCount > 0) { ((TermsBuilder) dimensionAgg).size(memberCount); } else { // TODO 需动态获取全部的分组个数进行设置 ((TermsBuilder) dimensionAgg).size(1000); } } else { String aggName = dimensionCode + "-date_histogram"; dimensionAgg = AggregationBuilders.dateHistogram(aggName).field(dimensionCode); ((DateHistogramBuilder) dimensionAgg).minDocCount(0); ((DateHistogramBuilder) dimensionAgg).format(viewDimension.getMemberDateFormat()); ((DateHistogramBuilder) dimensionAgg).interval(new DateHistogramInterval(viewDimension.getMemberDateInterval())); // 设置排序 Histogram.Order order = isAsc ? Histogram.Order.KEY_ASC : Histogram.Order.KEY_DESC; if (StringUtils.isEmpty(orderType) || "name".equals(orderType)) { ((DateHistogramBuilder) dimensionAgg).order(order); } else { // 按子聚合结果排序，拼接子聚合路径 if (StringUtils.isNotEmpty(memberOrderQuotaId)) { ViewQuota viewQuota = viewQuotaDao.findOne(memberOrderQuotaId); if ("basic".equals(viewQuota.getFormulaMode())) { String subAggPath = viewQuota.getBasicFormulaType() + "-result"; ((DateHistogramBuilder) dimensionAgg).order(Histogram.Order.aggregation(subAggPath, isAsc)); } else { ((DateHistogramBuilder) dimensionAgg).order(order); } } } // TODO 动态根据条件范围设置，或设置默认值 // ((DateHistogramBuilder) dimensionAgg).extendedBounds("", ""); } return dimensionAgg; } /** * 汇总列维度的聚合及嵌套其指标聚合，并列嵌套于上层聚合 * * @param upperAgg 上层维度的聚合构建器 * @param rowViewDimension 视图的行维度 * @param colDimensionList 视图的列维度集合 * @param viewQuotaList 视图的指标集合 * @throws IOException */ private void gatherColDimensionsAndQuotasAgg(AggregationBuilder upperAgg, ViewDimension rowViewDimension, List colDimensionList, List viewQuotaList) throws IOException { if (colDimensionList != null && colDimensionList.size() != 0) { // 组装列维度的聚合 for (ViewDimension colDimension : colDimensionList) { AggregationBuilder calAgg = this.joinDimensionAgg(colDimension); // 在列维度聚合上，嵌套指标的聚合 List quotaAggList = this.gatherQuotaMetricsAgg(viewQuotaList, colDimension); for (AbstractAggregationBuilder quotaAgg : quotaAggList) { calAgg.subAggregation(quotaAgg); } upperAgg.subAggregation(calAgg); } } else { // 没有列维度的场合，在上层行维度聚合上，嵌套指标的聚合 List quotaAggList = this.gatherQuotaMetricsAgg(viewQuotaList, rowViewDimension); for (AbstractAggregationBuilder quotaAgg : quotaAggList) { upperAgg.subAggregation(quotaAgg); } } } /** * 汇总指标的度量聚合 * * @param viewQuotaList 视图的指标 * @param viewDimension 视图的行/列维度 * @return 指标的度量聚合构建器集合 * @throws IOException */ private List gatherQuotaMetricsAgg(List viewQuotaList, ViewDimension viewDimension) throws IOException { List quotaAggList = new ArrayList<>(); for (ViewQuota quota : viewQuotaList) { if ("basic".equals(quota.getFormulaMode())) { String aggNamePre = ""; if (viewDimension == null) { // 视图展示为数值类型的场合 aggNamePre += quota.getCode() + "-"; } if ("sum".equals(quota.getBasicFormulaType())) { // 求和 String aggName = aggNamePre + "sum-result"; SumBuilder sumAgg = AggregationBuilders.sum(aggName).field(quota.getDimensionCode()); quotaAggList.add(this.joinMetricAgg(sumAgg, quota)); } else if ("count".equals(quota.getBasicFormulaType())) { // 计数 String aggName = aggNamePre + "count-result"; ValueCountBuilder countAgg = AggregationBuilders.count(aggName).field(quota.getDimensionCode()); quotaAggList.add(this.joinMetricAgg(countAgg, quota)); } else if ("avg".equals(quota.getBasicFormulaType())) { // 均值 String aggName = aggNamePre + "avg-result"; AvgBuilder avgAgg = AggregationBuilders.avg(aggName).field(quota.getDimensionCode()); quotaAggList.add(this.joinMetricAgg(avgAgg, quota)); } else if ("max".equals(quota.getBasicFormulaType())) { // 最大值 String aggName = aggNamePre + "max-result"; MaxBuilder maxAgg = AggregationBuilders.max(aggName).field(quota.getDimensionCode()); quotaAggList.add(this.joinMetricAgg(maxAgg, quota)); } else if ("min".equals(quota.getBasicFormulaType())) { // 最小值 String aggName = aggNamePre + "min-result"; MinBuilder minAgg = AggregationBuilders.min(aggName).field(quota.getDimensionCode()); quotaAggList.add(this.joinMetricAgg(minAgg, quota)); } else if ("cardinality".equals(quota.getBasicFormulaType())) { // 去重计数 String aggName = aggNamePre + "cardinality-result"; CardinalityBuilder cardinalityAgg = AggregationBuilders.cardinality(aggName).field(quota.getDimensionCode()); quotaAggList.add(this.joinMetricAgg(cardinalityAgg, quota)); } } } return quotaAggList; } /** * 组装度量聚合 * * @param metricAgg ES度量聚合构建器 * @param quota 视图指标 * @throws IOException */ private AbstractAggregationBuilder joinMetricAgg(MetricsAggregationBuilder metricAgg, ViewQuota quota) throws IOException { List quotaFilterList = quota.getQuotaFilterList(); FilterAggregationBuilder filterAgg = null; if (quotaFilterList != null && quotaFilterList.size() > 0) { String quotaFilterStr = objectMapper.writeValueAsString(quotaFilterList); List quotaFilterModelList = objectMapper.readValue(quotaFilterStr, new TypeReference>() { }); BoolQueryBuilder quotaBoolQueryBuilder = QueryBuilders.boolQuery(); this.joinFilters(quotaBoolQueryBuilder, quotaFilterModelList); String aggName = quota.getCode() + "-filter"; filterAgg = AggregationBuilders.filter(aggName).filter(quotaBoolQueryBuilder); filterAgg.subAggregation(metricAgg); } if (filterAgg == null) { return metricAgg; } else { return filterAgg; } } }