Example - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-30
dita:mapPath
tpt1555966086716.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantageā„¢

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.