Re: [sqlite] Is it possible to use SQLITE to calculate the streakedness of numeric data?
Does this get you started? It calculates a running standard deviation over a window. With a bit more effort you can add a running average and other calculations on the window. Do you have a reference for this streakedness measurement? I couldn't find one. I used this library extension from http://www.sqlite.org/contrib http://www.sqlite.org/contrib/download/extension-functions.c?get=25 Assumes window size of 10 but you could put that value in the data (if it's variable) and use it from there too if you want. FYI...a linear sequence like this has a constant standard deviation. select load_extension('./libsqlitefunctions.so'); create table a(n,deviation); create table window(n); create trigger trig1 after insert on a begin delete from window where rowid%10 = new.rowid%10; insert into window values(new.n); update a set deviation = (select sqrt(sum(square(n-(select sum(n)/10 from window)))/10) from window) where new.rowid >=10 and rowid=new.rowid; end; insert into a(n) values(1.0); insert into a(n) values(2.0); insert into a(n) values(3.0); insert into a(n) values(4.0); insert into a(n) values(5.0); insert into a(n) values(6.0); insert into a(n) values(7.0); insert into a(n) values(8.0); insert into a(n) values(9.0); insert into a(n) values(10.0); insert into a(n) values(11.0); insert into a(n) values(12.0); insert into a(n) values(13.0); insert into a(n) values(14.0); insert into a(n) values(15.0); insert into a(n) values(16.0); insert into a(n) values(17.0); insert into a(n) values(18.0); insert into a(n) values(19.0); insert into a(n) values(20.0); select * from a; 1.0| 2.0| 3.0| 4.0| 5.0| 6.0| 7.0| 8.0| 9.0| 10.0|2.87228132326901 11.0|2.87228132326901 12.0|2.87228132326901 13.0|2.87228132326901 14.0|2.87228132326901 15.0|2.87228132326901 16.0|2.87228132326901 17.0|2.87228132326901 18.0|2.87228132326901 19.0|2.87228132326901 20.0|2.87228132326901 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Frank Chang Sent: Monday, February 18, 2013 9:50 AM To: sqlite-users@sqlite.org Subject: [sqlite] Is it possible to use SQLITE to calculate the streakedness of numeric data? Would anyone know how to use SQLITE to calculate the streakedness of data? The definition of streakedness is how many deviations away from the mean(i.e running average a numerical data streak is Thank you for your help. A variable R can be used to indicate how many deviations away from the mean a particular streak is. According to the disclosed embodiment, the level of a streak can be defined not just in (integer*deviation) distances from the mean but also as (integer*fraction_of_deviation) distances. To accomplish this, a variable R-factor can be used. The R-factor indicates the separation between two successive R-levels in terms of a fraction of the deviation. By varying the R-factor, streaks can be ranked as required. However, the "credibility" of the streak should also be considered, and included in a ranking mechanism. The deviation within the streak is an obvious measure of how staggered the data is within the streak. A good streak should be less staggered, or in other words, have less deviation. For this reason, a very high level streak is considered to be good, even if its deviation is more than what would normally be desired. Thus, while the level R influences the ranking positively, the deviation within the streak influences it negatively. ___ 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
Re: [sqlite] Deletion slow?
Wondering how nobody suggested, did you try "PRAGMA synchronous = OFF" ?? For me it is always the default... I can imagine how slow such a combination can be. Just my 2c... Gabriel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating a summary table
On 2013-02-18 17:02:53 +, Paul Sanderson wrote: > nc > 1a > 2a > 3a > 4b > 5b > 3b > 4b > 2b > 3a > 5b > 2b > > > I have a table as above > > I want to create a summary table that shows in the first column the total > number of occurrences of a value in the first column (n) and in the second > column for each value in n a count of the unique entries in c > > it should look like this > > noccurenceunique > 111 > 232 > 332 > 421 > 522 I think you can do this SELECT n , sum(cou) AS oc, count(*) AS un FROM (SELECT n, c, count(*) AS cou FROM tablename GROUP BY n, c) GROUP BY n; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating a summary table
On Mon, 18 Feb 2013 17:02:53 + Paul Sanderson wrote: > nc > 1a > 2a > 3a > 4b > 5b > 3b > 4b > 2b > 3a > 5b > 2b > > > I have a table as above > > I want to create a summary table that shows in the first column the > total number of occurrences of a value in the first column (n) and in > the second column for each value in n a count of the unique entries > in c Is this what you have in mind? sqlite> select n, count(*) as occurence, count(distinct c) as uniq from t group by n; n occurence uniq -- -- -- 1 1 1 2 3 2 3 3 2 4 2 1 5 2 1 --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to use SQLITE to calculate the, streakedness of numeric data?
Frank, Don't really understand your question. Should be no problem to calculate anything once. Just write your algorithm. The problem comes in when you try to calculate it for a set of data. SQLite like MySQL doesn't have SQL 2003/2008 Window Functions. Oracle recently stated they will not be adding Window Functions to MySQL. That leaves just Oracle, DB2, and PostgreSQL for Window Functions. I often access my SQLite data from the GNU R programming language using the RSQLite package. In R you can also slice and dice your data but it's not as easy as SQL. R is the best for Statistics. Then, you can write back to the database using UPDATE or INSERT. Here's an example from "Demo_R_to_SQLite.R" # Load Libraries require(DBI) # for database require(RSQLite)# for database # Connect to Database drv <- dbDriver("SQLite") my_sqlite_ex1_RW <- dbConnect(drv, dbname = "my_sqlite_ex1.db", flags=SQLITE_RW) my_sqlite_ex2_RO <- dbConnect(drv, dbname = "my_sqlite_ex2.db", flags=SQLITE_RO) # Do something --> SQL queries (it's simple to make SQL calls) --> check the documentation dbDisconnect(my_sqlite_ex1_RW) dbDisconnect(my_sqlite_ex2_RO) dbUnloadDriver(drv) Joe Fisher Oregon State University Would anyone know how to use SQLITE to calculate the streakedness of data? The definition of streakedness is how many deviations away from the mean(i.e running average a numerical data streak is Thank you for your help. A variable R can be used to indicate how many deviations away from the mean a particular streak is. According to the disclosed embodiment, the level of a streak can be defined not just in (integer*deviation) distances from the mean but also as (integer*fraction_of_deviation) distances. To accomplish this, a variable R-factor can be used. The R-factor indicates the separation between two successive R-levels in terms of a fraction of the deviation. By varying the R-factor, streaks can be ranked as required. However, the "credibility" of the streak should also be considered, and included in a ranking mechanism. The deviation within the streak is an obvious measure of how staggered the data is within the streak. A good streak should be less staggered, or in other words, have less deviation. For this reason, a very high level streak is considered to be good, even if its deviation is more than what would normally be desired. Thus, while the level R influences the ranking positively, the deviation within the streak influences it negatively. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on VACCUUM, WAL, and Encryption Codecs
On Mon, Feb 18, 2013 at 11:21 AM, Jeffrey Walton wrote: > Hi All, > > Can anyone verify that VACCUUM and WAL uses encryption codecs if available? > > I think I found answers for other components such as rollback > journals, but I'm not clear on the two items above. > Yes. VACUUM and WAL work the same. With the SQLite Encryption Extension ( http://www.hwaci.com/sw/sqlite/see.html) everything works as with ordinary SQLite, include VACUUM, WAL, ROLLBACK, and ATTACH. The only difference is that your database file might be slightly larger (about 0.1% larger) due to space used to hold encryption nounces, and the database file will look like white noise to anybody without SEE and knowledge of the encryption key. > > Jeff > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] creating a summary table
nc 1a 2a 3a 4b 5b 3b 4b 2b 3a 5b 2b I have a table as above I want to create a summary table that shows in the first column the total number of occurrences of a value in the first column (n) and in the second column for each value in n a count of the unique entries in c it should look like this noccurenceunique 111 232 332 421 522 getting the first 2 columns is easy select n, count(*) from table group by cat but how do i create a combined table which also list s the unique counts on c thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question on VACCUUM, WAL, and Encryption Codecs
Hi All, Can anyone verify that VACCUUM and WAL uses encryption codecs if available? I think I found answers for other components such as rollback journals, but I'm not clear on the two items above. Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it possible to use SQLITE to calculate the streakedness of numeric data?
Would anyone know how to use SQLITE to calculate the streakedness of data? The definition of streakedness is how many deviations away from the mean(i.e running average a numerical data streak is Thank you for your help. A variable R can be used to indicate how many deviations away from the mean a particular streak is. According to the disclosed embodiment, the level of a streak can be defined not just in (integer*deviation) distances from the mean but also as (integer*fraction_of_deviation) distances. To accomplish this, a variable R-factor can be used. The R-factor indicates the separation between two successive R-levels in terms of a fraction of the deviation. By varying the R-factor, streaks can be ranked as required. However, the "credibility" of the streak should also be considered, and included in a ranking mechanism. The deviation within the streak is an obvious measure of how staggered the data is within the streak. A good streak should be less staggered, or in other words, have less deviation. For this reason, a very high level streak is considered to be good, even if its deviation is more than what would normally be desired. Thus, while the level R influences the ranking positively, the deviation within the streak influences it negatively. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] question on request "Linq is SQL"
question on request "Linq is SQL" I hooked a provider edmx model and try to pull the essence. Two tables and typearmatura beton ying this request is no error var quer = gf.typearmatura.Where (c => c.idtypearmatura == Ls1.SelectedIndex + 1). FirstOrDefault (); this request var quer = gf.beton.Where (c => c.idBeton == Ls1.SelectedIndex + 1). FirstOrDefault (); bug InnerExeption make sure that the base type can be converted to final InnerExeption upon actuation of its value must be finite -- С Уважением Алексей Куликов Справочник строителя INCD www.rossecorp.ru ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users