A more intelligent ntile

Question

When using the ntile() window function, the major issue is that it arbitrarily groups into roughly equal parts regardless of the actual value.

For example with the following query:

select
    id,title,price,
    row_number() over(order by price) as row_number,
    rank() over(order by price) as rank,
    count(*) over(order by price) as count,
    dense_rank() over(order by price) as dense_rank,
    ntile(10) over(order by price) as decile
from paintings
order by price;

I will get 10 groups of about the same size, with the strong likelihood that paintings with the same price will end up in different bins.

For Example:

┌────┬────────────────────────────────────────────┬───────┬────────────┬──────┬───────┬────────────┬────────┐
│ id │ title                                      │ price │ row_number │ rank │ count │ dense_rank │ decile │
╞════╪════════════════════════════════════════════╪═══════╪════════════╪══════╪═══════╪════════════╪════════╡
│ 11 │ Eyes in the Heat                           │ 10    │ 1          │ 1    │ 1     │ 1          │ 1      │
│ 19 │ Deux fillettes, fond jaune et rouge        │ 11    │ 2          │ 2    │ 2     │ 2          │ 1      │
│ 17 │ Flowers in a Pitcher                       │ 12    │ 3          │ 3    │ 6     │ 3          │ 1      │
│ 5  │ Composition with Red, Yellow and Blue      │ 12    │ 4          │ 3    │ 6     │ 3          │ 2      │
│ 18 │ La lecon de musique (The Music Lesson)     │ 12    │ 5          │ 3    │ 6     │ 3          │ 2      │
│ 9  │ The Adoration of the Magi                  │ 12    │ 6          │ 3    │ 6     │ 3          │ 2      │
│ 29 │ Self-Portrait                              │ 14    │ 7          │ 7    │ 10    │ 4          │ 3      │
│ 25 │ Symphony in White, No. 1: The White Girl   │ 14    │ 8          │ 7    │ 10    │ 4          │ 3      │
│ 30 │ The Anatomy Lecture of Dr. Nicolaes Tulp   │ 14    │ 9          │ 7    │ 10    │ 4          │ 3      │
│ 20 │ Les repasseuses (Women Ironing)            │ 14    │ 10         │ 7    │ 10    │ 4          │ 4      │
│ 1  │ The Birth of Venus                         │ 15    │ 11         │ 11   │ 14    │ 5          │ 4      │
│ 12 │ Femme se promenant dans une foret exotique │ 15    │ 12         │ 11   │ 14    │ 5          │ 4      │
│ 24 │ Portrait of the Painter’s Mother           │ 15    │ 13         │ 11   │ 14    │ 5          │ 5      │
│ 28 │ Jeunes filles au piano                     │ 15    │ 14         │ 11   │ 14    │ 5          │ 5      │
│ 7  │ Portrait de l artiste (Self-portrait)      │ 16    │ 15         │ 15   │ 17    │ 6          │ 5      │
│ 3  │ The Last Supper                            │ 16    │ 16         │ 15   │ 17    │ 6          │ 6      │
│ 13 │ Combat of a Tiger and a Buffalo            │ 16    │ 17         │ 15   │ 17    │ 6          │ 6      │
│ 4  │ The Creation of Man                        │ 17    │ 18         │ 18   │ 19    │ 7          │ 6      │
│ 22 │ Le Chemin de Fer                           │ 17    │ 19         │ 18   │ 19    │ 7          │ 7      │
│ 6  │ Femmes de Tahiti [Sur la plage]            │ 18    │ 20         │ 20   │ 24    │ 8          │ 7      │
│ 21 │ Le Bar aux Folies-Berg                     │ 18    │ 21         │ 20   │ 24    │ 8          │ 7      │
│ 26 │ Lady at the Piano                          │ 18    │ 22         │ 20   │ 24    │ 8          │ 8      │
│ 15 │ Remembrance of a Garden                    │ 18    │ 23         │ 20   │ 24    │ 8          │ 8      │
│ 16 │ 1914                                       │ 18    │ 24         │ 20   │ 24    │ 8          │ 8      │
│ 14 │ Ancient Sound, Abstract on Black           │ 19    │ 25         │ 25   │ 28    │ 9          │ 9      │
│ 8  │ The Large Turf                             │ 19    │ 26         │ 25   │ 28    │ 9          │ 9      │
│ 23 │ On the Beach                               │ 19    │ 27         │ 25   │ 28    │ 9          │ 9      │
│ 2  │ Portrait of Mona Lisa                      │ 19    │ 28         │ 25   │ 28    │ 9          │ 10     │
│ 27 │ On the Terrace                             │ 20    │ 29         │ 29   │ 30    │ 10         │ 10     │
│ 10 │ The She-Wolf                               │ 20    │ 30         │ 29   │ 30    │ 10         │ 10     │
└────┴────────────────────────────────────────────┴───────┴────────────┴──────┴───────┴────────────┴────────┘

