MongoDB 的分组操作 In C#
2016-04-20 10:59 无抿屎的牛牛 阅读(2978) 评论(0) 编辑 收藏 举报C#对mongodb的分组查询操作,主要通过脚本或Aggregate方法来实现,以下通过一些实例作解析:
参考资料:http://www.tuicool.com/articles/2iqUZj
http://www.cnblogs.com/vnii/archive/2012/08/23/2652891.html
http://www.cnblogs.com/zhwl/archive/2013/12/19/3482140.html
1. 根据任务id进行分组,统计每个任务的浏览数量。
public static List<vm_quantityModel> GetMyIncomeDetail(int member_id, int pageIndex, int pageSize, out int totalCount) { //筛选条件 IMongoQuery query = Query.And(Query.EQ("member_id", member_id), Query.EQ("mark", 1)); MongoCollection<BsonDocument> myCollection = MongoDBHelper.GetCollection("mstg_record_history"); //分组 Dictionary<string, int> dic_initial = new Dictionary<string, int>(); dic_initial["num"] = 0; var r = myCollection.Group( query, "task_id", //分组的字段 BsonDocument.Create(dic_initial), BsonJavaScript.Create("function(doc,prev){prev.num++;}"), null ).ToList(); List<vm_quantityModel> result = new List<vm_quantityModel>(); if (r != null && r.Count > 0) { foreach (var item in r) { vm_quantityModel m = new vm_quantityModel(); m.task_id = Common.StringHelper.StrToInt(item["task_id"].ToString()); m.valid_count = Common.StringHelper.StrToLong(item["num"].ToString()); result.Add(m); } } totalCount = result.Count; result = result.OrderByDescending(x => x.task_id).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList(); return result; }
2.根据会员编号分组,累加价格的统计
/// <summary> /// 帮帮赚会员总收益 /// </summary> /// <param name="memberIds"></param> /// <returns></returns> public static List<vm_rankingModel> GetBangZhuanList(List<int> memberIds) { var ids = memberIds.ConvertAll<BsonValue>(x => x); //筛选字段 IMongoQuery query = Query.And(Query.In("member_id", ids), Query.EQ("mark", 1)); MongoCollection<BsonDocument> myCollection = MongoDBHelper.GetCollection("mstg_record_history"); //GroupBy的字段 GroupByBuilder groupbyBuilder = new GroupByBuilder(new string[] { "member_id" }); Dictionary<string, double> dic_M = new Dictionary<string, double>(); dic_M["msum"] = 0.0; var result_M = myCollection.Group(query, groupbyBuilder, BsonDocument.Create(dic_M), BsonJavaScript.Create("function(obj,prev){prev.msum += obj.price;}"), BsonJavaScript.Create("function(obj){obj.sum=obj.msum;delete obj.sum;}")).ToList(); List<vm_rankingModel> result = new List<vm_rankingModel>(); if (result_M != null && result_M.Count > 0) { foreach (var item in result_M) { double amount = Math.Round(Convert.ToDouble(item["msum"]), 2); result.Add(new vm_rankingModel { member_id = Common.StringHelper.StrToInt(item["member_id"].ToString()), amount = amount }); } } result = result.OrderByDescending(x => x.amount).ToList(); return result; }
用Aggregate实现:
public static List<vm_rankingModel> GetRankingList(int top) { MongoCollection<BsonDocument> myCollection = MongoDBHelper.GetCollection("mstg_record_history"); //分组获取金额总数 var group = new BsonDocument { { "$group", new BsonDocument { { "_id", new BsonDocument { { "MemberId","$member_id" } } }, { "Count", new BsonDocument { { "$sum", "$price" } } } } } }; //筛选条件 var match = new BsonDocument { { "$match", new BsonDocument { {"mark", 1} } } }; var pipeline = new[] { match, group }; var list = myCollection.Aggregate(pipeline); var matchingExamples = list.ResultDocuments .Select(x => x.ToDynamic()) .ToList(); List<vm_rankingModel> result = new List<vm_rankingModel>(); //foreach (var example in matchingExamples) //{ // var message = string.Format("{0} - {1}", example._id.MyUser, example.Count); // Console.WriteLine(message); //} if (matchingExamples.Count > 0) { foreach (var example in matchingExamples) { int member_id = Convert.ToInt32(example._id.MemberId.Value); double amount = Math.Round(example.Count.Value, 2); result.Add(new vm_rankingModel { member_id = member_id, amount = amount }); } } result = result.OrderByDescending(x => x.amount).Take(top).ToList(); return result; }
3. 按日期分组,累加价格的统计
public static List<vm_bangzhuan_detailModel> GetBangZhuanDetails(List<int> memberIds) { var ids = memberIds.ConvertAll<BsonValue>(x => x); //有效浏览数 IMongoQuery query = Query.And(Query.In("member_id", ids), Query.EQ("mark", 1)); MongoCollection<BsonDocument> myCollection = MongoDBHelper.GetCollection("mstg_record_history"); Dictionary<string, double> dic_M = new Dictionary<string, double>(); dic_M["msum"] = 0.0; var result_M = myCollection.Group(query, BsonJavaScript.Create("function(doc){ var date = new Date(doc.add_time); var dateKey = date.getFullYear()+'年'+(date.getMonth()+1)+'月'+date.getDate(); return {'day':dateKey}; }"), BsonDocument.Create(dic_M), BsonJavaScript.Create("function(obj,prev){prev.msum += obj.price;}"), BsonJavaScript.Create("function(obj){obj.sum=obj.msum;delete obj.sum;}")).ToList(); List<vm_bangzhuan_detailModel> result = new List<vm_bangzhuan_detailModel>(); if (result_M != null && result_M.Count > 0) { foreach (var item in result_M) { double amount = Math.Round(Convert.ToDouble(item["msum"]), 2); result.Add(new vm_bangzhuan_detailModel { day = Convert.ToDateTime(item["day"]), amount = amount }); } } result = result.OrderByDescending(x => x.day).ToList(); return result; }