Existe-t-il une fonction de régression linéaire dans SQL Server?

Existe-t-il une fonction de régression linéaire dans SQL Server 2005/2008, similaire au Régression Linéaire des fonctions d'Oracle ?

24
demandé sur marc_s 2010-03-29 13:31:12

8 réponses

à ma connaissance, il n'y en a pas. L'écriture est assez simple, cependant. Ce qui suit vous donne la constante alpha et la pente bêta pour y = Alpha + bêta * x + epsilon:

-- test data (GroupIDs 1, 2 normal regressions, 3, 4 = no variance)
WITH some_table(GroupID, x, y) AS
(       SELECT 1,  1,  1    UNION SELECT 1,  2,  2    UNION SELECT 1,  3,  1.3  
  UNION SELECT 1,  4,  3.75 UNION SELECT 1,  5,  2.25 UNION SELECT 2, 95, 85    
  UNION SELECT 2, 85, 95    UNION SELECT 2, 80, 70    UNION SELECT 2, 70, 65    
  UNION SELECT 2, 60, 70    UNION SELECT 3,  1,  2    UNION SELECT 3,  1, 3
  UNION SELECT 4,  1,  2    UNION SELECT 4,  2,  2),
 -- linear regression query
/*WITH*/ mean_estimates AS
(   SELECT GroupID
          ,AVG(x * 1.)                                             AS xmean
          ,AVG(y * 1.)                                             AS ymean
    FROM some_table
    GROUP BY GroupID
),
stdev_estimates AS
(   SELECT pd.GroupID
          -- T-SQL STDEV() implementation is not numerically stable
          ,CASE      SUM(SQUARE(x - xmean)) WHEN 0 THEN 1 
           ELSE SQRT(SUM(SQUARE(x - xmean)) / (COUNT(*) - 1)) END AS xstdev
          ,     SQRT(SUM(SQUARE(y - ymean)) / (COUNT(*) - 1))     AS ystdev
    FROM some_table pd
    INNER JOIN mean_estimates  pm ON pm.GroupID = pd.GroupID
    GROUP BY pd.GroupID, pm.xmean, pm.ymean
),
standardized_data AS                   -- increases numerical stability
(   SELECT pd.GroupID
          ,(x - xmean) / xstdev                                    AS xstd
          ,CASE ystdev WHEN 0 THEN 0 ELSE (y - ymean) / ystdev END AS ystd
    FROM some_table pd
    INNER JOIN stdev_estimates ps ON ps.GroupID = pd.GroupID
    INNER JOIN mean_estimates  pm ON pm.GroupID = pd.GroupID
),
standardized_beta_estimates AS
(   SELECT GroupID
          ,CASE WHEN SUM(xstd * xstd) = 0 THEN 0
                ELSE SUM(xstd * ystd) / (COUNT(*) - 1) END         AS betastd
    FROM standardized_data pd
    GROUP BY GroupID
)
SELECT pb.GroupID
      ,ymean - xmean * betastd * ystdev / xstdev                   AS Alpha
      ,betastd * ystdev / xstdev                                   AS Beta
FROM standardized_beta_estimates pb
INNER JOIN stdev_estimates ps ON ps.GroupID = pb.GroupID
INNER JOIN mean_estimates  pm ON pm.GroupID = pb.GroupID

Ici GroupID est utilisé pour montrer comment Grouper par une certaine valeur dans votre table de données source. Si vous voulez seulement les statistiques à travers toutes les données dans le tableau (pas de sous-groupes spécifiques), vous pouvez le laisser tomber et les jointures. J'ai utilisé le WITH déclaration par souci de clarté. Comme un alternative, vous pouvez utiliser des sous-requêtes à la place. Veuillez tenir compte de la précision du type de données utilisé dans vos tableaux, car la stabilité numérique peut se détériorer rapidement si la précision n'est pas assez élevée par rapport à vos données.

EDIT: (en réponse à Pierre de la question pour ce qui est des statistiques tels que R2 dans les commentaires)

vous pouvez facilement calculer des statistiques supplémentaires en utilisant la même technique. Voici une version avec R2, corrélation, et échantillon la covariance:

