QUANTILE Function Examples | VantageCloud Lake - QUANTILE Function Examples - Teradata Vantage

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;

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 may look like the following:

      Item    Profit  Decile
----------    ------  ------
 High Tops     97112       9
  Low Tops     74699       7
   Running     69712       6
    Casual     28912       3
    Xtrain    100129       9

QUANTILE uses equal-width histograms to partition the specified data, and therefore does not partition the data equally using equal-height histograms. That is, 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 may look like this.

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.