Scriddie

Scriddie

Scriddie

Scriddie

# Calculate X * X

create table XtX (row_num int, col_num int, value int);
insert into XtX
select f1.col_num row_num
     , f2.col_num col_num
     , sum(f1.feature_value * f2.feature_value) value
from features f1
join features f2
on f1.row_num = f2.row_num

Scriddie

# Calculate determinant of X * X

select @determinant := sum(product * product_sign) 
from (
	select (xtx_1.value * xtx_2.value * xtx_3.value) 
	        product
# we need to weight the row_numbers according to the inverse of their natural order
# to estimate degree of permutation
	      , power(-1, dense_rank() over(
         	order by 3 * xtx_1.row_num + 2 * xtx_2.row_num + 1 * xtx_3.row_num) - 1)

Scriddie

# @author https://github.com/agemcipe
# Calculate inverse of X * X

create table XtX_inverse (row_num int, col_num int, value float);

insert into XtX_inverse
select mat_pos.col_num as row_num # transponse
     , mat_pos.row_num as col_num 
     , (1 / @determinant) * power(-1, mat_pos.row_num + mat_pos.col_num) * 
     (mat_1.value * mat_4.value - mat_2.value

Scriddie

# Find coefficients as XtX_inverse * XtY

select round(sum(XtX_inverse.value * XtY.value), 4) coefficients
from XtX_inverse
join XtY
on XtX_inverse.row_num = XtY.row_num
group by XtX_inverse.col_num, XtY.col_num;