代码收藏家 Java中使用MongoDB实现条件分组聚合查询并带时间戳分页，按日期分组（年月日级细分）
废话不多说，先看效果图：
原SQL：
db.getCollection("hbdd_order").aggregate([
{
// 把时间戳格式化
$addFields: {
orderDate: {
"$dateToString": {
"format": "%Y-%m-%d",
"date": {
"$toDate": "$hzdd_order_addtime"
}
}
}
}
},
{
$match: {
// 筛选条件
hzdd_order_addtime: {
$gte: 1722441600000,
$lt: 1725120000000
}
}
},
{
// 按格式过的时间分组
$group: {
"_id": "$orderDate",
paidAmount: {
$sum: { // 统计
$cond: [{ // 条件类似if true =1 else =0
$eq: ["$hzdd_order_ispay", 1]
}, "$hzdd_order_amount", 0]
}
},
paidCount: {
$sum: {
$cond: [{
$eq: ["$hzdd_order_ispay", 1]
}, 1, 0]
}
},
unpaidAmount: {
$sum: {
$cond: [{
$eq: ["$hzdd_order_ispay", 0]
}, "$hzdd_order_amount", 0]
}
},
unpaidCount: {
$sum: {
$cond: [{
$eq: ["$hzdd_order_ispay", 0]
}, 1, 0]
}
}
}
},
{
$project: {
date: "$_id",
paidAmount: 1,
paidCount: 1,
unpaidAmount: 1,
unpaidCount: 1
}
},
{
$sort: { // 排序
date: 1
}
}
]);
Java语句：
代码中多了些内容，但是和SQL语句大差不差
（懒得替换类名，大家看到陌生的类就是自己建的）
import com.mongodb.client.result.UpdateResult;
import jodd.util.StringUtil;
import org.bson.Document;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.*;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.*;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.stereotype.Service;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.Optional;
public Page<OrderStatVo> orderStatistical(OrderStatQuery query) {
Pageable pageable = PageRequest.of(query.getPageNum() - 1, query.getPageSize());
MongoTemplate mongoTemplate = mongoFactory.mongoTemplate(OrderConstants.ORDER_DB);
// 时间筛选
Long startTime = query.getStartTime();
Long endTime = query.getEndTime();
// 区分 1年,2月,3日
int type = query.getType();
// 按商家id
String shopId = query.getShopId();
// 按code筛选
Integer areaCode = query.getAreaCode();
Integer provinceCode = query.getProvinceCode();
Integer cityCode = query.getCityCode();
Integer countyCode = query.getCountyCode();
// 基础匹配条件：按年初和年末 时间戳
Criteria baseCriteria = new Criteria();
// 额外的筛选条件
List<Criteria> additionalCriteria = new ArrayList<>();
if (startTime != null && endTime != null) {
additionalCriteria.add(Criteria.where("hzdd_order_addtime").gte(startTime).lt(endTime));
}
if (StringUtil.isNotEmpty(shopId)) {
additionalCriteria.add(Criteria.where("hzdd_order_sjid").is(shopId));
}
if (areaCode != null && areaCode != 0) {
additionalCriteria.add(Criteria.where("hzdd_order_area_code").is(areaCode));
}
if (provinceCode != null && provinceCode != 0) {
additionalCriteria.add(Criteria.where("hzdd_order_province_code").is(provinceCode));
}
if (cityCode != null && cityCode != 0) {
additionalCriteria.add(Criteria.where("hzdd_order_city_code").is(cityCode));
}
if (countyCode != null && countyCode != 0) {
additionalCriteria.add(Criteria.where("hzdd_order_county_code").is(countyCode));
}
// 合并所有条件
if (!additionalCriteria.isEmpty()) {
baseCriteria.andOperator(additionalCriteria.toArray(new Criteria[0]));
}
// 构建匹配操作
MatchOperation matchOperation = Aggregation.match(baseCriteria);
// 添加字段操作，将 Unix 时间戳转换为日期字符串
String expression = switch (type) {
case 1 -> "{$dateToString: { format: '%Y', date: { $toDate: '$hzdd_order_addtime' }}}";
case 2 -> "{$dateToString: { format: '%Y-%m', date: { $toDate: '$hzdd_order_addtime' }}}";
case 3 -> "{$dateToString: { format: '%Y-%m-%d', date: { $toDate: '$hzdd_order_addtime' }}}";
default -> "{$dateToString: { format: '%Y-%m-%d', date: { $toDate: '$hzdd_order_addtime' }}}";
};
AddFieldsOperation addFieldsOperation = Aggregation.addFields().addField("orderDate")
.withValueOfExpression(expression).build();
// 分组操作
GroupOperation groupOperation = Aggregation.group("orderDate")
.sum(ConditionalOperators.Cond.when(Criteria.where("hzdd_order_ispay").is(1))
.then("$hzdd_order_amount").otherwise(0)).as("paidAmount")
.sum(ConditionalOperators.Cond.when(Criteria.where("hzdd_order_ispay").is(1))
.then(1).otherwise(0)).as("paidCount")
.sum(ConditionalOperators.Cond.when(Criteria.where("hzdd_order_ispay").is(0))
.then("$hzdd_order_amount").otherwise(0)).as("unpaidAmount")
.sum(ConditionalOperators.Cond.when(Criteria.where("hzdd_order_ispay").is(0))
.then(1).otherwise(0)).as("unpaidCount");
// 投影操作
ProjectionOperation projectionOperation = Aggregation.project()
.and("_id").as("date")
.andInclude("paidAmount", "paidCount", "unpaidAmount", "unpaidCount");
// 排序操作
SortOperation sortOperation = Aggregation.sort(Sort.Direction.ASC, "date");
// 分页操作
SkipOperation skipOperation = Aggregation.skip((long) pageable.getPageNumber() * pageable.getPageSize());
LimitOperation limitOperation = Aggregation.limit(pageable.getPageSize());
// 构建不包含分页的聚合查询以获取总条数
Aggregation countAggregation = Aggregation.newAggregation(
matchOperation,
addFieldsOperation,
groupOperation,
Aggregation.group("orderDate").count().as("totalCount"), // 添加计数操作
Aggregation.project("totalCount").andExclude("_id") // 只包含 totalCount 字段
);
// 执行聚合查询以获取总条数
AggregationResults<Document> totalCountResults = mongoTemplate.aggregate(countAggregation, "hbdd_order", Document.class);
Document document = totalCountResults.getMappedResults().stream().findFirst().orElse(null);
int total = document != null ? (int) document.get("totalCount") : 0;
// 构建包含分页的聚合查询
Aggregation aggregation = Aggregation.newAggregation(
matchOperation,
addFieldsOperation,
groupOperation,
projectionOperation,
sortOperation,
skipOperation,
limitOperation
);
// 第二个参数是文档名(表名)，第三个参数是接收的类，字段对应上面代码中的as别名字段
AggregationResults<OrderStatVo> results = mongoTemplate.aggregate(aggregation, "hbdd_order", OrderStatVo.class);
List<OrderStatVo> everyDayOrderStats = results.getMappedResults();
// 分页操作
return new PageImpl<>(everyDayOrderStats, pageable, total);
}
** OrderStatQuery 类就不展示了，就是传值进来的筛选条件 **
OrderStatVo类
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;
@Data
@Schema(description = "订单统计")
public class OrderStatVo {
@Schema(description = "周期")
private String date;
@Schema(description = "已支付金额")
private Double paidAmount;
@Schema(description = "已支付订单数")
private Long paidCount;
@Schema(description = "未支付金额")
private Double unpaidAmount;
@Schema(description = "未支付订单数")
private Long unpaidCount;
}
Java中使用mongoDB小技巧：
配置文件中加上下面这行，可以打印出mongo的SQL语句
logging:
level:
org.springframework.data.mongodb.core.MongoTemplate: DEBUG
作者：哒不溜-w
