问表中列间相关性的计算

EN

CREATE TABLE CountryData (DataDate date,
                          Country varchar(15),
                          SomeDecimal decimal(2,1)); --No idea what this represents

WITH VTE AS(
    SELECT *
    FROM (VALUES('20180101',5  ,3.2,1.6,2.8),
                ('20180102',6.8,8.2,0.9,3.5),
                ('20180103',4.3,6.2,3.3,2.1),
                ('20180104',3.9,6.2,4.5,7.8),
                ('20180131',7.6,5.1,4.7,5.1)) V([Date],USA,JAPAN,FRANCE,UK))
--Pivot the data. This is not dynamic, I am simply showing you what you need to data to look like.
--It was far quicker for me to take your existing data and pivot it, than transcribe it all in Normal Form
INSERT INTO CountryData
SELECT V.[Date],
       C.Country,
       CASE C.Country WHEN 'USA' THEN USA
                      WHEN 'JAPAN' THEN JAPAN
                      WHEN 'FRANCE' THEN FRANCE
                      WHEN 'UK' THEN UK
       END AS SomeDecimal
FROM VTE V
     CROSS APPLY (VALUES('USA'),('JAPAN'),('FRANCE'),('UK')) C(Country);
GO
--How your data should look
SELECT *
FROM CountryData;
GO
--And now the (much simpler) non-dynamic solution
SELECT CDa.Country AS Country1,
       CDb.Country As Country2,
       (Avg(CDa.SomeDecimal * CDb.SomeDecimal) - (Avg(CDa.SomeDecimal) * Avg(CDb.SomeDecimal))) / (StDevP(CDa.SomeDecimal) * StDevP(CDb.SomeDecimal)) AS Correlation
FROM CountryData CDa
     JOIN CountryData CDb ON CDa.Country != CDb.Country
GROUP BY CDa.Country, CDb.Country
ORDER BY CDa.Country, CDb.Country;

GO
DROP TABLE CountryData;