文章目录
1 摘要
MongoDb 如何实现聚合查询?分组查询?分组分页查询?自定义时间区间查询?时间格式转换查询?不要慌,本文将介绍基于 SpringBoot 2.X MongoTemplate 实现各种维度的分组聚合查询,将 MongoDB 的复杂查询难点一网打尽。
2 按照固定字段分组查询
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
/**
* 按照作者分组查询博客数据
*
* @param queryGroupByAuthorParam
* @return
*/
@Override
public ApiResult queryGroupByAuthor(BlogQueryGroupByAuthorParam queryGroupByAuthorParam) {
// 查询条件
Criteria criteria = new Criteria();
if (StrUtil.isNotBlank(queryGroupByAuthorParam.getAuthor())) {
criteria.and("author").is(queryGroupByAuthorParam.getAuthor());
}
MatchOperation matchOperation = Aggregation.match(criteria);
// 查询字段
ProjectionOperation projectionOperation = Aggregation.project("id", "author", "countRead", "countLike");
// 分组统计
GroupOperation groupOperation = Aggregation.group("author")
.first("author").as("author")
.count().as("totalBlog")
.sum("countRead").as("totalRead")
.avg("countRead").as("aveRead")
.sum("countLike").as("totalLike")
.avg("countLike").as("aveLike");
// 查询结果
AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
BlogEntity.class,matchOperation, projectionOperation, groupOperation), BlogSummaryVo.class);
log.info("查询结果: {}", results.getMappedResults());
return ApiResult.success(results.getMappedResults());
}
注意事项:
分组的字段必须要在 ProjectionOperation 中
3 分组分页查询
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
/**
* 按照作者分组分页查询你博客数据
*
* @param queryGroupByAuthorPageParam
* @return
*/
@Override
public ApiResult queryGroupByAuthorPage(BlogQueryGroupByAuthorPageParam queryGroupByAuthorPageParam) {
// 查询条件
Criteria criteria = new Criteria();
MatchOperation matchOperation = Aggregation.match(criteria);
// 查询字段
ProjectionOperation projectionOperation = Aggregation.project("id", "author","countRead", "countLike");
// 分组统计
GroupOperation groupOperation = Aggregation.group("author")
.first("author").as("author")
.count().as("totalBlog")
.sum("countRead").as("totalRead")
.avg("countRead").as("aveRead")
.sum("countLike").as("totalLike")
.avg("countLike").as("aveLike");
// 查询总条数
GroupOperation groupOperation2 = Aggregation.group().count().as("totalAuthor");
AggregationResults<Map> totalAuthorResult = mongoTemplate.aggregate(Aggregation.newAggregation(BlogEntity.class,
matchOperation, projectionOperation, groupOperation, groupOperation2), Map.class);
int totalAuthor = (int) totalAuthorResult.getUniqueMappedResult().getOrDefault("totalAuthor", 0);
// 设置分页信息
SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.DESC, "author"));
SkipOperation skipOperation = Aggregation.skip((queryGroupByAuthorPageParam.getCurrentPage() - 1)
* (long) queryGroupByAuthorPageParam.getPageSize());
LimitOperation limitOperation = Aggregation.limit(queryGroupByAuthorPageParam.getPageSize());
// 查询结果
AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation,
skipOperation, limitOperation), BlogSummaryVo.class);
// 组装分页结果
Page<BlogSummaryVo> page = PageableExecutionUtils.getPage(results.getMappedResults(),
PageRequest.of(queryGroupByAuthorPageParam.getCurrentPage() -1,
queryGroupByAuthorPageParam.getPageSize()), () -> totalAuthor);
return ApiResult.success(page);
}
注意事项:
Spring 的分页类 org.springframework.data.domain.Pageable
是从 0 开始计算第一页的
4 按照日期分组查询(字段为精确到秒的时间戳)
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
/**
* 按照客户端时间分组查询博客数据
*
* @param queryGroupByClientTimestampParam
* @return
*/
@Override
public ApiResult queryGroupByClientTimestamp(BlogQueryGroupByClientTimestampParam
queryGroupByClientTimestampParam) {
// 获取日期分组信息
BlogGroupDateVo groupDateVo = getGroupDate(queryGroupByClientTimestampParam.getDateType());
// 查询条件
Criteria criteria = Criteria.where("clientTimestamp").gte(queryGroupByClientTimestampParam
.getMinClientTimestamp()).lte(queryGroupByClientTimestampParam.getMaxClientTimestamp());
MatchOperation matchOperation = Aggregation.match(criteria);
// 查询字段
ProjectionOperation projectionOperation = Aggregation.project("countRead","countLike","clientTimestamp")
.and(DateOperators.DateToString.dateOf(aggregationOperationContext ->
new Document("$add", Arrays.asList(new Date(28800000),
new Document("$multiply", Arrays.asList("$clientTimestamp", 1000)))))
.toString(groupDateVo.getDateFormat())).as(groupDateVo.getDateGroupField());
// 分组统计
GroupOperation groupOperation = Aggregation.group(groupDateVo.getDateGroupField())
.first(groupDateVo.getDateGroupField()).as("date")
.count().as("totalBlog")
.sum("countRead").as("totalRead")
.avg("countRead").as("aveRead")
.sum("countLike").as("totalLike")
.avg("countLike").as("aveLike");
// 排序
SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "date"));
// 查询结果
AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation),
BlogSummaryVo.class);
return ApiResult.success(results.getMappedResults());
}
注意事项:
MongoDB 的时间是按照 UTC 的时区进行计算的,基本单位为毫秒,国内的时区相对于 UTC 时间要早 8小时,所以在计算时间的时候需要先乘以 1000,再加 8 小时。28800000 = 8 * 60 * 60 * 1000 (毫秒)
5 按照日期分组查询(字段为精确到毫秒的时间戳)
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
/**
* 按照创建时间分组查询博客数据
*
* @param queryGroupByCreateTimeParam
* @return
*/
@Override
public ApiResult queryGroupByCreateTime(BlogQueryGroupByCreateTimeParam queryGroupByCreateTimeParam) {
// 获取日期分组信息
BlogGroupDateVo groupDateVo = getGroupDate(queryGroupByCreateTimeParam.getDateType());
// 查询条件
Criteria criteria = Criteria.where("createTime").gte(queryGroupByCreateTimeParam.getMinCreateTime())
.lte(queryGroupByCreateTimeParam.getMaxCreateTime());
MatchOperation matchOperation = Aggregation.match(criteria);
// 查询字段
ProjectionOperation projectionOperation = Aggregation.project("countRead", "countLike","createTime")
.and(DateOperators.DateToString.dateOf(aggregationOperationContext ->
new Document("$add", Arrays.asList(new Date(28800000),"$createTime")))
.toString(groupDateVo.getDateFormat())).as(groupDateVo.getDateGroupField());
// 分组统计
GroupOperation groupOperation = Aggregation.group(groupDateVo.getDateGroupField())
.first(groupDateVo.getDateGroupField()).as("date")
.count().as("totalBlog")
.sum("countRead").as("totalRead")
.avg("countRead").as("aveRead")
.sum("countLike").as("totalLike")
.avg("countLike").as("aveLike");
// 排序
SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "date"));
// 查询结果
AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation),
BlogSummaryVo.class);
return ApiResult.success(results.getMappedResults());
}
注意事项:
Spring 为 MongoDB 提供的日期转字符串的操作工具类为 org.springframework.data.mongodb.core.aggregation.DateOperators
,该类提供了多种维度的时间区间,包括: 分钟、小时、天、周、月、自定义日期格式等
6 按照日期分组查询(字段为Date)
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
/**
* 按照更新时间分组查询博客数据
*
* @param queryGroupByUpdateTimeParam
* @return
*/
@Override
public ApiResult queryGroupByUpdateTime(BlogQueryGroupByUpdateTimeParam queryGroupByUpdateTimeParam) {
// 查询条件
Criteria criteria = Criteria.where("updateTime").gte(new Date(queryGroupByUpdateTimeParam.getMinUpdateTime()))
.lte(new Date(queryGroupByUpdateTimeParam.getMaxUpdateTime()));
MatchOperation matchOperation = Aggregation.match(criteria);
// 查询字段
ProjectionOperation projectionOperation = Aggregation.project("countRead", "countLike", "updateTime")
.and(DateOperators.DayOfMonth.dayOfMonth("updateTime")).as("day")
.and(DateOperators.Month.monthOf("updateTime")).as("month")
.and(DateOperators.Year.yearOf("updateTime")).as("year");
// 分组统计
GroupOperation groupOperation = Aggregation.group("year","month","day")
.addToSet("year").as("year")
.addToSet("month").as("month")
.addToSet("day").as("date")
.count().as("totalBlog")
.sum("countRead").as("totalRead")
.avg("countRead").as("aveRead")
.sum("countLike").as("totalLike")
.avg("countLike").as("aveLike");
// 排序
SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "_id.year",
"_id.month", "_id.day"));
// 查询结果
AggregationResults<BlogSummaryDiyVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation),
BlogSummaryDiyVo.class);
return ApiResult.success(results.getMappedResults());
}
注意事项:
-
如果数据库字段为
java.util.Date
,则可以直接从字段中获取到日期信息,不需要再进行转换 -
多个字段排序,则需要按照
_id.field
格式书写,否则可能报错;排序优先级是按照字段从左到右的顺序,进行排序
7 按照自定义时间区间分组查询
常用的时间分组区间为小时、天、月,Spring 支持的时间区间已经足够应对,但是针对特定场景,需要自定义时间区间的时候,Spring 自带的时间区间就不够用了。作者也是查遍全网才找到解决方案,赶紧把它记下来,以免下次忘记
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
/**
* 按照创建时间自定义区间分组查询博客数据
*
* @param queryGroupByCreateTimeDiyParam
* @return
*/
@Override
public ApiResult queryGroupByCreateTimeDiy(BlogQueryGroupByCreateTimeDiyParam queryGroupByCreateTimeDiyParam) {
// 查询条件
Criteria criteria = Criteria.where("createTime").gte(queryGroupByCreateTimeDiyParam.getMinCreateTime())
.lte(queryGroupByCreateTimeDiyParam.getMaxCreateTime());
MatchOperation matchOperation = Aggregation.match(criteria);
// 查询字段
ProjectionOperation projectionOperation = Aggregation.project("countRead", "countLike","createTime")
.and(DateOperators.Minute.minuteOf(aggregationOperationContext ->
new Document("$add", Arrays.asList(new Date(28800000),"$createTime"))))
.as("minute")
.and(DateOperators.Hour.hourOf(aggregationOperationContext ->
new Document("$add", Arrays.asList(new Date(28800000),"$createTime"))))
.as("hour")
.and(DateOperators.DateToString.dateOf(aggregationOperationContext ->
new Document("$add", Arrays.asList(new Date(28800000),"$createTime")))
.toString("%Y-%m-%d")).as("day");
ProjectionOperation projectionOperation2 = Aggregation.project("countRead", "countLike",
"createTime", "minute", "hour","day")
.andExpression("minute - minute % 30").as("halfHour");
// 分组统计
GroupOperation groupOperation = Aggregation.group("day","hour","halfHour")
.addToSet("hour").as("hour")
.addToSet("day").as("date")
.addToSet("halfHour").as("minute")
.count().as("totalBlog")
.sum("countRead").as("totalRead")
.avg("countRead").as("aveRead")
.sum("countLike").as("totalLike")
.avg("countLike").as("aveLike");
// 排序
SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "day", "hour", "halfHour"));
// 查询结果
AggregationResults<BlogSummaryDiyVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
BlogEntity.class, matchOperation, projectionOperation, projectionOperation2,
groupOperation, sortOperation), BlogSummaryDiyVo.class);
return ApiResult.success(results.getMappedResults());
}
注意事项:
-
在第二个
ProjectionOperation
中必须包含上一个的所有字段 -
本示例中的时间区间为 30 分钟
8 数据库实体类及其他相关类
8.1 数据库实体类
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/model/entity/BaseEntity.java
package com.ljq.demo.springboot.mongodb.model.entity;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.Id;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.mongodb.core.mapping.Field;
import java.io.Serializable;
import java.util.Date;
/**
* @Description: 基础实体类
* @Author: junqiang.lu
* @Date: 2021/9/24
*/
@Data
public class BaseEntity implements Serializable {
private static final long serialVersionUID = -3003658740476069858L;
/**
* id,主键
*/
@Id
@ApiModelProperty(value = "id,主键", name = "id")
private String id;
/**
* 创建时间
*/
@Field
@CreatedDate
@ApiModelProperty(value = "创建时间", name = "createTime")
private Long createTime;
/**
* 修改时间
*/
@Field
@LastModifiedDate
@ApiModelProperty(value = "修改时间", name = "updateTime")
private Date updateTime;
}
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/model/entity/BlogEntity.java
package com.ljq.demo.springboot.mongodb.model.entity;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.ToString;
import org.springframework.data.mongodb.core.mapping.Document;
import org.springframework.data.mongodb.core.mapping.Field;
/**
* @Description: 博客信息
* @Author: junqiang.lu
* @Date: 2021/11/13
*/
@Data
@ToString(callSuper = true)
@Document(value = "blog")
@ApiModel(value = "博客信息", description = "博客信息")
public class BlogEntity extends BaseEntity {
private static final long serialVersionUID = -2124422309475024490L;
/**
* 标题
*/
@Field
@ApiModelProperty(value = "标题", name = "title")
private String title;
/**
* 作者
*/
@Field
@ApiModelProperty(value = "作者", name = "author")
private String author;
/**
* 内容
*/
@Field
@ApiModelProperty(value = "内容", name = "content")
private String content;
/**
* 阅读数量
*/
@Field
@ApiModelProperty(value = "阅读数量", name = "countRead")
private Integer countRead;
/**
* 点赞数量
*/
@Field
@ApiModelProperty(value = "点赞数量", name = "countLike")
private Integer countLike;
/**
* 客户端时间戳(精确到秒)
*/
@Field
@ApiModelProperty(value = "客户端时间戳(精确到秒)", name = "clientTimestamp")
private Integer clientTimestamp;
}
8.2 统计结果类
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/model/vo/BlogSummaryVo.java
package com.ljq.demo.springboot.mongodb.model.vo;
import com.fasterxml.jackson.annotation.JsonInclude;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.io.Serializable;
/**
* @Description: 博客统计结果
* @Author: junqiang.lu
* @Date: 2021/11/15
*/
@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
@ApiModel(value = "博客统计结果", description = "博客统计结果")
public class BlogSummaryVo implements Serializable {
private static final long serialVersionUID = 6350918498981106620L;
/**
* 作者
*/
@ApiModelProperty(value = "作者", name = "author")
private String author;
/**
* 总博客数量
*/
@ApiModelProperty(value = "总博客数量", name = "totalBlog")
private Long totalBlog;
/**
* 总阅读量
*/
@ApiModelProperty(value = "总阅读量", name = "totalRead")
private Long totalRead;
/**
* 平均阅读量
*/
@ApiModelProperty(value = "平均阅读量", name = "aveRead")
private Long aveRead;
/**
* 总点赞数量
*/
@ApiModelProperty(value = "总点赞数量", name = "totalLike")
private Long totalLike;
/**
* 平均点赞数量
*/
@ApiModelProperty(value = "平均点赞数量", name = "aveLike")
private Long aveLike;
/**
* 日期
*/
@ApiModelProperty(value = "日期", name = "date")
private String date;
}
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/model/vo/BlogSummaryDiyVo.java
package com.ljq.demo.springboot.mongodb.model.vo;
import com.fasterxml.jackson.annotation.JsonInclude;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
* @Description: 博客自定义时间区间统计结果
* @Author: junqiang.lu
* @Date: 2021/11/18
*/
@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
@ApiModel(value = "博客自定义时间区间统计结果", description = "博客自定义时间区间统计结果")
public class BlogSummaryDiyVo extends BlogSummaryVo {
private static final long serialVersionUID = 6688850910361408341L;
/**
* 年
*/
@ApiModelProperty(value = "年", name = "year")
private String year;
/**
* 月
*/
@ApiModelProperty(value = "月", name = "month")
private String month;
/**
* 小时
*/
@ApiModelProperty(value = "小时", name = "day")
private String hour;
/**
* 分钟
*/
@ApiModelProperty(value = "分钟", name = "minute")
private String minute;
}
8.3 日期分组信息类
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/model/vo/BlogGroupDateVo.java
package com.ljq.demo.springboot.mongodb.model.vo;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.io.Serializable;
/**
* @Description: 博客日期分组信息
* @Author: junqiang.lu
* @Date: 2021/11/19
*/
@Data
@ApiModel(value = "博客日期分组信息", description = "博客日期分组信息")
public class BlogGroupDateVo implements Serializable {
private static final long serialVersionUID = -4828325421504898453L;
/**
* 日期分组字段
*/
@ApiModelProperty(value = "日期分组字段", name = "dateGroupField")
private String dateGroupField;
/**
* 日期格式
*/
@ApiModelProperty(value = "日期格式", name = "dateFormat")
private String dateFormat;
}
8.4 获取日期分组信息的方法
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
/**
* 获取博客日期分组信息
*
* @param dateType
* @return
*/
private BlogGroupDateVo getGroupDate(Integer dateType) {
BlogGroupDateVo groupDateVo = new BlogGroupDateVo();
switch (dateType) {
case BlogConst.DATE_TYPE_DAY:
groupDateVo.setDateGroupField("day");
groupDateVo.setDateFormat("%Y-%m-%d");
break;
case BlogConst.DATE_TYPE_MONTH:
groupDateVo.setDateGroupField("month");
groupDateVo.setDateFormat("%Y-%m");
break;
default:
groupDateVo.setDateGroupField("day");
groupDateVo.setDateFormat("%Y-%m-%d");
break;
}
return groupDateVo;
}
9 推荐参考资料
使用mongoTemplate进行Aggregation聚合查询
springboot使用MongoTemplate分组统计数据
SpringBoot之MongoTemplate的查询可以怎么耍
java 使用mongoTemplate 按月分组、聚合的实现 (要求返回非分组字段)_deelness的博客-程序员宅基地
Group by time interval spring-data-mongo
Mongodb系列- spring-data-mongodb使用MongoTemplate实现分页查询
记一次使用mongoTemplate的Aggregation类进行分组,分页操作
mongoDB对时间的处理ISODate与咱们时区相差8小时
5 Github 源码
Gtihub 源码地址 : https://github.com/Flying9001/springBootDemo
个人公众号:404Code,分享半个互联网人的技术与思考,感兴趣的可以关注.