کوئری‌های تجمعی این قسمت، کمی پیچیده‌تر هستند و برای حل آنها باید از
window functions استفاده کرد و چون این مفهوم توسط EF-Core پشتیبانی نمیشود (منظور توسط LINQ to Entities آن است و نه SQL نویسی مستقیم)، در بعضی از موارد مجبور خواهیم شد اطلاعات مورد نیاز گزارش را از بانک اطلاعاتی دریافت کرده و سپس در سمت کلاینت توسط LINQ to Objects شکل دهی کنیم.
مثال 12: محاسبه کنید در سال 2012 و به ازای هر ماه مجزای آن، چه تعداد slots رزرو شده‌اند؛ قسمت دوم.
این مثال را در
قسمت قبل (مثال 6 آن) نیز بررسی کردیم. در اینجا میخواهیم در گزارش نهایی تولید شده، پس از اتمام ردیف‌های یک ماه به ازای یک امکان خاص، جمع کل آن نیز درج شود و همچنین در پایان تمام ردیف‌ها، جمع کل نهایی ذکر شود؛ چیزی شبیه به تصویر زیر که در آن 910، جمع کل slots ماه 8 است و 9191، جمع کل سال.
روش پیشنهادی حل این مساله استفاده از مفهومی به نام «
GROUP BY ROLLUP» است:
SELECT facid,
DATEPART(month, [StartTime]) AS month,
sum(slots) AS slots
FROM bookings
WHERE starttime >= '2012-01-01'
AND starttime < '2013-01-01'
GROUP BY ROLLUP(facid, DATEPART(month, [StartTime]))
ORDER BY facid, month;
یک چنین گروه بندی توسط LINQ to Entities پشتیبانی نمیشود. اما خلاصه‌ی این گزارش به این صورت است:
ابتدا جمع slots را گروه بندی شده بر اساس هر ماه سال محاسبه میکنیم. این قسمت توسط LINQ to Entities قابل انجام است؛ همان مثال 6 قسمت قبل است.
سپس این اطلاعات که اکنون در سمت کلاینت (یعنی برنامه‌ی ما) در حافظه موجود هستند، نیاز دارند به ازای هر گروه، یک جمع کل (sub total) و به ازای کل سال نیز یک جمع کل (grand total یا total) پیدا کنند.
ROLLUP(facid, month) اطلاعات تجمعی سلسه مراتبی پارامترهای ارسالی به آن را تولید میکند. یعنی (facid, month), (facid) و (). پیاده سازی LINQ to Objects این تابع را در اینجا میتوانید مشاهده کنید:
Utils\GroupingExtensions.cs
بنابراین راه حل این مساله به صورت زیر خواهد بود:
var date1 = new DateTime(2012, 01, 01);
var date2 = new DateTime(2013, 01, 01);
var facilities = context.Bookings
.Where(booking => booking.StartTime >= date1
&& booking.StartTime < date2)
.GroupBy(booking => new { booking.FacId, booking.StartTime.Month })
.Select(group => new
{
group.Key.FacId,
group.Key.Month,
TotalSlots = group.Sum(booking => booking.Slots)
})
.OrderBy(result => result.FacId)
.ThenBy(result => result.Month)
.ToList()
//This is new
.GroupByWithRollup(
item => item.FacId,
item => item.Month,
(primaryGrouping, secondaryGrouping) => new
{
FacId = primaryGrouping.Key,
Month = secondaryGrouping.Key,
TotalSlots = secondaryGrouping.Sum(item => item.TotalSlots)
},
item => new
{
FacId = item.Key,
Month = -1,
TotalSlots = item.SubTotal(subItem => subItem.TotalSlots)
},
items => new
{
FacId = -1,
Month = -1,
TotalSlots = items.GrandTotal(subItem => subItem.TotalSlots)
});
تا جائیکه متد ToList فراخوانی شده، همان مثال 6 قسمت قبل است. پس از آن چون این لیست را درون حافظه داریم، اکنون متد الحاقی جدید GroupByWithRollup را به آن اعمال میکنیم تا اطلاعات گروه بندی اصلی، اطلاعات subTotal (همان ردیف اضافه‌ی تولید شده‌ی حاصل جمع هر گروه) و total (یا همان ردیف جمع کل گزارش) را تولید کند.
در اینجا سلول‌هایی که اطلاعاتی ندارند، با منهای یک مشخص شده‌اند؛ در گزارش اصلی با null مقدار دهی شده بودند.
مثال 13: به ازای نام هر کدام از امکانات موجود، جمع کل تعداد ساعات رزرو شده‌ی آنها را محاسبه کنید.
هر slot تنها نیم ساعت است و گزارش نهایی باید به همراه ستون‌های facid, name, Total Hours باشد؛ مرتب شده بر اساس facid.
var items = context.Bookings
.GroupBy(booking => new { booking.FacId, booking.Facility.Name })
.Select(group => new
{
group.Key.FacId,
group.Key.Name,
TotalHours = group.Sum(booking => booking.Slots) / 2M
})
.OrderBy(result => result.FacId)
.ToList();
در اینجا روش گروه بندی بر اساس FacId که از جدول Bookings تامین میشود و Facility.Name را که از جدول دیگری به نامFacilities تامین میشود، ملاحظه می‌کنید که به صورت خودکار جوین لازم آن در کوئری نهایی تولید خواهد شد:
مثال 14: گزارشی را از اولین رزرو کاربران پس از September 1st 2012، تهیه کنید.
این گزارش باید به همراه ستون‌های surname, firstname, memid, starttime باشد؛ مرتب شده بر اساس memid.
var date1 = new DateTime(2012, 09, 01);
var items = context.Bookings
.Where(booking => booking.StartTime >= date1)
.GroupBy(booking => new
{
booking.Member.Surname,
booking.Member.FirstName,
booking.Member.MemId
})
.Select(group => new
{
group.Key.Surname,
group.Key.FirstName,
group.Key.MemId,
StartTime = group.Min(booking => booking.StartTime)
})
.OrderBy(result => result.MemId)
.ToList();
هدف از این مثال محاسبه‌ی حداقل StartTimeها به ازای اطلاعات گروه بندی شده‌ی بر اساس هر کاربر است که روش آنرا با استفاده از متد group.Min مشاهده می‌کنید.
مثال 15: گزارشی را از کاربران تهیه کنید که هر ردیف آن، به همراه تعداد کل کاربران باشد.
این گزارش باید به همراه ستون‌های count, firstname, surname باشد؛ مرتب شده بر اساس joindate.
var members = context.Members
.OrderBy(member => member.JoinDate)
.Select(member => new
{
Count = context.Members.Count(),
member.FirstName,
member.Surname
})
.ToList();
EF-Core این گزارش به همراه یک sub-query را تبدیل به دو کوئری میکند؛ ابتدا مقدار ثابت تعداد اعضاء را محاسبه میکند و سپس این تعداد ثابت را در کوئری دوم بکار می‌گیرد:
SELECT COUNT(*)
FROM [Members] AS [m];
SELECT [m].[FirstName],
[m].[Surname],
@__Count_0 AS [Count]
FROM [Members] AS [m]
ORDER BY [m].[JoinDate];
مثال 16: گزارشی را از کاربران تهیه کنید که به همراه ستون شماره ردیف آنها نیز باشد.
باید بخاطر داشت که ID کاربران پشت سرهم نیست و همچنین این گزارش باید به همراه ستون‌های row_number, firstname, surname باشد؛ مرتب شده بر اساس joindate.
هدف اصلی از این مثال، کار با مفهوم window functionها و تابع row_number است:
SELECT row_number() OVER (ORDER BY joindate) AS row_number,
firstname,
surname
FROM members
ORDER BY joindate;
اما چون چنین قابلیتی با LINQ to Entities قابل پیاده سازی نیست، در اینجا نیز ابتدا ردیف‌های گزارش را تولید میکنیم و سپس شماره ردیف را در سمت کلاینت (در سمت برنامه و توسط LINQ to Objects)، اضافه خواهیم کرد:
var members = context.Members
.OrderBy(member => member.JoinDate)
.Select(member => new
{
member.FirstName,
member.Surname
})
.ToList()
/*
SELECT [m].[FirstName], [m].[Surname]
FROM [Members] AS [m]
ORDER BY [m].[JoinDate]
*/
// Now using LINQ to Objects
.Select((member, index) => new
{
RowNumber = index + 1,
member.FirstName,
member.Surname
})
.ToList();
تا قسمت ToList، یک کوئری LINQ to Entities استاندارد مشاهده میشود. پس از آن چون این اطلاعات درون حافظه هستند، میتوان با استفاده از LINQ to Objects و قابلیت index
ذاتی موجود در متد Select، شماره ردیف‌ها را که همان index + 1 هستند، تولید کرد.
مثال 17: کدامیک از امکانات موجود، بیشترین slots رزرو شده را دارد؟ قسمت دوم.
این مورد همان مثال 11 قسمت قبل است که پاسخ آنرا یافتیم (و از تکرار مجدد آن صرفنظر میکنیم) و هدف اصلی آن رسیدن به کوئری window function دار زیر است که تنها از طریق اجرای یک raw sql در EF-Core قابل اجرا است:
SELECT facid,
total
FROM (SELECT facid,
sum(slots) AS total,
rank() OVER (ORDER BY sum(slots) DESC) AS rank
FROM bookings
GROUP BY facid) AS ranked
WHERE rank = 1; مثال 18: به کاربران بر اساس تعداد ساعات رزرو آنها، امتیاز دهی (رتبه بندی) کنید.
این گزارش باید به همراه ستون‌های firstname, surname, hours, rank باشد؛ مرتب شده بر اساس rank, surname.
هدف اصلی از این مثال، رسیدن به کوئری rank دار زیر است:
SELECT mems.firstname,
mems.surname,
((sum(bks.slots) + 10) / 20) * 10 AS hours,
rank() OVER (ORDER BY ((sum(bks.slots) + 10) / 20) * 10 DESC) AS rank
FROM bookings AS bks
INNER JOIN
members AS mems
ON bks.memid = mems.memid
GROUP BY mems.firstname,
mems.surname
ORDER BY rank, mems.surname, mems.firstname;
هرچند نمی‌توان از window functions به همراه LINQ to Entities استفاده کرد، اما میتوان نتیجه‌ای را که خواسته (تولید rank بر اساس تعداد ساعات استفاده شده) به صورت زیر نیز تولید کرد که شامل استفاده‌ی از LINQ to Objects هم نمیشود؛ یعنی برای تولید Rank، الزاما نیازی به Window Functions نیست:
var itemsQuery = context.Bookings
.GroupBy(booking => new
{
booking.Member.FirstName,
booking.Member.Surname
})
.Select(group => new
{
group.Key.FirstName,
group.Key.Surname,
Hours = (group.Sum(booking => booking.Slots) + 10) / 20 * 10
})
.OrderByDescending(result => result.Hours)
.ThenBy(result => result.Surname)
.ThenBy(result => result.FirstName);
var rankedItems = itemsQuery.Select(thisItem => new
{
thisItem.FirstName,
thisItem.Surname,
thisItem.Hours,
Rank = itemsQuery.Count(mainItem => mainItem.Hours > thisItem.Hours) + 1
})
.ToList();
در ابتدا یک کوئری متداول گروه بندی شده‌ی بر اساس کاربران را مشاهده می‌کنید که به ازای هر کاربر، جمع تعداد ساعات رزور شده‌ی او محاسبه شده‌است. البته itemsQuery یک IQueryable مرتب سازی شده‌است؛ یعنی چون هنوز ToList بر روی آن فراخوانی نشده، بر روی بانک اطلاعاتی اجرا نشده‌است و فقط یک LINQ Expression است. سپس این LINQ Expression را به صورت زنجیروار در یک کوئری دیگر استفاده کرده‌ایم که در آن sub-query دارای itemsQuery.Count، مقدار rank را تشکیل داده‌است. این ساب کوئری به این معنا است: چه تعداد ساعت حاصل از کوئری گروه بندی و مرتب شده، از مقدار ساعت ردیف جاری بیشتر است + 1 که رتبه‌ی هر ردیف را نسبت به ردیف‌های دیگر محاسبه میکند.
با این خروجی SQL نهایی:
مثال 19: سه امکانی را لیست کنید که بالاترین میزان فروش را داشته‌اند.
این گزارش باید به همراه ستون‌های name, rank باشد؛ مرتب شده بر اساس rank.
روش محاسبه‌ی این گزارش با مثال قبلی یکی است (البته اینبار رتبه بندی بر اساس TotalRevenue است) و فقط در انتهای آن یک Where(result => result.Rank <= 3) را بیشتر دارد:
var facilitiesQuery =
context.Bookings.Select(booking =>
new
{
booking.Facility.Name,
Revenue = booking.MemId == 0 ?
booking.Slots * booking.Facility.GuestCost
: booking.Slots * booking.Facility.MemberCost
})
.GroupBy(b => b.Name)
.Select(group => new
{
Name = group.Key,
TotalRevenue = group.Sum(b => b.Revenue)
})
.OrderBy(result => result.TotalRevenue);
var rankedFacilities = facilitiesQuery.Select(thisItem => new
{
thisItem.Name,
thisItem.TotalRevenue,
Rank = facilitiesQuery.Count(mainItem => mainItem.TotalRevenue > thisItem.TotalRevenue) + 1
})
.Where(result => result.Rank <= 3)
.OrderBy(result => result.Rank)
.ToList();
ابتدا به نحو متداولی گروه بندی بر اساس نام صورت گرفته و محاسبه‌ی میزان فروش هر گروه انجام شده‌است. سپس در کوئری زنجیروار دوم، ستون Rank، به نتیجه‌ی حاصل اضافه شده‌است و اگر این Rank کمتر از 3 باشد، پاسخ مساله‌است.
مثال 20: امکانات موجود را بر اساس میزان فروشی که دارند به گروه‌هایی با تعداد مساوی high, average, low تقسیم بندی کنید.
این گزارش باید به همراه ستون‌های name, revenue باشد؛ مرتب شده بر اساس revenue, name.
هدف اصلی از این گزارش کار با
تابع ntile است که اطلاعات را بر اساس پارامتر ارسالی به آن تاجای ممکن به گروه‌های مساوی تقسیم میکند:
SELECT name,
CASE WHEN class = 1 THEN 'high' WHEN class = 2 THEN 'average' ELSE 'low' END AS revenue
FROM (SELECT facs.name AS name,
ntile(3) OVER (ORDER BY sum(CASE WHEN memid = 0 THEN slots * facs.guestcost ELSE slots * membercost END) DESC) AS class
FROM bookings AS bks
INNER JOIN
facilities AS facs
ON bks.facid = facs.facid
GROUP BY facs.name) AS subq
ORDER BY class, name;
Ntile نیز در LINQ to Entities معادلی ندارد. بنابراین ابتدا رزروهای انجام شده را بر اساس نوع امکانات رزرو شده، گروه بندی کرده و میزان فروش هر گروه را پیدا میکنیم:
var facilities =
context.Bookings.Select(booking =>
new
{
booking.Facility.Name,
Revenue = booking.MemId == 0 ?
booking.Slots * booking.Facility.GuestCost
: booking.Slots * booking.Facility.MemberCost
})
.GroupBy(b => b.Name)
.Select(group => new
{
Name = group.Key,
TotalRevenue = group.Sum(b => b.Revenue)
})
.OrderByDescending(result => result.TotalRevenue)
.ToList();
که یک چنین SQL ای را تولید میکند:
SELECT [f].[Name],
SUM(CASE WHEN [b].[MemId] = 0 THEN CAST ([b].[Slots] AS DECIMAL (18, 6)) * [f].[GuestCost] ELSE CAST ([b].[Slots] AS DECIMAL (18, 6)) * [f].[MemberCost] END) AS [TotalRevenue]
FROM [Bookings] AS [b]
INNER JOIN
[Facilities] AS [f]
ON [b].[FacId] = [f].[FacId]
GROUP BY [f].[Name]
ORDER BY SUM(CASE WHEN [b].[MemId] = 0 THEN CAST ([b].[Slots] AS DECIMAL (18, 6)) * [f].[GuestCost] ELSE CAST ([b].[Slots] AS DECIMAL (18, 6)) * [f].[MemberCost] END) DESC;
سپس با استفاده از LINQ to Objects، تابع ntile را شبیه سازی میکنیم:
var n = 3;
var tiledFacilities = facilities.Select((item, index) =>
new
{
Item = item,
Index = (index / n) + 1
})
.GroupBy(x => x.Index)
.Select(g =>
g.Select(z =>
new
{
z.Item.Name,
z.Item.TotalRevenue,
Tile = g.Key,
GroupName = g.Key == 1 ? "High" : (g.Key == 2 ? "Average" : "Low")
})
.OrderBy(x => x.GroupName)
.ThenBy(x => x.Name)
)
.ToList();
var flatTiledFacilities = tiledFacilities.SelectMany(group => group)
.Select(tile => new { tile.Name, Revenue = tile.GroupName })
.ToList();
هدف از این گزارش این است که در نتیجه‌ی مرتب سازی شده‌ی بر اساس TotalRevenue، به سه تای اول، برچسب High را بدهیم، به سه تای دوم برچسب average و به مابقی برچسب low. به همین جهت ردیف‌های حاصل را بر اساس ستون جدیدی به نام Index که بیانگر شماره ردیف گروه‌های سه تایی است، گروه بندی میکنیم و به هر گروه برچسبی را انتساب می‌دهیم. حاصل آن، گروه‌های تو در تویی است که با SelectMany، نسبت به مسطح سازی آنها اقدام شده‌است.
مثال 21: چندماه طول می‌کشد تا هر کدام از امکانات موجود بر اساس فروشی که دارند، هزینه‌ی مالکیت ابتدایی خود را کسب کنند.
این گزارش باید به همراه ستون‌های name, months باشد؛ مرتب شده بر اساس name.
var facilities =
context.Bookings.Select(booking =>
new
{
booking.Facility.Name,
booking.Facility.InitialOutlay,
booking.Facility.MonthlyMaintenance,
Revenue = booking.MemId == 0 ?
booking.Slots * booking.Facility.GuestCost
: booking.Slots * booking.Facility.MemberCost
})
.GroupBy(b => new
{
b.Name,
b.InitialOutlay,
b.MonthlyMaintenance
})
.Select(group => new
{
group.Key.Name,
RepayTime =
group.Key.InitialOutlay /
((group.Sum(b => b.Revenue) / 3) - group.Key.MonthlyMaintenance)
})
.OrderBy(result => result.Name)
.ToList();
ابتدا رزروهای انجام شده را بر اساس نوع امکانات رزرو شده گروه بندی کرده و میزان فروش هر گروه را پیدا میکنیم. سپس بر روی این حاصل، محاسبات خاص RepayTime را انجام داده و نتیجه را بازگشت می‌دهیم:
مثال 22: گزارش میانگین متحرک فروش کل هر کدام از روزهای August 2012 را برای یک بازه‌ی 15 روزه‌ی قبل، محاسبه کنید.
این گزارش باید به همراه ستون‌های date, revenue باشد؛ مرتب شده بر اساس date. در این گزارش روزهای ماه 8 میلادی ردیف شده و به ازای هر ردیف، میانگین فروش 15 روز قبل از آن تاریخ، نمایش داده میشود. به همین جهت به آن میانگین متحرک نیز می‌گویند.
هدف اصلی از این گزارش، استفاده از توابع avg(revdata.rev) over است. اما چون نمی‌توان از آنها در LINQ to Entities استفاده کرد، از روش دیگری که شامل جوین یک جدول با خودش است، استفاده میکنیم:
var startDate = new DateTime(2012, 08, 1);
var endDate = new DateTime(2012, 08, 31);
var period = 14;
var dailyRevenueQuery =
context.Bookings
.Select(booking =>
new
{
StartDate = booking.StartTime.Date, // How to group by date (or TruncateTime) in EF-Core
Revenue = booking.MemId == 0 ?
booking.Slots * booking.Facility.GuestCost
: booking.Slots * booking.Facility.MemberCost
})
.GroupBy(b => b.StartDate)
.Select(group =>
new
{
Date = group.Key,
TotalRevenue = group.Sum(b => b.Revenue)
});
ابتدا میزان کل فروش‌ها را بر حسب تاریخ هر روز ماه 8 میلادی، محاسبه میکنیم. برای این گروه بندی خاص نیاز خواهیم داشت تا از زمان یک تاریخ صرفنظر کنیم (چون StartTime به همراه تاریخ و ساعت است). برای اینکار فقط کافی است بجای booking.StartTime از booking.StartTime.
Date استفاده شود تا نتیجه‌ی حاصل به CONVERT(date, [b0].[StartTime]) ترجمه شده و قسمت زمان تاریخ از کوئری نهایی حذف شود.
اکنون که میزان کل فروش روزها را داریم، میخواهیم میانگین فروش 15 روز قبل شروع شده‌ی از از ابتدای ماه 8، تا انتهای آنرا محاسبه کنیم. برای اینکار نیاز است کوئری فوق را یکبار دیگر با خودش جوین کنیم تا از یک سر آن تاریخ هر روز و از طرف دیگر، میانگین 15 روز قبل، تولید شود:
var movingAvgs =
dailyRevenueQuery
.Select(dr1 =>
new
{
dr1.Date,
MovingAvg = dailyRevenueQuery
.Where(dr2 => dr2.Date <= dr1.Date && dr2.Date >= dr1.Date.AddDays(-period))
.Average(dr2 => dr2.TotalRevenue)
})
.Where(result => result.Date >= startDate && result.Date <= endDate)
.OrderBy(result => result.Date)
.ToList(); 
کدهای کامل این قسمت را در اینجا میتوانید مشاهده کنید.
