想完成在数据库中这样的操作
SELECT coupon_type_id, count( * ) FROM coupon_statistics_record WHERE DATE_FORMAT( create_time, "%Y-%m" ) = '2020-05' AND coupon_type_id NOT IN ( '1', '2' ) AND counter_code = '111' GROUP BY coupon_type_id
现在用 ElasticsearchRepository 去做了聚合,剩下的 DATE_FORMAT 和 not in 不知道该怎么去做
NativeSearchQueryBuilder queryBuilder = new NativeSearchQueryBuilder();
// 不查询任何结果
queryBuilder.withSourceFilter(new FetchSourceFilter(new String[]{""}, null));
// 1 、添加一个新的聚合,聚合类型为 terms,聚合名称为 brands,聚合字段为 brand
queryBuilder.addAggregation(
AggregationBuilders.terms("couponTypeIds").field("couponTypeId.keyword"));
// 2 、查询,需要把结果强转为 AggregatedPage 类型
AggregatedPage<CouponStatisticsRecordESDto> aggPage = (AggregatedPage<CouponStatisticsRecordESDto>) couponStatisticsRecordESRepository.search(queryBuilder.build());
// 3 、解析
// 3.1 、从结果中取出名为 brands 的那个聚合,
// 因为是利用 String 类型字段来进行的 term 聚合,所以结果要强转为 StringTerm 类型
StringTerms agg = (StringTerms) aggPage.getAggregation("couponTypeIds");
// 3.2 、获取桶
List<StringTerms.Bucket> buckets = agg.getBuckets();
// 3.3 、遍历
for (StringTerms.Bucket bucket : buckets) {
// 3.4 、获取桶中的 key,即品牌名称
System.out.println(bucket.getKeyAsString());
// 3.5 、获取桶中的文档数量
System.out.println(bucket.getDocCount());
}
1
rqxiao OP {
"mapping": { "CouponStatisticsRecord": { "properties": { "consultantCode": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "couponId": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "couponTypeId": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "createTime": { "type": "long" }, "id": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "memberCode": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "verifyExtraInfo": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "verifyTime": { "type": "long" } } } } } |
2
arloor 2020-06-03 15:52:34 +08:00
看下官方文档中 date 和 date_range 这两个数据类型,相信就能解决
|
3
helloZwq 2020-06-03 16:32:55 +08:00 1
GET index/_search
{ "query": { "bool": { "must": [ { "range": { "date": { "gte": "2020-05-03 11:00:00", "lte": "2020-06-03 12:00:00", "format":"yyyy-MM-dd HH:mm:ss" } } } ], "must_not": [ { "terms": { "xxx.keyword": [ "1", "2" ] } } ] } } } |
4
ben1024 2020-06-03 16:43:22 +08:00 1
DATE_FORMAT 可以用 format 来处理
not in 可以用 query string N OT AND 来处理或 must_not 嵌套处理 |