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

Для расчета коэффициента корреляции в PostgreSQL используется агрегатная функция corr

corr ( Y double precision, X double precision ) → double precision
Вычисляет коэффициент корреляции.

Postgres Pro Enterprise : Документация: 17: 9.21. Агрегатные функции : Компания Postgres Professional

Однако, в ходе экспериментов была обнаружена интересная особенность функции corr - несовпадение результата с вычислениями в Excel.

Демонстрация для PostgreSQL версии 17

Тестовая таблица

CREATE TABLE test
( id integer , n1 double precision ,
n2 double precision , n3 double precision ,
n4 double precision ,n5 double precision );

Заполнение тестовой таблицы

INSERT INTO test
( id , n1 ,
n2 ,n3 ,
n4 ,n5 )
VALUES
( generate_series(1, 100),trunc(random()*100),
0.09 ,0.01 ,
50 ,50.0 + ( random() / 100.0 ) );

Данные в тестовой таблице

SELECT * FROM test ;
id | n1 | n2 | n3 | n4 | n5
-----+----+------+------+----+--------------------
1 | 36 | 0.09 | 0.01 | 50 | 50.00621654293942
2 | 92 | 0.09 | 0.01 | 50 | 50.006497742852645
3 | 1 | 0.09 | 0.01 | 50 | 50.006280993837635
4 | 69 | 0.09 | 0.01 | 50 | 50.00841365225481
5 | 78 | 0.09 | 0.01 | 50 | 50.00154316058645
6 | 47 | 0.09 | 0.01 | 50 | 50.00738933579143
7 | 17 | 0.09 | 0.01 | 50 | 50.00603762461176
8 | 10 | 0.09 | 0.01 | 50 | 50.00758576917584
9 | 76 | 0.09 | 0.01 | 50 | 50.00134152728483
10 | 34 | 0.09 | 0.01 | 50 | 50.00645489433778
11 | 16 | 0.09 | 0.01 | 50 | 50.002059723234744
12 | 6 | 0.09 | 0.01 | 50 | 50.00966318353639
13 | 27 | 0.09 | 0.01 | 50 | 50.009460725903594
14 | 81 | 0.09 | 0.01 | 50 | 50.009483957949875
15 | 21 | 0.09 | 0.01 | 50 | 50.00821428161928
16 | 46 | 0.09 | 0.01 | 50 | 50.0033679016193
17 | 37 | 0.09 | 0.01 | 50 | 50.006204066803825
18 | 72 | 0.09 | 0.01 | 50 | 50.000081383021204
19 | 1 | 0.09 | 0.01 | 50 | 50.00456641068732
20 | 46 | 0.09 | 0.01 | 50 | 50.00244477619546
21 | 87 | 0.09 | 0.01 | 50 | 50.00176368771677
22 | 3 | 0.09 | 0.01 | 50 | 50.00569580845861
23 | 70 | 0.09 | 0.01 | 50 | 50.0095379964205
24 | 23 | 0.09 | 0.01 | 50 | 50.009802959442894
25 | 87 | 0.09 | 0.01 | 50 | 50.00569967390437
26 | 21 | 0.09 | 0.01 | 50 | 50.009189504439476
27 | 95 | 0.09 | 0.01 | 50 | 50.00900754265528
28 | 78 | 0.09 | 0.01 | 50 | 50.00238383410292
29 | 61 | 0.09 | 0.01 | 50 | 50.00084203208884
30 | 10 | 0.09 | 0.01 | 50 | 50.00435131919839
31 | 47 | 0.09 | 0.01 | 50 | 50.004035289654404
32 | 59 | 0.09 | 0.01 | 50 | 50.00445991601925
33 | 62 | 0.09 | 0.01 | 50 | 50.00878572611977
34 | 71 | 0.09 | 0.01 | 50 | 50.002757109856425
35 | 52 | 0.09 | 0.01 | 50 | 50.00611056006292
36 | 66 | 0.09 | 0.01 | 50 | 50.00697885620605
37 | 9 | 0.09 | 0.01 | 50 | 50.006011915628285
38 | 67 | 0.09 | 0.01 | 50 | 50.00859411134951
39 | 96 | 0.09 | 0.01 | 50 | 50.004438428438476
40 | 96 | 0.09 | 0.01 | 50 | 50.00204724660321
41 | 47 | 0.09 | 0.01 | 50 | 50.00637634321826
42 | 15 | 0.09 | 0.01 | 50 | 50.00976974510356
43 | 8 | 0.09 | 0.01 | 50 | 50.00624291944184
44 | 51 | 0.09 | 0.01 | 50 | 50.005173603996624
45 | 20 | 0.09 | 0.01 | 50 | 50.00210520583459
46 | 9 | 0.09 | 0.01 | 50 | 50.00363434421869
47 | 0 | 0.09 | 0.01 | 50 | 50.006149827589745
48 | 22 | 0.09 | 0.01 | 50 | 50.00378023237334
49 | 66 | 0.09 | 0.01 | 50 | 50.007290786978416
50 | 34 | 0.09 | 0.01 | 50 | 50.00056022030347
51 | 74 | 0.09 | 0.01 | 50 | 50.00638723914476
52 | 95 | 0.09 | 0.01 | 50 | 50.007043480245116
53 | 78 | 0.09 | 0.01 | 50 | 50.000835732376615
54 | 3 | 0.09 | 0.01 | 50 | 50.00492664579969
55 | 85 | 0.09 | 0.01 | 50 | 50.0055832735501
56 | 98 | 0.09 | 0.01 | 50 | 50.00069012074435
57 | 84 | 0.09 | 0.01 | 50 | 50.00311092699821
58 | 44 | 0.09 | 0.01 | 50 | 50.00211598464375
59 | 68 | 0.09 | 0.01 | 50 | 50.00980213460893
60 | 25 | 0.09 | 0.01 | 50 | 50.000213922580144
61 | 11 | 0.09 | 0.01 | 50 | 50.00491783229052
62 | 16 | 0.09 | 0.01 | 50 | 50.00385799142445
63 | 84 | 0.09 | 0.01 | 50 | 50.00315243952117
64 | 8 | 0.09 | 0.01 | 50 | 50.00256690991191
65 | 90 | 0.09 | 0.01 | 50 | 50.002797358413225
66 | 45 | 0.09 | 0.01 | 50 | 50.00457478193562
67 | 77 | 0.09 | 0.01 | 50 | 50.00018115160071
68 | 25 | 0.09 | 0.01 | 50 | 50.007349523094966
69 | 65 | 0.09 | 0.01 | 50 | 50.001488573852114
70 | 93 | 0.09 | 0.01 | 50 | 50.0075771417218
71 | 37 | 0.09 | 0.01 | 50 | 50.0066571961394
72 | 87 | 0.09 | 0.01 | 50 | 50.00480149092147
73 | 23 | 0.09 | 0.01 | 50 | 50.00569788662592
74 | 94 | 0.09 | 0.01 | 50 | 50.00415552203464
75 | 93 | 0.09 | 0.01 | 50 | 50.002042239062405
76 | 69 | 0.09 | 0.01 | 50 | 50.00659559667872
77 | 65 | 0.09 | 0.01 | 50 | 50.00858634168854
78 | 35 | 0.09 | 0.01 | 50 | 50.004245405616565
79 | 56 | 0.09 | 0.01 | 50 | 50.005895444411436
80 | 23 | 0.09 | 0.01 | 50 | 50.00710996591808
81 | 35 | 0.09 | 0.01 | 50 | 50.00655347804682
82 | 16 | 0.09 | 0.01 | 50 | 50.00649347589736
83 | 1 | 0.09 | 0.01 | 50 | 50.0028453545188
84 | 22 | 0.09 | 0.01 | 50 | 50.00516821142212
85 | 12 | 0.09 | 0.01 | 50 | 50.00156739277321
86 | 32 | 0.09 | 0.01 | 50 | 50.00520623376633
87 | 21 | 0.09 | 0.01 | 50 | 50.00239659550629
88 | 32 | 0.09 | 0.01 | 50 | 50.00663612351142
89 | 9 | 0.09 | 0.01 | 50 | 50.00020650827494
90 | 62 | 0.09 | 0.01 | 50 | 50.008316709037935
91 | 37 | 0.09 | 0.01 | 50 | 50.00328408974682
92 | 98 | 0.09 | 0.01 | 50 | 50.008635831213816
93 | 85 | 0.09 | 0.01 | 50 | 50.00747649345121
94 | 34 | 0.09 | 0.01 | 50 | 50.005888321085465
95 | 45 | 0.09 | 0.01 | 50 | 50.00259685294329
96 | 35 | 0.09 | 0.01 | 50 | 50.001741489475876
97 | 16 | 0.09 | 0.01 | 50 | 50.00261103814009
98 | 87 | 0.09 | 0.01 | 50 | 50.00848172675686
99 | 98 | 0.09 | 0.01 | 50 | 50.00741565325358
100 | 20 | 0.09 | 0.01 | 50 | 50.00501197001834
(100 rows)

