Archives of the TeradataForum

Message Posted: Wed, 19 Jul 2006 @ 19:31:08 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

>From the manual:

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_list 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_list 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-2003 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()

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;

jdg

www.jgraas.com

	<Prev	Next>		<<First	<Prev	Next>	Last>>