Alexander Reisach Scriddie

## features.csv

          
            intercept
             feature1
             feature2

            
              1
                1
                1

            
              1
                2
                4

            
              1
                3
                9

            
              1
                4
               16

            
              1
                5
               25

## features_long_table.csv

          
            row_num
             col_num
             feature_value

            
              1
               1
               1

            
              2
               1
               1

            
              3
               1
               1

            
              4
               1
               1

            
              5
               1
               1

            
              1
               2
               1

            
              2
               2
               2

            
              3
               2
               3

            
              4
               2
               4

## labels.csv

          
            row_num
             col_num
             label_value

            
              2
               1
               11

            
              3
               1
               20

            
              4
               1
               33

            
              5
               1
               50

## mat_mult.sql
# 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

## determinant.sql
# 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)

## inverse_matrix.sql
# @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

## lin_reg_result.sql
# 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;
	# 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
	# 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)
	# @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
	# 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;