Note that there are four items with price 12, but two of them are in the have decile 1, and two of them decile 2. I would like to keep these items together, and I’m not fussed about which decile.

I have included other window functions to make the comparison.

It seems that ntile() uses the row_number() only and bases the cuttoffs on that. It would be more fair if it used either the rank() or count(*) function as items with the same price would end up in the same bin.

This is the behaviour for both PostgreSQL and SQL Server, and, presumably, for the rest.

The question is, is there a way to achieve this?

Answer 1

You could use rank() and do integer division with number of rows for each bin.

declare @T table(id int, title varchar(100), price int);

insert into @T(id, title, price) values
(19, 'Deux fillettes, fond jaune et rouge        ', 11),
(17, 'Flowers in a Pitcher                       ', 12),
(5 , 'Composition with Red, Yellow and Blue      ', 12),
(18, 'La lecon de musique (The Music Lesson)     ', 12),
(9 , 'The Adoration of the Magi                  ', 12),
(29, 'Self-Portrait                              ', 14),
(25, 'Symphony in White, No. 1: The White Girl   ', 14),
(30, 'The Anatomy Lecture of Dr. Nicolaes Tulp   ', 14),
(20, 'Les repasseuses (Women Ironing)            ', 14),
(1 , 'The Birth of Venus                         ', 15),
(12, 'Femme se promenant dans une foret exotique ', 15),
(24, 'Portrait of the Painter’s Mother           ', 15),
(28, 'Jeunes filles au piano                     ', 15),
(7 , 'Portrait de l artiste (Self-portrait)      ', 16),
(3 , 'The Last Supper                            ', 16),
(13, 'Combat of a Tiger and a Buffalo            ', 16),
(4 , 'The Creation of Man                        ', 17),
(22, 'Le Chemin de Fer                           ', 17),
(6 , 'Femmes de Tahiti [Sur la plage]            ', 18),
(21, 'Le Bar aux Folies-Berg                     ', 18),
(26, 'Lady at the Piano                          ', 18),
(15, 'Remembrance of a Garden                    ', 18),
(16, '1914                                       ', 18),
(14, 'Ancient Sound, Abstract on Black           ', 19),
(8 , 'The Large Turf                             ', 19),
(23, 'On the Beach                               ', 19),
(2 , 'Portrait of Mona Lisa                      ', 19),
(27, 'On the Terrace                             ', 20),
(10, 'The She-Wolf                               ', 20);

declare @BinCount int = 10;
declare @BinSize int;
select @BinSize = 1 + count(*) / @BinCount from @T;

select T.id,
       T.title,
       T.price,
       1 + rank() over(order by T.price) / @BinSize as decile
from @T as T;

Result:

id  title                                       price  decile
--- ------------------------------------------- ------ --------------------
19  Deux fillettes, fond jaune et rouge         11     1
17  Flowers in a Pitcher                        12     1
5   Composition with Red, Yellow and Blue       12     1
18  La lecon de musique (The Music Lesson)      12     1
9   The Adoration of the Magi                   12     1
29  Self-Portrait                               14     3
25  Symphony in White, No. 1: The White Girl    14     3
30  The Anatomy Lecture of Dr. Nicolaes Tulp    14     3
20  Les repasseuses (Women Ironing)             14     3
1   The Birth of Venus                          15     4
12  Femme se promenant dans une foret exotique  15     4
24  Portrait of the Painter’s Mother            15     4
28  Jeunes filles au piano                      15     4
7   Portrait de l artiste (Self-portrait)       16     5
3   The Last Supper                             16     5
13  Combat of a Tiger and a Buffalo             16     5
4   The Creation of Man                         17     6
22  Le Chemin de Fer                            17     6
6   Femmes de Tahiti [Sur la plage]             18     7
21  Le Bar aux Folies-Berg                      18     7
26  Lady at the Piano                           18     7
15  Remembrance of a Garden                     18     7
16  1914                                        18     7
14  Ancient Sound, Abstract on Black            19     9
8   The Large Turf                              19     9
23  On the Beach                                19     9
2   Portrait of Mona Lisa                       19     9
27  On the Terrace                              20     10
10  The She-Wolf                                20     10

