Categories: Aggregate, Window

COVAR_POP

Returns the population covariance for non-NULL pairs across all input values.

Syntax

COVAR_POP(expression1 NUMERIC, expression2 NUMERIC) → DOUBLE

• expression1: An expression that evaluates to a numeric type. This parameter is the dependent value.
• expression2: An expression that evaluates to a numeric type. This parameter is the independent value.

Examples

COVAR_POP example: Aggregate function

SELECT COVAR_POP(trip_distance_mi, fare_amount) FROM Samples."samples.dremio.com"."NYC-taxi-trips";
-- 31.705367711861427

COVAR_POP example: Aggregate function with optional DISTINCT clause

SELECT COVAR_POP(DISTINCT trip_distance_mi, fare_amount
FROM Samples."samples.dremio.com"."NYC-taxi-trips";
-- 302.592806814534

COVAR_POP example: Window function

SELECT COVAR_POP(trip_distance_mi, fare_amount)
  OVER (PARTITION BY trip_distance_mi)
FROM Samples."samples.dremio.com"."NYC-taxi-trips";
-- EXPR$0
-- 1.9984014443252818E-15
-- 1.9984014443252818E-15
-- 1.9984014443252818E-15
-- ...

COVAR_POP example: Window function with cumulative window frame

SELECT COVAR_POP(trip_distance_mi, fare_amount)
  OVER (PARTITION BY trip_distance_mi ORDER BY pickup_datetime RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM Samples."samples.dremio.com"."NYC-taxi-trips";
-- EXPR$0
-- -9.38969718520366E-4
-- -9.398569655090783E-4
-- -9.405475644511108E-4
-- ...

COVAR_POP example: Window function with sliding window frame

SELECT COVAR_POP(trip_distance_mi, fare_amount)
  OVER (PARTITION BY trip_distance_mi ORDER BY pickup_datetime ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
FROM Samples."samples.dremio.com"."NYC-taxi-trips";
-- EXPR$0
-- 2.7755575615628914E-17
-- 0.0
-- 2.7755575615628914E-17
-- ...

Usage Notes

The COVAR_POP function supports ALL and DISTINCT:
   SELECT COVAR_POP( [ { ALL | DISTINCT } ] expression1, expression2).

The COVAR_POP function supports optional PARTITION BY, ORDER_BY, and cumulative and sliding window frame subclauses. See [Window Functions](See Window Functions) for more information and syntax.