> ## Documentation Index
> Fetch the complete documentation index at: https://thenile.dev/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Xicor

> Incremental correlation calculations in PostgreSQL

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:

```sql theme={null}
-- 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:

```sql theme={null}
-- Xi correlation detects monotonic relationships
-- even when they're not linear
SELECT xicor(x, exp(x)) FROM generate_series(1, 5) as x;
```

2. Range of Values:

* Returns values between 0 and 1
* 0 indicates no correlation
* 1 indicates perfect correlation (monotonic relationship)

3. Interpretation:

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

* [Xi Correlation Extension repository](https://github.com/Florents-Tselai/pgxicor)
* [Xi Correlation Paper](https://arxiv.org/abs/1909.10140)
* [Statistical Correlation Measures in PostgreSQL](https://www.postgresql.org/docs/current/functions-aggregate.html)