and I’m not fussed about which decile

Note that bin 2 and 8 with your sample data ended up empty.

Answer 2

You can simulate WIDTH_BUCKET function:

CREATE FUNCTION dbo.width_bucket(@val decimal, @min_val decimal, @max_val decimal, @groups int)
RETURNS int
AS
BEGIN
    DECLARE @res int;

    IF @val = @max_val RETURN @groups;

    RETURN CAST((@val - @min_val) / ((@max_val - @min_val) / @groups) AS int) + 1;
END

SELECT
    id, title, price, dbo.width_bucket(price, 11.0, 20.0, 10)
FROM
    t;

Or without a function:

DECLARE @groups int = 10;

WITH mm AS
(
    SELECT MIN(price) AS min_price, MAX(price) AS max_price
    FROM   t
)
SELECT
    id, title, price,
    CASE
        WHEN 
            price = max_price THEN @groups
        ELSE
            CAST((price - min_price) / ((max_price - min_price) / @groups) AS int) + 1
    END as ngroup
FROM
    t
CROSS APPLY (SELECT min_price, max_price FROM mm) mm;

Note I've change price type from int to decimal.

id | title                                         | price | ngroup
-: | :-------------------------------------------- | :---- | -----:
19 | Deux fillettes, fond jaune et rouge           | 11.00 |      1
17 | Flowers in a Pitcher                          | 12.00 |      2
 5 | Composition with Red, Yellow and Blue         | 12.00 |      2
18 | La lecon de musique (The Music Lesson)        | 12.00 |      2
 9 | The Adoration of the Magi                     | 12.00 |      2
29 | Self-Portrait                                 | 14.00 |      4
25 | Symphony in White, No. 1: The White Girl      | 14.00 |      4
30 | The Anatomy Lecture of Dr. Nicolaes Tulp      | 14.00 |      4
20 | Les repasseuses (Women Ironing)               | 14.00 |      4
 1 | The Birth of Venus                            | 15.00 |      5
12 | Femme se promenant dans une foret exotique    | 15.00 |      5
24 | Portrait of the Painter’s Mother              | 15.00 |      5
28 | Jeunes filles au piano                        | 15.00 |      5
 7 | Portrait de l artiste (Self-portrait)         | 16.00 |      6
 3 | The Last Supper                               | 16.00 |      6
13 | Combat of a Tiger and a Buffalo               | 16.00 |      6
 4 | The Creation of Man                           | 17.00 |      7
22 | Le Chemin de Fer                              | 17.00 |      7
 6 | Femmes de Tahiti [Sur la plage]               | 18.00 |      8
21 | Le Bar aux Folies-Berg                        | 18.00 |      8
26 | Lady at the Piano                             | 18.00 |      8
15 | Remembrance of a Garden                       | 18.00 |      8
16 | 1914                                          | 18.00 |      8
14 | Ancient Sound, Abstract on Black              | 19.00 |      9
 8 | The Large Turf                                | 19.00 |      9
23 | On the Beach                                  | 19.00 |      9
 2 | Portrait of Mona Lisa                         | 19.00 |      9
27 | On the Terrace                                | 20.00 |     10
10 | The She-Wolf                                  | 20.00 |     10

db<>fiddle here

Answer 3

I have accepted an answer, but I would like to illustrate how I have put the answer into practice.

WITH data AS (SELECT count(*)/10.0 AS bin FROM prints)
SELECT
    id,title,price,
    row_number() OVER(ORDER BY price) AS row_number,
    ntile(10) OVER(ORDER BY price) AS decile,
    floor((row_number() OVER(ORDER BY price)-1)/bin)+1 AS row_decile,
    floor((rank() OVER(ORDER BY price)-1)/bin)+1 AS rank_decile,
    floor((count(*) OVER(ORDER BY price)-1)/bin)+1 AS count_decile,
    bin
FROM prints,data
ORDER BY price;

Points of interest:

• I have put the bin size in a CTE rather than in a variable to make the result more DB neutral
• The WHERE clause is simply to force an awkward bin size.
• The 10.0 is to generate a decimal rather than a truncated integer.
• The row_number()/bin+1 calculation is included to duplicate the native ntile function.

Of course, there is the risk of missing deciles when there is a relatively small number of rows, but at least it keeps the same values together.

It is now a matter of deciding which option is more to one’s taste.

Fiddle: http://sqlfiddle.com/#!17/8bb42/15