Return to Answer

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 WHERE price<20)
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
WHERE price<20
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/1

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

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 WHERE price<20)
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
WHERE price<20
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/d50f75/2

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 WHERE price<20)
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
WHERE price<20
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/1

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 WHERE price<20)
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
WHERE price<20
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.

Fiddle to follow …

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 WHERE price<20)
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
WHERE price<20
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/d50f75/2