STDDEV_SAMP
Purpose
Returns the sample standard deviation for the non-null data points in value_expression.
Syntax
where:
|
Syntax element … |
Specifies … |
|
ALL |
to include all non-null values specified by value_expression, including duplicates, in the computation. This is the default. |
|
DISTINCT |
to exclude duplicates of value_expression from the computation. |
|
value_expression |
a numeric literal or column expression whose sample standard deviation is to be computed. The expression cannot contain any ordered analytical or aggregate functions. |
ANSI Compliance
This is ANSI SQL:2011 compliant.
Definition
The standard deviation is the second moment of a distribution. For a sample, it is a measure of dispersion from the mean of that sample. The computation is more conservative for the population standard deviation to minimize the effect of outliers on the computed value.
Computation
The equation for computing STDDEV_SAMP is as follows:
where:
|
This variable … |
Represents … |
|
x |
value_expression |
Division by zero results in NULL rather than an error.
When there are fewer than two non-null data points in the sample used for the computation, then STDDEV_SAMP returns NULL.
Result Type and Attributes
The data type, format, and title for STDDEV_SAMP(x) are as follows.
Data type: REAL
For information on the default format of data types, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.
Support for UDTs
By default, Teradata Database performs implicit type conversion on a UDT argument that has an implicit cast that casts between the UDT and any of the following predefined types:
To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.
Implicit type conversion of UDTs for system operators and functions, including STDDEV_SAMP, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. For details, see Utilities: Volume 1 (A-K).
For more information on implicit type conversion of UDTs, see Chapter 13: “Data Type Conversions.”
Combination With Other Functions
STDDEV_SAMP can be combined with ordered analytical functions in a SELECT list, QUALIFY clause, or ORDER BY clause. For more information on ordered analytical functions, see Chapter 22: “Ordered Analytical / Window Aggregate Functions.”
STDDEV_SAMP cannot be combined with aggregate functions within the same SELECT list, QUALIFY clause, or ORDER BY clause.
How GROUP BY Affects Report Breaks
The GROUP BY clause affects the STDDEV_SAMP operation.
|
IF the query … |
THEN STDDEV_SAMP is reported for … |
|
specifies a GROUP BY clause |
each individual group. |
|
does not specify a GROUP BY clause |
all the rows in the sample. |
Measuring the Standard Deviation of a Population
If your data represents the entire population for the variable, then use the STDDEV_POP function. For information, see “STDDEV_POP” on page 94.
As the sample size increases, the values for STDDEV_SAMP and STDDEV_POP approach the same number, but you should use the more conservative STDDEV_SAMP calculation unless you are absolutely certain that your data constitutes the entire population for the variable.
STDDEV_SAMP Window Function
For the STDDEV_SAMP window function that performs a group, cumulative, or moving computation, see “Window Aggregate Functions” on page 984.