On this page

    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

    Aggregate function example

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

    Aggregate function example using optional DISTINCT clause

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

    Window function example

    SELECT COVAR_POP(trip_distance_mi, fare_amount)
      OVER (PARTITION BY trip_distance_mi)
    FROM Samples."samples.dremio.com"."NYC-taxi-trips"
    
    -- 1.5543122344752192e-15
    

    Usage Notes

    This function supports the use of ALL and DISTINCT:

    SELECT COVAR_POP( [ { ALL | DISTINCT } ] expression1, expression2)