Est-il un moyen de calculer la corrélation dans TSQL à l'aide de PLUS de Clauses au lieu de la CTE?

Disons que vous avez une table avec des colonnes, la Date, l'id de groupe, X et Y.

CREATE TABLE #sample
  (
     [Date]  DATETIME,
     GroupID INT,
     X       FLOAT,
     Y       FLOAT
  )

DECLARE @date DATETIME = getdate()

INSERT INTO #sample VALUES(@date, 1, 1,3)
INSERT INTO #sample VALUES(DATEADD(d, 1, @date), 1, 1,1)
INSERT INTO #sample VALUES(DATEADD(d, 2, @date), 1, 4,2)
INSERT INTO #sample VALUES(DATEADD(d, 3, @date), 1, 3,3)
INSERT INTO #sample VALUES(DATEADD(d, 4, @date), 1, 6,4)
INSERT INTO #sample VALUES(DATEADD(d, 5, @date), 1, 7,5)
INSERT INTO #sample VALUES(DATEADD(d, 6, @date), 1, 1,6)

et que vous voulez calculer la corrélation de X et Y pour chaque groupe. Actuellement, je utiliser d'expressions de table communes qui obtenir un peu désordonné:

;WITH DataAvgStd
     AS (SELECT GroupID,
                AVG(X)   AS XAvg,
                AVG(Y)   AS YAvg,
                STDEV(X) AS XStdev,
                STDEV(Y) AS YSTDev,
                COUNT(*) AS SampleSize
         FROM   #sample
         GROUP  BY GroupID),
     ExpectedVal
     AS (SELECT s.GroupID,
                SUM(( X - XAvg ) * ( Y - YAvg )) AS ExpectedValue
         FROM   #sample s
                JOIN DataAvgStd das
                  ON s.GroupID = das.GroupID
         GROUP  BY s.GroupID)
SELECT das.GroupID,
       ev.ExpectedValue / ( das.SampleSize - 1 ) / ( das.XStdev * das.YSTDev )
       AS
       Correlation
FROM   DataAvgStd das
       JOIN ExpectedVal ev
         ON das.GroupID = ev.GroupID

DROP TABLE #sample

Il semble qu'il devrait y avoir un moyen de l'utiliser SUR et PARTITION pour ce faire d'un seul coup, sans aucune sous-requêtes. Idéalement TSQL aurait une fonction de sorte que vous pourriez écrire:

SELECT GroupID, CORR(X, Y) OVER(PARTITION BY GroupID)
FROM #sample
GROUP BY GroupID

Je serais curieux de voir si quelqu'un arrive avec une solution viable, cependant, j'ai toujours tirer toutes mes données à la couche de gestion et d'effectuer des corrélations. Nous réalisons également ce que nous appelons des "corrélations négatives" - où l'on ignorer les valeurs positives et les inclure uniquement des valeurs négatives, il serait également intéressant de voir si c'était viable en SQL.
Le code que vous avez posté n'a pas exécuter pour diverses raisons. Je l'ai changé donc il va effectivement vous voudrez peut-être vérifier qu'il ne fait toujours ce que vous attendiez...
Si X ou Y sont les valeurs null, vous devez remplacer "#exemple" avec "DE l' #exemple OÙ X EST NON NUL, ET Y n'EST PAS NUL", sinon vous pouvez vous retrouver avec la mauvaise corrélation

OriginalL'auteur bpeikes | 2011-08-03

À l'aide de cette formule de correlation vous ne pouvez pas éviter toutes les requêtes imbriquées, même si vous utilisez over(). Le truc, c'est que vous ne pouvez pas utiliser à la fois du groupe et par la même requête et aussi vous ne pouvez pas avoir imbriqués fonctions d'agrégation par exemple sum(x - avg(x)). De sorte que vous dans le meilleur des cas, en fonction de vos données, vous aurez besoin de garder au moins le with.

Votre code ressemblera à quelque chose comme ça

