使用Linq对数据库进行多GroupBy查询

现状：

Oracle数据库，某表中的数据3000万行

目标：

先按照年进行分组，然后按照月分组，再按照日分组，再按照小时分组，最后统计。

问题：

通过年进行分组，统计，很简单。一句group by就行。
但是如果先按照年进行分组，然后以年进行了分组的组里，再按照月分组，尝试了多次使用group by，结果EF不支持。

解决

只需要写一个group by，但以一个自己组合的object进行分组即可。

示例

请注意看

 group x by new 
 { 
 x.处理时间!.Value.Year, 
 x.处理时间!.Value.Month, 
 x.处理时间!.Value.Day 
 } into g

这一句。

 private object GetBookBRCountByDay(DateTime startDateTime, DateTime endDateTime)
        {
            var data = from x in oracleDbContext.流通日志
                       where x.处理时间.HasValue
                             && x.处理时间.Value >= startDateTime
                             && x.处理时间.Value <= endDateTime
                       group x by new { x.处理时间!.Value.Year, x.处理时间!.Value.Month, x.处理时间!.Value.Day } into g
                       select new
                       {
                           yearMonthDay = g.Key,
                           JCounts = g.Where(x => x.操作类型 == "J").Count(),
                           HCounts = g.Where(x => x.操作类型 == "H").Count(),
                           XCounts = g.Where(x => x.操作类型 == "X").Count(),
                       };
            //从数据库返回数据
            var dataList = data.ToList();

            //遍历每一年
            List<object> yearCounts = new();
            foreach (var year in Enumerable.Range(startDateTime.Year, endDateTime.Year - startDateTime.Year + 1))
            {
                //遍历每一月
                List<object> monthCounts = new();
                foreach (var month in Enumerable.Range(1, 12))
                {
                    //遍历每一天
                    List<object> dayCounts = new();
                    foreach (var day in Enumerable.Range(1, 31))
                    {
                        var jCounts = dataList
                                    .Where(x => x.yearMonthDay.Year == year
                                    && x.yearMonthDay.Month == month
                                    && x.yearMonthDay.Day == day)
                                    .Select(x => x.JCounts)
                                    .Sum();
                        var hCounts = dataList
                                    .Where(x => x.yearMonthDay.Year == year
                                    && x.yearMonthDay.Month == month
                                    && x.yearMonthDay.Day == day)
                                    .Select(x => x.HCounts)
                                    .Sum();
                        var xCounts = dataList
                                    .Where(x => x.yearMonthDay.Year == year
                                    && x.yearMonthDay.Month == month
                                    && x.yearMonthDay.Day == day)
                                    .Select(x => x.XCounts)
                                    .Sum();

                        if ((xCounts | hCounts | xCounts) > 0)
                        {
                            dayCounts.Add(new
                            {
                                day = day,
                                counts = new BookBRCount
                                {
                                    borrowCount = jCounts,
                                    returnCount = hCounts,
                                    renewCount = xCounts,
                                }
                            });
                        }
                    }
                    if (dayCounts.Count() > 0)
                    {
                        monthCounts.Add(new
                        {
                            month = year,
                            months = dayCounts
                        });
                    }
                }

                if (monthCounts.Count() > 0)
                {
                    yearCounts.Add(new
                    {
                        year = year,
                        years = monthCounts
                    });
                }
            }
            return yearCounts;
        }

EF生成的SQL语句

 Executed DbCommand (112ms) 
 [Parameters=[:startDateTime_0='?' (DbType = Date), :endDateTime_1='?' (DbType = Date)],
  CommandType='Text',
  CommandTimeout='0']
  
      SELECT "t"."Year", "t"."Month", "t"."Day", "t"."Hour", COUNT(CASE
          WHEN "t"."操作类型" = 'J' THEN 1
      END) "JCounts", COUNT(CASE
          WHEN "t"."操作类型" = 'H' THEN 1
      END) "HCounts", COUNT(CASE
          WHEN "t"."操作类型" = 'X' THEN 1
      END) "XCounts"
      FROM (
          SELECT "流"."操作类型", 
          EXTRACT(YEAR FROM "流"."处理时间") "Year", 
          EXTRACT(MONTH FROM "流"."处理时间") "Month", 
          EXTRACT(DAY FROM "流"."处理时间") "Day", 
          TO_NUMBER(TO_CHAR("流"."处理时间", 'HH24')) "Hour"
          FROM "GDLISNET"."流通日志" "流"
          WHERE (((("流"."处理时间" IS NOT NULL) 
          AND ("流"."处理时间" >= :startDateTime_0)))
           AND ("流"."处理时间" <= :endDateTime_1))
      ) "t"
      GROUP BY "t"."Year", "t"."Month", "t"."Day", "t"."Hour"

执行结果

{
  "level": "hour",
  "startDateTime": "2023-03-22T00:00:00",
  "endDateTime": "2023-03-22T19:00:00",
  "data": [
    {
      "year": 2023,
      "years": [
        {
          "month": 3,
          "months": [
            {
              "day": 22,
              "days": [
                {
                  "hour": 8,
                  "counts": {
                    "borrowCount": 0,
                    "returnCount": 9,
                    "renewCount": 0
                  }
                },
                {
                  "hour": 9,
                  "counts": {
                    "borrowCount": 28,
                    "returnCount": 36,
                    "renewCount": 0
                  }
                },
                {
                  "hour": 10,
                  "counts": {
                    "borrowCount": 4,
                    "returnCount": 3,
                    "renewCount": 0
                  }
                },
                {
                  "hour": 11,
                  "counts": {
                    "borrowCount": 14,
                    "returnCount": 6,
                    "renewCount": 0
                  }
                },
                {
                  "hour": 12,
                  "counts": {
                    "borrowCount": 14,
                    "returnCount": 10,
                    "renewCount": 1
                  }
                },
                {
                  "hour": 13,
                  "counts": {
                    "borrowCount": 8,
                    "returnCount": 8,
                    "renewCount": 0
                  }
                },
                {
                  "hour": 14,
                  "counts": {
                    "borrowCount": 19,
                    "returnCount": 20,
                    "renewCount": 1
                  }
                },
                {
                  "hour": 15,
                  "counts": {
                    "borrowCount": 22,
                    "returnCount": 22,
                    "renewCount": 0
                  }
                },
                {
                  "hour": 16,
                  "counts": {
                    "borrowCount": 34,
                    "returnCount": 21,
                    "renewCount": 19
                  }
                },
                {
                  "hour": 17,
                  "counts": {
                    "borrowCount": 23,
                    "returnCount": 22,
                    "renewCount": 0
                  }
                },
                {
                  "hour": 18,
                  "counts": {
                    "borrowCount": 3,
                    "returnCount": 0,
                    "renewCount": 3
                  }
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}