Проблема: Мне необходимо вычислить коэффициент корреляции Пирсона для двух наборов измерений, которые хранятся в моей базе данных SQL Server. Как это сделать?
Решение: Коэффициент корреляции Пирсона, также известный как коэффициент Пирсона, является математическим расчетом для определения линейной корреляции двух наборов данных. Коэффициент Пирсона может принимать значения от -1 до +1 включительно. Чем ближе коэффициент Пирсона к +1, тем сильнее положительная корреляция. Другими словами, с увеличением значений первого измерения увеличиваются и значения второго измерения. Чем ближе коэффициент Пирсона к -1, тем сильнее отрицательная корреляция. Другими словами, с увеличением значений первого измерения уменьшаются значения второго измерения. Коэффициент Пирсона, близкий к 0, как положительный, так и отрицательный, указывает на отсутствие корреляции между двумя наборами данных.
Как только наши данные находятся в таблице, написание запроса T-SQL для вычисления коэффициента корреляции Пирсона довольно просто. Однако, поскольку расчет включает числа с плавающей точкой и деление, мы должны быть осторожными, чтобы все наши числа были числами с плавающей точкой, чтобы не получить коэффициент Пирсона, имеющий только целочисленные значения -1, 0 или +1.
Давайте рассмотрим три примера, чтобы понять, как вычислить коэффициент Пирсона в SQL Server.
Пример положительной корреляции
Начнем с примера положительной корреляции. Мы создадим таблицу с колонками для измерения A и измерения B и вставим 20 строк данных в таблицу.
create table dbo.tblPositiveCorrelation
(
pkID integer identity(1,1) primary key,
measurementA float,
measurementB float
)
insert into dbo.tblPositiveCorrelation values (1.23350258, 1.095695912)
insert into dbo.tblPositiveCorrelation values (2.740809824, 2.515451457)
insert into dbo.tblPositiveCorrelation values (3.921364427, 3.535163576)
insert into dbo.tblPositiveCorrelation values (5.727112392, 5.440233141)
insert into dbo.tblPositiveCorrelation values (7.20400738, 7.034966693)
insert into dbo.tblPositiveCorrelation values (8.631639604, 8.657499396)
insert into dbo.tblPositiveCorrelation values (9.861575884, 10.25266377)
insert into dbo.tblPositiveCorrelation values (11.26773017, 11.26025417)
insert into dbo.tblPositiveCorrelation values (12.5538764, 12.73863763)
insert into dbo.tblPositiveCorrelation values (13.77519226, 14.52156486)
insert into dbo.tblPositiveCorrelation values (15.75008844, 16.27021747)
insert into dbo.tblPositiveCorrelation values (16.8040222, 18.04587966)
insert into dbo.tblPositiveCorrelation values (18.63079433, 19.52058173)
insert into dbo.tblPositiveCorrelation values (20.39947639, 21.38713489)
insert into dbo.tblPositiveCorrelation values (21.97242484, 22.42767485)
insert into dbo.tblPositiveCorrelation values (23.19091439, 24.18502987)
insert into dbo.tblPositiveCorrelation values (24.28721758, 26.09195563)
insert into dbo.tblPositiveCorrelation values (26.24160723, 27.51123988)
insert into dbo.tblPositiveCorrelation values (27.33126123, 28.93676183)
insert into dbo.tblPositiveCorrelation values (28.73483376, 29.97030518)
Затем мы выполним запрос T-SQL для вычисления коэффициента Пирсона. Мы также выведем значения числителя и знаменателя, чтобы помочь нам проверить правильность формулы.
SELECT
(Avg(measurementA * measurementB) - (Avg(measurementA) * Avg(measurementB))) / (StDevP(measurementA) * StDevP(measurementB)) as PearsonCoefficient,
(Avg(measurementA * measurementB) - (Avg(measurementA) * Avg(measurementB))) as numerator,
(StDevP(measurementA) * StDevP(measurementB)) as denominator
FROM dbo.tblPositiveCorrelation
Результатом будет коэффициент Пирсона, близкий к +1, указывающий на сильную положительную корреляцию между измерением A и измерением B.
Пример отрицательной корреляции
Затем перейдем к примеру отрицательной корреляции. Мы создадим и заполним отдельную таблицу с колонками измерения A и измерения B.
create table dbo.tblNegativeCorrelation
(
pkID integer identity(1,1) primary key,
measurementA float,
measurementB float
)
insert into dbo.tblNegativeCorrelation values (1.581662726, 29.65152293)
insert into dbo.tblNegativeCorrelation values (3.4369907, 28.01601842)
insert into dbo.tblNegativeCorrelation values (5.20372199, 26.64762377)
insert into dbo.tblNegativeCorrelation values (6.548564275, 25.52941989)
insert into dbo.tblNegativeCorrelation values (8.236812206, 24.46357622)
insert into dbo.tblNegativeCorrelation values (9.37715358, 22.94984262)
insert into dbo.tblNegativeCorrelation values (10.51055702, 21.25097239)
insert into dbo.tblNegativeCorrelation values (11.82382087, 19.31299184)
insert into dbo.tblNegativeCorrelation values (13.40427251, 17.97538895)
insert into dbo.tblNegativeCorrelation values (15.24348405, 16.23958242)
insert into dbo.tblNegativeCorrelation values (16.64768328, 14.69010224)
insert into dbo.tblNegativeCorrelation values (18.04064143, 12.86494034)
insert into dbo.tblNegativeCorrelation values (19.51671012, 11.4152393)
insert into dbo.tblNegativeCorrelation values (20.58177787, 10.30092096)
insert into dbo.tblNegativeCorrelation values (22.04140726, 8.625347334)
insert into dbo.tblNegativeCorrelation values (23.81958423, 6.721438358)
insert into dbo.tblNegativeCorrelation values (25.02552769, 5.340616899)
insert into dbo.tblNegativeCorrelation values (26.9841361, 3.958114624)
insert into dbo.tblNegativeCorrelation values (28.04718847, 2.624742871)
insert into dbo.tblNegativeCorrelation values (29.6213496, 1.264092442)
Запрос T-SQL для вычисления коэффициента Пирсона такой же, как и выше, за исключением отличающегося имени таблицы.
Результатом будет коэффициент Пирсона, близкий к -1, указывающий на сильную отрицательную корреляцию между измерением A и измерением B.
Пример отсутствия корреляции
Наконец, рассмотрим пример, где отсутствует корреляция. Мы создадим и заполним отдельную таблицу с колонками измерения A и измерения B.
create table dbo.tblNoCorrelation
(
pkID integer identity(1,1) primary key,
measurementA float,
measurementB float
)
insert into dbo.tblNoCorrelation values (0.80544264, 0.16208355)
insert into dbo.tblNoCorrelation values (0.895563605, 0.764382306)
insert into dbo.tblNoCorrelation values (0.169711771, 0.958280698)
insert into dbo.tblNoCorrelation values (0.377226503, 0.26927763)
insert into dbo.tblNoCorrelation values (0.695216156, 0.889767386)
insert into dbo.tblNoCorrelation values (0.864166528, 0.93030605)
insert into dbo.tblNoCorrelation values (0.909937206, 0.146715435)
insert into dbo.tblNoCorrelation values (0.126278394, 0.090460955)
insert into dbo.tblNoCorrelation values (0.743036786, 0.284939579)
insert into dbo.tblNoCorrelation values (0.482858255, 0.958005081)
insert into dbo.tblNoCorrelation values (0.452936554, 0.521588833)
insert into dbo.tblNoCorrelation values (0.232092942, 0.891831068)
insert into dbo.tblNoCorrelation values (0.303048204, 0.543747358)
insert into dbo.tblNoCorrelation values (0.562067989, 0.852912301)
insert into dbo.tblNoCorrelation values (0.752175511, 0.721606105)
insert into dbo.tblNoCorrelation values (0.09224805, 0.443346524)
insert into dbo.tblNoCorrelation values (0.390609317, 0.470526115)
insert into dbo.tblNoCorrelation values (0.925485575, 0.815146232)
insert into dbo.tblNoCorrelation values (0.598509836, 0.060795557)
insert into dbo.tblNoCorrelation values (0.87523696, 0.334668821)
Запрос T-SQL для вычисления коэффициента Пирсона для этой таблицы такой же, как и выше.
Результатом будет коэффициент Пирсона, близкий к 0, указывающий на отсутствие корреляции между измерением A и измерением B.
Заключение
Вычисление коэффициента корреляции Пирсона в SQL Server – это простой процесс. Используя соответствующий запрос T-SQL, мы можем определить силу и направление корреляции между двумя наборами измерений. Будь то положительная корреляция, отрицательная корреляция или отсутствие корреляции, коэффициент Пирсона предоставляет ценную информацию о взаимосвязи между данными.
Не забудьте убедиться, что все числа, используемые в расчете, являются числами с плавающей точкой, чтобы избежать неправильных результатов. Кроме того, всегда полезно проверить вычисленный коэффициент Пирсона с помощью других инструментов или методов, таких как Excel, чтобы обеспечить точность.