;WITH DataAvgStd
     AS (SELECT GroupID,
                STDEV(X) over(partition by GroupID) AS XStdev,
                STDEV(Y) over(partition by GroupID) AS YSTDev,
                COUNT(*) over(partition by GroupID) AS SampleSize,
                ( X - AVG(X) over(partition by GroupID)) * ( Y - AVG(Y) over(partition by GroupID)) AS ExpectedValue
         FROM   #sample s)         
SELECT distinct GroupID,
       SUM(ExpectedValue) over(partition by GroupID) / (SampleSize - 1 ) / ( XStdev * YSTDev ) AS Correlation
FROM DataAvgStd

Une alternative est d'utiliser un equilevant formule de corrélation comme Wikipédia décrit.

Cela peut être écrite comme

SELECT GroupID,
       Correlation=(COUNT(*) * SUM(X * Y) - SUM(X) * SUM(Y)) / 
                   (SQRT(COUNT(*) * SUM(X * X) - SUM(X) * SUM(x))
                    * SQRT(COUNT(*) * SUM(Y* Y) - SUM(Y) * SUM(Y)))
FROM #sample s
GROUP BY GroupID;

La deuxième formule que vous mentionnez, où est-il dans l'article de Wikipedia? Je n'arrive pas à le voir.
C'est la dernière formule de la Pearson's product-moment coefficient section.

OriginalL'auteur Athafoud

Une Passe de Solution pour les Deux Calcs:

Il y a deux versions du coefficient de corrélation de Pearson, l'un pour l'Échantillon et pour l'ensemble de la Population. Ce sont de simple, un seul passage, et je crois, de corriger les formules pour les deux:

-- Methods for calculating the two Pearson correlation coefficients
SELECT  
        -- For Population
        (avg(x * y) - avg(x) * avg(y)) / 
        (sqrt(avg(x * x) - avg(x) * avg(x)) * sqrt(avg(y * y) - avg(y) * avg(y))) 
        AS correlation_coefficient_population,
        -- For Sample
        (count(*) * sum(x * y) - sum(x) * sum(y)) / 
        (sqrt(count(*) * sum(x * x) - sum(x) * sum(x)) * sqrt(count(*) * sum(y * y) - sum(y) * sum(y))) 
        AS correlation_coefficient_sample
    FROM (
        -- The following generates a table of sample data containing two columns with a luke-warm and tweakable correlation 
        -- y = x for 0 thru 99, y = x - 100 for 100 thru 199, etc.  Execute it as a stand-alone to see for yourself
        -- x and y are CAST as DECIMAL to avoid integer math, you should definitely do the same
        -- Try TOP 100 or less for full correlation (y = x for all cases), TOP 200 for a PCC of 0.5, TOP 300 for one near 0.33, etc.
        -- The superfluous "+ 0" is where you could apply various offsets to see that they have no effect on the results
        SELECT TOP 200
                CAST(ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 + 0 AS DECIMAL) AS x, 
                CAST((ROW_NUMBER() OVER (ORDER BY [object_id]) - 1) % 100 AS DECIMAL) AS y 
            FROM sys.all_objects
    ) AS a

Comme je l'ai noté dans les commentaires, vous pouvez essayer l'exemple avec le TOP 100 ou moins complète de corrélation (y = x pour tous les cas); TOP 200 des rendements des corrélations très près de 0,5; TOP 300, autour de 0,33; etc. Il y a un endroit ("+ 0") pour ajouter un décalage si vous le souhaitez; spoiler alert, il n'a pas d'effet. Assurez-vous de jeter vos valeurs DÉCIMALE d'un entier mathématiques peuvent impact significatif de ces calcs.

OriginalL'auteur jwolf

1

SQL est un peu drôle sur l'imbrication des agrégats ou des fonctions de fenêtrage, d'où la nécessité pour les expressions de table communes ou les tables dérivées.

Si elle doit être implémentée sur le serveur de base de données, et vous êtes à la recherche de quelque chose de plus lisible que les expressions de table communes, votre seule option est de faire rouler votre propre ensemble avec des CLR.

Il y a un bon tutoriel ici http://www.sqlservercentral.com/articles/SQLCLR/71942/ sur la construction d'une semblable CLR globale.

Ouais. Je ne sais pas pourquoi quelqu'un voudrait le faire dans le "business" de la couche. Vous devez tirer une tonne de données de la base de données pour calculer des corrélations. Ce que je n'ai vraiment pas comprendre, c'est comment SQL server ne l'a pas comme un construit en. Semble comme SQL server peut faire un bien meilleur travail en optimisant les chiffres.

OriginalL'auteur Code Magician

Vous devez vous connecter pour publier un commentaire.