stdev for SQLite [Alex Forencich]

Writing /var/www/alexforencich.com/wiki/data/cache/d/df34a3789c0a4816ecc143693ec59b5b.xhtml failed

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

====== stdev for SQLite ======

SQLite does not have a function for standard deviation.  Not really sure why.  Fortunately, the sqlite3 library included in Python supports user-defined functions.  Here is a test script that creates a user-defined aggregate function for sample standard deviation and then runs it with a set of test values.  The implementation of stdev skips null values and retuns null when it does not get enough points.  

<code python>
#!/usr/bin/env python

import sqlite3
import math

class StdevFunc:
    def __init__(self):
        self.M = 0.0
        self.S = 0.0
        self.k = 1

    def step(self, value):
        if value is None:
            return
        tM = self.M
        self.M += (value - tM) / self.k
        self.S += (value - tM) * (value - self.M)
        self.k += 1

    def finalize(self):
        if self.k < 3:
            return None
        return math.sqrt(self.S / (self.k-2))

with sqlite3.connect(':memory:') as con:

    con.create_aggregate("stdev", 1, StdevFunc)

    cur = con.cursor()

    cur.execute("create table test(i)")
    cur.executemany("insert into test(i) values (?)", [(1,), (2,), (3,), (4,), (5,)])
    cur.execute("insert into test(i) values (null)")
    cur.execute("select avg(i) from test")
    print("avg: %f" % cur.fetchone()[0])
    cur.execute("select stdev(i) from test")
    print("stdev: %f" % cur.fetchone()[0])

</code>

When you run this code, it should print

<code>
avg: 3.000000
stdev: 1.581139
</code>

Comparison with MySQL implementation: [[http://sqlfiddle.com/#!2/ad42f3/3/0]]