Because QUANTILE uses equal-width histograms to partition the specified data, it does not partition the data equally using equal-height histograms. In other words, do not expect equal row counts per specified quantile. Expect empty quantile histograms when, for example, duplicate values for sort_expression are found in the data.
For example, consider the following simple SELECT statement.
SELECT itemNo, quantity, QUANTILE(10,quantity) FROM inventory;
The report might look like this.
itemNo | quantity | Quantile(10, quantity) |
---|---|---|
13 | 1 | 0 |
9 | 1 | 0 |
7 | 1 | 0 |
2 | 1 | 0 |
5 | 1 | 0 |
3 | 1 | 0 |
1 | 1 | 0 |
6 | 1 | 0 |
4 | 1 | 0 |
10 | 1 | 0 |
8 | 1 | 0 |
11 | 1 | 0 |
12 | 9 | 9 |
Because the quantile sort is on quantity, and there are only two quantity scores in the inventory table, there are no scores in the report for deciles 1 through 8.