Percentiles Example - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
8.10
1.1
Published
October 2019
Language
English (United States)
Last Update
2019-12-31
dita:mapPath
ima1540829771750.ditamap
dita:ditavalPath
jsj1481748799576.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

Input

The input table, london_olympics, contains the age, height, weight, sex, sport and country for a subset of the participants in the 2012 Summer Olympics.

london_olympics
name country age height weight sex sport
Adriatik Hoxha Albania 22 194 130 M Athletics
Arben Kucana Albania 44 184 95 M Shooting
Briken Calja Albania 22 169 69 M Weightlifting
Daniel Godelli Albania 20 168 68 M Weightlifting
Endri Karina Albania 23 186 94 M Weightlifting
Klodiana Shala Albania 32     F Athletics
Majlinda Kelmendi Albania 21   51 F Judo
Noel Borshi Albania 16 164 54 F Swimming
Romela Begaj Albania 25 160 58 F Weightlifting
Sidni Hoxha Albania 20 193 86 M Swimming
Ali Hasan Mahboob Bahrain 30 175 70 M Athletics
... ... ... ... ... ... ...

SQL Call

SELECT * FROM Percentiles (
  ON london_olympics PARTITION BY country
  USING
  Percentile (0, 100)
  TargetColumns ('height', 'weight', 'age')
  GroupByColumns ('country')
) AS dt ORDER BY 1,2;

Output

The output table displays the values for each target column, partitioned by country, corresponding to the requested percentiles.

 country      percentile height weight age  
 ------------ ---------- ------ ------ ---- 
 albania             0.0  160.0   51.0 16.0
 albania           100.0  194.0  130.0 44.0
 bahrain             0.0  162.0   43.0 15.0
 bahrain           100.0  175.0   72.0 30.0
 costa rica          0.0  163.0   50.0 21.0
 costa rica        100.0  195.0   96.0 32.0
 cyprus              0.0  162.0   55.0 16.0
 cyprus            100.0  193.0  110.0 37.0
 el salvador         0.0  159.0   54.0 19.0
 el salvador       100.0  185.0   80.0 29.0
 eritrea             0.0  160.0   51.0 18.0
 eritrea           100.0  188.0   71.0 34.0
 grenada             0.0  162.0   56.0 19.0
 grenada           100.0  187.0   75.0 32.0
 guatemala           0.0  147.0   44.0 16.0
 guatemala         100.0  190.0  132.0 41.0
 indonesia           0.0  152.0   49.0 16.0
 indonesia         100.0  183.0   80.0 30.0
 kuwait              0.0  165.0   55.0 17.0
 kuwait            100.0  195.0  110.0 48.0
 kyrgyzstan          0.0  155.0   52.0 16.0
 kyrgyzstan        100.0  186.0  100.0 29.0
 lebanon             0.0  152.0   52.0 18.0
 lebanon           100.0  188.0   87.0 26.0
 luxembourg          0.0  157.0   52.0 20.0
 luxembourg        100.0  192.0   80.0 49.0
 mauritius           0.0  154.0   52.0 18.0
 mauritius         100.0  187.0   75.0 32.0
 peru                0.0  155.0   46.0 17.0
 peru              100.0  183.0   80.0 33.0
 philippines         0.0  157.0   53.0 18.0
 philippines       100.0  180.0  125.0 33.0
 puerto rico         0.0  170.0   48.0 16.0
 puerto rico       100.0  191.0   86.0 32.0
 qatar               0.0  155.0   43.0 17.0
 qatar             100.0  189.0  104.0 41.0
 saudi arabia        0.0   NULL   59.0 16.0
 saudi arabia      100.0   NULL   94.0 42.0
 tajikistan          0.0  160.0   55.0 19.0
 tajikistan        100.0  189.0  120.0 44.0
 turkmenistan        0.0  165.0   56.0 18.0
 turkmenistan      100.0  194.0  100.0 30.0
 uganda              0.0  153.0   50.0 15.0
 uganda            100.0  183.0   76.0 33.0
 vietnam             0.0  150.0   45.0 15.0
 vietnam           100.0  178.0   80.0 37.0

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