Результаты расчета коэффициентов корреляции в PostgreSQL

Корреляция между столбцами n1-n2 (Разница между величинами ~3 порядка, одна величина - константа)

SELECT corr( n1 , n2 ) FROM test ;
corr
-----------------------
-0.009999684804104647
(1 row)

Корреляция между столбцами n1-n3 (Разница между величинами ~3 порядка, одна величина - константа)

SELECT corr( n1 , n3 ) FROM test ;
corr
-----------------------
-0.020890460418644157
(1 row)

Корреляция между столбцами n1-n4 (Cлучайная величина и константа одного порядка)

SELECT corr( n1 , n4 ) FROM test ;
corr
------
(1 row)

Корреляция между столбцами n1-n5 (Случайные величины одного порядка)

SELECT corr( n1 , n5 ) FROM test ;
corr
-----------------------
-0.008818762482844123
(1 row)

Результаты расчета коэффициентов корреляции в Excel - числовой тип , 15 символов после запятой

Корреляция между столбцами n1-n2 (Разница между величинами - ~3 порядка)

Корреляция между столбцами n1-n3 (Разница между величинами - ~3 порядка)

Корреляция между столбцами n1-n4 (Cлучайная величина и константа одного порядка)

Корреляция между столбцами n1-n5 (Случайные величины одного порядка)

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

