SQL Server – Does NTILE Work Over Floating Point Numbers?

sql servert-sql

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?

Best Answer

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

Related Question