Chapter 22 – Statistical Aggregate Function
"You can make more friends in two months by becoming interested in other people than you will in two years by trying to get other people interested in you."
-Dale Carnegie
The Stats Table
Above is the Stats_Table data in which we will use in our statistical examples.
The KURTOSIS Function
SELECT KURTOSIS(col1) AS KofCol1
FROM Stats_Table;
KofCol1
-1.20
The KURTOSIS function is used to return a number that represents the sharpness of a peak on a plotted curve of a probability function for a distribution compared with the normal distribution.
A high value result is referred to as leptokurtic. While a medium result is referred to as mesokurtic and a low result is referred to as platykurtic.
A positive value indicates a sharp or peaked distribution and a negative number represents a flat distribution. A peaked distribution means that one value exists more often than the other values. A flat distribution means there is the same quantity values exist for each number.
If you compare this to the row distribution associated within Teradata, most of the time a flat distribution is best, with the same number of rows stored on each AMP. Having skewed data represents more of a lumpy distribution.
A Kurtosis Example
The SKEW Function
Syntax for using SKEW:
SKEW(<column-name>)
A median value < mean value = a positive skew
A median value > mean value = a negative skew
A median value = mean value = no skew
SELECT SKEW(col1) AS SKofCol1
FROM Stats_Table;
SKofCol1
0.00
The Skew indicates that a distribution does not have equal probabilities above and below the mean (average). In a skew distribution, the median and the mean are not coincident, or equal.
A SKEW Example
The STDDEV_POP Function
Syntax for using STDDEV_POP:
STDDEV_POP(<column-name>)
SELECT STDDEV_POP(col1) AS SDPCol1
FROM Stats_Table;
SDPCol1
8.66
The standard deviation function is a statistical measure of spread or dispersion of values. It is the root’s square of the difference of the mean (average). This measure is to compare the amount by which a set of values differs from the arithmetical mean.
The STDDEV_POP function is one of two that calculates the standard deviation. The population is of all the rows included based on the comparison in the WHERE clause.
A STDDEV_POP Example
The STDDEV_SAMP Function
Syntax for using STDDEV_SAMP:
STDDEV_SAMP(<column-name>)
SELECT STDDEV_SAMP(col1) AS SDSCol1
FROM Stats_Table;
SDSCol1
8.80
The standard deviation function is a statistical measure of spread or dispersion of values. It is the root’s square of the difference of the mean (average). This measure is to compare the amount by which a set of values differs from the arithmetical mean.
The STDDEV_SAMP function is one of two that calculates the standard deviation. The sample is a random selection of all rows returned based on the comparisons in the WHERE clause. The population is for all of the rows based on the WHERE clause.
A STDDEV_SAMP Example
The VAR_POP Function
Syntax for using VAR_POP:
VAR_POP(<column-name>)
SELECT VAR_POP(col1) AS VPCol1
FROM Stats_Table;
VPCol1
74.92
The Variance function is a measure of dispersion (spread of the distribution) as the square of the standard deviation. There are two forms of Variance in Teradata, VAR_POP is for the entire population of data rows allowed by the WHERE clause.
Although standard deviation and variance are regularly used in statistical calculations, the meaning of variance is not easy to elaborate. Most often variance is used in theoretical work where a variance of the sample is needed.
There are two methods for using variance. These are the Kruskal-Wallis one-way Analysis of Variance and Friedman two-way Analysis of Variance by rank.
A VAR_POP Example
The VAR_SAMP Function
Syntax for using VAR_SAMP:
VAR_SAMP(<column-name>)
SELECT VAR_SAMP(col1) AS VSCol1
FROM Stats_Table;
VSCol1
77.50
The Variance function is a measure of dispersion (spread of the distribution) as the square of the standard deviation. There are two forms of Variance in Teradata, VAR_SAMP is used for a random sampling of the data rows allowed through by the WHERE clause.
Although standard deviation and variance are regularly used in statistical calculations, the meaning of variance is not easy to elaborate. Most often variance is used in theoretical work where a variance of the sample is needed to look for consistency.
There are two methods for using variance. These are the Kruskal-Wallis one-way Analysis of Variance and Friedman two-way Analysis of Variance by rank.
A VAR_SAMP Example
The CORR Function
Syntax for using CORR:
CORR(<column-name>, <column-name>)
SELECT CORR(col1, col2) AS CCol1#2
FROM Stats_Table;
CCol1#2
0.99
The correlation coefficient is a number between -1 and 1. It is calculated from a number of pairs of observations or linear points (X,Y) Where:
1 = perfect positive correlation
0 = no correlation
-1 = perfect negative correlation
The CORR function is a binary function, meaning that two variables are used as input to it. It measures the association between 2 random variables. If the variables are such that when one changes the other does so in a related manner, they are correlated. Independent variables are not correlated because the change in one does not necessarily cause the other to change.
A CORR Example
Another CORR Example so you can Compare
The COVAR_POP Function
Syntax:
COVAR(<column-name>, <column-name>)
SELECT COVAR_POP(col1, col2) AS CCol1#2
FROM Stats_Table;
CCol1#2
37.50
The covariance is a statistical measure of the tendency of two variables to change in conjunction with each other. It is equal to the product of their standard deviations and correlation coefficients.
The covariance is a statistic used for bivariate samples or bivariate distribution. It is used for working out the equations for regression lines and the product-moment correlation coefficient.
A COVAR_POP Example
Another COVAR_POP Example so you can Compare
The REGR_INTERCEPT Function
Syntax for using REGR_INTERCEPT:
REGR_INTERCEPT(dependent-expression, independent-expression)
SELECT REGR_INTERCEPT(col1, col2) AS RIofCol1#2
FROM Stats_Table;
RIofCol1#2
-1.35
A regression line is a line of best fit, drawn through a set of points on a graph for X and Y coordinates. It uses the Y coordinate as the Dependent Variable and the X value as the Independent Variable.
Two regression lines always meet or intercept at the mean of the data points(x,y), where x=AVG(x) and y=AVG(y) and is not usually one of the original data points.
A REGR_INTERCEPT Example
Another REGR_INTERCEPT Example so you can Compare
The REGR_SLOPE Function
Syntax for using REGR_SLOPE:
REGR_SLOPE(dependent-expression, independent-expression)
SELECT REGR_SLOPE(col1, col2) AS RSCol1#2
FROM Stats_Table;
RSCol1#2
1.94
A regression line is a line of best fit, drawn through a set of points on a graph of X and Y coordinates. It uses the Y coordinate as the Dependent Variable and the X value as the Independent Variable.
The slope of the line is the angle at which it moves on the X and Y coordinates. The vertical slope is Y on X and the horizontal slope is X on Y.
A REGR_SLOPE Example
Another REGR_SLOPE Example so you can Compare
Using GROUP BY
SELECT
col3
,count(*) |
AS Cnt |
,avg(col1) |
AS Avg1 |
,stddev_pop(col1) |
AS SD1 |
,var_pop(col1) |
AS VP1 |
,avg(col4) |
AS Avg4 |
,stddev_pop(col4) |
AS SD4 |
,var_pop(col4) |
AS VP4 |
,avg(col6) |
AS Avg6 |
,stddev_pop(col6) |
AS SD6 |
,var_pop(col6) |
AS VP6 |
FROM Stats_Table GROUP BY 1 ORDER BY 1;
No Having Clause Vs Use of HAVING
