- Notifications
You must be signed in to change notification settings - Fork 889
Group Aggregation Query
2881099 edited this page Dec 18, 2023 · 11 revisions
中文 | English
static IFreeSql fsql = new FreeSql.FreeSqlBuilder() .UseConnectionString(FreeSql.DataType.MySql, connectionString) //Be sure to define as singleton mode .Build(); class Topic { [Column(IsIdentity = true, IsPrimary = true)] public int Id { get; set; } public int Clicks { get; set; } public string Title { get; set; } public DateTime CreateTime { get; set; } }var list = fsql.Select<Topic>() .GroupBy(a => new { tt2 = a.Title.Substring(0, 2), mod4 = a.Id % 4 }) .Having(a => a.Count() > 0 && a.Avg(a.Key.mod4) > 0 && a.Max(a.Key.mod4) > 0) .Having(a => a.Count() < 300 || a.Avg(a.Key.mod4) < 100) .OrderBy(a => a.Key.tt2) .OrderByDescending(a => a.Count()) .ToList(a => new { a.Key, cou1 = a.Count(), arg1 = a.Avg(a.Value.Clicks), arg2 = a.Sum(a.Value.Clicks > 100 ? 1 : 0) }); //SELECT //substr(a.`Title`, 1, 2) as1, //count(1) as2, //avg(a.`Clicks`) as3, //sum(case when a.`Clicks` > 100 then 1 else 0 end) as4 //FROM `Topic` a //GROUP BY substr(a.`Title`, 1, 2), (a.`Id` % 4) //HAVING (count(1) > 0 AND avg((a.`Id` % 4)) > 0 AND max((a.`Id` % 4)) > 0) AND (count(1) < 300 OR avg((a.`Id` % 4)) < 100) //ORDER BY substr(a.`Title`, 1, 2), count(1) DESCTo find the aggregate value without grouping, please use
ToAggregateinstead ofToList
var list = fsql.Select<Topic>() .ToAggregate(a => new { cou1 = a.Count(), arg1 = a.Avg(a.Key.Clicks), arg2 = a.Sum(a.Key.Clicks > 100 ? 1 : 0) });If Topic has the navigation property Category, and Category has the navigation property Area, the navigation property grouping code is as follows:
var list = fsql.Select<Topic>() .GroupBy(a => new { a.Clicks, a.Category }) .ToList(a => new { a.Key.Category.Area.Name });Note: Write as above, an error will be reported and cannot be resolved a.Key.Category.Area.Name. The solution is to use Include:
var list = fsql.Select<Topic>() .Include(a => a.Category.Area) //This line must be added, //otherwise only the Category will be grouped without its sub-navigation property Area .GroupBy(a => new { a.Clicks, a.Category }) .ToList(a => new { a.Key.Category.Area.Name });However, you can also solve it like this:
var list = fsql.Select<Topic>() .GroupBy(a => new { a.Clicks, a.Category, a.Category.Area }) .ToList(a => new { a.Key.Area.Name });var list = fsql.Select<Topic, Category, Area>() .GroupBy((a, b, c) => new { a.Title, c.Name }) .Having(g => g.Count() < 300 || g.Avg(g.Value.Item1.Clicks) < 100) .ToList(g => new { count = g.Count(), Name = g.Key.Name });-
g.Value.Item1corresponds toTopic -
g.Value.Item2corresponds toCategory -
g.Value.Item3corresponds toArea
| 说明 | 方法 | SQL |
|---|---|---|
| 总数 | .Count() | select count(*) from ... |
| 求和 | .Sum(a => a.Score) | select sum([Score]) from ... |
| 平均 | .Avg(a => a.Score) | select avg([Score]) from ... |
| 最大值 | .Max(a => a.Score) | select max([Score]) from ... |
| 最小值 | .Min(a => a.Score) | select min([Score]) from ... |
| lambda | sql | 说明 |
|---|---|---|
| SqlExt.IsNull(id, 0) | isnull/ifnull/coalesce/nvl | 兼容各大数据库 |
| SqlExt.DistinctCount(id) | count(distinct id) | |
| SqlExt.GreaterThan | > | 大于 |
| SqlExt.GreaterThanOrEqual | >= | 大于或等于 |
| SqlExt.LessThan | < | 小于 |
| SqlExt.LessThanOrEqual | <= | 小于 |
| SqlExt.EqualIsNull | IS NULL | 是否为 NULL |
| SqlExt.Case(字典) | case when .. end | 根据字典 case |
| SqlExt.GroupConcat | group_concat(distinct .. order by .. separator ..) | MySql |
| SqlExt.FindInSet | find_in_set(str, strlist) | MySql |
| SqlExt.StringAgg | string_agg(.., ..) | PostgreSQL |
| SqlExt.Rank().Over().PartitionBy().ToValue() | rank() over(partition by xx) | 开窗函数 |
| SqlExt.DenseRank().Over().PartitionBy().ToValue() | dense_rank() over(partition by xx) | |
| SqlExt.Count(id).Over().PartitionBy().ToValue() | count(id) over(partition by xx) | |
| SqlExt.Sum(id).Over().PartitionBy().ToValue() | sum(id) over(partition by xx) | |
| SqlExt.Avg(id).Over().PartitionBy().ToValue() | avg(id) over(partition by xx) | |
| SqlExt.Max(id).Over().PartitionBy().ToValue() | max(id) over(partition by xx) | |
| SqlExt.Min(id).Over().PartitionBy().ToValue() | min(id) over(partition by xx) | |
| SqlExt.RowNumber(id).Over().PartitionBy().ToValue() | row_number(id) over(partition by xx) |
fsql.Select<User1>() .Where(a => a.Id < 1000) .WithTempQuery(a => new { item = a, rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue() }) .Where(a => a.rownum == 1) .ToList();SELECT * FROM ( SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum] FROM [User1] a WHERE a.[Id] < 1000 ) a WHERE (a.[rownum] = 1)or：
fsql.Select<User1>() .Where(a => a.Id < 1000) .GroupBy(a => a.Nickname) .WithTempQuery(g => new { min = g.Min(g.Value.Id) }) .From<User1>() .InnerJoin((a, b) => a.min == b.Id) .ToList((a, b) => b);SELECT b.[Id], b.[Nickname] FROM ( SELECT min(a.[Id]) [min] FROM [User1] a WHERE a.[Id] < 1000 GROUP BY a.[Nickname] ) a INNER JOIN [User1] b ON a.[min] = b.[Id]more.. 《Nested Query》
- Distinct
var list = fsql.Select<Topic>() .Aggregate(a => Convert.ToInt32("count(distinct title)"), out var count) .ToList();- SqlExt.DistinctCount
fsql.Select<Topic>() .Aggregate(a => SqlExt.DistinctCount(a.Key.Title), out var count);SELECT count(distinct a."title") as1 FROM "Topic" a
| Method | Return | Parameter | Description |
|---|---|---|---|
| ToSql | string | Return the SQL statement to be executed | |
| ToList<T> | List<T> | Lambda | Execute SQL query and return the records of the specified field. When the record does not exist, return a list with Count of 0. |
| ToList<T> | List<T> | string field | Execute SQL query, and return the record of the field specified by field, and receive it as a tuple or basic type (int, string, long). If the record does not exist, return a list with Count of 0. |
| ToAggregate<T> | List<T> | Lambda | Execute SQL query and return the aggregate result of the specified field. (Suitable for scenarios where GroupBy is not required) |
| Sum | T | Lambda | Specify a column to sum. |
| Min | T | Lambda | Specify a column to find the minimum. |
| Max | T | Lambda | Specify a column to find the maximum. |
| Avg | T | Lambda | Specify a column to average. |
| 【Grouping】 | |||
| GroupBy | <this> | Lambda | Group by the selected column, GroupBy(a => a.Name) |
| GroupBy | <this> | string, parms | Group by raw sql statement, GroupBy("concat(name, @cc)", new { cc = 1 }) |
| Having | <this> | string, parms | Filter by raw SQL statement aggregation conditions, Having("count(name) = @cc", new { cc = 1 }) |
| 【Members】 | |||
| Key | Returns the object selected by GroupBy | ||
| Value | Return to the main table or the field selector of From<T2,T3....> |