Purpose
You can call this function to calculate the sample covariance of a set of numeric value pairs. You can use it as an aggregate or analytic function.
Note
- When you use it as an analytic function, use the
OVERclause to define a window over the data on which the function operates. The function operates on a group of rows to return a list of values.- When you use it as an aggregate function, the function operates on a set of rows and returns a single value. You do not need to add the
OVERclause.
Syntax
COVAR_SAMP(expr1, expr2) [ OVER (analytic_clause) ]
Parameters
| Parameter | Description |
|---|---|
| expr1 | Specifies the first value expression, which is of the numeric data type or can be implicitly converted to the numeric data type. |
| expr2 | Specifies the second value expression, which is of the numeric data type or can be implicitly converted to the numeric data type. |
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information about analytic functions, see Analytic functions. |
Note
- The database determines the parameter with the highest numeric precedence, implicitly converts the remaining parameter values to this data type, and returns a result of this data type.
- The positions of
expr1andexpr2have no influence over the return result, that is,COVAR_POP(expr1, expr2)is equivalent toCOVAR_POP(expr2, expr1).- The returned result is calculated by using the following formula:
(SUM(expr1 * expr2) - SUM(expr1) * SUM(expr2) / n) / (n-1), wherenindicates the number of expression pairs (expr1,expr2).expr1andexpr2cannot be empty.
Return types
If any parameter is unspecified or you have only one row of data, the function returns NULL. Otherwise, the function returns a value of the NUMBER type.
Examples
Assume that you have created the table tbl1.
obclient> SELECT * FROM tbl1;
+------+------+------+------+
COL1 COL2 COL3 COL4
+------+------+------+------+
1 A1 8 12
1 A2 10 15
1 A3 11 16
2 B1 9 14
2 B2 10 15
2 B3 8 13
2 B4 11 16
3 C1 8 18
3 C2 9 16
3 C3 10 15
3 C4 11 12
3 C5 12 10
+------+------+------+------+
12 rows in set
Aggregate example
Calculate the sample covariance of the numeric pairs in the col3 and col4 columns.
obclient> SELECT COVAR_SAMP(col3,col4) FROM tbl1;
+--------------------------------------------+
CORR(COL3,COL4)
+--------------------------------------------+
-.2705008904002296868793073195758520224002
+--------------------------------------------+
1 row in set
Analytic example
Group data by the col1 column. Sort data by the col2 column. Calculate the cumulative sample covariance of the values in the col3 and col4 columns.
obclient> SELECT col1,col3,col4,
COVAR_SAMP(col3,col4) OVER(PARTITION BY col1 ORDER BY col2) "COVAR_SAMP"
FROM tbl1;
+------+------+------+-------------------------------------------+
COL1 COL3 COL4 COVAR_SAMP
+------+------+------+-------------------------------------------+
1 8 12 NULL
1 10 15 3
1 11 16 3.1666666666666666666666666666666666665
2 9 14 NULL
2 10 15 .5
2 8 13 1
2 11 16 1.66666666666666666666666666666666666667
3 8 18 NULL
3 9 16 -1
3 10 15 -1.5
3 11 12 -3.16666666666666666666666666666666666667
3 12 10 -5
+------+------+------+-------------------------------------------+
12 rows in set