• Questions
• # REGR_R2 returns a value of 1 when y column contains a constant value (e.g., all rows have value of 0.0038).

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Thanks for the question, Stephen.

Asked: October 22, 2019 - 3:40 pm UTC

Answered by: Connor McDonald - Last updated: October 25, 2019 - 5:04 am UTC

Category: SQL - Version: Version 18.2.0.183

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: 18c database creation on Windows

I was searching a large emissions database for pairs of columns with correlated values.
My first effort identified a large number of sets with an R2 value of 1 based on blindly applying the REGR_R2 function to discrete pairs of numeric data columns.

When I started reviewing outputs I noticed that many of the REGR_R2 results were not reliable because REGR_R2 appears to return a value of 1 when the y column is constant (i.e., y_col) in the syntax REGR_R2(y_col, x_col).

I plan to add a standard deviation calculation to the script prior to the REGR_R2 calculation and forgo the REGR_R2 calculation when the standard deviation of y_col = 0.

But why does REGR_R2 return a value of 1 when the y parameter is constant and the x parameter is variable? Shouldn't it return either 0 or null?

table xy_values

y_val x_val
row 1 0.0038 0.023
row 2 0.0038 0.022
row 3 0.0038 0.085
row 4 0.0038 0.087
row 5 0.0038 0.019

```select regr_r2(xy_values.y_val, xy_values.x_val)
from  xy_values;```

yields
REGR_R2(Y_VAL,X_VAL) = 1

Thanks,
Stephen

## and we said...

As per the docs:

https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/REGR_-Linear-Regression-Functions.html#GUID-A675B68F-2A88-4843-BE2C-FCDE9C65F9A9

```NULL if VAR_POP(expr2)  = 0

1 if VAR_POP(expr1)  = 0 and
VAR_POP(expr2) != 0

POWER(CORR(expr1,expr),2) if VAR_POP(expr1)  > 0 and
VAR_POP(expr2  != 0
```

# More to Explore

##### PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.