SpringBoot Mongodb Group By 查询统计

SQL示例

	SELECT	studentId,COUNT(*) FROM t_student;

JAVA 示例

createTime 在mongoDb 存储格式为ISODate 例如: ISODate("2021-10-29T08:35:07.007Z")

示例1(时间条件)

    public List<Map> groupCountBySiteId() {
	    //检索条件
        Date startDate = DateUtil.parseDate("2022-03-03 16:16:32");
        Date enDate = DateUtil.parseDate("2022-03-03 18:16:32");
        Criteria criteria = new Criteria();
        criteria.andOperator(
                Criteria.where("createTime").gte(startDate),
                Criteria.where("createTime").lt(enDate)
        );
        MatchOperation matchOperation = Aggregation.match(criteria);
        
        // 匹配返回的字段 match有检索条件就要加上去,即使聚合查询没有也要加,否则检索条件不生效
        ProjectionOperation projectionOperation = Aggregation.project(
                "studentId",
                "createTime",
                "age",
                "sex",
                "total");
                
        // 分组操作，并对每个studentId的总条数进行统计
        GroupOperation groupOperation = Aggregation.group("studentId").count().as("total");
        // 组合条件
        Aggregation aggregation = Aggregation.newAggregation(projectionOperation, matchOperation, groupOperation);
        //检索表名
        String dataBaseName = "Student";
        // 执行操作
        AggregationResults<Map> aggregate = mongoOperations.aggregate(aggregation, dataBaseName, Map.class);
        List<Map> list = aggregate.getMappedResults();
        return list;

示例2 (多个检索条件)

    public List<Map> groupCountBySiteId() {
	    //检索条件
        Date startDate = DateUtil.parseDate("2022-03-03 16:16:32");
        Date enDate = DateUtil.parseDate("2022-03-03 18:16:32");
        Criteria criteria = new Criteria();
        criteria.andOperator(
                Criteria.where("createTime").gte(startDate),
                Criteria.where("createTime").lt(enDate),
                Criteria.where("age").in(18)
        );
        MatchOperation matchOperation = Aggregation.match(criteria);
        
        // 匹配返回的字段 match有检索条件就要加上去,即使聚合查询没有也要加,否则检索条件不生效
        ProjectionOperation projectionOperation = Aggregation.project(
                "studentId",
                "createTime",
                "age",
                "sex",
                "total");
                
        // 分组操作，并对每个studentId的总条数进行统计
        GroupOperation groupOperation = Aggregation.group("studentId").count().as("total");
        // 组合条件
        Aggregation aggregation = Aggregation.newAggregation(projectionOperation, matchOperation, groupOperation);
        //检索表名
        String dataBaseName = "Student";
        // 执行操作
        AggregationResults<Map> aggregate = mongoOperations.aggregate(aggregation, dataBaseName, Map.class);
        List<Map> list = aggregate.getMappedResults();
        return list;

其他API

	# 年龄叠加
	Aggregation.group("studentId").sum("age")
	#年龄 平均值
	Aggregation.group("studentId").avg("age")

相关资料

其他资料1