Y a-Linéaire de la Fonction de Régression dans SQL Server?

Y a-Linéaire de la Fonction de Régression dans SQL Server 2005/2008, semblable à la La Régression linéaire des fonctions d'Oracle ?

InformationsquelleAutor rao | 2010-03-29
  1. 40

    Au meilleur de ma connaissance, il n'en est rien. L'écriture est assez simple, cependant. Le suivant vous donne la constante de l'alpha et de la pente de la bêta pour y = Alpha + Beta * 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 le groupe en a de la valeur à votre source de données de la table. Si vous voulez juste les statistiques de toutes les données dans la table (pas de sous-groupes spécifiques), vous pouvez les supprimer et les jointures. J'ai utilisé le WITH déclaration pour des raisons de clarté. Comme alternative, vous pouvez utiliser des sous-requêtes à la place. Veuillez être conscient de la précision du type de données utilisées dans vos tables comme la stabilité numérique peut se détériorer rapidement si la précision n'est pas assez élevé 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 à l'aide de la même technique. Voici une version avec R2, corrélation, et l'échantillon de 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 par la standardisation des données (au lieu de seulement centrage) et par le remplacement de STDEV en raison de numérique des problèmes de stabilité. Pour moi, l'implémentation actuelle semble être le meilleur compromis entre la stabilité et de la complexité. J'ai pu améliorer la stabilité en remplacement de mon écart-type avec un numériquement stable algorithme en ligne, mais cela ne ferait que compliquer la mise en œuvre substantantially (et de le ralentir). De même, les mises en œuvre à l'aide par exemple Kahan(-Babuška-Neumaier) compensations pour le 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 pourrait déjà être à l'aide de paires de sommation), je ne peux pas garantir que de telles modifications de toujours améliorer la précision.

    • Merci!! a utiliser pour résoudre mon problème. Problème, dans une perspective plus large, était d'obtenir une ligne de tendance en SSR (2005) rapport. C'était le seul moyen.
    • vous êtes les bienvenus. Ajouté estimation constante de l'alpha à la requête
    • Je me rends compte que le thread est de 2 ans, mais est-il possible pour vous d'obtenir le r-carré de la valeur avec cette méthode?
    • sûr, facile. Voir modifié réponse.
    • Est-il possible d'étendre votre solution de régression multiple?
    • en théorie, oui, pas dans la réalité. Longue histoire courte, vous devez inverser une matrice qui devient laid rapidement. Il y a au moins trois possibilités: 1) mettre en œuvre la régression linéaire en fonction de la minimisation ou moins directe de la méthode en ne s'appuyant pas sur une totale inversion de matrice qui est facile à mettre en œuvre (par exemple, la réponse stackoverflow.com/a/20978697/92092 par Colin); 2) le SQL Server Analysis Services (msdn.microsoft.com/en-US/library/ms174824.aspx); 3) la troisième partie du package, peut-être en dehors de SQL Server (avec beaucoup de données, vous voudrez peut-être "en ligne de régression linéaire").
    • sélectionnez (avg(xy) - avg(x)*avg(y))/VARIANCE(X) comme la pente , avg(y) - ((avg(xy) - avg(x)*avg(y))/VARIANCE(X)) * avg(x) comme l'interception de #rawdata --FARSHORTER
    • vous ne savez pas où commencer, donc je commence avec le coupant les cheveux en quatre ;). De toute évidence vous dire avg(x*y) et VARP(X) dans votre formule. Un autre petit point, c'est que avg(expression) vous donne un entier si vos données d'entrée est de type integer. Mais maintenant, la vraie question: votre code n'est pas numériquement stable, voir les commentaires dans le code et commencer à "Edit 2". Aussi n'hésitez pas à regarder l'historique de la révision de la réponse et vous remarquerez que la première version est assez proche de la vôtre. Longue histoire courte: je n'aurais jamais utiliser votre version parce que je n'aurais pas confiance en elle dans de nombreux numériquement comporte bien des situations.
    • Merci pour l'instabilité de l'alerte. J'ai remarqué que j'ai utilisé ce dans un projet avant et il calcule la même chose que ce que vous avez, mais il est plus concis: select avg(y) - avg(x) * ((count() * sum(x * y)) - (sum(x) * sum(y)))/ ((count() * sum(x * x)) - (Sum(x) * Sum(x))) comme le point d'intersection ((count() * sum(x * y)) - (sum(x) * sum(y)))/ ((count() * sum(x * x)) - (sum(x) * sum(x))) la pente de tablexy --Le FARSHORTER 2 commentaires ci-dessus ne souffrent de l'instabilité, comme je l'ai testé et il produit des résultats différents. J'ai validé votre solution et la concision de solution dans R et il est des matchs lm()
    • d'accord, c'est beaucoup mieux. Les deux seules différences du code ci-dessus par rapport à votre sont 1) - je forcer le type (étrange AVG(x * 1.) hack) - je crois que votre version donne un mauvais résultat si x et y sont des entiers; 2) la version dans mes réponses normalise les données qui pourraient aider certains idiosyncrazies / edge-cas de virgule flottante de l'arithmétique. Mais en tout normal de cas d'utilisation de votre version semble bien pour moi.
    • En ce qui concerne la régression multiple question je suis d'accord en partie. Vous n'avez pas besoin d'aller à la multiplication de matrice de la route cependant. Vous pouvez réellement estimer les paramètres de la régression multiple en utilisant de multiples simples régressions linéaires. stats.stackexchange.com/a/166718/4737 Il serait très fastidieux, mais c'est possible...
    • Il ne fait pas mal à ajouter une marge de tolérance dans votre comparaison, lors de la vérification si l'écart-type de x est différent de 0. Je suis essentiellement à l'aide de votre code, mais a certains cas où les données semblent constante pour x, mais en raison de certaines numérique des problèmes qu'il n'était pas exactement sur le même (même si elle doit l'être) et qui a fait sauter la pente estimation à la hausse depuis le x écart-type était juste un poil au-dessus de 0.
    • c'est un très bon point. Ne m'est jamais arrivé, mais peut très bien imaginer qu'il peut arriver. Permettez-moi de réfléchir à la meilleure façon de le faire. Toutes les suggestions?
    • De le faire en général pourrait être dur. Dans mon cas particulier, je savais que le plus petit l'écart entre deux valeurs de x peut être alors j'ai testé, si la série est plus petite. Je pense qu'on pourrait faire un test en comparant la gamme de valeurs de x à un certain multiple de la machine la plus petite virgule flottante ou quelque chose le long de ces lignes.
    • Dans l'AVG(x * 1.) ligne de code, ce qui ne l'1. signifie? Est 1. la même que la 1.0?
    • est en effet le même que 1.0. C'est un hack pour forcer un type de données non entier, même si le type de données de x est entier (en T-SQL, AVG pour les entiers retourne un entier qui n'est pas ce que nous voulons ici). Il aurait probablement été plus propre d'écrire CAST(x as FLOAT), mais c'est plus court, et autant que je me souvienne, il fonctionne mieux avec NUMERIC (pas de garantie). C'est RDB-dépendant, j'ai uniquement testé avec MS SQL Server.
    • Bon à savoir, merci. Je vais continuer à l'utiliser 1.0 1. mais parce que je pense que ça rend le code plus lisible. Convenu que peut-être le casting est mieux.

    InformationsquelleAutor stephan
  2. 15

    Ceci est une autre méthode, basée sur un blog sur la Régression Linéaire en T-SQL, qui utilise les équations suivantes:

    Y a-Linéaire de la Fonction de Régression dans SQL Server?

    SQL suggestion dans le blog utilise des curseurs bien. Voici une version embellie de forum répondre 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
    • Cela prouve la réponse avec le deuxième plus grand nombre de votes est le meilleur.
    InformationsquelleAutor icc97
  3. 3

    En fait, j'ai écrit une routine SQL à l'aide de Gram-Schmidt orthoganalization. Elle, comme d'autres l'apprentissage de la machine et de la prévision de routines, est disponible à sqldatamine.blogspot.com

    À la suggestion de Brad Larson, j'ai ajouté le code ici, plutôt que de simplement diriger les utilisateurs vers mon blog. Ce produit les mêmes résultats que la fonction droitereg 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
    • Plutôt que de simplement poster un lien vers votre blog (ce qui pourrait aller à un certain moment dans l'avenir), pourriez-vous résumer les informations pertinentes à partir de votre blog dans votre réponse ici?
    • J'ai un dataset et lorsque j'utilise votre code, tout ce qui ressemble à ce que j'attendais à l'exception de R au Carré. Êtes-vous sûr que le calcul est bien dans R2. Je suis en comparant le résultat avec excel régression et ils sont différents.
    • Aussi pouvez-vous développer votre solution pour inclure les p-valeurs pour chaque variable(X)?
    InformationsquelleAutor colin campbell
  4. 2

    Il n'y a pas de régression linéaire des fonctions dans SQL Server. Mais pour calculer une Simple Régression Linéaire (Y = bX + A) entre des paires de points de données x,y - y compris le calcul du Coefficient de Corrélation, le Coefficient de Détermination (R^2) et Standard de l'Estimation d'Erreur (Écart-type), procédez de la manière suivante:

    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
    • Pouvez-vous développer votre solution pour inclure la valeur de p ainsi? Aussi comment peut-on faire plusieurs liner de régression basé sur vos réponses?
    • Le R-carré est trop grand parce que la somme totale des carrés utilise cru Y valeurs plutôt que les écarts à la moyenne. Dans la suite, yv devrait être remplacé par yv-@meanY sélectionnez 1-sum(puissance(err,2))/somme(puissance(yv,2)) à partir de
    InformationsquelleAutor SyntaxGoonoo
  5. 1

    À ajouter à @icc97 réponse, j'ai inclus la pondérée des versions pour la pente et l'ordonnée à l'origine. Si les valeurs sont toutes constante de la pente va être NUL (avec les paramètres 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é cela sur R pour confirmer les résultats.
    On peut avoir besoin de jeter les données de somedatasource à virgule flottante.
    J'ai inclus les versions instables de vous mettre en garde contre ceux-ci. (Un merci spécial va à Stephan dans une autre réponse.)

    Garder à 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.

    • +1 La version pondérée est utile, mais l'excès de crochets de le rendre plus difficile à lire. Il est également beaucoup plus propre pour définir le point d'intersection à l'aide de la pente.
    InformationsquelleAutor Chris
  6. 0

    J'ai traduit la Régression Linéaire de la Fonction utilisée pour la fonction de Prévision dans Excel, et créé une fonction SQL qui retourne a,b, et les Prévisions.
    Vous pouvez voir l'intégralité de l'teorical explication dans l'aide d'excel pour les PRÉVISIONS 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)

    Puis écrire la suite de la fonction:

        /*
-- =============================================
-- 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);
*/
    InformationsquelleAutor Alberto Conde Beltrán
  7. 0

    Ici, c'est aussi une fonction qui prend un tableau de type de type: de table (Y, float, double X) qui est
    appelé XYDoubleType et n'assume notre fonction linéaire est de la forme AX + B. Il retourne A et B d'une colonne de Table, juste au cas où vous souhaitez 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
    InformationsquelleAutor Painless Coding
  8. 0

    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 la mise en œuvre de la solution pour quelque chose au-delà de 3 variables que vous aurez de Gram-Schmidt (Voir Colin Campbell réponse ci-dessus) ou une autre matrice algorithme d'inversion.

    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 dans la RUCHE pour faire d'étalonnage linéaire des scores d'un modèle Logistique - parmi les nombreux avantages, l'un est que vous pouvez fonctionner entièrement à l'intérieur de la RUCHE sans aller et retour de certains langage de script.

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

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

    Le modèle "linéaire", mais ne doit pas ê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 dans cela, 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 ). Afin de minimiser cette w.r.t. les 3 paramètres (définir 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 fait de la Régression Linéaire) et peut être résolu analytiquement. Faire cela pour un modèle simple (1 variable, modèle linéaire, il y a donc deux paramètres) est simple et instructif. La généralisation à un non-Euclidienne métrique norme du vecteur d'erreur de l'espace est simple, la diagonale cas particulier des montants à l'aide de "poids".

    Retour à notre modèle à 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)

    Ces deux équations à deux inconnues, que vous pouvez résoudre par inversion de la matrice 2X2.

    Sous forme matricielle:
    ...
    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-moi un peu si vous voulez voir les équations.)

    En tout cas, maintenant que vous avez m1 et m2 dans la forme fermée, vous pouvez résoudre (0) pour c.

    J'ai vérifié la solution analytique ci-dessus pour le Solveur d'Excel pour une équation du second degré avec le bruit Gaussien et les erreurs résiduelles engagez à 6 chiffres significatifs.

    Me contacter si vous voulez faire la transformée de Fourier Discrète dans SQL dans environ 20 lignes.

    InformationsquelleAutor climbert8