Разница между величинами ~3 порядка

Нулевое значение корреляции - при расчете с помощью Excel.
Ненулевое значение - коэффициента корреляции при расчете с помощью PostgreSQL.

Cлучайная величина и константа одного порядка

Отсутствие корреляции при расчете с помощью Excel и PostgreSQL.

Случайные величины одного порядка

Разница в расчете коэффициента корреляции между значениями полученными с помощью Excel и PostgreSQL = 0,000000000002502

Дополнительная информация по расчету коэффициента корреляции для столбцов n1-n2 , n1-n3(Разница между величинами ~3 порядка, одна величина - константа)

В описанном сценарии (разница значений одной величины превышает 2-3 порядка, а вторая величина постоянна) коэффициент корреляции Пирсона будет неопределен (NaN - Not a Number) или, в некоторых реализациях, может быть возвращен как 0.

1.Суть коэффициента Пирсона: Он измеряет линейную связь между двумя изменяющимися величинами. Его формула основана на ковариации двух переменных, деленной на произведение их стандартных отклонений:

r = cov(X, Y) / (σ_X * σ_Y)

2.Проблема с постоянной величиной: Если одна из величин (допустим, Y) постоянна (все ее значения одинаковы), то:

Стандартное отклонение σ_Y = 0: Поскольку нет никакой вариации значений вокруг среднего.

Ковариация cov(X, Y) = 0: Ковариация измеряет, как совместно отклоняются величины от своих средних. Постоянная величина Y никогда не отклоняется от своего среднего (которое равно любому ее значению), поэтому cov(X, Y) всегда будет равна 0, независимо от того, как ведет себя X.

3.Деление на ноль: Формула коэффициента корреляции Пирсона превращается в:

r = 0 / (σ_X * 0) = 0 / 0

Деление на ноль (σ_Y = 0) математически не определено. Это основная причина, почему коэффициент не может быть рассчитан.

4. Почему неважен масштаб изменений X: Даже если значения X варьируются на 2, 3, 10 порядков (т.е., σ_X очень велико), это не спасает ситуацию. В числителе формулы ковариация все равно будет 0 (потому что Y не меняется), а в знаменателе σ_Y все равно будет 0. Результат 0 / 0 остается неопределенным.

5. Практическая интерпретация в статистических пакетах:

NaN (Not a Number): Большинство статистических программ (R, Python с библиотеками типа pandas, scipy, numpy) вернут NaN при попытке рассчитать корреляцию, если одна из переменных постоянна. Это корректное математическое представление неопределенности.

0: Некоторые очень простые реализации или скрипты могут вернуть 0, так как ковариация равна 0. Однако это технически неверно. Нулевая корреляция подразумевает отсутствие линейной связи между двумя изменяющимися величинами. Здесь же одна величина вообще не изменяется, поэтому говорить о связи ее изменений с изменениями другой величины бессмысленно. Состояние "постоянная величина" принципиально отличается от состояния "величина есть, но не коррелирует".

Итог:

Математически: Коэффициент корреляции Пирсона не определен (NaN), так как в его формуле происходит деление на ноль (стандартное отклонение постоянной величины равно 0).
Концептуально: Корреляция измеряет согласованность изменений двух величин. Если одна величина не изменяется, понятие "корреляция ее изменений с изменениями другой величины" лишено смысла.
На практике: Ожидайте результат NaN (или, реже и менее корректно, 0) при расчете в статистическом ПО.

Очень простой пример проблемы расчета коэффициента корреляции в PostgreSQL

WITH dataset AS (

SELECT x, 0.09 AS y

FROM generate_series(0, 100) AS x)

SELECT corr(x, y) FROM dataset;

Результат расчета коэффициента корреляции в PostgreSQL

$ psql
psql (17.5)
Type "help" for help.
postgres=# WITH dataset AS (
postgres(# SELECT x, 0.09 AS y
postgres(# FROM generate_series(0, 100) AS x
postgres(# )
postgres-# SELECT corr(x, y) FROM dataset;
corr
--------------------
0.9501367579819386
(1 row)
postgres=#