4

I am trying to use NTILE on my data using a float data type in my PARTITION BY clause. Please excuse the contrived example, but I think the best way to illustrate my question and confusion is through this code:

CREATE TABLE Test 
(
    Cash float NOT NULL,
    Number int NOT NULL
)
GO

INSERT INTO TEST VALUES(1.05,1);
INSERT INTO TEST VALUES(1.368,1);
INSERT INTO TEST VALUES(0.775,1);
INSERT INTO TEST VALUES(1.699,1);
INSERT INTO TEST VALUES(1.599,1);
INSERT INTO TEST VALUES(0.80,1);
INSERT INTO TEST VALUES(0.80,1);
INSERT INTO TEST VALUES(0.994,1);
INSERT INTO TEST VALUES(0.848,1);
INSERT INTO TEST VALUES(0.675,1);
INSERT INTO TEST VALUES(0.575,1);
INSERT INTO TEST VALUES(12.998,1);
INSERT INTO TEST VALUES(1.999,1);
INSERT INTO TEST VALUES(0.65,1);
INSERT INTO TEST VALUES(0.80,1);
INSERT INTO TEST VALUES(2.60,1);

SELECT CASH, 
    NTILE(3) OVER (PARTITION BY Cash ORDER BY Cash) AS Trio
    INTO #Test
FROM Test

I would have expected the groupings to be something like this:

0.575   1
0.65    1
0.675   1
0.775   1
0.8     1
0.8     2
0.8     2
0.848   2
0.994   2
1.05    2
1.368   3
1.599   3
1.699   3
1.999   3
2.6     3
12.998  3

But instead, The result set looks like this:

CASH    Trio
0.575   1
0.65    1
0.675   1
0.775   1
0.8     1
0.8     2
0.8     3
0.848   1
0.994   1
1.05    1
1.368   1
1.599   1
1.699   1
1.999   1
2.6     1
12.998  1

And the range groupings look really strange when max and min are applied by group

SELECT 
    MAX(CASH), MIN(CASH), Trio
FROM #Test
GROUP BY Trio

MAX     MIN     TRIO
12.998  0.575   1
0.8     0.8     2
0.8     0.8     3

Can anyone explain what is going on here?

5

The NTILE function breaks an input set down into N equal sized groups. To determine how many rows belong in each group, SQL Server must first determine the total number of rows in the input set.

If the NTILE function includes a PARTITION BY clause, SQL Server must compute the number of rows in each partition separately. Once we know the number of rows in each partition, we can write the NTILE function as

NTILE(N) := (N * (ROW_NUMBER() - 1) / COUNT(*)) + 1

where COUNT(*) is the number of rows in each partition.

Taking above into consideration, below will explain :

select *, (3*(RowNumber-1)/Cnt)+1 AS MyNTile
from 
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY Cash ORDER BY cash) AS RowNumber,
        COUNT(*) OVER (PARTITION BY Cash ) AS Cnt,
        NTILE(3) OVER (PARTITION BY Cash ORDER BY Cash) AS NTile,
        NTILE(3) OVER (ORDER BY Cash) AS WholeTableNtile

    FROM Test
) T

enter image description here

Refer to : Ranking Functions: RANK, DENSE_RANK, and NTILE

  • 1
    Thank you for not just answering the question, but explaining it as well. Sometimes in the midst of a problem, the obvious explanation escapes and your explanation is both clear and concise! – Thronk Apr 29 '15 at 1:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.