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

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:

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

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

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

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);
*/

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

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.

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.