rdbms is indeed not a place for store a single sparse matrix like this. However I have hundreds of them, and I need to break them and recombine them frequently; furthermore, I need to drop a few rows or columns successively, and need to be able to trace back what's a row's original index. I think sqlite in my C/C++ code will save lots of boring reindexing coding, and it is more robust.
On Fri, Oct 16, 2009 at 1:14 PM, P Kishor <punk.k...@gmail.com> wrote: > 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 > -- 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