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.

#!/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])

When you run this code, it should print

avg: 49.500000
percentile: 90.000000