1.0 - 8.00 - UnivariateStatistics Example 1: ExcludeColumns, All Statistics by Default - 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 excludes columns id and period from the target columns and outputs all three statistics tables.

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.

This query returns the following table:

SELECT * FROM moments;
moments
stats expenditure income investment
Coefficient of variation 0.506502784308876 0.515781476323667 0.446579502072297
Corrected sum of squares 31776352.2173913 44453627.3043478 4041689.30434783
Mean 1166.67391304348 1355.08695652174 471.913043478261
Number of observations 92 92 92
Skewness 0.473364666302052 0.446362736743103 0.422507147168344
Standard error 61.6080425745307 72.868359489299 21.9718614961239
Sum 107334 124668 43416
Variance 349190.683707597 488501.398948877 44414.1681796464
Kurtosis -1.15712585537242 -1.1624376180194 -1.001282169732
Standard deviation 590.923585337053 698.928750981727 210.746691977944
Uncorrected sum of squares 157000130 213389608 24530266

This query returns the following table:

SELECT * FROM basic ORDER BY stats;
basic
stats expenditure income investment
Top 5 (1) 415 451 179
Top 5 (2) 421 465 180
Top 5 (3) 434 485 185
Top 5 (4) 448 493 192
Top 5 (5) 458 509 202
Geometric mean 1020.53565750432 1176.50711695451 425.089198191843
Harmonic mean 891.824091552794 1017.9035289329 381.44257427176
Interquartile range 997 1159 311
Mean 1166.67391304348 1355.08695652174 471.913043478261
Median 1013 1178 494
Mode 574 799 519
Number of NULL values 0 0 0
Number of negative values 0 0 0
Number of positive values 92 92 92
Number of unique values 91 91 83
Number of zero values 0 0 0
Range 1856 2200 691
Standard deviation 590.923585337053 698.928750981727 210.746691977944
Top 5 (1) 2271 2651 870
Top 5 (2) 2250 2639 860
Top 5 (3) 2237 2628 853
Top 5 (4) 2235 2620 852
Top 5 (5) 2225 2618 844
Variance 349190.683707597 488501.398948877 44414.1681796464

This query returns the following table:

SELECT * FROM quantiles;
quantiles
stats expenditure income investment
75% 1650 75% 1650
95% 2206 95% 2206
99% 2250 99% 2250
1% 415 1% 415
10% 497 10% 497
25% 653 25% 653
5% 458 5% 458
50% 1013 50% 1013
90% 2102 90% 2102
Maximum 2271 Maximum 2271
Minimum 415 Minimum 415