我该如何使用SQLAlchemy编写自己的聚合函数?以numpy计算方差为例,使用sqlite的代码如下:
import sqlite3 as sqlite
import numpy as np
class self_written_SQLvar(object):
def __init__(self):
import numpy as np
self.values = []
def step(self, value):
self.values.append(value)
def finalize(self):
return np.array(self.values).var()
cxn = sqlite.connect(':memory:')
cur = cxn.cursor()
cxn.create_aggregate("self_written_SQLvar", 1, self_written_SQLvar)
# Now - how to use it:
cur.execute("CREATE TABLE 'mytable' ('numbers' INTEGER)")
cur.execute("INSERT INTO 'mytable' VALUES (1)")
cur.execute("INSERT INTO 'mytable' VALUES (2)")
cur.execute("INSERT INTO 'mytable' VALUES (3)")
cur.execute("INSERT INTO 'mytable' VALUES (4)")
a = cur.execute("SELECT avg(numbers), self_written_SQLvar(numbers) FROM mytable")
print a.fetchall()
>>> [(2.5, 1.25)]