SQLite Forum


2 forum posts by user rgarnett1955

23:09 Post: Standard Deiation/Variance Aggregate Functions (artifact: 037d48c82e user: rgarnett1955)


Just wondered why a calc for a variance aggregate is not included as a standard function in sqLite3. I use it a lot and find it a nuisance that sqLite doesn't have this. Using Welfords method calculating the variance can be done in the same code as averages and adds very little to the size of the code: i.e.

oldMean = outPut->I1.Mean; outPut->I1.Mean = outPut->I1.Mean + (pP.I1 - outPut->I1.Mean) / (N); outPut->I1.StdDev = outPut->I1.StdDev + (pP.I1 - outPut->I1.Mean) * (pP.I1 - oldMean);

I understand that there is multiplication for every record but this is the case if you do variance outside of a query anyway.

I understand that it is possible to add user defined aggregate functions to sqlite3, but this is different for every language/IDE being used and adds to the programming work, testing and debugging. Not every code base provides access to the create_aggregate function.

Best regards


23:17 Post: Are Views just stored SQL Strings or something more? (artifact: 421f3af6b1 user: rgarnett1955)


I know views are stored in the sqlite_master table as:

type view: name "myname" tbl_name "myname" rootpage 0 sql "mysqlString"

Is this all they are or is there some way the compilation of the view is retained/stored for the next time it is run?

I know some databases store Views in raw and compiled to speed things up e.g MS Access (I think) SQL Server

Best regards