MongoDB aggregate 运用以及在C#对应方法
-
最近一直在用mongodb,有时候会需要用到统计,在网上查了一些资料,最适合用的就是用aggregate,以下介绍一下自己运用的心得。。
别人写过的我就不过多描述了,大家一搜能搜索到N多一样的,我写一下我的总结。
基础知识
请大家自行查找更多,以下是关键文档。
操作符介绍:
$project:包含、排除、重命名和显示字段
$match:查询,需要同find()一样的参数
$limit:限制结果数量
$skip:忽略结果的数量
$sort:按照给定的字段排序结果
$group:按照给定表达式组合结果
$unwind:分割嵌入数组到自己顶层文件
文档: MongoDB 官方 aggregate 说明 。
相关使用:
db.collection.aggregate([array]);
array可是是任何一个或多个操作符。
group和match的用法,使用过sqlserver,group的用法很好理解,根据指定列进行分组统计,可以统计分组的数量,也能统计分组中的和或者平均值等。
group之前的match,是对源数据进行查询,group之后的match是对group之后的数据进行筛选;
同理,sort,skip,limit也是同样的原理;
1 {_id:1,name:"a",status:1,num:1}
2 {_id:2,name:"a",status:0,num:2}
3 {_id:3,name:"b",status:1,num:3}
4 {_id:4,name:"c",status:1,num:4}
5 {_id:5,name:"d",status:1,num:5}
以下是示例:应用一:统计name的数量和总数;
db.collection.aggregate([
{$group:{_id:"$name",count:{$sum:1},total:{$sum:"$num"}}
]);
应用二:统计status=1的name的数量;
db.collection.aggregate([
{$match:{status:1}},
{$group:{_id:"$name",count:{$sum:1}}}
]);
应用三:统计name的数量,并且数量为小于2的;
db.collection.aggregate([
{$group:{_id:"$name",count:{$sum:1}},
{$match:{count:{$lt:2}}}
]);
应用四:统计stauts=1的name的数量,并且数量为1的;
db.collection.aggregate([
{$match:{status:1}},
{$group:{_id:"$name",count:{$sum:1}}},
{$match:{count:1}}
]);
多列group,根据name和status进行多列
db.collection.aggregate([
{$group:{_id:{name:"$name",st:"$status"},count:{$sum:1}}}
]);
$project该操作符很简单,
db.collection.aggregate([
{$project:{name:1,status:1}}
]);
结果是,只有_id,name,status三个字段的表数据,相当于sql表达式 select _id,name,status from collection
$unwind
这个操作符可以将一个数组的文档拆分为多条文档,在特殊条件下有用,本人暂没有进行过多的研究。
以上基本就可以实现大部分统计了,group前条件,group后条件,是重点。
-
那么C#对应方法怎么写呢? 直接上代码
using MongoDB.Bson; using MongoDB.Driver; using MongoDB.Driver.Builders; /*MongoDB的驱动*/ using System; using System.Collections.Generic; using System.Linq; using System.Web;
DateTime today = DateTime.Parse(strToday); DateTime yesterday = today.AddDays(-1); var match = new BsonDocument { { "$match", new BsonDocument{ { "visite_time", new BsonDocument{ {"$gte", new BsonDateTime(yesterday)}, {"$lt", new BsonDateTime(today)} } } } } }; var group = new BsonDocument { { "$group", new BsonDocument{ { "_id", new BsonDocument{ { "thedate", new BsonDocument{ { "year", new BsonDocument{{"$year", "$visite_time" }}}, { "month", new BsonDocument{{"$month", "$visite_time" }}}, { "date", new BsonDocument{{"$dayOfMonth", "$visite_time" }}}, } }, { "website_code", "$website_code" }, { "request_page", "$request_page" }, { "nation", "$nation" }, } }, { "click", new BsonDocument{ {"$sum", 1} } }, { "person", new BsonDocument{ { "$sum", new BsonDocument { { "$cond", new BsonDocument{ {"if", "$is_new_session"}, {"then", 1}, {"else", 0} } } } } } } } } }; var sort = new BsonDocument { { "$sort", new BsonDocument{ { "click", -1}, { "person", -1} } } }; AggregateArgs args = new AggregateArgs(); args.Pipeline = new List<BsonDocument> { match, group, sort }; List<BsonDocument> list = mongoh.GetCollection<BsonDocument>("counters").Aggregate(args).ToList(); ; List<ModDailyStatistcs> listDailys = new List<ModDailyStatistcs>(); foreach (BsonDocument bson in list) { listDailys.Add(new ModDailyStatistcs { the_date = DateTime.Parse(bson["_id"]["thedate"]["year"] + "-" + bson["_id"]["thedate"]["month"] + "-" + bson["_id"]["thedate"]["date"]), request_page = bson["_id"]["request_page"].ToString(), website_code = bson["_id"]["website_code"].ToString(), nation = bson["_id"]["nation"].ToString(), click_num = bson["click"].ToInt32(), person_num = bson["person"].ToInt32() }); } mongoh.GetCollection<BsonDocument>("daily_statistcs").InsertBatch(listDailys);
/// <summary> /// Class MongoHelper. /// </summary> public class MongoHelper { /// <summary> /// 数据库连接 /// </summary> private string conn; /// <summary> /// The database name /// </summary> private string dbName; /// <summary> /// Initializes a new instance of the <see cref="MongoHelper" /> class. /// </summary> /// <param name="dbName">Name of the database.</param> public MongoHelper(string dbName) { conn = System.Configuration.ConfigurationManager.AppSettings["MongoDb"]; if (string.IsNullOrEmpty(dbName)) { int index = conn.LastIndexOf("/"); if (index != -1) { this.dbName = conn.Substring(index + 1); } } else { this.dbName = dbName; } } /// <summary> /// 獲得Mongo DataBase /// </summary> /// <returns>MongoDatabase.</returns> public MongoDatabase GetDb() { //创建数据连接 MongoServer server = MongoServer.Create(conn); //获取指定数据库 MongoDatabase db = server.GetDatabase(dbName); return db; } /// <summary> /// Gets the collection. /// </summary> /// <param name="collectionName">Name of the collection.</param> /// <returns>MongoCollection.</returns> public MongoCollection GetCollection(string collectionName) { MongoDatabase db = GetDb(); return db.GetCollection(collectionName); } /// <summary> /// Gets the collection. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="collectionName">Name of the collection.</param> /// <returns>MongoCollection<T>.</returns> public MongoCollection<T> GetCollection<T>(string collectionName) { MongoDatabase db = GetDb(); return db.GetCollection<T>(collectionName); } }
-
Aggregate不仅用于统计,也就是不一定要$group哟!
BsonDocument match = new BsonDocument { {"$match", new BsonDocument{{"username", new BsonString(CivetNo.ToLower())}}} };
BsonDocument project = new BsonDocument { {"$project", new BsonDocument{ {"civetno", "$username"}, {"nickname", "$vCard.NICKNAME"}, {"realname", "$vCard.FN"}, {"sex", "$vCard.SEX"}, {"icon", "$vCard.HEAD"}, {"area", "$vCard.AREA"}, {"sign", "$vCard.DESC"}, {"hrtype", "$vCard.COUNTRY"} }} };
AggregateArgs args = new AggregateArgs(); args.Pipeline = new BsonDocument[] { match, project }; BsonDocument bson = MongoDb.GetCollection<BsonDocument>("ofUser").Aggregate(args).FirstOrDefault();
另外要注意$project中,对应字段全空,此字段就不返回了,怎么办呢,有办法用$ifNULL,代码如下:
BsonDocument project = new BsonDocument { {"$project", new BsonDocument{ {"civetno", "$username"}, {"nickname", "$vCard.NICKNAME"}, {"realname", "$vCard.FN"}, {"sex", "$vCard.SEX"}, {"icon", new BsonDocument{{ "$ifNull", new BsonArray(new string[]{"$vCard.HEAD", ""})}}}, //注意ifNull的大小写。 {"area", "$vCard.AREA"}, {"sign", "$vCard.DESC"}, {"hrtype", "$vCard.COUNTRY"} }} };
另外可判断是否存在某个键值
foreach (BsonDocument bson in listBson) { list.Add(new ModOpenGetEmpName.jsonResult { EmpNo = bson.Contains("EmpNo") ? bson["EmpNo"].ToString() : "", EmpName = bson.Contains("EmpName") ? bson["EmpName"].ToString() : "", Unit = bson.Contains("Unit") ? bson["Unit"].ToString() : "" }); }
-
查询相关操作符
"$gt" 、"$gte"、 "$lt"、 "$lte"、"$ne"、"null查询"、"$all"、"$size"、"$in"、"$nin"、 "$and"、"$nor"、"$not"、"$or"、"$exists"、"$mod"、"$regex"、"$where"、"$slice"、"$elemMatch"
来个例子
BsonDocument match = new BsonDocument { { "phone.phoneNum", new BsonDocument{ { "$regex", new BsonRegularExpression(PhoneNum + "$") }, { "$not", new BsonRegularExpression(@"$\+886") }, { "$not", new BsonRegularExpression(@"$\+86") } } }, { "isActive", "1"} };
原来是这样
//他们的效果是一样一样的 {"phone.phoneNum": {$regex: /15994780250/}} {"phone.phoneNum": /15994780250/}
-
那么问题来了,怎么比较两个字段的值呢。
{ $where : "this.username != this.civetId"}
就这样。
//这样也可以 { $where : "this.username != this.civetId", "phone.phoneNum": /15994780250/}
-
分享一個查詢時間的,時間是現在的時間-8小時,區間要設短一點,因查出的結果差過16M就無法查詢
{ RunTime: {$gt: ISODate("2017-02-07T00:00:04.989Z"), $lte: ISODate("2017-02-07T00:00:06.000Z")} }
-