UnivariateStatistics Example: ExcludeColumns, All Statistics - 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ā„¢

This example excludes columns id and period from the target columns and outputs all three statistics tables, by default.

Input

SQL Call

DROP TABLE moments;
DROP TABLE basic;  
DROP TABLE quantiles;
SELECT * FROM UnivariateStatistics (
ON finance_data3 AS InputTable
OUT TABLE MomentsTableName(moments)
OUT TABLE BasicTableName(basic)
OUT TABLE QuantilesTableName(quantiles)
USING
ExcludeColumns('id','period')
) AS dt ;

Output

 message                                                      
 ------------------------------------------------------------ 
 UnivariateStatistics succeeded. The output tables are saved.
SELECT * FROM moments;
 stats                      expenditure        income             investment        
 -------------------------- ------------------ ------------------ ----------------- 
 Standard deviation           590.923585337053   698.928750981727  210.746691977944
 Corrected sum of squares   3.17763522173913E7 4.44536273043478E7  4041689.30434783
 Skewness                    0.473364666302052  0.446362736743103 0.422507147168344
 Number of observations                   92.0               92.0              92.0
 Kurtosis                    -1.15712585537242   -1.1624376180194   -1.001282169732
 Variance                     349190.683707597   488501.398948877  44414.1681796464
 Uncorrected sum of squares        1.5700013E8       2.13389608E8       2.4530266E7
 Coefficient of variation    0.506502784308876  0.515781476323667 0.446579502072297
 Standard error               61.6080425745307    72.868359489299  21.9718614961239
 Mean                         1166.67391304348   1355.08695652174  471.913043478261
 Sum                                  107334.0           124668.0           43416.0
SELECT * FROM basic ORDER BY 1;
 stats                     expenditure      income           investment       
 ------------------------- ---------------- ---------------- ---------------- 
 Bottom 5 (1)                         415.0            451.0            179.0
 Bottom 5 (2)                         421.0            465.0            180.0
 Bottom 5 (3)                         434.0            485.0            185.0
 Bottom 5 (4)                         448.0            493.0            192.0
 Bottom 5 (5)                         458.0            509.0            202.0
 Geometric mean            1020.53565750432 1176.50711695451 425.089198191843
 Harmonic mean             891.824091552794  1017.9035289329  381.44257427176
 Interquartile range                  997.0           1159.0            311.0
 Mean                      1166.67391304348 1355.08695652174 471.913043478261
 Median                              1013.0           1178.0            494.0
 Mode                                 574.0            799.0            519.0
 Number of negative values              0.0              0.0              0.0
 Number of NULL values                  0.0              0.0              0.0
 Number of positive values             92.0             92.0             92.0
 Number of unique values               91.0             91.0             83.0
 Number of zero values                  0.0              0.0              0.0
 Range                               1856.0           2200.0            691.0
 Standard deviation        590.923585337053 698.928750981727 210.746691977944
 Top 5 (1)                           2271.0           2651.0            870.0
 Top 5 (2)                           2250.0           2639.0            860.0
 Top 5 (3)                           2237.0           2628.0            853.0
 Top 5 (4)                           2235.0           2620.0            852.0
 Top 5 (5)                           2225.0           2618.0            844.0
 Variance                  349190.683707597 488501.398948877 44414.1681796464
SELECT * FROM quantiles;
 stats   expenditure income investment 
 ------- ----------- ------ ---------- 
 1%            415.0  451.0      179.0
 10%           497.0  548.0      214.0
 25%           653.0  751.0      286.0
 5%            458.0  509.0      202.0
 50%          1013.0 1178.0      494.0
 75%          1650.0 1910.0      597.0
 90%          2102.0 2457.0      830.0
 95%          2206.0 2580.0      833.0
 99%          2250.0 2639.0      860.0
 Maximum      2271.0 2651.0      870.0
 Minimum       415.0  451.0      179.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.