1.1 - 8.10 - Approximate Percentile Example - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
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')
    GroupByColumns('choice')
    ErrorRate(2)
  ) AS InputTable PARTITION BY choice
  USING
  GroupByColumns('choice')
  Percentile(0,25,50,75,100)
  TargetColumns('pricesunshine','pricekeebler', 'pricenabisco','priceprivate')
) AS dt order by choice,percentiles;

Output

 choice   percentiles pricesunshine pricekeebler pricenabisco priceprivate 
 -------- ----------- ------------- ------------ ------------ ------------ 
 keebler          0.0     49.000001         88.0         88.0         38.0
 keebler         25.0     88.999999    99.000001    99.000001    58.999997
 keebler         50.0     97.000003        109.0        109.0    64.999998
 keebler         75.0         105.0        109.0        125.0    77.999997
 keebler        100.0         129.0        135.0        129.0    95.999998
 nabisco          0.0     49.000001         88.0          0.0         38.0
 nabisco         25.0     88.999999        105.0    99.000001    61.000001
 nabisco         50.0     97.000003        115.0        103.0    64.999998
 nabisco         75.0         105.0        121.0    119.00001    77.999997
 nabisco        100.0         129.0        139.0    169.00001        115.0
 private          0.0     49.000001         88.0    88.999999         38.0
 private         25.0     88.999999        109.0    99.000001    55.000001
 private         50.0     98.000002        113.0        109.0    58.999997
 private         75.0         109.0        121.0        125.0    79.000002
 private        100.0         129.0        135.0        129.0        115.0
 sunshine         0.0     49.000001         88.0         88.0    49.000001
 sunshine        25.0     79.000002    107.00001    99.000001    61.000001
 sunshine        50.0     88.999999        109.0        109.0    64.999998
 sunshine        75.0     97.000003        121.0    119.00001    77.999997
 sunshine       100.0         129.0        135.0        129.0    95.999998

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.