-- test data (GroupIDs 1, 2 normal regressions, 3, 4 = no variance)
WITH some_table(GroupID, x, y) AS
(       SELECT 1,  1,  1    UNION SELECT 1,  2,  2    UNION SELECT 1,  3,  1.3  
  UNION SELECT 1,  4,  3.75 UNION SELECT 1,  5,  2.25 UNION SELECT 2, 95, 85    
  UNION SELECT 2, 85, 95    UNION SELECT 2, 80, 70    UNION SELECT 2, 70, 65    
  UNION SELECT 2, 60, 70    UNION SELECT 3,  1,  2    UNION SELECT 3,  1, 3
  UNION SELECT 4,  1,  2    UNION SELECT 4,  2,  2),
 -- linear regression query
/*WITH*/ mean_estimates AS
(   SELECT GroupID
          ,AVG(x * 1.)                                             AS xmean
          ,AVG(y * 1.)                                             AS ymean
    FROM some_table pd
    GROUP BY GroupID
),
stdev_estimates AS
(   SELECT pd.GroupID
          -- T-SQL STDEV() implementation is not numerically stable
          ,CASE      SUM(SQUARE(x - xmean)) WHEN 0 THEN 1 
           ELSE SQRT(SUM(SQUARE(x - xmean)) / (COUNT(*) - 1)) END AS xstdev
          ,     SQRT(SUM(SQUARE(y - ymean)) / (COUNT(*) - 1))     AS ystdev
    FROM some_table pd
    INNER JOIN mean_estimates  pm ON pm.GroupID = pd.GroupID
    GROUP BY pd.GroupID, pm.xmean, pm.ymean
),
standardized_data AS                   -- increases numerical stability
(   SELECT pd.GroupID
          ,(x - xmean) / xstdev                                    AS xstd
          ,CASE ystdev WHEN 0 THEN 0 ELSE (y - ymean) / ystdev END AS ystd
    FROM some_table pd
    INNER JOIN stdev_estimates ps ON ps.GroupID = pd.GroupID
    INNER JOIN mean_estimates  pm ON pm.GroupID = pd.GroupID
),
standardized_beta_estimates AS
(   SELECT GroupID
          ,CASE WHEN SUM(xstd * xstd) = 0 THEN 0
                ELSE SUM(xstd * ystd) / (COUNT(*) - 1) END         AS betastd
    FROM standardized_data
    GROUP BY GroupID
)
SELECT pb.GroupID
      ,ymean - xmean * betastd * ystdev / xstdev                   AS Alpha
      ,betastd * ystdev / xstdev                                   AS Beta
      ,CASE ystdev WHEN 0 THEN 1 ELSE betastd * betastd END        AS R2
      ,betastd                                                     AS Correl
      ,betastd * xstdev * ystdev                                   AS Covar
FROM standardized_beta_estimates pb
INNER JOIN stdev_estimates ps ON ps.GroupID = pb.GroupID
INNER JOIN mean_estimates  pm ON pm.GroupID = pb.GroupID

