Standard Deiation/Variance Aggregate Functions
(1) By Rob (rgarnett1955) on 2020-06-02 23:09:31 [source]
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.