The xicor extension provides support for calculating the Xi correlation coefficient, a robust measure of correlation that works well with non-linear relationships. Your Nile database arrives with the xicor extension already enabled.

​
Overview

The xicor extension provides:

  • Calculation of Xi correlation coefficient
  • Support for incremental correlation updates
  • Robust handling of non-linear relationships
  • Better detection of dependencies between variables compared to Pearson correlation

​
Basic Usage

Calculate correlations:

-- Create a table with grouped measurements
CREATE TABLE group_measurements (
    tenant_id uuid,
    group_id int,
    x numeric,
    y numeric,
    PRIMARY KEY (tenant_id, group_id, x)
);

-- Insert grouped data
INSERT INTO group_measurements (tenant_id, group_id, x, y) VALUES
    ('11111111-1111-1111-1111-111111111111', 1, 1, 1),
    ('11111111-1111-1111-1111-111111111111', 1, 2, 4),
    ('11111111-1111-1111-1111-111111111111', 1, 3, 9),
    ('11111111-1111-1111-1111-111111111111', 2, 1, 2),
    ('11111111-1111-1111-1111-111111111111', 2, 2, 3),
    ('11111111-1111-1111-1111-111111111111', 2, 3, 5);

-- Calculate Xi correlation
SELECT xicor(x, y) FROM measurements;

-- Calculate correlation by group
SELECT 
    group_id,
    xicor(x, y) as correlation
FROM group_measurements
GROUP BY group_id;

​
Understanding Xi Correlation

The Xi correlation coefficient has several advantages over traditional correlation measures:

  1. Robust to Non-linearity:
-- Xi correlation detects monotonic relationships
-- even when they're not linear
SELECT xicor(x, exp(x)) FROM generate_series(1, 5) as x;
  1. Range of Values:
  • Returns values between 0 and 1
  • 0 indicates no correlation
  • 1 indicates perfect correlation (monotonic relationship)
  1. Interpretation:
-- Perfect correlation (monotonic)
SELECT xicor(x, x) FROM generate_series(1, 5) as x;  -- Returns 1.0

-- No correlation (random)
SELECT xicor(x, random()) FROM generate_series(1, 1000) as x;  -- Returns ~0

​
Use Cases

​
Financial Analysis

-- Analyze stock price correlations
CREATE TABLE stock_prices (
    tenant_id uuid,
    date date,
    stock_symbol text,
    price numeric,
    PRIMARY KEY (tenant_id, date, stock_symbol)
);

-- Calculate correlation between stock prices
SELECT 
    s1.stock_symbol as stock1,
    s2.stock_symbol as stock2,
    xicor(s1.price, s2.price) as price_correlation
FROM stock_prices s1
JOIN stock_prices s2 ON s1.date = s2.date
WHERE s1.stock_symbol < s2.stock_symbol
GROUP BY s1.stock_symbol, s2.stock_symbol;

​
Scientific Measurements

-- Analyze sensor data correlations
CREATE TABLE sensor_readings (
    tenant_id uuid,
    timestamp timestamp,
    sensor_id text,
    temperature numeric,
    humidity numeric,
    PRIMARY KEY (tenant_id, timestamp, sensor_id)
);

-- Calculate correlation between temperature and humidity
SELECT 
    sensor_id,
    xicor(temperature, humidity) as temp_humidity_correlation
FROM sensor_readings
GROUP BY sensor_id;

​
Additional Resources

Was this page helpful?

TablefuncEmailaddr