Оптимальные два переменных вычисления линейной регрессии
Проблема
Надеюсь применяться y = mx + b уравнение (где m SLOPE, b INTERCEPT) к набору данных, который получен как показано в коде SQL. Значения от (MySQL) запрос:
SLOPE = 0.0276653965651912
INTERCEPT = -57.2338357550468
Код SQL
SELECT
((sum(t.YEAR) * sum(t.AMOUNT)) - (count(1) * sum(t.YEAR * t.AMOUNT))) /
(power(sum(t.YEAR), 2) - count(1) * sum(power(t.YEAR, 2))) as SLOPE,
((sum( t.YEAR ) * sum( t.YEAR * t.AMOUNT )) -
(sum( t.AMOUNT ) * sum(power(t.YEAR, 2)))) /
(power(sum(t.YEAR), 2) - count(1) * sum(power(t.YEAR, 2))) as INTERCEPT,
FROM
(SELECT
D.AMOUNT,
Y.YEAR
FROM
CITY C, STATION S, YEAR_REF Y, MONTH_REF M, DAILY D
WHERE
-- For a specific city ...
--
C.ID = 8590 AND
-- Find all the stations within a 15 unit radius ...
--
SQRT( POW( C.LATITUDE - S.LATITUDE, 2 ) + POW( C.LONGITUDE - S.LONGITUDE, 2 ) ) < 15 AND
-- Gather all known years for that station ...
--
S.STATION_DISTRICT_ID = Y.STATION_DISTRICT_ID AND
-- The data before 1900 is shaky; insufficient after 2009.
--
Y.YEAR BETWEEN 1900 AND 2009 AND
-- Filtered by all known months ...
--
M.YEAR_REF_ID = Y.ID AND
-- Whittled down by category ...
--
M.CATEGORY_ID = '001' AND
-- Into the valid daily climate data.
--
M.ID = D.MONTH_REF_ID AND
D.DAILY_FLAG_ID <> 'M'
GROUP BY Y.YEAR
ORDER BY Y.YEAR
) t
Вопрос
Следующие результаты (для вычисления запуска и конечных точек строки) кажутся неправильными. Почему результаты прочь на ~10 градусов (например, выбросы, скашивающие данные)?
(1900 * 0.0276653965651912) + (-57.2338357550468) =-4.66958228
(2009 * 0.0276653965651912) + (-57.2338357550468) =-1.65405406
(Обратите внимание, что данные больше не соответствуют изображению; код.)
Я ожидал бы, что результат 1900 года будет приблизительно 10 (не-4.67) и результат 2009 года, чтобы быть приблизительно 11,50 (не-1.65).
Связанные сайты
2 ответа
Это было подтверждено как правильное:
SELECT
((sum(t.YEAR) * sum(t.AMOUNT)) - (count(1) * sum(t.YEAR * t.AMOUNT))) /
(power(sum(t.YEAR), 2) - count(1) * sum(power(t.YEAR, 2))) as SLOPE,
((sum( t.YEAR ) * sum( t.YEAR * t.AMOUNT )) -
(sum( t.AMOUNT ) * sum(power(t.YEAR, 2)))) /
(power(sum(t.YEAR), 2) - count(1) * sum(power(t.YEAR, 2))) as INTERCEPT,
((avg(t.AMOUNT * t.YEAR)) - avg(t.AMOUNT) * avg(t.YEAR)) /
(stddev( t.AMOUNT ) * stddev( t.YEAR )) as CORRELATION
FROM (
SELECT
AVG(D.AMOUNT) as AMOUNT,
Y.YEAR as YEAR
FROM
CITY C,
STATION S,
YEAR_REF Y,
MONTH_REF M,
DAILY D
WHERE
C.ID = 8590 AND
SQRT(
POW( C.LATITUDE - S.LATITUDE, 2 ) +
POW( C.LONGITUDE - S.LONGITUDE, 2 ) ) < 15 AND
S.STATION_DISTRICT_ID = Y.STATION_DISTRICT_ID AND
Y.YEAR BETWEEN 1900 AND 2009 AND
M.YEAR_REF_ID = Y.ID AND
M.CATEGORY_ID = '001' AND
M.ID = D.MONTH_REF_ID AND
D.DAILY_FLAG_ID <> 'M'
GROUP BY
Y.YEAR
) t
См. Изображение для получения подробной информации о наклоне, пересечении и (Пирсоновской) корреляции.
Попробуйте разделить функцию, вы неправильно рассчитали параметры. См. здесь для справки.
Я бы сделал что-то вроде следующего (прошу прощения за то, что я мало что помню о синтаксисе SQL и временных переменных, поэтому код на самом деле может быть неправильным):
SELECT
sum(t.YEAR) / count(1) AS avgX,
sum(t.AMOUNT) / count(1) AS avgY,
sum(t.AMOUNT*t.YEAR) / count(1) AS avgXY,
sum(power(t.YEAR, 2)) / count(1) AS avgXsq,
( avgXY - avgX * avgY ) / ( avgXsq - power(avgX, 2) ) as SLOPE,
avgY - SLOPE * avgX as INTERCEPT,