NTILE function equivalent in C#

Question

I need to implement the following SQL in C# Linq:

SELECT NTILE (3) OVER (ORDER BY TransactionCount DESC) AS A...

I couldn't find any answer to a similar problem except this. However I don't think that is what I am looking for.

I don't even know where to start, if anyone could please give me at least a starting point I'd appreciated.

-- EDIT --

Trying to explain a little better. I have one Store X with Transactions, Items, Units and other data that I retrieve from SQL and store in a object in C#.

I have a list of all stores with the same data but in this case I retrieve it from Analysis Services due to the large amount of data retrieved (and other reasons) and I store all of it in another object in C#.

So what I need is to order the list and find out if store X is in the top quartile of that list or second or third...

I hope that helps to clarify what I am trying to achieve.

Thank you

Your first step is to read the docs (https://learn.microsoft.com/en-us/sql/t-sql/functions/ntile-transact-sql). As far as I can see, LINQ does not implement an NTILE function so you'll have to roll your own. — dumetrulo, Feb 28 '18 at 06:20
It's not quite clear what you want/need. Do you need the number of the groups along with the original objects? Maybe you could provide a use case or describe what you need more thoroughly. — Paul Kertscher, Feb 28 '18 at 06:32

score 3 · Accepted Answer · answered Feb 28 '18 at 07:39

I believe that there is no simple LINQ equivalent of NTILE(n). Anyway, depending on your needs it's not that hard to write one.

The T-SQL documentation says

Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

(see here)

For a very crude implementation of NTILE you can use GroupBy. The following example uses an int[] for sake of simplicity, but of course you are not restricted to

int n = 4;
int[] data = { 5, 2, 8, 2, 3, 8, 3, 2, 9, 5 };
var ntile = data.OrderBy(value => value)
                .Select((value,index) => new {Value = value, Index = index})
                .GroupBy(c => Math.Floor(c.Index / (data.Count() / (double)n)), c => c.Value);

First, our data is ordered ascending by it's values. If you are not using simple ints this could be something like store => store.Revenue (given you'd like to get the quantiles by revenue of the stores). Futhermore we are selecting the ordered data to an anonymous type, to include the indices. This is necessary since the indices are necessary for grouping, but it seems as GroupBy does not support lambdas with indices, as Select does.

The third line is a bit less intuitive, but I'll try and explain: The NTILE function assigns groups, the rows are assigned to. To create n groups, we devide N (number of items) by n to get the items per group and then device the current index by that, to determine in which group the current item is. To get the number of groups right I had to make the number of items per group fractional and floor the calculated group number, but admittedly, this is rather empirical.

ntile will contain n groups, each one having Key equal to the group number. Each group is enumerable. If you'd like to determine, if an element is in the second quartile, you can check if groups.Where(g => g.Key == 1) contains the element.

Remarks: The method I've used to determine the group may need some fine adjustment.

score 2 · Answer 2 · answered Feb 28 '18 at 07:37

You can do it using GroupBy function by grouping based on index of the object. Consider a list of integers like this:-

List<int> numbers = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8 };

You can first project the Index of all elements using Select and finally group by their resp. index. While calculating the Index we can divide it by NTILE value (3 in this case):-

var result = numbers.Select((v, i) => new { Value = v, Index = i / 3 })
                    .GroupBy(x => x.Index)
                    .Select(x => x.Select(z => z.Value).ToList());

Fiddle.

NTILE function equivalent in C#

2 Answers2