Расчёт статистической значимости коэффициента корреляции в PostgreSQL

Для проверки статистической значимости коэффициента корреляции между vm_dirty и показателями vmstat в PostgreSQL можно использовать следующие методы:

1. Базовый расчёт корреляции Пирсона с проверкой значимости

Метод 1: Использование встроенных статистических функций

WITH correlation_data AS (
SELECT
-- Рассчитываем коэффициент корреляции Пирсона
CORR(vm_dirty_value, vmstat_value) AS pearson_r,
-- Количество наблюдений
COUNT(*) AS n,
-- Рассчитываем t-статистику
CORR(vm_dirty_value, vmstat_value) *
SQRT((COUNT(*) - 2) /
(1 - POWER(CORR(vm_dirty_value, vmstat_value), 2))) AS t_stat
FROM monitoring_data
WHERE vm_dirty_value IS NOT NULL
AND vmstat_value IS NOT NULL
)
SELECT
pearson_r,
n,
t_stat,
-- Рассчитываем p-value (двусторонний тест)
-- Используем функцию распределения Стьюдента
2 * (1 - cdf_t(t_stat, n - 2)) AS p_value,
-- Интерпретация значимости
CASE
WHEN 2 * (1 - cdf_t(t_stat, n - 2)) < 0.01 THEN 'ОЧЕНЬ ВЫСОКАЯ значимость (p < 0.01)'
WHEN 2 * (1 - cdf_t(t_stat, n - 2)) < 0.05 THEN 'Высокая значимость (p < 0.05)'
WHEN 2 * (1 - cdf_t(t_stat, n - 2)) < 0.1 THEN 'Умеренная значимость (p < 0.1)'
ELSE 'Незначимо (p ≥ 0.1)'
END AS significance_level
FROM correlation_data;

2. Расширенная функция для расчёта полной статистики

CREATE OR REPLACE FUNCTION calculate_correlation_stats(
table_name text,
column_x text,
column_y text,
from_time timestamp DEFAULT NULL,
to_time timestamp DEFAULT NULL
)
RETURNS TABLE (
correlation_coef numeric,
sample_size integer,
t_statistic numeric,
p_value numeric,
is_significant boolean,
confidence_interval_lower numeric,
confidence_interval_upper numeric,
interpretation text
) AS $$
DECLARE
r numeric;
n integer;
t numeric;
p numeric;
se numeric;
z_alpha numeric := 1.96; -- для 95% доверительного интервала
BEGIN
EXECUTE format('
SELECT
CORR(%I, %I),
COUNT(*)
FROM %I
WHERE (%I IS NOT NULL AND %I IS NOT NULL)
AND ($1 IS NULL OR sample_time >= $1)
AND ($2 IS NULL OR sample_time <= $2)',
column_x, column_y, table_name, column_x, column_y)
INTO r, n
USING from_time, to_time;

-- Рассчитываем t-статистику
IF r IS NOT NULL AND n > 2 AND ABS(r) < 1 THEN
t := r * SQRT((n - 2)::numeric / (1 - r*r));

-- Рассчитываем p-value через функцию распределения Стьюдента
-- Используем аппроксимацию для функции CDF t-распределения
p := 2 * (1 - (1 + erf(t / SQRT(2 * (n - 2)))) / 2);

-- Стандартная ошибка
se := SQRT((1 - r*r) / (n - 2));

-- Возвращаем результат
RETURN QUERY SELECT
r,
n,
t,
GREATEST(0.000001, LEAST(0.999999, p)), -- Ограничиваем p-value
p < 0.05,
r - z_alpha * se,
r + z_alpha * se,
CASE
WHEN p < 0.01 AND ABS(r) >= 0.7 THEN 'Очень сильная значимая связь'
WHEN p < 0.05 AND ABS(r) >= 0.5 THEN 'Сильная значимая связь'
WHEN p < 0.05 AND ABS(r) >= 0.3 THEN 'Умеренная значимая связь'
WHEN p < 0.05 THEN 'Слабая, но значимая связь'
ELSE 'Статистически незначимая связь'
END;
ELSE
RETURN QUERY SELECT
NULL::numeric, 0, NULL::numeric, 1.0,
false, NULL::numeric, NULL::numeric,
'Недостаточно данных для анализа';
END IF;
END;
$$ LANGUAGE plpgsql;

3. Практический пример использования для анализа производительности

