-
Notifications
You must be signed in to change notification settings - Fork 6
/
RankTests.cs
94 lines (70 loc) · 2.55 KB
/
RankTests.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
namespace Zomp.EFCore.WindowFunctions.Testing;
public partial class RankTests
{
[Fact]
public void RowNumberBasic()
{
var query = DbContext.TestRows
.Select(r => EF.Functions.RowNumber(EF.Functions.Over().OrderBy(r.Id).PartitionBy(r.Id / 10)));
var result = query.ToList();
var expectedSequence = TestRows.GroupBy(r => r.Id / 10)
.SelectMany(g =>
g.Select((j, i) => (long)(i + 1)));
Assert.Equal(expectedSequence, result);
}
[SkippableFact]
public void RowNumberEmptyOver()
{
Skip.If(DbContext.IsSqlServer);
var query = DbContext.TestRows
.Select(r => EF.Functions.RowNumber(EF.Functions.Over()));
var result = query.ToList();
var expectedSequence = TestRows
.Select((j, i) => (long)(i + 1));
Assert.Equal(expectedSequence, result);
}
[Fact]
public void RankBasic()
{
var query = DbContext.TestRows
.Select(r => EF.Functions.Rank(EF.Functions.Over().OrderBy(r.Id / 10)));
var result = query.ToList();
var groups = TestRows.GroupBy(r => r.Id / 10);
var expectedSequence = TestRows
.Select(r => r.Id / 10)
.Select(v => (long)groups
.Where(g => g.Key < v)
.Select(g => g.Count())
.Sum() + 1);
Assert.Equal(expectedSequence, result);
}
[Fact]
public void DenseRankBasic()
{
var query = DbContext.TestRows
.Select(r => EF.Functions.DenseRank(EF.Functions.Over().OrderBy(r.Id / 10)));
var result = query.ToList();
var expectedSequence = TestRows
.GroupBy(r => r.Id / 10)
.SelectMany((g, i) => g.Select(j => (long)(i + 1)));
Assert.Equal(expectedSequence, result);
}
[Fact]
public void PercentRankBasic()
{
bool nullsLast = DbContext.IsPostgreSQL;
var query = DbContext.TestRows
.Select(r => EF.Functions.PercentRank(EF.Functions.Over().OrderBy(r.Col1)));
var result = query.ToList();
var groups = TestRows.GroupBy(r => r.Col1);
var comparer = new NullSensitiveComparer<int>(nullsLast);
var expectedSequence = TestRows
.Select(r => r.Col1)
.OrderBy(x => x, comparer)
.Select(v => groups
.Where(g => comparer.Compare(g.Key, v) < 0)
.Select(g => g.Count())
.Sum() / (double)(TestRows.Length - 1));
Assert.Equal(expectedSequence, result.Select(r => r));
}
}