Contents
pgxicor: XI (ξ) Correlation Coefficient in Postgres
pgxicor is a Postgres extension that exposes a SELECT xicor(X, Y) aggregate function.
XI can detect functional relationships between X and Y.
You can use it as a more powerful alternative to standard corr(X, Y) which works best on linear relationships only.
For more information on XI, see the original paper. A New Coefficient of Correlation S. Chatterjee 2020
Why xicor ?
Standard Pearson correlation (corr()) only detects linear relationships. I
f your data has a strong, but non-linear pattern (like a parabola),
corr() will fail to detect it, while xicor() will easily pick it up.
CREATE TABLE non_linear (x float8, y float8);
INSERT INTO non_linear (x, y)
SELECT x, x * x FROM generate_series(-10, 10, 0.5) AS x;
SELECT corr(x, y) AS pearson, xicor(x, y) AS xi FROM non_linear;
pearson | xi
----------------------+--------------------
7.02754905861851e-18 | 0.9303882195448461
(1 row)
Usage
CREATE TABLE xicor_test (x float8, y float8);
INSERT INTO xicor_test (x, y)
VALUES
(1.0, 2.0),
(2.5, 3.5),
(3.0, 4.0),
(4.5, 5.5),
(5.0, 6.0);
-- Query to calculate the Xi correlation using the aggregate function
SELECT xicor(x, y) FROM xicor_test;
If your data contains ties and you want 100% reproducible results, you should also set the following.
SET xicor.ties = true;
SET xicor.seed = 42;
[!TIP] If you’re interested in this, also check out vasco; another similar extension based on the Maximal Information Coefficient (MIC). A standalone C implementation of ξ is also available libxicor.
Installation
cd /tmp
git clone https://github.com/Florents-Tselai/pgxicor.git
cd pgxicor
make
make install # may need sudo
After the installation, in a session:
CREATE EXTENSION xicor;
Docker
Get the Docker image with:
docker pull florents/pgxicor:pg17
This adds pgxicor to the Postgres image (replace 17 with your Postgres server version, and run it the same way).
Run the image in a container.
docker run --name pgxicor -p 5432:5432 -e POSTGRES_PASSWORD=pass florents/pgxicor:pg17
Through another terminal, connect to the running server (container).
PGPASSWORD=pass psql -h localhost -p 5432 -U postgres
PGXN
Install from the PostgreSQL Extension Network with:
pgxn install pgxicor