mongodb 高级部分 group by case when select distinct substr(sdf,0,6)

一、gourp by 和case when 的混用

1、且看一张表格，表格的结构是（其中一条数据）

{
    "_id" : ObjectId("57876215b522253ff42e3346"),
    "type" : NumberInt(0),
    "userId" : NumberInt(101920),
    "pointsNum" : NumberInt(50),
    "createdDate" : NumberLong(1468490260993),
    "lastModifiedDate" : NumberLong(1468494579572),
}

 

2、现在需要根据userId分组统计pointsNum，并根据type值的不同汇总成两个字段，于是就需要实现类似于sql的写法

这是一条没有校验的sql写法，各位看官能看懂就好：

select  outPointsTotalNum as sum(case when type=0 then pointsNum else 0),

consumePointsTotalNum as sum(case when type=1 then pointsNum else 0)

from table_aaa

group by userId;

 

3、在mongodb 中实现类似的需求，需要使用 $cond,$group

 

$cond[aaa,bbb,ccc] 类似于case when，和三元操作符 aaa?bbb:ccc

本文中还用到了aggregate，最终语句如下

db.d_points_detail.aggregate(
   [ 
{ "$group" : { "_id" : "$userId" , 
"outPointsTotalNum" : { "$sum" : { "$cond" : [ { "$eq" : [ "$type" , 0]} , "$pointsNum" , 0]}},
"consumePointsTotalNum" : { "$sum" : { "$cond" : [ { "$eq" : [ "$type" , 1]} , "$pointsNum" , 0]}}
}},
{ "$sort" : { "outPointsTotalNum" : -1}}
   ]
);

二、distict 实现类似于select distinct substr(name,0,6) from student

1、这是Oracle的写法

 select distinct (long 转换为日期) from detail;

2、mongodb中实现

 

db.d_points_detail1.aggregate(
                { "$group" : { "_id" : { '$dateToString': { 'format': '%Y-%m-%d', 'date': { '$add': [new Date(0), '$createdDate'] } } }}},
                { $group: { _id: 1, total: { $sum: 1 } } });

 

转载注明出处，谢谢~