16.20 - Example - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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.