REGR_R2 returns wrong results:
Row 6 (grp=same_x):
Column 'out': actual=1.0, expected=null
Row 7 (grp=same_y):
Column 'out': actual=null, expected=1.0
Repro
| CREATE | |||
| OR REPLACE TEMPORARY VIEW t AS | |||
| SELECT * | |||
| FROM VALUES | |||
| ('all_null', NULL, NULL), ('all_null', NULL, NULL), | |||
| ('all_null', NULL, NULL), | |||
| ('single', 1.0, 2.0), | |||
| ('single_null', NULL, NULL), | |||
| ('same_x', 1.0, 5.0), | |||
| ('same_x', 2.0, 5.0), | |||
| ('same_x', 3.0, 5.0), | |||
| ('same_y', 5.0, 1.0), | |||
| ('same_y', 5.0, 2.0), | |||
| ('same_y', 5.0, 3.0) AS t(grp, y, x); |
select grp, regr_r2(y, x) as out from t group by grp order by grp;
Notice y and x are passed in the flipped order as per https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html#:~:text=Returns%20the%20average%20of%20the,regr_sxy(y%2C%20x)
Why I believe this is wrong result?
Unfortunately, I gave all my stats 101 knowledge back to my college professor. So I googled it:
Summary of Behavioral Edge Cases
| Scenario | Data Condition | REGR_R2(y, x) Result |
|---|---|---|
| Same 𝑥{} |
Unable to render embedded object: File (wAAACH5BAEAAAAALAAAAAABAAEAAAICRAEAOw==) not found. VAR_POP |
NULL (undefined slope) |
| Same 𝑦{} |
Unable to render embedded object: File (wAAACH5BAEAAAAALAAAAAABAAEAAAICRAEAOw==) not found. VAR_POP Unable to render embedded object: File (wAAACH5BAEAAAAALAAAAAABAAEAAAICRAEAOw==) not found. VAR_POP |
1 (perfect "fit" to horizontal line) |
| Empty Set | No rows or only NULL pairs | NULL |