1.0 - 8.00 - UnivariateStatistics Example 2: TargetColumns, PartitionColumns - 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 specifies the target columns explicitly and partitions the data.

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
  TargetColumns ('expenditure','income','investment')
  PartitionColumns ('id')
) AS dt;

Output

message
UnivariateStatistics succeeded. The output tables are saved.

This query returns the following table:

SELECT * FROM moments ORDER BY id, stats;
moments
id stats expenditure income investment
1 Coefficient of variation 0.539121167106132 0.554342357601259 0.434248235983952
1 Corrected sum of squares 5781986.4 7886360.4 666306
1 Kurtosis 4.8015109257614 4.71437243981462 4.42294797371972
1 Mean 714.2 811.2 301
1 Number of observations 40 40 40
1 Skewness 2.41487088335036 2.38651478870263 2.25360944369739
1 Standard deviation 385.0403375472 449.682520486142 130.70871903117
1 Standard error 60.8802228844601 71.1010494350768 20.6668631090747
1 Sum 28568 32448 12040
1 Uncorrected sum of squares 26185252 34208178 4290346
1 Variance 148256.061538462 202214.369230769 17084.7692307692
2 Coefficient of variation 0.298644349474261 0.296222528540145 0.255444956277228
2 Corrected sum of squares 6391993.6 8643683.975 788610.775
2 Kurtosis -0.816037129460566 -0.858783399504378 0.169604612557607
2 Mean 1355.6 1589.275 556.675
2 Number of observations 40 40 40
2 Skewness 0.539127192993609 0.475410247931558 0.806624515469788
2 Standard deviation 404.842280147308 470.779059045639 142.199821035626
2 Standard error 64.0111849200731 74.4367050647558 22.4837658670451
2 Sum 54224 63571 22267
2 Uncorrected sum of squares 79898048 109675485 13184093
2 Variance 163897.271794872 221632.922435897 20220.7891025641
3 Coefficient of variation 0.125472653913842 0.132987505843069 0.149328261070645
3 Corrected sum of squares 724349.666666667 1108844.91666667 141336.916666667
3 Kurtosis -1.54284162536314 -1.57575089282068 -1.61592354816352
3 Mean 2045.16666666667 2387.41666666667 759.083333333333
3 Number of observations 12 12 12
3 Skewness -0.779317508353147 -0.784652108192004 -0.736147966158991
3 Standard deviation 256.612489362793 317.496587908175 113.352594174375
3 Standard error 74.0776449055143 91.6533702477862 32.722075379959
3 Sum 24542 28649 9109
3 Uncorrected sum of squares 50916830 69505945 7055827
3 Variance 65849.9696969697 100804.083333333 12848.8106060606

This query returns the following table:

SELECT * FROM basic ORDER BY id, stats;
basic
id stats expenditure income investment
1 Bottom 5 (1) 415 451 179
1 Bottom 5 (2) 421 465 180
1 Bottom 5 (3) 434 485 185
1 Bottom 5 (4) 448 493 192
1 Bottom 5 (5) 458 509 202
1 Geometric mean 652.322122537438 736.802888310562 282.108617595293
1 Harmonic mean 614.458471878887 691.093427241673 268.951437475886
1 Interquartile range 194 241 75
1 Mean 714.2 811.2 301
1 Median 602 694 280
1 Mode 574 799 280
1 Number of NULL values 0 0 0
1 Number of negative values 0 0 0
1 Number of positive values 40 40 40
1 Number of unique values 39 39 37
1 Number of zero values 0 0 0
1 Range 1427 1681 521
1 Standard deviation 385.0403375472 449.682520486142 130.70871903117
1 Top 5 (1) 1842 2132 700
1 Top 5 (2) 1831 2121 675
1 Top 5 (3) 1807 2070 658
1 Top 5 (4) 1774 2040 635
1 Top 5 (5) 779 897 322
1 Variance 148256.061538462 202214.369230769 17084.7692307692
2 Bottom 5 (1) 798 922 315
2 Bottom 5 (2) 816 949 339
2 Bottom 5 (3) 837 979 364
2 Bottom 5 (4) 858 988 371
2 Bottom 5 (5) 881 1025 375
2 Geometric mean 1299.05445462922 1523.24641529642 540.054672867882
2 Harmonic mean 1246.1032651127 1460.64283484258 524.31105430778
2 Interquartile range 554 653 76
2 Mean 1355.6 1589.275 556.675
2 Median 1267 1493 526
2 Mode     519
2 Number of NULL values 0 0 0
2 Number of negative values 0 0 0
2 Number of positive values 40 40 40
2 Number of unique values 40 40 36
2 Number of zero values 0 0 0
2 Range 1323 1548 538
2 Standard deviation 404.842280147308 470.779059045639 142.199821035626
2 Top 5 (1) 2121 2470 853
2 Top 5 (2) 2102 2457 852
2 Top 5 (3) 2061 2423 844
2 Top 5 (4) 2056 2369 830
2 Top 5 (5) 1994 2318 816
2 Variance 163897.271794872 221632.922435897 20220.7891025641
3 Bottom 5 (1) 1650 1910 597
3 Bottom 5 (2) 1685 1943 603
3 Bottom 5 (3) 1722 1976 611
3 Bottom 5 (4) 1752 2018 619
3 Bottom 5 (5) 2145 2521 801
3 Geometric mean 2029.47884297898 2366.7551731771 750.758507052472
3 Harmonic mean 2012.97784954935 2344.96421517024 741.962569222229
3 Interquartile range 513 644 220
3 Mean 2045.16666666667 2387.41666666667 759.083333333333
3 Median 2164 2545 824
3 Mode     830
3 Number of NULL values 0 0 0
3 Number of negative values 0 0 0
3 Number of positive values 12 12 12
3 Number of unique values 12 12 11
3 Number of zero values 0 0 0
3 Range 621 741 273
3 Standard deviation 256.612489362793 317.496587908175 113.352594174375
3 Top 5 (1) 2271 2651 870
3 Top 5 (2) 2250 2639 860
3 Top 5 (3) 2237 2628 833
3 Top 5 (4) 2235 2620 831
3 Top 5 (5) 2225 2618 830
3 Variance 65849.9696969697 100804.083333333 12848.8106060606

This query returns the following table:

SELECT * FROM quantiles ORDER BY id, stats;
quantiles
id stats expenditure income investment
1 1% 415 451 179
1 10% 448 493 192
1 25% 510 558 229
1 5% 421 465 180
1 50% 602 694 280
1 75% 704 799 304
1 90% 779 897 322
1 95% 1807 2070 658
1 99% 1842 2132 700
1 Maximum 1842 2132 700
1 Minimum 415 451 179
2 1% 798 922 315
2 10% 858 988 371
2 25% 1013 1178 494
2 5% 816 949 339
2 50% 1267 1493 526
2 75% 1567 1831 570
2 90% 1994 2318 816
2 95% 2061 2423 844
2 99% 2121 2470 853
2 Maximum 2121 2470 853
2 Minimum 798 922 315
3 1% 1650 1910 597
3 10% 1650 1910 597
3 25% 1722 1976 611
3 5% 1650 1910 597
3 50% 2164 2545 824
3 75% 2235 2620 831
3 90% 2250 2639 860
3 95% 2250 2639 860
3 99% 2271 2651 870
3 Maximum 2271 2651 870
3 Minimum 1650 1910 597