> -- 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
