Example - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/djk1612415574830.ditamap
dita:ditavalPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/wrg1590696035526.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.