CORR

​

Syntax

​

Aggregate

corr(y, x)

​

Window

corr(y, x) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])

• Use a frame that includes all rows in the group or starts at the first row (UNBOUNDED PRECEDING). Sliding frames that drop earlier rows are not supported. RANGE frames with a numeric or time offset are not supported.

​

Arguments

​

Aggregate and window

​

Returns

• Type: DOUBLE
• NULL behavior: Pairs with either value NULL are excluded. Fewer than two valid pairs or zero variance yields NULL. Both means NaN yields NaN; one NaN mean yields NULL.
• Empty input: Returns NULL on empty input.

​

Properties

​

Usage notes

• Inputs are converted to DOUBLE. Floating-point rounding can cause small differences in the last digits of the result.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• Supports DISTINCT: Adding DISTINCT does not remove duplicate input values.

​

Examples

​

Basic example

WITH t(y,x) AS (VALUES (0.0,0.0),(2.0,2.0)) SELECT corr(y,x) AS result FROM t;

​

A negative relationship and a missing pair

WITH sample(x, y) AS (VALUES (1, 3), (3, 1), (CAST(NULL AS INT), 100))
SELECT corr(CAST(x AS DOUBLE), CAST(y AS DOUBLE)) AS result FROM sample;

​

Limitations

• Use all rows in the group or a frame starting with UNBOUNDED PRECEDING. Other starting boundaries are outside the forms covered here.
• DISTINCT does not remove duplicate input values in grouped calls. Removing duplicates can change the result, so remove duplicates from the input separately when needed.