15.00 - QUANTILE - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

QUANTILE

Purpose  

Computes the quantile scores for the values in a group.

Type

Teradata-specific function.

Syntax  

where:

 

Syntax element …

Specifies …

quantile_literal

a positive integer literal used to define the number of quantile partitions to be used.

sort_expression

a literal or column expression or comma-separated list of literal or column expressions to be used to sort the values.

For example, QUANTILE(10, Region ASC, Store DESC), where 10 is the quantile_literal and Region ASC, Store DESC is the sort_expression list.

ASC

ascending sort order.

DESC

descending sort order.

The default sort direction is DESC.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Definition

A quantile is a generic interval of user-defined width. For example, percentiles divide data among 100 evenly spaced intervals, deciles among 10 evenly spaced intervals, quartiles among 4, and so on. A quantile score indicates the fraction of rows having a sort_expression value lower than the current value. For example, a percentile score of 98 means that 98 percent of the rows in the list have a sort_expression value lower than the current value.

Using ANSI Window Functions Instead of QUANTILE

The use of QUANTILE is strongly discouraged. It is a Teradata extension to the ANSI SQL:2011 standard and is retained only for backward compatibility with existing applications.

To compute QUANTILE(q, s) using ANSI window functions, use the following:

   (RANK() OVER (ORDER BY s) - 1) * q / COUNT(*) OVER()

QUANTILE Report

For each row in the group, QUANTILE returns an integer value that represents the quantile of the sort_expression value for that row relative to the sort_expression value for all the rows in the group.

Quantile Value Range

Quantile values range from 0 through (Q-1), where Q is the number of quantile partitions specified by quantile_literal.

Result Type and Attributes

The data type, format, and title for QUANTILE(Q, list) are as follows:

 

Data Type

Format

Title

INTEGER

the default format for the INTEGER data type

Quantile(Q, list)

For information on the default format of data types, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

Example  

Display each item and its total sales in the ninth (top) decile according to the total sales.

   SELECT itemID, sumPrice
   FROM (SELECT a1.itemID, SUM(price)
   FROM Sales a1
   GROUP BY a1.itemID) AS T1(itemID, sumPrice)
   QUALIFY QUANTILE(10,sumPrice)=9;

Example  

The following example groups all items into deciles by profitability.

   SELECT Item, Profit, QUANTILE(10, Profit) AS Decile
   FROM
      (SELECT Item, Sum(Sales) — (Count(Sales) * ItemCost) AS Profit
      FROM DailySales, Items
      WHERE DailySales.Item = Items.Item
      GROUP BY Item) AS Item;

The result might look like the following table:

 

Item

Profit

Decile

High Tops
	97112
9
Low Tops
	74699
7
Running
	69712
6
Casual
	28912
3
Xtrain
	100129
9 

Example  

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.