# 1.1 - 8.10 - UnivariateStatistics Example: TargetColumns, PartitionColumns - Teradata Vantage

## Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
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.

## 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.