private static async Task InsertCustomers(IScenarioContext context)
{
var userTable = AllTables.GetItUser();
var customerTable = AllTables.GetItCustomer();
await InsertInto(customerTable, customerTable.UserId)
.From(Select(userTable.UserId)
.From(userTable)
.Where(!Exists(SelectOne()
.From(customerTable)
.Where(customerTable.UserId == userTable.UserId))))
.Exec(context.Database);
context.WriteLine("Customers inserted:");
var clCount = CustomColumnFactory.Int64("Count");
var res = await SelectDistinct(customerTable.CustomerId, userTable.UserId, Cast(CountOneOver(), SqlType.Int64).As(clCount))
.From(customerTable)
.InnerJoin(userTable, @on: customerTable.UserId == userTable.UserId)
.OrderBy(userTable.UserId)
.OffsetFetch(0, 5)
.QueryList(context.Database, r => (UserId: userTable.UserId.Read(r), CustomerId: customerTable.CustomerId.Read(r), Count: clCount.Read(r)));
foreach (var tuple in res)
{
Console.WriteLine(tuple);
}
}
private static async Task Step11AnalyticAndWindowFunctions(ISqDatabase database)
{
var cUserName = CustomColumnFactory.String("Name");
var cNum = CustomColumnFactory.Int64("Num");
var cFirst = CustomColumnFactory.String("First");
var cLast = CustomColumnFactory.String("Last");
var user = new TableUser();
await Select(
(user.FirstName + " " + user.LastName)
.As(cUserName),
RowNumber()
/*.OverPartitionBy(some fields)*/
.OverOrderBy(user.FirstName)
.As(cNum),
FirstValue(user.FirstName + " " + user.LastName)
/*.OverPartitionBy(some fields)*/
.OverOrderBy(user.FirstName)
.FrameClauseEmpty()
.As(cFirst),
LastValue(user.FirstName + " " + user.LastName)
/*.OverPartitionBy(some fields)*/
.OverOrderBy(user.FirstName)
.FrameClause(
FrameBorder.UnboundedPreceding,
FrameBorder.UnboundedFollowing)
.As(cLast))
.From(user)
.Query(database,
r => Console.WriteLine(
$"Num: {cNum.Read(r)}, Name: {cUserName.Read(r)}, " +
$"First: {cFirst.Read(r)}, Last: {cLast.Read(r)}"));
}