EDIT 2 améliore la stabilité numérique en standardisant les données (au lieu de centrer uniquement) et en remplaçant STDEV à cause de numérique des problèmes de stabilité. Pour moi, la mise en œuvre actuelle semble être le meilleur compromis entre stabilité et complexité. Je pourrais améliorer la stabilité en remplaçant mon écart type par un algorithme en ligne numériquement stable, mais cela compliquerait considérablement la mise en œuvre (et le ralentir). De même, les implémentations utilisant par exemple des compensations Kahan(-Babuška-Neumaier) pour les SUM et AVG semblent effectuer modestement mieux dans les tests limités, mais faire la requête beaucoup plus complexe. Et tant que je ne sais pas combien de T-SQL implémente SUM et AVG (par exemple, il est possible qu'il utilise déjà la sommation par paires), Je ne peux pas garantir que de telles modifications améliorent toujours la précision.

38
répondu stephan 2016-12-29 12:04:58

il s'agit d'une méthode alternative, basée sur un blog sur la Régression Linéaire en T-SQL, qui utilise les équations suivantes:

enter image description here

la suggestion SQL dans le blog utilise cependant des curseurs. Voici une version prétifiée d'un forum de réponse que j'ai utilisé:

table
-----
X (numeric)
Y (numeric)

/**
 * m = (nSxy - SxSy) / (nSxx - SxSx)
 * b = Ay - (Ax * m)
 * N.B. S = Sum, A = Mean
 */
DECLARE @n INT
SELECT @n = COUNT(*) FROM table
SELECT (@n * SUM(X*Y) - SUM(X) * SUM(Y)) / (@n * SUM(X*X) - SUM(X) * SUM(X)) AS M,
       AVG(Y) - AVG(X) *
       (@n * SUM(X*Y) - SUM(X) * SUM(Y)) / (@n * SUM(X*X) - SUM(X) * SUM(X)) AS B
FROM table
13
répondu icc97 2017-12-28 14:17:18

j'ai en fait écrit une routine SQL en utilisant L'orthoganalisation Gram-Schmidt. Il, ainsi que d'autres routines d'apprentissage et de prévision machine, est disponible à sqldatamine.blogspot.com

a la suggestion de Brad Larson, j'ai ajouté le code ici plutôt que de diriger les utilisateurs vers mon blog. Cela produit les mêmes résultats que la fonction linest dans Excel. Ma principale source est Éléments de l'Apprentissage Statistique (2008) par Hastie, Tibshirni et Friedman.

--Create a table of data
create table #rawdata (id int,area float, rooms float, odd float,  price float)

insert into #rawdata select 1, 2201,3,1,400
insert into #rawdata select 2, 1600,3,0,330
insert into #rawdata select 3, 2400,3,1,369
insert into #rawdata select 4, 1416,2,1,232
insert into #rawdata select 5, 3000,4,0,540

--Insert the data into x & y vectors
select id xid, 0 xn,1 xv into #x from #rawdata
union all
select id, 1,rooms  from #rawdata
union all
select id, 2,area  from #rawdata
union all
select id, 3,odd  from #rawdata

select id yid, 0 yn, price yv  into #y from #rawdata

--create a residuals table and insert the intercept (1)
create table #z (zid int, zn int, zv float)
insert into #z select id , 0 zn,1 zv from #rawdata

--create a table for the orthoganal (#c) & regression(#b) parameters
create table #c(cxn int, czn int, cv float) 
create table #b(bn int, bv float) 


--@p is the number of independent variables including the intercept (@p = 0)
declare @p int
set @p = 1


--Loop through each independent variable and estimate the orthagonal parameter (#c)
-- then estimate the residuals and insert into the residuals table (#z)
while @p <= (select max(xn) from #x)
begin   
        insert into #c
    select  xn cxn,  zn czn, sum(xv*zv)/sum(zv*zv) cv 
        from #x join  #z on  xid = zid where zn = @p-1 and xn>zn group by xn, zn

    insert into #z
    select zid, xn,xv- sum(cv*zv) 
        from #x join #z on xid = zid   join  #c  on  czn = zn and cxn = xn  where xn = @p and zn<xn  group by zid, xn,xv

    set @p = @p +1
end

--Loop through each independent variable and estimate the regression parameter by regressing the orthoganal
-- resiuduals on the dependent variable y
while @p>=0 
begin

    insert into #b
    select zn, sum(yv*zv)/ sum(zv*zv) 
        from #z  join 
            (select yid, yv-isnull(sum(bv*xv),0) yv from #x join #y on xid = yid left join #b on  xn=bn group by yid, yv) y
        on zid = yid where zn = @p  group by zn

    set @p = @p-1
end

--The regression parameters
select * from #b

--Actual vs. fit with error
select yid, yv, fit, yv-fit err from #y join 
    (select xid, sum(xv*bv) fit from #x join #b on xn = bn  group by xid) f
     on yid = xid

--R Squared
select 1-sum(power(err,2))/sum(power(yv,2)) from 
(select yid, yv, fit, yv-fit err from #y join 
    (select xid, sum(xv*bv) fit from #x join #b on xn = bn  group by xid) f
     on yid = xid) d
2
répondu colin campbell 2014-02-06 20:19:35

il n'y a pas de fonctions de régression linéaire dans SQL Server. Mais pour calculer une régression linéaire Simple (Y' = bX + A) entre les paires de points de données x, y-y compris le calcul du Coefficient de corrélation, du Coefficient de détermination (R^2) et de L'estimation Standard de L'erreur( écart-type), faites ce qui suit:

pour une table regression_data avec des colonnes numériques x et y:

declare @total_points int 
declare @intercept DECIMAL(38, 10)
declare @slope DECIMAL(38, 10)
declare @r_squared DECIMAL(38, 10)
declare @standard_estimate_error DECIMAL(38, 10)
declare @correlation_coefficient DECIMAL(38, 10)
declare @average_x  DECIMAL(38, 10)
declare @average_y  DECIMAL(38, 10)
declare @sumX DECIMAL(38, 10)
declare @sumY DECIMAL(38, 10)
declare @sumXX DECIMAL(38, 10)
declare @sumYY DECIMAL(38, 10)
declare @sumXY DECIMAL(38, 10)
declare @Sxx DECIMAL(38, 10)
declare @Syy DECIMAL(38, 10)
declare @Sxy DECIMAL(38, 10)

Select 
@total_points = count(*),
@average_x = avg(x),
@average_y = avg(y),
@sumX = sum(x),
@sumY = sum(y),
@sumXX = sum(x*x),
@sumYY = sum(y*y),
@sumXY = sum(x*y)
from regression_data

set @Sxx = @sumXX - (@sumX * @sumX) / @total_points
set @Syy = @sumYY - (@sumY * @sumY) / @total_points
set @Sxy = @sumXY - (@sumX * @sumY) / @total_points

set @correlation_coefficient = @Sxy / SQRT(@Sxx * @Syy) 
set @slope = (@total_points * @sumXY - @sumX * @sumY) / (@total_points * @sumXX - power(@sumX,2))
set @intercept = @average_y - (@total_points * @sumXY - @sumX * @sumY) / (@total_points * @sumXX - power(@sumX,2)) * @average_x
set @r_squared = (@intercept * @sumY + @slope * @sumXY - power(@sumY,2) / @total_points) / (@sumYY - power(@sumY,2) / @total_points)

-- calculate standard_estimate_error (standard deviation)
Select
@standard_estimate_error = sqrt(sum(power(y - (@slope * x + @intercept),2)) / @total_points)
From regression_data
2
répondu SyntaxGoonoo 2014-04-02 01:35:48

j'ai traduit la fonction de régression linéaire utilisée dans la prévision de funcion dans Excel, et j'ai créé une fonction SQL qui renvoie a,b, et la prévision. Vous pouvez voir l'explication teorique complète dans l'aide excel pour la prévision fuction. Des sapins de tout ce dont vous aurez besoin pour créer la table type de données XYFloatType:

 CREATE TYPE [dbo].[XYFloatType] 
AS TABLE(
[X] FLOAT,
[Y] FLOAT)

alors écrivez la fonction suivante:

    /*
-- =============================================
-- Author:      Me      :)
-- Create date: Today   :)
-- Description: (Copied Excel help): 
--Calculates, or predicts, a future value by using existing values. 
The predicted value is a y-value for a given x-value. 
The known values are existing x-values and y-values, and the new value is predicted by using linear regression. 
You can use this function to predict future sales, inventory requirements, or consumer trends.
-- =============================================
*/

CREATE FUNCTION dbo.FN_GetLinearRegressionForcast

(@PtXYData as XYFloatType READONLY ,@PnFuturePointint)
RETURNS @ABDData TABLE( a FLOAT, b FLOAT, Forecast FLOAT)
AS

BEGIN 
    DECLARE  @LnAvX Float
            ,@LnAvY Float
            ,@LnB Float
            ,@LnA Float
            ,@LnForeCast Float
    Select   @LnAvX = AVG([X])
            ,@LnAvY = AVG([Y])
    FROM @PtXYData;
    SELECT @LnB =  SUM ( ([X]-@LnAvX)*([Y]-@LnAvY) )  /  SUM (POWER([X]-@LnAvX,2))
    FROM @PtXYData;
    SET @LnA = @LnAvY - @LnB * @LnAvX;
    SET @LnForeCast = @LnA + @LnB * @PnFuturePoint;
    INSERT INTO @ABDData ([A],[B],[Forecast]) VALUES (@LnA,@LnB,@LnForeCast)
    RETURN 
END

/*
your tests: 

 (I used the same values that are in the excel help)
DECLARE @t XYFloatType 
INSERT @t VALUES(20,6),(28,7),(31,9),(38,15),(40,21)        -- x and y values
SELECT *, A+B*30 [Prueba]FROM dbo.FN_GetLinearRegressionForcast@t,30);
*/
0
répondu Alberto Conde Beltrán 2015-01-15 17:52:18

ici, C'est comme une fonction qui prend un type de table de type: table (y float, x double) qui est appelé Xydouletype et suppose que notre fonction linéaire est de la forme AX + B. Il retourne A et B une colonne de Table juste au cas où vous voulez l'avoir dans une jointure ou quelque chose

CREATE FUNCTION FN_GetABForData(
 @XYData as XYDoubleType READONLY
 ) RETURNS  @ABData TABLE(
            A  FLOAT,
            B FLOAT, 
            Rsquare FLOAT )
 AS
 BEGIN
    DECLARE @sx FLOAT, @sy FLOAT
    DECLARE @sxx FLOAT,@syy FLOAT, @sxy FLOAT,@sxsy FLOAT, @sxsx FLOAT, @sysy FLOAT
    DECLARE @n FLOAT, @A FLOAT, @B FLOAT, @Rsq FLOAT

    SELECT @sx =SUM(D.X) ,@sy =SUM(D.Y), @sxx=SUM(D.X*D.X),@syy=SUM(D.Y*D.Y),
        @sxy =SUM(D.X*D.Y),@n =COUNT(*)
    From @XYData D
    SET @sxsx =@sx*@sx
    SET @sxsy =@sx*@sy
    SET @sysy = @sy*@sy

    SET @A = (@n*@sxy -@sxsy)/(@n*@sxx -@sxsx)
    SET @B = @sy/@n  - @A*@sx/@n
    SET @Rsq = POWER((@n*@sxy -@sxsy),2)/((@n*@sxx-@sxsx)*(@n*@syy -@sysy))

    INSERT INTO @ABData (A,B,Rsquare) VALUES(@A,@B,@Rsq)

    RETURN 
 END
0
répondu Painless Coding 2015-02-03 19:11:27

pour ajouter à la réponse @icc97, j'ai inclus le versions pondérées pour la pente et l'ordonnée à l'origine. Si les valeurs sont toutes constantes, la pente sera nulle (avec les réglages appropriés SET ARITHABORT OFF; SET ANSI_WARNINGS OFF;), et devra être substitué par 0 via fusionnent().

Voici une solution écrite en SQL:

with d as (select segment,w,x,y from somedatasource)
select segment,

avg(y) - avg(x) *
((count(*) * sum(x*y)) - (sum(x)*sum(y)))/
((count(*) * sum(x*x)) - (Sum(x)*Sum(x)))   as intercept,

((count(*) * sum(x*y)) - (sum(x)*sum(y)))/
((count(*) * sum(x*x)) - (sum(x)*sum(x))) AS slope,

avg(y) - ((avg(x*y) - avg(x)*avg(y))/var_samp(X)) * avg(x) as interceptUnstable,
(avg(x*y) - avg(x)*avg(y))/var_samp(X) as slopeUnstable,
(Avg(x * y) - Avg(x) * Avg(y)) / (stddev_pop(x) * stddev_pop(y)) as correlationUnstable,

(sum(y*w)/sum(w)) - (sum(w*x)/sum(w)) *
((sum(w)*sum(x*y*w)) - (sum(x*w)*sum(y*w)))/
  ((sum(w)*sum(x*x*w)) - (sum(x*w)*sum(x*w)))   as wIntercept,

((sum(w)*sum(x*y*w)) - (sum(x*w)*sum(y*w)))/
  ((sum(w)*sum(x*x*w)) - (sum(x*w)*sum(x*w))) as wSlope,

(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,

count(*) as n

from d where x is not null and y is not null group by segment

Où w est le poids. J'ai vérifié avec R pour confirmer les résultats. Il peut être nécessaire de lancer les données de somedatasource à floating point. Je inclus les versions instables pour vous mettre en garde contre celles-ci. (Un merci spécial à Stephan dans une autre réponse.)

Gardez à l'esprit que la corrélation est la corrélation des points de données x et y et non pas de la prédiction.

0
répondu Chris 2017-04-14 16:55:44

j'espère que la réponse suivante permet de comprendre où les solutions viennent de. Je vais l'illustrer par un exemple simple, mais la généralisation à plusieurs variables est théoriquement simple, aussi longtemps que vous savez comment utiliser l'indice de notation ou des matrices. Pour mettre en œuvre la solution pour tout au-delà de 3 variables, vous Gram-Schmidt (voir la réponse de Colin Campbell ci-dessus) ou un autre algorithme d'inversion de matrice.

Depuis toutes les fonctions dont nous avons besoin sont la variance, covariance, la moyenne, la somme etc. sont des fonctions d'agrégation en SQL, on peut facilement mettre en œuvre la solution. Je l'ai fait à HIVE pour faire un calibrage linéaire des scores D'un modèle logistique - parmi de nombreux avantages, l'un est que vous pouvez fonctionner entièrement à L'intérieur de HIVE sans sortir et revenir à partir d'un langage de script.

le modèle pour vos données (x_1, x_2, y) où vos points de données sont indexés par i, est

y (x_1, x_2) = m_1*x_1 + m_2*x_2 + c

le modèle apparaît "linéaire", mais ne doit pas l'être, par exemple x_2 peut être n'importe quelle fonction non linéaire de x_1, tant qu'il n'a pas de paramètres libres en elle, par exemple x_2 = Sinh(3*(x_1)^2 + 42). Même si x_2 est "juste" x_2, et le modèle est linéaire, la régression problème n'est pas le cas. Seulement lorsque vous décidez que le problème est de trouver les paramètres m_1, m_2, c tels qu'ils minimisent l'erreur L2 avez-vous un problème de Régression Linéaire.

l'erreur L2 est sum_i ((y[i] - f (x_1[i], x_2[i]))^2). Minimisation de ce W. R. T. les 3 paramètres (réglez les dérivées partielles W. R. T. chaque paramètre = 0) donne 3 équations linéaires pour 3 inconnues. Ces équations sont linéaires dans les paramètres (C'est ce qui en fait une régression linéaire) et peuvent être résolues analytiquement. Faire cela pour un modèle simple (1 variable, modèle linéaire, donc deux paramètres) est simple et instructif. La généralisation à une norme métrique non euclidienne sur l'espace du vecteur d'erreur est simple, la diagonale le cas particulier revient à utiliser des"poids".

retour à notre modèle en deux variables:

y = m_1*x_1 + m_2*x_2 + c

Prendre l'attente de la valeur =>

= m_1* + m_2* + c (0)

Maintenant prendre la covariance w.r.t. x_1 et x_2, et l'utilisation cov(x,x) = var(x):

cov(y, x_1) = m_1*var(x_1) + m_2*covar(x_2, x_1) (1)

cov(y, x_2) = m_1*covar (x_1, x_2) + m_2 * var (x_2) (2)

ce sont deux équations dans deux inconnues, que vous pouvez résoudre en inversant la matrice 2X2.

sous forme De matrice: ... qui peut être inversé de rendement ... où

det = var (x_1) * var (x_2) - covar(x_1, x_2)^2

(oh barf, ce que le diable sont des "points de réputation? Donne-m'en si tu veux voir les équations.)

dans tous les cas, maintenant que vous avez m1 et m2 en forme fermée, vous pouvez résoudre (0) pour C.

j'ai vérifié la solution analytique ci-dessus au solveur D'Excel pour un quadratique avec le bruit gaussien et les erreurs résiduelles s'accordent à 6 chiffres significatifs.

contactez-moi si vous voulez faire discrète transformée de Fourier en SQL en environ 20 lignes.

-1
répondu climbert8 2015-09-19 08:19:48