1.1 - 8.10 - UnivariateStatistics Example: TargetColumns, PartitionColumns - 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 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.
SELECT * FROM moments;
 id stats                      expenditure        income             investment         
 -- -------------------------- ------------------ ------------------ ------------------ 
  2 Coefficient of variation    0.298644349474261  0.296222528540145  0.255444956277228
  2 Kurtosis                   -0.816037129460566 -0.858783399504378  0.169604612557607
  1 Corrected sum of squares            5781986.4          7886360.4           666306.0
  1 Standard deviation             385.0403375472   449.682520486142    130.70871903117
  3 Corrected sum of squares     724349.666666667   1108844.91666667   141336.916666667
  3 Standard deviation           256.612489362793   317.496587908175   113.352594174375
  2 Mean                                   1355.6           1589.275            556.675
  2 Uncorrected sum of squares        7.9898048E7       1.09675485E8        1.3184093E7
  1 Number of observations                   40.0               40.0               40.0
  1 Skewness                     2.41487088335036   2.38651478870263   2.25360944369739
  3 Number of observations                   12.0               12.0               12.0
  3 Skewness                   -0.779317508353147 -0.784652108192004 -0.736147966158991
  2 Sum                                   54224.0            63571.0            22267.0
  2 Standard error               64.0111849200731   74.4367050647558   22.4837658670451
  1 Variance                     148256.061538462   202214.369230769   17084.7692307692
  1 Kurtosis                      4.8015109257614   4.71437243981462   4.42294797371972
  3 Variance                     65849.9696969697   100804.083333333   12848.8106060606
  3 Kurtosis                    -1.54284162536314  -1.57575089282068  -1.61592354816352
  1 Coefficient of variation    0.539121167106132  0.554342357601259  0.434248235983952
  2 Standard deviation           404.842280147308   470.779059045639   142.199821035626
  3 Coefficient of variation    0.125472653913842  0.132987505843069  0.149328261070645
  1 Uncorrected sum of squares        2.6185252E7        3.4208178E7          4290346.0
  2 Corrected sum of squares            6391993.6        8643683.975         788610.775
  3 Uncorrected sum of squares         5.091683E7        6.9505945E7          7055827.0
  1 Mean                                    714.2              811.2              301.0
  2 Skewness                    0.539127192993609  0.475410247931558  0.806624515469788
  3 Mean                         2045.16666666667   2387.41666666667   759.083333333333
  1 Standard error               60.8802228844601   71.1010494350768   20.6668631090747
  2 Number of observations                   40.0               40.0               40.0
  3 Standard error               74.0776449055143   91.6533702477862    32.722075379959
  1 Sum                                   28568.0            32448.0            12040.0
  3 Sum                                   24542.0            28649.0             9109.0
  2 Variance                     163897.271794872   221632.922435897   20220.7891025641
