Xicor

-- 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;

-- Xi correlation detects monotonic relationships
-- even when they're not linear
SELECT xicor(x, exp(x)) FROM generate_series(1, 5) as x;

-- 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

-- 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;

-- 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;