How to apply NTILE(4) using range of column values?

Question

Would like to use NTILE to see the distribution of countries by forested land percent of total land area. The range of values in the column I'd like to use is from 0.00053 to very close to 98.25, and countries are not evenly distributed across the quartiles implied by that range, i.e 0 to 25, 25 to 50, 50 to 75, and 75 to 100 approximately. Instead, NTILE is just dividing the table into four groups with the same number of rows. How do I use NTILE to assign quantiles based on values?

SELECT country, forest, pcnt_forest,
       NTILE(4) OVER(ORDER BY pcnt_forest) AS quartile
FROM percent_forest

Tag your question with the database you are working. – Gordon Linoff May 21 '20 at 12:14 — Gordon Linoff, May 21 '20 at 12:14

score 2 · Answer 1 · edited Jul 28 '22 at 02:00

WIDTH_BUCKET function matches this scenario ideally:

WIDTH_BUCKET(Oracle) lets you construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size. (Compare this function with NTILE, which creates equiheight histograms.)

It is supported by Oracle, Snowflake, PostgreSQL, hive, ...

Your code:

SELECT country,  pcnt_forest
       ,WIDTH_BUCKET(pcnt_forest, 0, 1, 4) AS w
       ,NTILE(4) OVER(ORDER BY pcnt_forest) AS ntile  -- for comparison
FROM percent_forest
ORDER BY w

db<>fiddle demo

Output:

+----------+--------------+----+-------+
| COUNTRY  | PCNT_FOREST  | W  | NTILE |
+----------+--------------+----+-------+
| A        |         .05  | 1  |     1 |
| B        |         .06  | 1  |     1 |
| C        |         .07  | 1  |     2 |
| E        |         .49  | 2  |     2 |
| D        |         .51  | 3  |     3 |
| F        |         .96  | 4  |     3 |
| G        |         .97  | 4  |     4 |
| H        |         .98  | 4  |     4 |
+----------+--------------+----+-------+

score 2 · Accepted Answer · answered May 21 '20 at 12:15

You can use a case expression:

select pf.*,
       (case when pcnt_forest < 0.25 then 1
             when pcnt_forest < 0.50 then 2
             when pcnt_forest < 0.75 then 3
             else 4
        end) as bin
from percent_forest pf;

Or, even simpler, use arithmetic:

select pf.*,
       floor(pcnt_forest * 4) + 1 bin
from percent_forest pf;

I would not use the term "quartile" for this column. A quartile implies four equal sized bins (or at least as close as possible given duplicate values).

How to apply NTILE(4) using range of column values?

2 Answers2