COVAR_SAMP¶

Returns the sample covariance of value pairs.

Syntax¶

-- As an aggregate
COVAR_SAMP( expr1, expr2 )

-- As a window function
COVAR_SAMP ( expr1, expr2 ) OVER (
         [ PARTITION BY value_expression [, ...] ]
         [ ORDER BY value_expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
         [ frame_clause ]
      )

Arguments¶

Parameter	Description
`expr1`, `expr2`	Numeric expression

Returns¶

Returns the sample covariance with type DOUBLE.

Notes¶

When all rows contain NULL values, the function returns NULL.
The function also returns NULL when only one value is non-NULL.

Examples¶

For these examples, assume a table named nba, with the following structure:

CREATE TABLE nba
(
   "Name" text(40),
   "Team" text(40),
   "Number" tinyint,
   "Position" text(2),
   "Age" tinyint,
   "Height" text(4),
   "Weight" real,
   "College" text(40),
   "Salary" float
 );

Here’s a peek at the table contents (Download nba.csv):

nba.csv¶
Name	Team	Number	Position	Age	Height	Weight	College	Salary
Avery Bradley	Boston Celtics	0.0	PG	25.0	6-2	180.0	Texas	7730337.0
Jae Crowder	Boston Celtics	99.0	SF	25.0	6-6	235.0	Marquette	6796117.0
John Holland	Boston Celtics	30.0	SG	27.0	6-5	205.0	Boston University
R.J. Hunter	Boston Celtics	28.0	SG	22.0	6-5	185.0	Georgia State	1148640.0
Jonas Jerebko	Boston Celtics	8.0	PF	29.0	6-10	231.0		5000000.0
Amir Johnson	Boston Celtics	90.0	PF	29.0	6-9	240.0		12000000.0
Jordan Mickey	Boston Celtics	55.0	PF	21.0	6-8	235.0	LSU	1170960.0
Kelly Olynyk	Boston Celtics	41.0	C	25.0	7-0	238.0	Gonzaga	2165160.0
Terry Rozier	Boston Celtics	12.0	PG	22.0	6-2	190.0	Louisville	1824360.0

Simple covariance¶

t=> SELECT "Team", COVAR_SAMP("Age","Salary") FROM nba GROUP BY 1 ORDER BY 2 ASC;
Team                   | covar_samp
-----------------------+--------------
Cleveland Cavaliers    | -9899969.4945
San Antonio Spurs      | -7434481.0571
Oklahoma City Thunder  | -3960471.5619
Detroit Pistons        | -1407390.5429
Los Angeles Clippers   | -1037225.7619
New Orleans Pelicans   |  -464825.0117
Utah Jazz              |   997312.2524
Philadelphia 76ers     |  1570272.6593
Sacramento Kings       |  2470827.9429
Dallas Mavericks       |  2591500.1095
Washington Wizards     |  2601352.3905
Milwaukee Bucks        |  2790831.8458
Orlando Magic          |  3029242.3187
Golden State Warriors  |  3591810.3571
Portland Trail Blazers |  4223202.2714
Denver Nuggets         |  4271493.8132
Toronto Raptors        |  4847589.7762
Minnesota Timberwolves |  4867005.0256
Charlotte Hornets      |  5418069.4286
Houston Rockets        |   5688478.081
Phoenix Suns           |   5979617.881
Indiana Pacers         |  6169271.6857
Boston Celtics         |  6243718.6264
Brooklyn Nets          |  6556855.7857
Chicago Bulls          |  6971097.7714
Atlanta Hawks          |  9492270.0714
Memphis Grizzlies      | 10256905.0769
New York Knicks        | 10949120.7333
Miami Heat             | 14093744.6795
Los Angeles Lakers     |    16500218.2