thanks Pavel ! On Fri, Oct 16, 2009 at 1:24 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
> > -- IA = [ 1 3 5 7 ] // IA(i) = Index of the first nonzero element > of > > row i in A > > Why 4th element if A has only 3 rows? > > > 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. > > You want it like this: > > select a1.rowid, ifnull(sum(a2.ct), 0) + 1 as IA > from rowwiseB a1 left outer join rowwiseB a2 on a2.rowid < a1.rowid > group by a1.rowid; > > > Pavel > > On Fri, Oct 16, 2009 at 2: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 > > > _______________________________________________ > 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
