C#代碼利用MongoDB中Group聚合函數查詢

例子：

public static void getUserRFM(DateTime beginTime, DateTime endTime)

        {

            MongoDatabase mongoDB = getConnection();

            //會員消費明細的實體類

            List<Ky_Shopplist> shopplist = new List<Ky_Shopplist>();

            //獲得Ky_shopplist集合

            MongoCollection col = mongoDB.GetCollection("Ky_Shopplist");

            //獲得Ky_UserRFM的集合

            MongoCollection colRFM = mongoDB.GetCollection("Ky_UserRFM");

            //删除Ky_UserRFM集合中所有的資料

            colRFM.RemoveAll();

            //定義擷取“buyTime”相關的查詢條件

            var query = Query.And(Query.GTE("buyTime", beginTime.ToString("yyyy-MM-dd HH:mm:ss")), Query.LTE("buyTime", endTime.ToString("yyyy-MM-dd HH:mm:ss")));

            //var result = col.FindAs<Ky_Shopplist>(query).ToList();

            //var result_all = col.FindAllAs<Ky_Shopplist>().ToList();

            //會員RFM值實體類

            List<Ky_UserRFM> userRFMlist = new List<Ky_UserRFM>();

            //GroupBy的字段

            GroupByBuilder groupbyBuilder = new GroupByBuilder(new string[] { "userId" });

            #region 計算會員R值

            try

            {

                //計算每組UserId的最大時間

                Dictionary<string, string> dic_R = new Dictionary<string, string>();

                dic_R["time"] = DateTime.Now.ToString();

                var result_R = col.Group(query, groupbyBuilder, BsonDocument.Create(dic_R),

                                    BsonJavaScript.Create("function(obj,prev){if(prev.time>obj.buyTime){prev.time=obj.buyTime;}}"),

                                    BsonJavaScript.Create("function(obj){ obj.btime=obj.time;delete obj.time; }")).ToList();

                 if (result_R.Count != 0)

                 {

                    //計算出會員距今最近的消費時間和最遠的消費時間

                    DateTime Mintime = DateTime.Now;

                    DateTime Maxtime = DateTime.Now;

                    for (int i = 1; i < result_R.Count; i++)

                    {

                        if (i == 1)

                        {

                            Maxtime = Convert.ToDateTime(result_R[i]["btime"]) > Convert.ToDateTime(result_R[0]["btime"]) ? Convert.ToDateTime(result_R[i]["btime"]) : Convert.ToDateTime(result_R[0]["btime"]);

                            Mintime = Convert.ToDateTime(result_R[i]["btime"]) < Convert.ToDateTime(result_R[0]["btime"]) ? Convert.ToDateTime(result_R[i]["btime"]) : Convert.ToDateTime(result_R[0]["btime"]);

                        }

                        else

                            Maxtime = Maxtime > Convert.ToDateTime(result_R[i]["btime"]) ? Maxtime : Convert.ToDateTime(result_R[i]["btime"]);

                            Mintime = Mintime < Convert.ToDateTime(result_R[i]["btime"]) ? Mintime : Convert.ToDateTime(result_R[i]["btime"]);

                    }

                    double R_interval = 0.0;

                    R_interval = (Maxtime - Mintime).TotalSeconds / 5;

                    //計算會員R值

                    for (int i = 0; i < result_R.Count; i++)

                        DateTime dtTime = Convert.ToDateTime(result_R[i]["btime"]);

                        string userId = result_R[i]["userId"].ToString();

                        string struser_R = "0";

                        if (dtTime >= Mintime && dtTime <= Mintime.AddSeconds(R_interval))

                            struser_R = "1";

                        if (dtTime >= Mintime.AddSeconds(R_interval) && dtTime <= Mintime.AddSeconds(R_interval * 2))

                            struser_R = "2";

                        if (dtTime >= Mintime.AddSeconds(R_interval * 2) && dtTime <= Mintime.AddSeconds(R_interval * 3))

                            struser_R = "3";

                        if (dtTime >= Mintime.AddSeconds(R_interval * 3) && dtTime <= Mintime.AddSeconds(R_interval * 4))

                            struser_R = "4";

                        if (dtTime >= Mintime.AddSeconds(R_interval * 4) && dtTime <= Maxtime)

                            struser_R = "5";

                        Ky_UserRFM userRFM = new Ky_UserRFM();

                        userRFM.user_R = struser_R;

                        userRFM.user_F = "0";

                        userRFM.user_M = "0";

                        userRFM.user_Tagid = "";

                        userRFM.user_Id = userId;

                        userRFM.current_Time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                        userRFMlist.Add(userRFM);

                    //批量插入會員RFM表

                    colRFM.InsertBatch(typeof(Ky_UserRFM), userRFMlist);

                }

            }

            catch (Exception ex)

                throw;

            #endregion

            #region 計算會員F值

　　　　　　//計算每組UserId的次數

                Dictionary<string, int> dic_F = new Dictionary<string, int>();

                dic_F["num"] = 0;

                var result_F = col.Group(query, groupbyBuilder, BsonDocument.Create(dic_F),

                                               BsonJavaScript.Create("function(doc,prev){prev.num++;}"),

                                               BsonJavaScript.Create("function(doc){ doc.count=doc.num;delete doc.num; }")).ToList();

                if (result_F.Count != 0)

                {

                    //計算出會員最大消費頻率和最小消費頻率

                    double Maxcount = 0;

                    double Mincount = 0;

                    for (int i = 1; i < result_F.Count; i++)

                            Maxcount = Convert.ToInt32(result_F[i]["count"]) > Convert.ToInt32(result_F[0]["count"]) ? Convert.ToInt32(result_F[i]["count"]) : Convert.ToInt32(result_F[0]["count"]);

                            Mincount = Convert.ToInt32(result_F[i]["count"]) < Convert.ToInt32(result_F[0]["count"]) ? Convert.ToInt32(result_F[i]["count"]) : Convert.ToInt32(result_F[0]["count"]);

                            Maxcount = Maxcount > Convert.ToInt32(result_F[i]["count"]) ? Maxcount : Convert.ToInt32(result_F[i]["count"]);

                            Mincount = Mincount < Convert.ToInt32(result_F[i]["count"]) ? Mincount : Convert.ToInt32(result_F[i]["count"]);

                    double F_inteval = 0.0;

                    F_inteval = (Maxcount - Mincount) / 5;

                    //計算每個會員的F值

                        string strUserid = result_F[i]["userId"].ToString();

                        int icount = Convert.ToInt32(result_F[i]["count"]);

                        string strUser_F = "0";

                        if (icount >= Mincount && icount <= F_inteval)

                            strUser_F = "1";

                        if (icount >= F_inteval && icount <= (F_inteval * 2))

                            strUser_F = "2";

                        if (icount >= (F_inteval * 2) && icount <= (F_inteval * 3))

                            strUser_F = "3";

                        if (icount >= (F_inteval * 3) && icount <= (F_inteval * 4))

                            strUser_F = "4";

                        if (icount >= (F_inteval * 4) && icount <= Maxcount)

                            strUser_F = "5";

                        //執行更新操作

                        var query_F = Query.EQ("user_Id", strUserid);

                        var update_F = new UpdateDocument { { "$set", new QueryDocument { { "user_F", strUser_F } } } };

                        colRFM.Update(query_F, update_F);

            #region 計算會員M值

　　　　　　//計算每組UserId中price（金額）的總和

                //var query_price = new QueryDocument{{"price",true}};

                //var update_price = new UpdateDocument{{"price",BsonType.Double}};

                //colRFM.Update(query_price, update_price);

                Dictionary<string, double> dic_M = new Dictionary<string, double>();

                dic_M["msum"] = 0.0;

                var result_M = col.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();

                if (result_M.Count != 0)

                    //計算出會員最大的消費金額和最小的消費金額

                    double Maxsum = 0.0;

                    double Minsum = 0.0;

                    for (int i = 1; i < result_M.Count; i++)

                            Maxsum = Convert.ToDouble(result_M[i]["msum"]) > Convert.ToDouble(result_M[0]["msum"]) ? Convert.ToDouble(result_M[i]["msum"]) : Convert.ToDouble(result_M[0]["msum"]);

                            Minsum = Convert.ToDouble(result_M[i]["msum"]) < Convert.ToDouble(result_M[0]["msum"]) ? Convert.ToDouble(result_M[i]["msum"]) : Convert.ToDouble(result_M[0]["msum"]);

                            Maxsum = Maxsum > Convert.ToDouble(result_M[i]["msum"]) ? Maxsum : Convert.ToDouble(result_M[i]["msum"]);

                            Minsum = Minsum < Convert.ToDouble(result_M[i]["msum"]) ? Minsum : Convert.ToDouble(result_M[i]["msum"]);

                    double M_interval = 0.0;

                    M_interval = (Maxsum - Minsum) / 5;

                    //計算每個會員的M值

                    for (int i = 0; i < result_M.Count; i++)

                        string strUserId = result_M[i]["userId"].ToString();

                        double dsum = Convert.ToDouble(result_M[i]["msum"]);

                        string struser_M = "0";

                        if (dsum >= Minsum && dsum <= M_interval)

                            struser_M = "1";

                        if (dsum >= M_interval && dsum <= (M_interval * 2))

                            struser_M = "2";

                        if (dsum >= (M_interval * 2) && dsum <= (M_interval * 3))

                            struser_M = "3";

                        if (dsum >= (M_interval * 3) && dsum <= (M_interval * 4))

                            struser_M = "4";

                        if (dsum >= (M_interval * 4) && dsum <= Maxsum)

                            struser_M = "5";

                        var query_M = Query.EQ("user_Id", strUserId);

                        var update_M = new UpdateDocument { { "$set", new QueryDocument { { "user_M", struser_M } } } };

                        colRFM.Update(query_M, update_M);

        }