-- Анализ корреляции между грязными страницами и временем ожидания I/O
WITH hourly_metrics AS (
SELECT
date_trunc('hour', sample_time) as hour,
AVG(vm_dirty_pages) as avg_dirty,
AVG(vmstat_wa) as avg_wa,
AVG(vmstat_so) as avg_so,
AVG(vmstat_b) as avg_blocked
FROM system_metrics
WHERE sample_time >= now() - interval '7 days'
GROUP BY date_trunc('hour', sample_time)
HAVING COUNT(*) >= 30 -- Минимум 30 точек для статистической мощности
)
SELECT
'vm_dirty vs wa' as correlation_pair,
(SELECT * FROM calculate_correlation_stats(
'hourly_metrics', 'avg_dirty', 'avg_wa'
)).*,
CASE
WHEN (SELECT p_value FROM calculate_correlation_stats(
'hourly_metrics', 'avg_dirty', 'avg_wa'
)) < 0.05 AND
(SELECT correlation_coef FROM calculate_correlation_stats(
'hourly_metrics', 'avg_dirty', 'avg_wa'
)) > 0.6
THEN 'ТРЕВОГА: I/O bottleneck'
WHEN (SELECT p_value FROM calculate_correlation_stats(
'hourly_metrics', 'avg_dirty', 'avg_wa'
)) < 0.05 AND
(SELECT correlation_coef FROM calculate_correlation_stats(
'hourly_metrics', 'avg_dirty', 'avg_wa'
)) > 0.3
THEN 'ВНИМАНИЕ: потенциальная проблема с I/O'
ELSE 'Норма'
END as recommendation

UNION ALL

SELECT
'vm_dirty vs so' as correlation_pair,
(SELECT * FROM calculate_correlation_stats(
'hourly_metrics', 'avg_dirty', 'avg_so'
)).*,
CASE
WHEN (SELECT p_value FROM calculate_correlation_stats(
'hourly_metrics', 'avg_dirty', 'avg_so'
)) < 0.05 AND
(SELECT correlation_coef FROM calculate_correlation_stats(
'hourly_metrics', 'avg_dirty', 'avg_so'
)) > 0.3
THEN 'КРИТИЧЕСКАЯ ТРЕВОГА: риск swapping'
ELSE 'Норма'
END as recommendation;

4. Упрощённая проверка значимости без сложных расчётов

-- Быстрая проверка значимости корреляции
WITH stats AS (
SELECT
CORR(vm_dirty, vmstat_value) as r,
COUNT(*) as n,
SQRT(COUNT(*) - 2) /
SQRT(1 - POWER(CORR(vm_dirty, vmstat_value), 2)) as test_value
FROM performance_data
WHERE ts BETWEEN '2024-01-01' AND '2024-01-31'
)
SELECT
r,
n,
-- Эмпирическое правило: для n > 30 и |r| > 2/√n корреляция значима
CASE
WHEN n > 30 AND ABS(r) > 2 / SQRT(n) THEN 'Значима (p < ~0.05)'
WHEN n > 100 AND ABS(r) > 1.65 / SQRT(n) THEN 'Значима (p < ~0.1)'
WHEN n > 10 AND ABS(r) > 3 / SQRT(n) THEN 'Значима (p < ~0.01)'
ELSE 'Незначима'
END as significance_empirical,
-- Более точная проверка через t-критерий
CASE
WHEN ABS(r * SQRT((n - 2) / (1 - r*r))) > 1.96 THEN 'Значима (95% уровень)'
WHEN ABS(r * SQRT((n - 2) / (1 - r*r))) > 1.645 THEN 'Значима (90% уровень)'
ELSE 'Незначима'
END as significance_t_test
FROM stats;

5. Автоматизированный мониторинг значимых корреляций

-- Ежедневный отчёт о значимых корреляциях
CREATE MATERIALIZED VIEW significant_correlations_daily
AS
SELECT
m.metric_name,
c.correlation_coef,
c.p_value,
c.sample_size,
CASE
WHEN c.p_value < 0.01 AND ABS(c.correlation_coef) > 0.7 THEN 'CRITICAL'
WHEN c.p_value < 0.05 AND ABS(c.correlation_coef) > 0.5 THEN 'HIGH'
WHEN c.p_value < 0.05 AND ABS(c.correlation_coef) > 0.3 THEN 'MEDIUM'
WHEN c.p_value < 0.1 AND ABS(c.correlation_coef) > 0.5 THEN 'LOW'
ELSE 'INSIGNIFICANT'
END as alert_level,
CURRENT_DATE as analysis_date
FROM vmstat_metrics m
CROSS JOIN LATERAL (
SELECT * FROM calculate_correlation_stats(
'system_performance',
'vm_dirty_pages',
m.metric_column
)
) c
WHERE c.is_significant = true
AND c.p_value < 0.05
AND ABS(c.correlation_coef) > 0.3
ORDER BY c.p_value ASC, ABS(c.correlation_coef) DESC;

Критерии интерпретации результатов в PostgreSQL

1. Минимальный размер выборки: n ≥ 30 для надежных результатов
2. Проверка нормальности: данные должны быть приблизительно нормально распределены
3. Уровень значимости для PostgreSQL-оптимизации:
   p < 0.01 — высокая уверенность для изменения конфигурации
   p < 0.05 — достаточная уверенность для дальнейшего исследования
   p ≥ 0.05 — недостаточно доказательств для действий