This is not the answer you are looking for, and there are SQL geniuses on this list who will help you better, but really, is an rdbms really a good place to store a matrix the way you are trying to do? So convoluted.
My approach, if I really was determined to store it in sqlite, would be to flatten the matrix in my code, and then stuff that into a table. Retrieve it and deserialize it as needed. For example, Perl's freeze, thaw are tailor made for this kind of stuff. On Fri, Oct 16, 2009 at 1:09 PM, Michael Chen <vancouver.mich...@gmail.com> wrote: > --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 > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= Sent from Madison, WI, United States _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
