1.4 Query‐GroupBy

1. Group query and use

1.1 Syntax

Having is used only when aggregate objects need to be filtered, and can be removed from group queries

var list = db.Queryable<Student>()
.GroupBy(it => new {it-id, it-name}) // Can have multiple fields
.Where (it=>it.Id>0)// Common filter
.Having(it => SqlFunc.AggregateCount(it.id) > 0)// Aggregate function filtering
.Select(it => new {
idAvg = SqlFunc.AggregateAvg(it.Id?? 0),
count = SqlFunc.AggregateCount(it.Id),
name = it.Name })
.ToList();

//      SELECT
//           AVG([Id]) AS[idAvg],
//           [Name] AS[name]
//
//               FROM[Student] GROUP BY[Name],[Id] Where Id > 0

//Count usage
//SqlFunc.AggregateCount(it.Id)


// Single field usage (multiple single can also be stacked)
.GroupBy(it =>SqlFunc.SubString(it.name,0,1))
.GroupBy(it =>it.Id)

// The new version supports grouping tape functions
.GroupBy(it=>new { it.Id, name= SqlFunc.ToString(it.Name) }

Group query allows you to perform summary query, average, maximum, and minimum operations

1.2 null removal (isnull or ifnull)

If there is null in the library then avg and sum will not query the data if it is not processed

SqlFunc. AggregateSumNoNull (it. Num) / / is equal to the sum (isnull (num, 0))
SqlFunc. AggregateAvgNoNull (it. Num) / / is equal to the avg (isnull (num, 0))

//nullable type can also be used?? null removal
SqlFunc.AggregateSum(it.num?? 0)// avg(isnull(num,0))
 

// Original usage
SqlFunc.AggregateSum(SqlFunc.Isnull(it.num,0))// avg(isnull(num,0))

1.3 Sorting statistical Columns

var list = db.Queryable<Student>()
.GroupBy(it => new { it.Id, it.Name })
.Where(it=>it.Id>0)
.Select(it => new {
idAvg = SqlFunc.AggregateAvg(it.Id?? 0),
count = SqlFunc.AggregateCount(it.Id),
name = it.Name })
.MergeTable()// MergeTable is required to sort the counted columns
.OrderBy(it=>it.count)
.ToList();

2. Distinct is used

It is generally used to specify the field to be repeated, query the value that is not repeated, and remove the field

var list = db.Queryable<Student>().Distinct().Select(it => new { it.Name }).ToList();
//SELECT  DISTINCT  [Name] AS [Name]  FROM [STudent]
Note: Upgrades to newer versions are compatible with rownumber conflicts

3. Get the first item (or several items) in groups

3.1 Common writing method for all databases

var list=db.Queryable<Order>()
.GroupBy(it => it.name)// Do not have an OrderBy before MergeTable
.Select(it => new
{
name = it.Name,
id = SqlFunc.AggregateMax(it.Id)
})
.MergeTable()
.LeftJoin<Order>((a, b) => a.id == b.Id)
//OrderBy((a,b)=a.Id)
.Select((a, b) => b).ToList();
// SELECT [b].*
//  FROM
//  (SELECT*FROM(SELECT [Name]AS[name],MAX([Id]) AS [id] FROM [Order] GROUP BY [Name]) MergeTable )[a]
//  Left JOIN
//  [Order] [b]  ON ( [a].[id] = [b].[Id] )

This method can only support obtaining 1 item. If you want to obtain more than 1 item in groups, see 3.2

3.2. Windows function syntax implementation (more library support)

The new version only supports 5.1.1

Support database: SqlServer, MySql8.0+, Oracle, PgSql, Dameng, Jincang and other database support

Note: partition by name is equal to group by name

var test48 = db.Queryable<Order>().Select(it => new
{
index2 = SqlFunc.RowNumber(it.Id,it.Name),//order by id partition by name
// Multi-field sort order by id asc,name desc
//SqlFunc.RowNumber($"{it.Id} asc ,{it.Name} desc ",$"{it.Name}")
price=it.Price,
date=it.CreateTime
})
.MergeTable()// Merges the results into one table
.Where(it=>it.index2==1) // Only one record with the same name is taken
// Use Where(it=>it.index2=<=20) for the first 20 items.
.ToList();

//SELECT * FROM
// (SELECT
//row_number() over( partition by [Name] order by [Id]) AS [index2],
//[Price] AS [price] ,
//[CreateTime] AS [date]  FROM [Order]
// ) MergeTable   WHERE ( [index2] = 1 )

// Multiple fields 5.1.2-preview01
SqlFunc.RowNumber($"{it.Id} asc ,{it.Name} desc "  , $"{it.Id},{it.Name}")
//partition by [id],[Name] order by [Id] asc,[name] desc

3.3 Individual database notation

//1. Unique implementation of individual libraries
// Like Oracle, SqlServer syntax sugar
db.Queryable<Order>().Take(1).PartitionBy(it=>it.Name).ToList()
db.Queryable<Order>().OrderBy(it=>it.id,OrderByType.Desc).Take(1).PartitionBy(it=>it.Name).ToList()

4. Grouping special dates

Example 1: Group the months and years

var students = db.Queryable<Order>()
.GroupBy(it=>it.CreateTime.ToString("yyyy-MM"))
.Select(it=>new {
Time=it.CreateTime.ToString("yyyy-MM"),
Count=SqlFunc.AggregateCount(it.name)

})
// If you want to follow the OrderBy
//.MergeTable().OrderBy(it=>it.Count)
.ToList();

Example 2: Group by year, month and day

var getOrderBy = db.Queryable<Order>().Select(it=>new  {
Id=it.Id,
Name=it.Name,// You can't write aggregate functions here because they're not grouped
CreateTime=it.CreateTime.Date// Specifies only the date
//DateTime? Type it.CreateTime.Value.Date
})
.MergeTable()// Converts the query results into a table
.GroupBy(it=>it.CreateTime)
.Select(it=>new { id =SqlFunc.AggregateMax(it.Id),crate=it.CreateTime })
.ToList();

More windows functions

 count = SqlFunc.RowCount(),// count (1) over() 
  max= SqlFunc.RowMax(it.num??0),// max(isnull(num,0)) over() 
  min= SqlFunc.RowMin(it.num??0),// min(isnull(num,0)) over() 
  avg= SqlFunc.RowAvg(it.num??0),// avg(isnull(num,0)) over() 
  index = SqlFunc.RowNumber(it.Id), // row_number() over( order by a.`Id`)
  index = SqlFunc.RowNumber(it.Id,it.Name)//  row_number() over( partition by name order by a.`Id`)
  index = SqlFunc.RowNumber(SqlFunc.Desc(it.Id),it.Name)//  row_number() over( partition by name order by a.`Id` desc)
  index = SqlFunc.Rank // rownumber Similar usage
 
     
  //order by id asc ,name desc
  //partition by name,id
    //SqlFunc.RowNumber($"{it.Id} asc ,{it.Name} desc ",$"{it.Name},{it.Id}")