#!python3 from __future__ import absolute_import, division, print_function, unicode_literals
import apsw db = apsw.Connection() db.executescript(""" create table x ( value integer not null ); insert into x values (120), (130), (140), (110); """) # Method 1: Using partial Running calc sumx = 0.0 sumx2 = 0.0 count = 0 for row in db.execute('select value from x'): sumx += row.value sumx2 += row.value ** 2 count += 1 sd1 = (sumx2 / count - (sumx / count) ** 2) ** 0.5 # Method 2: Using double retrieval brute force calc avgx = 0.0 avgm = 0.0 count = 0 for row in db.execute('select avg(value) as avg from x'): avgx = row.avg for row in db.execute('select value from x'): avgm += (row.value - avgx) ** 2 count += 1 sd2 = (avgm / count) ** 0.5 # Method 3: Using my extension which does full running calc for row in db.execute('select stdevp(value) as stdev from x'): sd3 = row.stdev # Method 4: Using mostly pure SQL for row in db.execute('select count(value) as count, sum(value) as sumx, sum(value*value) as sumx2 from x'): sd4 = (row.sumx2 / row.count - (row.sumx / row.count) ** 2) ** 0.5 # Method 5: Using even more calcs in SQL for row in db.execute('''select sumx2 / count - (sumx / count) * (sumx / count) as var from (select count(value) as count, sum(value) as sumx, sum(value*value) as sumx2 from x)'''): sd5 = row.var ** 0.5 print(sd1, sd2, sd3, sd4, sd5) => 11.180339887498949 11.180339887498949 11.180339887498949 11.180339887498949 11.180339887498949 You could even calculate the square root in a recursive CTE if you wanted and not have your application do anything at all other than submit the query. However, Method 1 is likely the most efficient (and easiest) to do assuming that you do not have an extension which calculates the aggregate, and is also likely to execute the fastest. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Richard Damon >Sent: Saturday, 12 October, 2019 09:33 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Standard deviation last x entries > >On 10/12/19 11:23 AM, Richard Damon wrote: >> On 10/12/19 10:08 AM, Bart Smissaert wrote: >>> How do I get the standard deviation of the last 4 entries (there could >be >>> less than 4) of an integer column grouped by an integer ID entry in >another >>> column in the same table. >>> >>> So data could be like this: >>> >>> ID xValue >>> ---------------- >>> 1 130 >>> 1 120 >>> 1 140 >>> 1 100 >>> 1 110 >>> 2 140 >>> 2 130 >>> 2 150 >>> >>> I know that SQLite doesn't have the SQRT function (and I can't make a >UDF >>> as I am doing this in an Android phone app) but I can do this last >step in >>> code. >>> >>> RBS >> I may not be the best at forming the SQL to do this, but here is the >> general idea I would use. First use a query with ORDER BY DECR and >LIMIT >> to get the last 4 items, and select the value, and a computer column of >> value2 = value * value. >> >> Then over this result, do a GROUP BY to compute N = count(value), S = >> sum(value), S2 =sum(value2) >> >> You can then compute the variance (standard deviation squared) from the >> equation >> >> Variance = S2 / N - (S / N) * (S / N) >> >> This equation assumes that you are working with the full population and >> not just a sample, if you data is to be considered a sample you need to >> make a small adjustment in the formula to >> >> Estimated Variance of Popultion = S2 / (N-1) - S * S / (N * (N-1)) >> >> >(Sorry about the messed up subject, reposting to fix it) > >-- >Richard Damon > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users