PERCENT_RANK()
function over a result set example
var year = 2017;
var query = DbContext.Set<StaffSalesPercentile>()
.Query((VwStaffSales sales, Staffs staffs, StaffSalesPercentile alias) =>
{
var fullName = CONCAT_WS(" ", staffs.FirstName, staffs.LastName);
var cumeDist = AggregateBy(PERCENT_RANK())
.OVER(ORDER(BY(sales.NetSales).DESC));
var r = SELECT<StaffSalesPercentile>(fullName.@as(alias.FullName),
sales.NetSales.@as(alias.NetSales),
sales.Year.@as(alias.Year),
cumeDist.@as(alias.Percentile));
FROM(sales).JOIN(staffs).ON(staffs.StaffId == sales.StaffId);
WHERE(sales.Year == year);
return r;
});
foreach (var percentile in query)
Console.WriteLine((percentile.Year, percentile.FullName, percentile.NetSales, percentile.Percentile));
PERCENT_RANK()
function over partitions example
var years = new int?[] { 2016, 2017 };
var query = DbContext.Set<StaffSalesPercentile>()
.Query((VwStaffSales sales, Staffs staffs, StaffSalesPercentile alias) =>
{
var fullName = CONCAT_WS(" ", staffs.FirstName, staffs.LastName);
var cumeDist = AggregateBy(PERCENT_RANK())
.OVER(PARTITION(BY(sales.Year))
.ORDER(BY(sales.NetSales).DESC));
var r = SELECT<StaffSalesPercentile>(fullName.@as(alias.FullName),
sales.NetSales.@as(alias.NetSales),
sales.Year.@as(alias.Year),
cumeDist.@as(alias.Percentile));
FROM(sales).JOIN(staffs).ON(staffs.StaffId == sales.StaffId);
WHERE(years.Contains(sales.Year));
return r;
});
foreach (var percentile in query)
Console.WriteLine((percentile.Year, percentile.FullName, percentile.NetSales, percentile.Percentile));