COVAR_SAMP - Teradata Database

COVAR_SAMP

Purpose  

Returns the sample covariance of its arguments for all non-null data point pairs.

Syntax  

where:

Syntax element …	Specifies …
value_expression_2	a numeric expression to be paired with a second numeric expression to determine their covariance. The expressions cannot contain any ordered analytical or aggregate functions.
value_expression_1

ANSI Compliance

This is ANSI SQL:2011 compliant.

Definition

Covariance measures whether or not two random variables vary in the same way. It is the sum of the products of deviations for each non-null data point pair.

Note that high covariance does not imply a causal relationship between the variables.

Combination With Other Functions

COVAR_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.”

COVAR_SAMP cannot be combined with aggregate functions within the same SELECT list, QUALIFY clause, or ORDER BY clause.

Computation

The equation for computing COVAR_SAMP is defined as follows:

where:

This variable …	Represents …
x	value_expression_2
y	value_expression_1

When there are no non-null data point pairs in the data used for the computation, then COVAR_SAMP returns NULL.

Division by zero results in NULL rather than an error.

Result Type and Attributes

The data type, format, and title for COVAR_SAMP(y, x) are as follows.

Data type: REAL

If the operand is character, the format is the default format for FLOAT.

If the operand is numeric, date, or interval, the format is the same format as x.

If the operand is a UDT, the format is the format for the data type to which the UDT is implicitly cast.

For information on the default format of data types and an explanation of the formatting characters in the format, 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 UDT arguments that have implicit casts that cast between the UDTs and any of the following predefined types:

Numeric

Character

DATE

Interval

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 COVAR_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.”

COVAR_SAMP Window Function

For the COVAR_SAMP window function that performs a group, cumulative, or moving computation, see “Window Aggregate Functions” on page 984.

Example  

This example is based the following regrtbl data. Nulls are indicated by the QUESTION MARK character.

c1	height	weight
1	60	84
2	62	95
3	64	140
4	66	155
5	68	119
6	70	175
7	72	145
8	74	197
9	76	150
10	76	?
11	?	150
12	?	?

The following SELECT statement returns the sample covariance of weight and height where neither weight nor height is null.

   SELECT COVAR_SAMP(weight,height) 

   FROM regrtbl;

   Covar_Samp(weight,height)

   -------------------------

                         150