mongodb在Java中条件分组聚合查询并且分页(时间戳,按日期分组,年月日...)

server/2024/11/15 6:04:19/

废话不多说,先看效果图:

  • SQL查询结果示例:
    在这里插入图片描述
  • 多种查询结果示例:
    在这里插入图片描述

原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语句大差不差
(懒得替换类名,大家看到陌生的类就是自己建的)

java">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();// 按商家idString 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类
java">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

http://www.ppmy.cn/server/111937.html

相关文章

TS中type和interface在类型声明时的区别

在TS中interface 和 type都可以用来自定义数据类型&#xff0c;两者有许多相同之处&#xff0c;但是也有差别。我们一般选择 type 来定义基本类型别名、联合类型、元组等类型&#xff0c;而选择 interface 来定义复杂的对象、类、以及进行接口的继承。 1. 声明常见类型 和 方法…

惠中科技RDS自清洁膜层:光伏领域的绿色革命

惠中科技RDS自清洁膜层&#xff1a;光伏领域的绿色革命 在全球能源转型和光伏产业蓬勃发展的背景下&#xff0c;光伏电站的运营维护面临着诸多挑战&#xff0c;其中灰尘污染问题尤为突出。灰尘的堆积不仅降低了光伏板的透光率&#xff0c;还直接影响了电站的发电效率和经济效益…

Java重修笔记 第四十六天 Map 接口、HashMap 类

Map 接口 1. 用于保存具有映射关系的数据&#xff1a;Key-Value&#xff08;双列元素&#xff09; 2. Map 中的 key 和 value 可以是任何引用类型的数据&#xff0c;会封装到 HashMap$Node 对象中 3. Map 中的 key 不允许重复&#xff0c;原因和 HashSet 一样&#xff0c;v…

发布npm包到GitLab教程

之前在研究如何搭建UI组件库&#xff08;内部使用&#xff09;&#xff0c;其中重要的一步就是发布npm包到GitLab。中间踩了很多坑&#xff0c;在这里记录一下整个流程方便大家快速上手。不足之处欢迎指出&#x1f64f; 1. 获取Token 在gitlab中打开access tokens申请页面&am…

09.定时器02

#include "reg52.h"sbit led P3^6;void delay10ms() { //1. 配置定时器0工作模式位16位计时TMOD 0x01;//2. 给初值&#xff0c;定一个10ms出来TL00x00;TH00xDC;//3. 开始计时TR0 1;TF0 0; } void main() {int cnt 0;led 1;while(1){if(TF0 1)//当爆表的时候&a…

【高等代数笔记】(18)N阶行列式

2. N阶行列式 2.12 行列式按k行&#xff08;列&#xff09;展开 【拉普拉斯定理】 n n n阶矩阵 A ( a i j ) \boldsymbol{A}(a_{ij}) A(aij​)&#xff0c;取定第 i 1 , i 2 , . . . , i k i_{1},i_{2},...,i_{k} i1​,i2​,...,ik​行&#xff08;其中 i 1 < i 2 < .…

Redis数据结构与连接

1 基本的数据结构 1.1 string string的实现有多种 int&#xff1a;字符串长度小于等于20且能转成整数raw&#xff1a;字符串长度大于44embstr&#xff1a;字符串长度小于等于44 字符串长度小于1M 时&#xff0c;加倍扩容&#xff1b;超过 1M 每次只多扩1M&#xff1b;字符串…

gitignore does not work

在.gitignore文件中新增想要ignore的文件路径&#xff0c;但是不生效。 这是由于git cache导致的&#xff0c;处理方法见下方链接&#xff1a; caching - Ignore files that have already been committed to a Git repository - Stack Overflow