percentile for SQLite [Alex Forencich]

Writing /var/www/alexforencich.com/wiki/data/cache/9/97a91a16387eb4e23132bbab8d2703cc.metadata failed

Writing /var/www/alexforencich.com/wiki/data/cache/5/5e47ccf9dc76633a9a429a925893c637.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.

====== percentile for SQLite ======

SQLite does not have a function for percentile.  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 percentile and then runs it with a set of test values.  The implementation of percentile skips null values and retuns null when it does not get enough points.  

<code python>
#!/usr/bin/env python
 
import sqlite3
 
class PercentileFunc:
    def __init__(self):
        self.list = []
        self.percent = None
 
    def step(self, value, percent):
        if value is None:
            return
        if self.percent is None:
            self.percent = percent
        if self.percent != percent:
            return
        self.list.append(value)
 
    def finalize(self):
        if len(self.list) == 0:
            return None
        self.list.sort()
        return self.list[int(round((len(self.list)-1)*self.percent/100.0))]
 
with sqlite3.connect(':memory:') as con:
 
    con.create_aggregate("percentile", 2, PercentileFunc)
 
    cur = con.cursor()
 
    cur.execute("create table test(i)")
    cur.executemany("insert into test(i) values (?)", [(k,) for k in range(100)])
    cur.execute("insert into test(i) values (null)")
    cur.execute("select avg(i) from test")
    print("avg: %f" % cur.fetchone()[0])
    cur.execute("select percentile(i, 90) from test")
    print("percentile: %f" % cur.fetchone()[0])

</code>

When you run this code, it should print

<code>
avg: 49.500000
percentile: 90.000000
</code>