Re: [sqlite] Is it possible to use SQLITE to calculate the streakedness of numeric data?

[Michael Black]

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?

[Gabriel Corneanu]

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

[James K. Lowden]

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?

[joe.fis...@tanguaylab.com]

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

[Richard Hipp]

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

[Paul Sanderson]

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

[Jeffrey Walton]

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?

[Frank Chang]

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