网站做seo第一步,小程序开发 上海,网络域名怎么查,宁波公司网页制作针对上面表的数据#xff0c;现在想根据age分组#xff0c;并获取每个分组后的name有哪些(去重后)。
select age, GROUP_CONCAT(DISTINCT(name)) from testtable group by age ;
结果#xff1a; 如果想要增加排序#xff1a;
SELECT age, GROUP_CONCAT(DISTINCT name)…
针对上面表的数据现在想根据age分组并获取每个分组后的name有哪些(去重后)。
select age, GROUP_CONCAT(DISTINCT(name)) from testtable group by age ;
结果 如果想要增加排序
SELECT age, GROUP_CONCAT(DISTINCT name) AS names,COUNT(DISTINCT name) AS name_count
FROM testtable
GROUP BY age
ORDER BY name_count DESC;
结果 使用ES实现类似上面的SQL效果
SearchRequest searchRequest new SearchRequest(indexName);SearchSourceBuilder searchSourceBuilder new SearchSourceBuilder().size(0);BoolQueryBuilder boolQueryBuilder new BoolQueryBuilder();//时间范围RangeQueryBuilder pubtimeBuilder QueryBuilders.rangeQuery(pubtime).gte(stime).lte(etime);boolQueryBuilder.must(pubtimeBuilder);searchSourceBuilder.query(boolQueryBuilder);// 添加 cardinality 聚合 AggregationBuilder cardinalityAggregationBuilder AggregationBuilders.cardinality(unique_organ_id).field(organ_id);// 添加 terms 聚合以获取去重后的具体数据AggregationBuilder termsAggregationBuilder AggregationBuilders.terms(distinct_ids).field(organ_id).size(10000);//根据实际的确定searchSourceBuilder.aggregation(cardinalityAggregationBuilder);searchSourceBuilder.aggregation(termsAggregationBuilder);searchRequest.source(searchSourceBuilder);try {SearchResponse searchResponse elasticsearchClient.search(searchRequest, RequestOptions.DEFAULT);ParsedCardinality uniqueMcnOrganIdAgg searchResponse.getAggregations().get(unique_organ_id);total uniqueMcnOrganIdAgg.getValue();if(total 0){Terms terms searchResponse.getAggregations().get(distinct_ids);for (Terms.Bucket bucket : terms.getBuckets()) {String mcnOrganId bucket.getKeyAsString();ids.add(mcnOrganId);}}} catch (Exception e) {e.printStackTrace();}Result result new Result();result.setIds(ids);result.setTotal(total);return result;
