mongodb的聚合查询aggregate group count操作

川长思鸟来 2022-12-23 00:53 430阅读 0赞

首先来看 Mysql统计数据

SELECT
    	a.entity_id,
    	COUNT( DISTINCT a.train_user_node_id ) 
    FROM
    	t_train_user_node_study_detail a 
    WHERE
    	a.entity_id IN ( 1, 26 ) 
    GROUP BY
    	a.entity_id

![在这里插入图片描述][20201121130417739.png_pic_center]

用MongoDB的aggregate查询

db.train_user_node_study_detail.aggregate([
     { 
      $match :{ 
       "entityId": { "$in":["1","26"]}
      }
     },
     { 
      $group :{ 
       _id:{ "entityId" : "$entityId", "trainUserNodeId" :"$trainUserNodeId"}
      }
     }
     ,
     { 
      $group :{ 
       _id:{ "entityId" : "$_id.entityId"},
       count :{ "$sum":1}
      }
     }
    ])

![在这里插入图片描述][20201121130501142.png_pic_center]  
**java代码**

@Test
        public void aggregation1() { 
            List<String> ids = new ArrayList<>();
            ids.add("1");
            ids.add("26");
            Criteria criteria = new Criteria();
            criteria.and("entityId").in(ids);
            String[] group1 = {  "entityId", "trainUserNodeId" };
            Aggregation aggregation = Aggregation.newAggregation(Aggregation.match(criteria), Aggregation.group(group1),
                    Aggregation.group("entityId").count().as("studNum"));
            AggregationResults<TrainUserNodeGroup> result = mongoTemplate.aggregate(aggregation, "train_user_node_study_detail",
                    TrainUserNodeGroup.class);
            log.info("result is {}", result.getMappedResults());
        }
        @Setter
        @Getter
        public class Group2 extends BaseBean { 
    
            private static final long serialVersionUID = 3924605590502137660L;
    
            private String id;
    
            private int sum;
        }

**控制台打印的结果**  
![在这里插入图片描述][20201121130741686.png_pic_center]

[20201121130417739.png_pic_center]: /images/20221120/36aa27ec3f0849fd92e8e16d8cc8edfe.png
[20201121130501142.png_pic_center]: /images/20221120/e9da6dec947c46b092d020a6b41172f0.png
[20201121130741686.png_pic_center]: /images/20221120/4ddc0093b57f48fa9c63dccaa7818b08.png