MongoDB sql分组查询，以及Java操作MongoDB分组查询

一、MongoDB SQL

1、使用group方法分组（写法一）

select c_sender,c_sendtime,total,dep_ids,avg  from goods_order group by c_sender,c_sendtime

db.getCollection('goods_order').group({
     key: { c_sender:1,c_sendtime:1},
     cond: { },
     reduce: function ( curr, result ) { 
             result.total += 1;
             var str = result.dep_ids;
             if(curr.dep_id && curr.dep_id !='' && (str.indexOf(curr.dep_id) == -1 )){
               result.dep_ids += ','+curr.dep_id;
             }
         },
     initial: { total : 0,dep_ids:'' },
     finalize: function(result) {                
                 result.avg = result.total /2;
              }
});

2、使用group方法分组（写法二）

select c_sender,total,dep_ids,avg  from goods_order group by c_sender

db.goods_order.group( {    
     key: { c_sender:-1},
     cond: { },
     reduce: function ( curr, result ) { 
             result.total += 1;
             var str = result.dep_ids;
             if(curr.dep_id && curr.dep_id !='' && (str.indexOf(curr.dep_id) == -1 )){
               result.dep_ids += ','+curr.dep_id;
             }
         },
     initial: { total : 0,dep_ids:'' },
     finalize: function(result) {                
                 result.avg = result.total /2;
              }
});

3、使用管道aggregate方法分组排序（写法三）

select c_sender,c_sendtime，sum(1) form goods_order where c_content like '%你%' group by c_sender,c_sendtime  order by c_sendtime desc
db.goods_order.aggregate([
   { $match: { c_content : {$regex :'你'} } },
   {
     $group: {
        _id:{ c_sender:'$c_sender',
              c_sendtime:{
                      millisecond:{$millisecond:'$c_sendtime'},
                      second:{$second:'$c_sendtime'},
                      minute:{$minute:'$c_sendtime'},
                      hour:{$hour:'$c_sendtime'},            
                      month: { $month: "$c_sendtime" },
                      day: { $dayOfMonth: "$c_sendtime" },
                      year: { $year: "$c_sendtime"}
                      }
            },        
         count:{ $sum : 1 }
     }
   },
   { $sort: { c_sendtime: -1 } }
]);

二、JAVA代码

//使用mongodb group 方法
Criteria criteria = new Criteria();
GroupBy groupBy = new GroupBy("c_sender","c_sendtime")
                .initialDocument("{total : 0,dep_ids:'' }")
                .reduceFunction("function (doc,pre){ }")
                .finalizeFunction("function (pre){}");
GroupByResults<Goods_order> res = mongoTemplate.group(criteria, "goods_order", groupBy, Goods_order.class);
List list = res.getRawResults().get("retval", List.class);//retval是固定的


//使用管道 select c_sender,c_sendtime,count(1) from goods_order group by c_sender asc,c_sendtime desc 
Criteria criteria = new Criteria();//条件语句
GroupOperation groupOperation = Aggregation.group("c_sender","c_sendtime").count().as("count");//sum("字段").as("xxx");
SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.fromString("desc"),"c_sender").and((Sort.by(Sort.Direction.fromString("asc"),"c_sendtime"))));
Aggregation aggregation = Aggregation.newAggregation(Goods_order.class,Aggregation.match(criteria),groupOperation,sortOperation);
AggregationResults<Goods_order> result = mongoTemplate.aggregate(aggregation,"goods_order",Goods_order.class);
List<Goods_order> list2 =  result.getMappedResults();