Calculated field
CORR([amount], [other])
Sample data
| id | amount | units | score | other |
|---|---|---|---|---|
| 1 | -2.5 | 2 | -0.8 | 3 |
| 2 | 0 | 0 | 0 | 4 |
| 3 | 1.5 | 3 | 0.25 | 2 |
| 4 | 4 | 4 | 0.5 | 8 |
| 5 | 9 | 5 | 0.8 | 11 |
| 6 | 16 | 6 | 1 | 14 |
| 7 | null | 7 | null | 9 |
| 8 | 4 | 8 | -0.5 | null |
The same calculation as SQL
SELECT corr(amount,other) AS result FROM input
Runs the example above.
SELECT corr(amount,other) AS result FROM input
Written for this dialect; not run here.
SELECT corr(amount,other) AS result FROM input
Written for this dialect; not run here.
Result
| result |
|---|
| 0.94417 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Replacing missing observations with zero changes the aggregate.
Naive SQL
SELECT corr(coalesce(amount,0),coalesce(other,0)) AS result FROM input
result 0.720132 Matches the calculation
SELECT corr(amount,other) AS result FROM input
result 0.94417
Signature
CORR(number, number)