On Fri, Oct 16, 2009 at 1:41 PM, Michael Chen <vancouver.mich...@gmail.com> wrote: > 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.
Would you not store all the matrices in one table, and use a "matrix_id" column to identify which records are for one matrix ? Stephan > > 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 > -- Stephan Wehner -> http://stephan.sugarmotor.org (blog and homepage) -> http://loggingit.com -> http://www.thrackle.org -> http://www.buckmaster.ca -> http://www.trafficlife.com -> http://stephansmap.org -- http://blog.stephansmap.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
