Re: [sqlite] How to recognize a sqlite database file?
In linux I use the command 'file yourfilenamehere' and I get as an answer something like 'yourfilenamehere: SQLite 3.x database' if it is an Sqlite3 database. Regards, Luis On Tue, Aug 14, 2012 at 09:57:33AM +0800, daedae11 wrote: > How can I judge whether a file is a database file? Is there a function that I > can use for this purpose? > Help! > Hope for you reply. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- o W. Luis Mochán, | tel:(52)(777)329-1734 /<(*) Instituto de Ciencias Físicas, UNAM | fax:(52)(777)317-5388 `>/ /\ Apdo. Postal 48-3, 62251 | (*)/\/ \ Cuernavaca, Morelos, México | moc...@fis.unam.mx /\_/\__/ O< ascii ribbon campaign - stop html mail - www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Filling missing records
Suppose I have a time series in a table such as time|value 0|... 1|... 2|... 5|... 7|... where ... denotes some value. There are some missing values from the table, such as those corresponding to time=3,4,6 in my example. I need to insert the missing rows using 0 for the corresponding value. Is there a simple way to detect and insert missing rows like these for a large within sqlite, i.e., without writing a C/perl/etc. program? Regards, Luis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] record number after reordering
Thanks Igor, What are the pros and cons of this approach vs. using a temporal table as suggested by Keith? Best regards, Luis On Sun, Jul 15, 2012 at 06:50:44PM -0400, Igor Tandetnik wrote: > Luis Mochan wrote: > > I want to reorder a table and then group and average it's values. I > > tried something similar to > > > > SELECT AVG(a) FROM (SELECT a FROM table ORDER BY a) group by ROWID/10; > > > > in order to take the average 'a' for groups of 10 succesive values. > > Something like this perhaps: > > select avg(a) from MyTable t1 > group by (select count(*) from MyTable t2 where t2.a < t1.a) / 10; > > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- o W. Luis Mochán, | tel:(52)(777)329-1734 /<(*) Instituto de Ciencias Físicas, UNAM | fax:(52)(777)317-5388 `>/ /\ Apdo. Postal 48-3, 62251 | (*)/\/ \ Cuernavaca, Morelos, México | moc...@fis.unam.mx /\_/\__/ O< ascii ribbon campaign - stop html mail - www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] record number after reordering
Thanks Keith! Creating a temporal table with it's own rowid solved the problem. Luis On Sun, Jul 15, 2012 at 10:48:32AM -0600, Keith Medcalf wrote: > How about: > > drop table if exists temp.ordered; > create temporary table ordered (a number not null); > insert into ordered select a from table order by a; > select avg(a) from ordered group by rowid/100; > drop table if exists temp.ordered; > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] record number after reordering
I want to reorder a table and then group and average it's values. I tried something similar to SELECT AVG(a) FROM (SELECT a FROM table ORDER BY a) group by ROWID/10; in order to take the average 'a' for groups of 10 succesive values. My example fails. To understand I tried SELECT ROWID FROM (SELECT * FROM table ORDER BY a); and found that ROWID is null, not the record number as I expected. I cannot modify my example adding ROWID to the nested select, as in SELECT AVG(a) FROM (SELECT ROWID, a FROM table ORDER BY a) group by ROWID/10; as the ROWIDs would be in disorder. Thus my question, what is the correct way of solving my problem? Best regards, Luis -- o W. Luis Mochán, | tel:(52)(777)329-1734 /<(*) Instituto de Ciencias Físicas, UNAM | fax:(52)(777)317-5388 `>/ /\ Apdo. Postal 48-3, 62251 | (*)/\/ \ Cuernavaca, Morelos, México | moc...@fis.unam.mx /\_/\__/ O< ascii ribbon campaign - stop html mail - www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users