SELECT * FROM basic ORDER BY 1;
 id stats                     expenditure      income           investment       
 -- ------------------------- ---------------- ---------------- ---------------- 
  1 Median                               602.0            694.0            280.0
  1 Standard deviation          385.0403375472 449.682520486142  130.70871903117
  1 Top 5 (5)                            779.0            897.0            322.0
  1 Bottom 5 (1)                         415.0            451.0            179.0
  1 Number of zero values                  0.0              0.0              0.0
  1 Bottom 5 (5)                         458.0            509.0            202.0
  1 Geometric mean            652.322122537438 736.802888310562 282.108617595293
  1 Harmonic mean             614.458471878887 691.093427241673 268.951437475886
  1 Interquartile range                  194.0            241.0             75.0
  1 Top 5 (1)                           1842.0           2132.0            700.0
  1 Top 5 (3)                           1807.0           2070.0            658.0
  1 Bottom 5 (3)                         434.0            485.0            185.0
  1 Top 5 (4)                           1774.0           2040.0            635.0
  1 Number of NULL values                  0.0              0.0              0.0
  1 Top 5 (2)                           1831.0           2121.0            675.0
  1 Bottom 5 (2)                         421.0            465.0            180.0
  1 Variance                  148256.061538462 202214.369230769 17084.7692307692
  1 Mean                                 714.2            811.2            301.0
  1 Mode                                 574.0            799.0            280.0
  1 Number of unique values               39.0             39.0             37.0
  1 Range                               1427.0           1681.0            521.0
  1 Bottom 5 (4)                         448.0            493.0            192.0
  1 Number of negative values              0.0              0.0              0.0
  1 Number of positive values             40.0             40.0             40.0
  2 Bottom 5 (3)                         837.0            979.0            364.0
  2 Top 5 (3)                           2061.0           2423.0            844.0
  2 Interquartile range                  554.0            653.0             76.0
  2 Median                              1267.0           1493.0            526.0
  2 Standard deviation        404.842280147308 470.779059045639 142.199821035626
  2 Bottom 5 (5)                         881.0           1025.0            375.0
  2 Harmonic mean              1246.1032651127 1460.64283484258  524.31105430778
  2 Top 5 (5)                           1994.0           2318.0            816.0
  2 Bottom 5 (1)                         798.0            922.0            315.0
  2 Geometric mean            1299.05445462922 1523.24641529642 540.054672867882
  2 Number of zero values                  0.0              0.0              0.0
  2 Top 5 (1)                           2121.0           2470.0            853.0
  2 Number of positive values             40.0             40.0             40.0
  2 Number of negative values              0.0              0.0              0.0
  2 Range                               1323.0           1548.0            538.0
  2 Top 5 (4)                           2056.0           2369.0            830.0
  2 Number of NULL values                  0.0              0.0              0.0
  2 Mean                                1355.6         1589.275          556.675
  2 Number of unique values               40.0             40.0             36.0
  2 Top 5 (2)                           2102.0           2457.0            852.0
  2 Bottom 5 (2)                         816.0            949.0            339.0
  2 Mode                                  NULL             NULL            519.0
  2 Variance                  163897.271794872 221632.922435897 20220.7891025641
  2 Bottom 5 (4)                         858.0            988.0            371.0
  3 Number of zero values                  0.0              0.0              0.0
  3 Harmonic mean             2012.97784954935 2344.96421517024 741.962569222229
  3 Top 5 (3)                           2237.0           2628.0            833.0
  3 Interquartile range                  513.0            644.0            220.0
  3 Standard deviation        256.612489362793 317.496587908175 113.352594174375
  3 Bottom 5 (5)                        2145.0           2521.0            801.0
  3 Geometric mean            2029.47884297898  2366.7551731771 750.758507052472
  3 Bottom 5 (1)                        1650.0           1910.0            597.0
  3 Top 5 (5)                           2225.0           2618.0            830.0
  3 Bottom 5 (3)                        1722.0           1976.0            611.0
  3 Top 5 (1)                           2271.0           2651.0            870.0
  3 Median                              2164.0           2545.0            824.0
  3 Variance                  65849.9696969697 100804.083333333 12848.8106060606
  3 Number of unique values               12.0             12.0             11.0
  3 Number of negative values              0.0              0.0              0.0
  3 Range                                621.0            741.0            273.0
  3 Number of NULL values                  0.0              0.0              0.0
  3 Mean                      2045.16666666667 2387.41666666667 759.083333333333
  3 Mode                                  NULL             NULL            830.0
  3 Bottom 5 (2)                        1685.0           1943.0            603.0
  3 Top 5 (2)                           2250.0           2639.0            860.0
  3 Number of positive values             12.0             12.0             12.0
  3 Bottom 5 (4)                        1752.0           2018.0            619.0
  3 Top 5 (4)                           2235.0           2620.0            831.0
SELECT * FROM quantiles ORDER BY 1,2;
 id stats   expenditure income investment 
 -- ------- ----------- ------ ---------- 
  1 1%            415.0  451.0      179.0
  1 10%           448.0  493.0      192.0
  1 25%           510.0  558.0      229.0
  1 5%            421.0  465.0      180.0
  1 50%           602.0  694.0      280.0
  1 75%           704.0  799.0      304.0
  1 90%           779.0  897.0      322.0
  1 95%          1807.0 2070.0      658.0
  1 99%          1842.0 2132.0      700.0
  1 Maximum      1842.0 2132.0      700.0
  1 Minimum       415.0  451.0      179.0
  2 1%            798.0  922.0      315.0
  2 10%           858.0  988.0      371.0
  2 25%          1013.0 1178.0      494.0
  2 5%            816.0  949.0      339.0
  2 50%          1267.0 1493.0      526.0
  2 75%          1567.0 1831.0      570.0
  2 90%          1994.0 2318.0      816.0
  2 95%          2061.0 2423.0      844.0
  2 99%          2121.0 2470.0      853.0
  2 Maximum      2121.0 2470.0      853.0
  2 Minimum       798.0  922.0      315.0
  3 1%           1650.0 1910.0      597.0
  3 10%          1650.0 1910.0      597.0
  3 25%          1722.0 1976.0      611.0
  3 5%           1650.0 1910.0      597.0
  3 50%          2164.0 2545.0      824.0
  3 75%          2235.0 2620.0      831.0
  3 90%          2250.0 2639.0      860.0
  3 95%          2250.0 2639.0      860.0
  3 99%          2271.0 2651.0      870.0
  3 Maximum      2271.0 2651.0      870.0
  3 Minimum      1650.0 1910.0      597.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.