数据为王

在用aggregate 进行数据统计处理的时候，由于系统默认使用Mongodb的UTC时间，与我们时区差了8小时，会出现结果误差。为了获得正确的结果，在进行

aggregate 处理时需要在原来的基础上做加8小时处理。

测试数据如下

下面我们来计算下shop_id 等于57300412且时间大于’2014-04-01T00:02:00Z’ 按天排序的统计结果。shell表达式如下

//经过时差处理
db.test.aggregate({$match:{'_id.shop_id':57300412}},
{$group:{
_id:{
year:{$year:{$add:['$_id.date',28800000]}},
month:{$month:{$add:['$_id.date',28800000]}},
day:{$dayOfMonth:{$add:['$_id.date',28800000]}
}},
amount:{$sum:'$value.amount'},
count:{$sum:'$value.count'},
cost:{$sum:'$value.cost'}}},
{$project:{_id:1,count:1,amount:1,cost:1}}
)

//没有经过时差处理
db.test.aggregate({$match:{'_id.shop_id':57300412}},
{$group:{
_id:{
year:{$year:'$_id.date'},
month:{$month:'$_id.date'},
day:{$dayOfMonth:'$_id.date'}
},
amount:{$sum:'$value.amount'},
count:{$sum:'$value.count'},
cost:{$sum:'$value.cost'}}},
{$project:{_id:1,count:1,amount:1,cost:1}}
)

对比结果如下

注意上图增加时差和未增加时差的结果是不一样的。

说明：

$dayOfYear:计算日期的该年第几天，返回1到366
$add:['$_id.date',28800000]: 时区数据校准，8小时换算成毫秒数为8*60*60*1000 = 28800000.

相应的Java代码片段如下：

DBObject match = new BasicDBObject("$match", new BasicDBObject("_id.shop_id", Integer.parseInt(shopAmount.getShopId())).
				append("_id.date", new BasicDBObject("$gte", startDate).append("$lte", endDate)));

BasicDBList dbList = new BasicDBList();
dbList.add("$_id.date");
dbList.add(28800000);//解决timezone 8小时时差

Map<String, Object> dbObjIdMap = new HashMap<String, Object>();
dbObjIdMap.put("year", new BasicDBObject("$year",new BasicDBObject("$add",dbList)));
dbObjIdMap.put("month", new BasicDBObject("$month",new BasicDBObject("$add",dbList)));
dbObjIdMap.put("dayOfMonth", new BasicDBObject("$dayOfMonth",new BasicDBObject("$add",dbList)));
DBObject groupFields = new BasicDBObject( "_id", new BasicDBObject(dbObjIdMap));

groupFields.put("amount", new BasicDBObject("$sum", "$value.amount"));
groupFields.put("count", new BasicDBObject("$sum", "$value.count"));
groupFields.put("cost", new BasicDBObject("$sum", "$value.cost"));
DBObject group = new BasicDBObject("$group", groupFields);

DBObject fields = new BasicDBObject("_id", 1); 
fields.put("cost", 1); 
fields.put("amount", 1); 
fields.put("count", 1); 
DBObject project = new BasicDBObject("$project", fields );
DBObject sort = new BasicDBObject("$sort", new BasicDBObject("_id", -1));
/* 查看Group结果 */
AggregationOutput output = collection.aggregate(match, group,project,sort); // 执行 aggregation命令
System.out.println(output.getCommandResult());
Collection<Object> c = output.getCommandResult().values();
Object o[] = c.toArray();
BasicDBList resultList = (BasicDBList) o[1];

参考链接 http://docs.mongodb.org/manual/reference/operator/aggregation/