Java 中MongoDB 聚合查询，涉及分页，分组，去重_springboot mongo 聚合去重 查询

Java中使用MongoDB聚合查询，涉及分页，分组，去重

做一下相关总结，本次是单表查询统计分析，下面做一下简单的使用过程
1

<!--MongoDB驱动连接包-->
<dependency>
    <groupId>org.mongodb</groupId>
    <artifactId>mongo-java-driver</artifactId>
    <version>3.4.2</version>
</dependency>

<!--springboot是2.1.6.RELEASE-->
<!--操作MongoDB核心包-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-mongodb</artifactId>
</dependency> 
1
2
3
4
5
6
7
8
9
10
11
12
13

需求分析

查询每个人服务了多少家企业以及服务的总次数
假设用sql语句：
1
2

select 
	user_id,
	count(distinct com_id) as comNum,
	sum(server_num) as serverNums 
from user_server_record 
group by user_id
1
2
3
4
5
6

用mongodb分组查询分页

---------------
 @Autowired
 private MongoTemplate mongoTemplate;
---------------

// 我需求中还有其他条件
List<Long> userIds = countVo.getUserIds();
Criteria criteria = new Criteria();
criteria.and("user_id").in(userIds);

// 最终聚合查询所有信息（利用两次分组达到去重comId的效果）
Aggregation aggregation = Aggregation.newAggregation(
            Aggregation.match(criteria),
            Aggregation.group("user_id", "com_id").first("user_id").as("userId")
                .first("name").as("name")
                .first("dept_names").as("deptName")
                .sum("server_nums").as("serverNums"),
            Aggregation.group("userId").first("userId").as("userId")
                .first("name").as("name")
                .first("deptName").as("deptName")
                .count().as("comNums")
                .sum("serverNums").as("serverNums"),
            // 分页
            Aggregation.skip(countVo.getPageNum()>1?(countVo.getPageNum()-1)*countVo.getPageSize():0),
            Aggregation.limit(countVo.getPageSize()),
            Aggregation.sort(Sort.by(Sort.Order.desc("serverNums"),Sort.Order.desc("comNums")))
);
Aggregation aggregation1 = Aggregation.newAggregation(
            Aggregation.match(criteria),
            Aggregation.group("user_id").first("user_id").as("userId")
                .first("name").as("name")
                .first("dept_names").as("deptName")
                .sum("server_nums").as("serverNums")
);

// 获取总数
int total = mongoTemplate.aggregate(aggregation1, mongoTemplate.getCollectionName(MongoCountServerNums.class), CountMainNumVo.class).getMappedResults().size();
// 查询结果
AggregationResults<CountMainNumVo> results = mongoTemplate.aggregate(aggregation, mongoTemplate.getCollectionName(MongoCountServerNums.class), CountMainNumVo.class);
//获取结果
List<CountMainNumVo> result = results.getMappedResults();
Map<String, Object> m = new HashMap<String, Object>();
m.put("rows", result);
m.put("pageNum", countMainVo.getPageNum());
m.put("total", total);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

普通列表查询分页

---------------
 @Autowired
 private MongoTemplate mongoTemplate;
---------------
..........
// 查询条件
List<Long> userIds = countVo.getUserIds();
Criteria criteria = new Criteria();
criteria.and("user_id").in(userIds);
criteria.and("server_num").gt(0);
Query query = new Query(criteria);

// 查询总数
int total = mongoTemplate.find(query, MongoCountServerNums.class).size();
//分页组件时我项目框架里的
PageDomain pageDomain = TableSupport.buildPageRequest();
Integer pageNum = pageDomain.getPageNum();
Integer pageSize = pageDomain.getPageSize();
if (pageNum == null) pageNum = 1;
if (pageSize == null) pageSize = 10;
Pageable pageable = PageRequest.of(pageNum - 1, pageSize, Sort.by(Sort.Order.desc("create_date")));
query.with(pageable);
// 查询结果
List<MongoCountServerNums> resultLit = mongoTemplate.find(query, MongoCountServerNums.class);

Map<String, Object> m = new HashMap<String, Object>();
m.put("rows", resultLit);
m.put("pageNum", pageNum);
m.put("total", total);

.............

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32