1.0 - 8.00 - Approximate Percentile Example - Teradata Vantage

Teradata® Vantage Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.0
8.00
Release Date
May 2019
Content Type
Programming Reference
Publication ID
B700-4003-098K
Language
English (United States)

This example calculates the approximate percentiles 0, 25, 50, 75, and 100 within a 2% error rate for four brands of crackers.

Input

The input table is cracker, as in the Approximate Cardinality Example.

SQL Call

SELECT * FROM ApproxPercentileReduce (
  ON ApproxPercentileMap (
    ON cracker AS InputTable
    USING
    TargetColumns ('pricesunshine','pricekeebler','pricenabisco','priceprivate')
    GroupColumns ('choice')
    ErrorRate (2)
  ) AS InputTable PARTITION BY choice
  USING
  GroupColumns ('choice')
  Percentile (0,25,50,75,100)
  TargetColumns ('pricesunshine','pricekeebler', 'pricenabisco','priceprivate')
) AS dt ORDER BY choice, percentile;

Output

choice percentile pricesunshine pricekeebler pricenabisco priceprivate
Keebler 0 49 88 88 38
Keebler 25 89 99 99 60.0000038146973
Keebler 50 97 109 109 65
Keebler 75 105 109 125 78
Keebler 100 129 135 129 96
Nabisco 0 49 88 0 38
Nabisco 25 89 105 99 61
Nabisco 50 97 115 105 65
Nabisco 75 105 121 119.000007629395 78
Nabisco 100 129 139 169.000015258789 115
private 0 49 88 89 38
private 25 89 109 99 55
private 50 98 113 109 58.9999961853027
private 75 109 121 125 79
private 100 129 135 129 115
Sunshine 0 49 88 88 49
Sunshine 25 79 107.000007629395 99 64
Sunshine 50 89 109 109 65
Sunshine 75 97 124 119.000007629395 78
Sunshine 100 129 135 129 96