Mongodb aggregate 统计SQL(金额分类、分时统计、left join)

记录一下用到的微信订单统计SQL:

- 订单统计：

db.orders.aggregate(
  { $match:{createDay:20180611} },
  { $group:{_id:null, recordCount:{$sum:1}, amount:{$sum:"$totalAmount"}, avgValue:{$avg:"$totalAmount"}} }
);

结果示例:

- 订单状态分类统计：

db.orders.aggregate(
  { $match:{createDay:20180611} },
  { $group:{_id:"$status", recordCount:{$sum:1}, amount:{$sum:"$totalAmount"}, avgValue:{$avg:"$totalAmount"}} }
);

结果示例:

- 订单金额分类统计：

db.orders.aggregate(
  { $match:{createDay:20180611} },
  { $group:{_id:"$totalAmount", recordCount:{$sum:1}, amount:{$sum:"$totalAmount"}, avgValue:{$avg:"$totalAmount"} } }
);

结果示例:

- 订单分时统计：

db.orders.aggregate(
  { $match:{day:20180611} },
  { $group:{_id:{ year: { $year: "$gmtCreate" }, month: { $month: "$gmtCreate" }, day: { $dayOfMonth: "$gmtCreate" }, hour: { $hour: "$gmtCreate" }  }, recordCount:{$sum:1}, amount:{$sum:"$totalAmount"} } },
  { $sort:{_id:1}}
).forEach(function(t){print(t._id.year+'-'+t._id.month+'-'+t._id.day+' '+t._id.hour+':00\t'+t.recordCount+'\t'+t.amount)});

结果示例(注意:这里要加8小时):

- 左连接查询：

--insert hour list:
db.hour.insert({hour:"1:00"});
db.hour.insert({hour:"2:00"});
db.hour.insert({hour:"3:00"});
db.hour.insert({hour:"4:00"});
db.hour.insert({hour:"5:00"});
--insert work log:
db.work_log.insert({name:'yown', hour:'3:00'});

db.hour.aggregate(
    {
      $lookup:
        {
          from: "work_log",
          localField: "hour",
          foreignField: "hour",
          as: "log_docs"
        }
   }
).forEach(function(t){
  print(t.hour+'\t' + (t.log_docs[0] == null ? '' : t.log_docs[0].name) + '\t' + (t.log_docs[0] == null ? '' : t.log_docs[0].hour));
});

结果示例: