Особенности расчета коэффициента корреляции в PostgreSQL

[Akina]

Не, а где конкретные формулы того, как именно Постгресс и Эксель считают этот самый коэффициент корреляции? Ну хотя бы для того, чтобы убедиться, что их мнения не совпадают, и они называют одним и тем же термином слегка различающиеся вещи, откуда и проистекает различие в результатах.

[OlegIct]

Пример проще:

postgres=# SELECT corr( 0.09 , 0.09000001 ) FROM generate_series(1,24) ;
 corr 
------
     
(1 row)

postgres=# SELECT corr( 0.09 , 0.09000001 ) FROM generate_series(1,25) ;
        corr         
---------------------
 -0.1877294297321991
(1 row)

postgres=# SELECT corr( 0.09 , 0.09000001 ) FROM generate_series(1,31) ;
         corr         
----------------------
 -0.03366532289960463
(1 row)

postgres=# SELECT corr( 0.09 , 0.09000001 ) FROM generate_series(1,32) ;
         corr         
----------------------
 0.037939234274452456
(1 row)

Для разницы на порядок число строк для проявления эффекта чуть больше:

postgres=# SELECT corr( 0.9 , 0.91 ) FROM generate_series(1,36) ;
 corr 
------
     
(1 row)

postgres=# SELECT corr( 0.9 , 0.91 ) FROM generate_series(1,37) ;
        corr         
---------------------
 0.37167954745944803
(1 row)

postgres=# SELECT corr( 0.9 , 0.91 ) FROM generate_series(1,113) ;
         corr         
----------------------
 0.022884787550167176
(1 row)

postgres=# SELECT corr( 0.9 , 0.91 ) FROM generate_series(1,114) ;
         corr          
-----------------------
 -0.004981175111303341
(1 row)

Функция corr() реализуется тремя функциями: float8_corr, float8_regr_accum, float8_regr_combine в исходном коде https://doxygen.postgresql.org/float_8c_source.html

которые используют тип с плавающей запятой, который считается по своим правилам.

В Oracle Database считает корректно, там используется тип number, типы с плавающей запятой редкость:

SQL> select corr( 0.09 , 0.09000001 ) FROM (select rownum as id from dual connect by level <=330);

CORR(0.09,0.09000001)
---------------------

Если сообщить о баге и написать, что "в Oracle результат другой", может быть поправят.

Какой вывод можно сделать:

1) чем проще, тем лучше (принцип KISS). Например, можно считать TPS и execution time, а не "точки наблюдения" и будет точнее, понятнее, проще

2) функции PostgreSQL, которые используют типы с плавающей запятой могут выдавать в специально подобранных случаях неверные результаты. Так как часть статистики PostgreSQL хранит в real (float4), возможно, можно найти пример, где расчеты планировщика будут такими же чудесными, как результат corr().

[pg_expecto]

Пример проще:

Да когда наткнулся на грабли, примерно такие примеры и делал.

функции PostreSQL, которые используют типы с плавающей запятой могут выдавать в специально подобранных случаях неверные результаты.

Пришлось добавить действия перед расчетом коэффициента корреляции

1) Если вторая переменная константа - коэффициент корреляции не расчитывается

2) Использовать стандартизацию данных с помощью z-score

Например, можно считать TPS и execution time, а не "точки наблюдения" и будет точнее и понятнее

Это очень большая тема для комментария. Есть детали и с "точнее" и с "понятнее".

[OlegIct]

Спасибо, что обнаружили баг! Я заполнил баг-репорт, ссылка на обсуждение: https://www.postgresql.org/message-id/flat/375068.1764696127%40sss.pgh.pa.us#b9bb9381e1331b4eff316dd9c0e4adb2

Возможно, подберут способ распознавания одинаковых значений и исправят

[pg_expecto]

Спасибо за спасибо .

Будет интересно понаблюдать.