--this is my first version for the purpose of storing sparse numerical matrix in sql --please let me know how to fix the bug at the end of the file, and how to tune the performance --or any better reference, thanks!
.explain -- return result in more readable format .echo on -- echo the sql stmnt --I plan to store my sparse matrix (3 x 4) in table like this: -- [ 1 2 0 0 ] -- [ 0 3 9 0 ] -- [ 0 1 4 0 ] -- -- is a three-by-four matrix with six nonzero elements, so -- -- A = [ 1 2 3 9 1 4 ] // List of non-zero matrix element in order -- IA = [ 1 3 5 7 ] // IA(i) = Index of the first nonzero element of row i in A -- JA = [ 1 2 2 3 2 3 ] // JA(i) = Column position of the non zero element A(i) -- -- Note: In this example, the row and line index begin by 1 and not 0. create table rowids( rows integer primary key -- there should be an easy way for a sequence 1..M? ); insert into rowids values (1); insert into rowids values (2); insert into rowids values (3); create table colids( cols integer primary key -- there should be an easy way for a sequence 1..N? ); insert into colids values (1); insert into colids values (2); insert into colids values (3); insert into colids values (4); create table matrix( rowid integer reference rowids, colid integer reference colids, value real non null ); insert into matrix values (1,1,1); insert into matrix values (1,2,2); insert into matrix values (2,2,3); insert into matrix values (2,3,9); insert into matrix values (3,2,1); insert into matrix values (3,3,4); insert into matrix values (2,4,0); --this entry is inserted on purpose select * from matrix; --A rowwise sparse matrix is then: create temp view rowwiseA as select value as A, colid as JA from matrix where value != 0 order by rowid, colid ; create temp view rowwiseB as select rowid, count(rowid) as ct from matrix where value != 0 group by rowid order by rowid ; create temp view rowwiseC as select a1.rowid, sum(a2.ct) +1 as JA from rowwiseB a1, rowwiseB a2 where a2.rowid < a1.rowid group by a1.rowid ; --this is not quite right yet, the first entry and last entry of JA vector is not here. select * from rowwiseA; select * from rowwiseB; select * from rowwiseC; create temp view rowwiseB as select count(rowid) as ia from matrix where value != 0 group by rowid order by rowid ; --Best regards, --Michael Chen --Google Voice Phone.: 847-448-0647 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
