sql - PIL 逊相关 SQL Server

Question

我有两个表:

ID,YRMO,计数

1,2013,12,4

1,2014年1月,6

1,2014,2,7

2,2014,1,6

2,2,2014,8

ID,YRMO,计数

2013-12-10

1,2014年1月,8

1、2014、3、12

2,2014年1月,6

2,2014,2,10

我想找出每组 ID 的 PIL 逊相关系数。大约有 200 多种不同的 IDS。

PIL 逊相关性是衡量两个变量 X 和 Y 之间线性相关性(相关性)的量度，给出的值介于 +1 和 -1 之间

可以在这里找到更多信息:http://oreilly.com/catalog/transqlcook/chapter/ch08.html在计算相关部分

Answer 1

计算 PIL 逊相关系数；您需要先计算Mean，然后是standard daviation，然后是correlation coefficient，如下所述

1。计算平均值

insert into tab2 (tab1_id, mean)
select ID, sum([counts]) / 
(select count(*) from tab1) as mean
from tab1
group by ID;

2。计算标准偏差

update tab2
set stddev = (
select sqrt(
sum([counts] * [counts]) / 
(select count(*) from tab1)
- mean * mean
) stddev
from tab1
where tab1.ID = tab2.tab1_id
group by tab1.ID);

3。最后 Pearson 相关系数

select ID,
((sf.sum1 / (select count(*) from tab1)
- stats1.mean * stats2.mean
)
/ (stats1.stddev * stats2.stddev)) as PCC
from (
select r1.ID,
 sum(r1.[counts] * r2.[counts]) as sum1
from tab1 r1
join tab1 r2
on r1.ID = r2.ID
group by r1.ID
) sf
join tab2 stats1
on stats1.tab1_id = sf.ID
join tab2 stats2
on stats2.tab1_id = sf.ID

根据您发布的数据得出的结果

在这里查看演示 fiddle http://sqlfiddle.com/#!3/0da20/5

编辑:

好精炼一点。您可以使用以下函数获取 PCC，但我没有得到与您完全相同的结果，而是得到 0.999996000000000 for ID = 1。

这对您来说可能是一个很好的切入点。您可以从此处进一步优化计算。

create function calculate_PCC(@id int)
returns decimal(16,15)
as
begin
declare @mean numeric(16,5);
declare @stddev numeric(16,5);
declare @count numeric(16,5);
declare @pcc numeric(16,12);
declare @store numeric(16,7);
select @count = CONVERT(numeric(16,5), count(case when Id=@id then 1 end)) from tab1;
select @mean = convert(numeric(16,5),sum([Counts])) / @count
from tab1 WHERE ID = @id;
select @store = (sum(counts * counts) / @count) from tab1 WHERE ID = @id;
set @stddev = sqrt(@store - (@mean * @mean)); 
set @pcc = ((@store - (@mean * @mean)) / (@stddev * @stddev)); 

return @pcc;
end

像这样调用函数

select db_name.dbo.calculate_PCC(1)