-
-
Notifications
You must be signed in to change notification settings - Fork 5
/
ClassifyFacilitiesByValue.cs
110 lines (105 loc) · 5.19 KB
/
ClassifyFacilitiesByValue.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
using System.Linq;
using EFCorePgExercises.DataLayer;
using EFCorePgExercises.Utils;
using FluentAssertions;
namespace EFCorePgExercises.Exercises.Aggregation
{
[FullyQualifiedTestClass]
public class ClassifyFacilitiesByValue
{
[FullyQualifiedTestMethod]
public void Test()
{
// https://pgexercises.com/questions/aggregates/classify.html
// Classify facilities into equally sized groups of high, average, and low
// based on their revenue. Order by classification and facility name.
//
//select name, case when class=1 then 'high'
// when class=2 then 'average'
// else 'low'
// end 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 cd.bookings bks
// inner join cd.facilities facs
// on bks.facid = facs.facid
// group by facs.name
// ) as subq
//order by class, name;
EFServiceProvider.RunInContext(context =>
{
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();
/*
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
*/
// Then using LINQ to Objects
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();
var expectedResult = new[]
{
new { Name = "Massage Room 1", Revenue= "High" },
new { Name = "Massage Room 2", Revenue= "High" },
new { Name = "Tennis Court 2", Revenue= "High" },
new { Name = "Badminton Court", Revenue= "Average" },
new { Name = "Squash Court", Revenue= "Average" },
new { Name = "Tennis Court 1", Revenue= "Average" },
new { Name = "Pool Table", Revenue= "Low" },
new { Name = "Snooker Table", Revenue= "Low" },
new { Name = "Table Tennis", Revenue= "Low" }
};
flatTiledFacilities.Should().BeEquivalentTo(expectedResult);
});
}
}
}