MongoDB使用Aggregate进行数据分组

之前有一篇关于MongoDB的博客，但实际使用中数据量过大时速度比较慢，并且使用中不是很灵活。

 

第一 创建 MongoDBHelper类，封装MongoDB相关的操作

/// <summary>
        /// 数量
        /// </summary>
        /// <typeparam name="TDoc"></typeparam>
        /// <param name="doc"></param>
        /// <param name="filter"></param>
        /// <param name="options"></param>
        /// <returns></returns>
        public long Count<TDoc>(Expression<Func<TDoc, bool>> filter, FindOptions options = null)
        {
            string collectionName = typeof(TDoc).Name;
            return Count<TDoc>(collectionName, filter, options);
        }


        /// <summary>
        /// 数量
        /// </summary>
        /// <typeparam name="TDoc"></typeparam>
        /// <param name="collectionName"></param>
        /// <param name="doc"></param>
        /// <param name="filter"></param>
        /// <param name="options"></param>
        /// <returns></returns>
        public long Count<TDoc>(string collectionName, Expression<Func<TDoc, bool>> filter, FindOptions options = null)
        {
            var colleciton = GetMongoCollection<TDoc>(collectionName);
            return colleciton.Find(filter, options).CountDocuments();
        }


        /// <summary>
        /// mongodb 聚合
        /// </summary>
        /// <typeparam name="TDocument"></typeparam>
        /// <typeparam name="TResult"></typeparam>
        /// <param name="doc"></param>
        /// <param name="pipeline"></param>
        /// <param name="options"></param>
        /// <returns></returns>
        public List<TResult> Aggregate<TDocument, TResult>(List<string> pipelines, AggregateOptions options = null)
        {
            string collectionName = typeof(TDocument).Name;
            return Aggregate<TDocument, TResult>(collectionName, pipelines, options);
        }


        /// <summary>
        /// mongodb 聚合
        /// </summary>
        /// <typeparam name="TDocument"></typeparam>
        /// <typeparam name="TResult"></typeparam>
        /// <param name="collectionName"></param>
        /// <param name="pipeline"></param>
        /// <param name="options"></param>
        /// <returns></returns>
        public List<TResult> Aggregate<TDocument, TResult>(string collectionName, List<string> pipelines, AggregateOptions options = null)
        {
            var colleciton = GetMongoCollection<TDocument>(collectionName);
            var pipelineDef = PipelineDefinition<TDocument, TResult>.Create(pipelines);
            var retAggregate = colleciton.Aggregate<TResult>(pipelineDef, options);
            //retAggregate.MoveNext();
            var result = retAggregate.ToList();
            return result;
        }

这里只有部分核心代码，要获取全部帮助类代码请结合之前关于MongoDB的博客文章。

第二 业务代码中封装公共方法

 /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <typeparam name="T2"></typeparam>
        /// <param name="collectionNames"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        public List<T2> GetLogsToMongo<T1,T2>(string collectionNames, List<string> obj)
        {
            var list = MongoDB.MongoTool.MongoDBHelper.Aggregate<T1,T2>(collectionNames, obj);
            return list;
        }

第三 使用$group 根据时间（每小时）和ticket_type分组统计数据

 /// <summary>
        /// 获取闸机票务种类统计 当天 用于安博会
        /// </summary>
        /// <param name="collectionName">表名称</param>
        /// <param name="company_uuid">所属公司</param>
 /// <param name="length ">过去几小时数据</param>
        /// <returns></returns>
public TicketTypeData GetDeviceTicketTypeCountByDayOnAli(string collectionName, string company_uuid, int length = 0)
        {
            TicketTypeData ticketTypeData = new TicketTypeData();
           


            var start =  DateTime.Now.AddHours(-8).AddHours(-length).ToString("yyyy-MM-ddTHH:mm:ss.001");

            var end = DateTime.Now.AddHours(-8).ToString("yyyy-MM-ddTHH:mm:ss.999");

            string whereRem = "{$match:{function_name:'Ticket_CheckTicket',create_time:{$gte:ISODate('" + start + "'),$lte:ISODate('" + end + "')}}}";
            string grouupRem = "{$group:{_id:{'key':{$hour: '$create_time' },'type':'$ticket_type'},'value':{$sum:NumberInt(1)}}}";
            var list = mongoDBData.GetLogsToMongo<ReportLogs, ReportDataCountMongTicket>(collectionName + "_" + DateTime.Now.ToString("yyyyMMdd"), new List<string> { whereRem, grouupRem });

            for (int i = 0; i < list.Count; i++)
            {
                if (Convert.ToInt32(list[i]._id.key) + 8 >= 24)
                {
                    list[i]._id.key = list[i]._id.key + 8 - 24;
                }
                else
                {
                    list[i]._id.key = list[i]._id.key + 8;
                }
            }
            DateTime date = new DateTime();

            date = DateTime.Parse(DateTime.Now.AddHours(-length).ToString("yyyy-MM-dd HH:00:00"));
            List<string> dateList = new List<string>();
            List<int> IDList = new List<int>();
            List<int> ICList = new List<int>();
            List<int> QRList = new List<int>();
            List<int> FAList = new List<int>();
            for (int d = 0; d <= length; d++)
            {
                int ID = 0, IC = 0, QR = 0, FA = 0;
                dateList.Add(date.AddHours(d).Hour.ToString() + "时");
                
                ReportDataCount reportDataCount = new ReportDataCount();
                foreach (var item in list)
                {

                    if (date.AddHours(d).Hour == item._id.key)
                    {
                        switch (item._id.type)
                        {
                            case "ID":
                                ID+=item.value;
                                break;
                            case "IC":
                                IC += item.value;
                                break;
                            case "QR":
                                QR += item.value;
                                break;
                            case "FA":
                                FA += item.value;
                                break;
                        }
                       

                    }

                }
                

                IDList.Add(ID);
                ICList.Add(IC);
                QRList.Add(QR);
                FAList.Add(FA);

                
            }

            ticketTypeData.date = dateList;
            ticketTypeData.typeID = IDList;
            ticketTypeData.typeIC = ICList;
            ticketTypeData.typeQR = QRList;
            ticketTypeData.typeFA = FAList;
            return ticketTypeData;


        }