Есть ли функция линейной регрессии в SQL Server 2005/2008, аналогичная функции Линейная регрессия в Oracle?
Есть ли функция линейной регрессии в SQL Server?
Ответ 1
Насколько мне известно, их нет. Написание одного довольно просто. Следующее дает вам постоянную бета-версию alpha и slope для 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
Здесь GroupID используется, чтобы показать, как группировать по некоторому значению в вашей таблице исходных данных. Если вам просто нужна статистика по всем данным в таблице (не определенные подгруппы), вы можете удалить ее и присоединиться. Для ясности я использовал инструкцию WITH. В качестве альтернативы вы можете использовать подзапросы. Помните о точности типа данных, используемых в ваших таблицах, поскольку численная стабильность может быстро ухудшиться, если точность недостаточно высока относительно ваших данных.
РЕДАКТИРОВАТЬ: (в ответ на вопрос Питера о дополнительных статистиках, таких как R2 в комментариях)
Вы можете легко рассчитать дополнительную статистику, используя ту же технику. Вот версия с R2, корреляцией и выборкой ковариации:
-- 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 улучшает численную стабильность путем стандартизации данных (а не только центрирования) и путем замены STDEV из-за проблем с числовой стабильностью. Для меня нынешняя реализация, по-видимому, является лучшим компромиссом между стабильностью и сложностью. Я мог бы улучшить стабильность, заменив мое стандартное отклонение на числовой стабильный онлайн-алгоритм, но это существенно усложнит реализацию (и замедлит ее). Аналогичным образом реализации, использующие, например, Компенсации Kahan (-Babuška-Neumaier) для SUM и AVG кажутся более скромными в ограниченных тестах, но делают запрос более сложным. И пока я не знаю, как T-SQL реализует SUM и AVG (например, он уже может использовать парное суммирование), я не могу гарантировать, что такие модификации всегда повышают точность.
Ответ 2
Это альтернативный метод, основанный на сообщении блога о линейной регрессии в T-SQL, в котором используются следующие уравнения:
Однако предложение SQL в блоге использует курсоры. Здесь приведенная версия ответ форума, который я использовал:
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
Ответ 3
Я на самом деле написал SQL-процедуру с использованием ортогонализации Gram-Schmidt. Это, а также другие процедуры машинного обучения и прогнозирования доступны по адресу sqldatamine.blogspot.com
По предложению Брэда Ларсона я добавил код здесь, а не только прямых пользователей в мой блог. Это дает те же результаты, что и функция linest в Excel. Мой основной источник - Элементы статистического обучения (2008) Хасти, Тиббирни и Фридмана.
--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)
[email protected] 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
Ответ 4
В SQL Server нет функций линейной регрессии. Но для вычисления простой линейной регрессии (Y '= bX + A) между парами точек данных x, y - включая расчет коэффициента корреляции, коэффициента определения (R ^ 2) и стандартной оценки ошибки (стандартное отклонение), выполните следующие действия:
Для таблицы regression_data с числовыми столбцами x и 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
Ответ 5
Здесь это как функция, которая принимает тип таблицы типа: table (Y float, X double), которая является называется XYDoubleType и предполагает, что наша линейная функция имеет вид AX + B. Он возвращает A и B столбец таблицы на всякий случай, если вы хотите иметь его в соединении или что-то
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 [email protected]*@sx
SET @sxsy [email protected]*@sy
SET @sysy = @sy*@sy
SET @A = (@n*@sxy [email protected])/(@n*@sxx [email protected])
SET @B = @sy/@n - @A*@sx/@n
SET @Rsq = POWER((@n*@sxy [email protected]),2)/((@n*@[email protected])*(@n*@syy [email protected]))
INSERT INTO @ABData (A,B,Rsquare) VALUES(@A,@B,@Rsq)
RETURN
END
Ответ 6
Я перевел функцию линейной регрессии, используемую в прогнозе funcion в Excel, и создал функцию SQL, которая возвращает a, b и прогноз. Вы можете увидеть полное теоретическое объяснение в помощи excel для FORECAST fuction. Прежде всего вам нужно создать тип данных таблицы XYFloatType:
CREATE TYPE [dbo].[XYFloatType]
AS TABLE(
[X] FLOAT,
[Y] FLOAT)
Затем напишите следующую функцию:
/*
-- =============================================
-- 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][email protected])*([Y][email protected]) ) / SUM (POWER([X][email protected],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 [email protected],30);
*/
Ответ 7
Чтобы добавить к ответу @icc97, я включил взвешенные версии для наклона и перехвата. Если значения все постоянны, наклон будет NULL (с соответствующими настройками SET ARITHABORT OFF; SET ANSI_WARNINGS OFF;) и должен быть заменен на 0 через coalesce().
Вот решение, написанное на 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
Где w - вес. Я дважды проверил это против R, чтобы подтвердить результаты. Может потребоваться передать данные из somedatasource в плавающую точку. Я включил нестабильные версии, чтобы предупредить вас об этом. (Особая благодарность Стефану в другом ответе.)
Имейте в виду, что корреляция - это соотношение точек данных x и y, а не прогноза.
Ответ 8
Надеюсь, следующий ответ поможет понять, откуда взялись решения. Я проиллюстрирую это на простом примере, но обобщение на многие переменные теоретически просто, если вы знаете, как использовать индексные обозначения или матрицы. Для реализации решения для чего-либо, кроме трех переменных, вы получите Gram-Schmidt (см. Ответ Колина Кэмпбелла выше) или другой алгоритм инверсии матрицы.
Поскольку все функции, которые нам нужны, - это дисперсия, ковариация, среднее значение, сумма и т.д. - это функции агрегации в SQL, легко реализовать решение. Я сделал это в HIVE, чтобы сделать линейную калибровку результатов логистической модели - среди многих преимуществ, один из них заключается в том, что вы можете полностью функционировать в рамках HIVE, не выходя из своего языка, и не возвращаетесь с него.
Модель для ваших данных (x_1, x_2, y), где ваши точки данных индексируются i, составляет
y (x_1, x_2) = m_1 * x_1 + m_2 * x_2 + c
Модель выглядит "линейной", но не обязательно. Например, x_2 может быть любой нелинейной функцией x_1, если в ней нет свободных параметров, например. x_2 = Sinh (3 * (x_1) ^ 2 + 42). Даже если x_2 "просто" x_2, а модель линейна, проблема регрессии - нет. Только когда вы решаете, что проблема заключается в том, чтобы найти параметры m_1, m_2, c, чтобы они минимизировали ошибку L2, у вас есть проблема с линейной регрессией.
Ошибка L2 равна sum_i ((y [i] - f (x_1 [i], x_2 [i])) ^ 2). Минимизация этого w.r.t. 3 параметра (задают частные производные w.r.t. каждый параметр = 0) дает 3 линейных уравнения для 3 неизвестных. Эти уравнения являются LINEAR в параметрах (это то, что делает его линейной регрессией) и могут быть решены аналитически. Выполнение этого для простой модели (1 переменная, линейная модель, следовательно, два параметра) является простым и поучительным. Обобщение на неевклидову метрическую норму на пространстве векторов ошибок простое, диагональный особый случай сводится к использованию "весов".
Вернемся к нашей модели по двум переменным:
y = m_1 * x_1 + m_2 * x_2 + c
Возьмите значение ожидания = >
= m_1 * + m_2 * + c (0)
Теперь возьмите ковариацию w.r.t. x_1 и x_2, и используйте 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)
Это два уравнения в двух неизвестных, которые вы можете решить, инвертируя матрицу 2X2.
В матричной форме: ... которая может быть инвертирована для получения ... где
det = var (x_1) * var (x_2) - covar (x_1, x_2) ^ 2
(oh barf, что это за "точки репутации"? Дайте некоторые, если вы хотите увидеть уравнения.)
В любом случае, теперь, когда у вас есть m1 и m2 в закрытой форме, вы можете решить (0) для c.
Я проверил аналитическое решение выше на Excel Solver для квадратичного с гауссовским шумом, а остаточные ошибки согласуются с 6 значащими цифрами.
Свяжитесь со мной, если вы хотите сделать дискретное преобразование Фурье в SQL примерно в 20 строках.