std() funkce pro SQLite

19.10.2007 10:51 | Přečteno: 1162× | programování | poslední úprava: 19.10.2007 11:09

Stojím před problémem portace aplikace z MySQL na SQLite. Prográmek je napsán v Pythonu, a protože využívá DB-API 2.0 interface, portace kódu byla většinou procházka růžovým sadem...

Co ale čert nechtěl, prográmek používá SQL funkci std() pro výpočet směrodatné odchylky. Ta je sice definována v MySQL, ale SQLite takovou funkcí bohužel nedisponuje.

Musel jsem tedy zameditovat, jak tento problém elegantně vyřešit. Nejdříve jsem zkoušel další select a výpočet směrodatné odchylky iterováním vracenými daty. Toto řešení se ukázalo jako velice neefektivní (a v podstatě nepoužitelné) právě kvůli nízké rychlosti, na které má největší podíl právě nutnost dotazu navíc.

Meditace tedy pokračovaly. Elementálové byli tentokráte nakloněni a dovedli mě až k testovacímu skriptu userfunctions.py v adresáři s pysqlite. Zde mi padla do oka funkce create_aggregate objektu Connection a já zvolal Heuréka!

Nyní už stačilo jen rozšířit agregační třídu o algoritmus pro výpočet rozptylu a pak odmocnit pro získání směrodatné odchylky. Kód pro přidání funkce std() do SQLite je tedy následující:

class AggrSTD:
    """Trida pro vypocet smerodatne odchylky"""
    def __init__(self):
        """konstruktor - inicializace promennych"""
        self.cnt  = 0
        self.sum  = 0.0
        self.sum2 = 0.0

    def step(self, val):
        """krok vypoctu - je volana pro vsechna data vyhovujici podminkam dotazu"""
        self.cnt  += 1
        self.sum  += val
        self.sum2 += val*val

    def finalize(self):
        """vraci hodnotu ziskanou agregaci - smerodatnou odchylku"""
        if self.cnt < 2: return 0.0
        std = sqrt((self.sum2 - self.sum * self.sum/self.cnt)/(self.cnt-1.0))
        return std

# nyni funkci zaregistrujeme
# con je instance objektu vraceneho metodou sqlite.connect()
# parametry jsou:
#    - nazev funkce (tak jak bude volana v SQL dotazu)
#    - pocet argumentu
#    - trida, ktera definuje, jak bude vypocet probihat
con.create_aggregate("std", 1, AggrSTD)

Tímto jsme nadefinovali třídu, která rozšíří množinu standardních funkcí SQLite o výpočet směrodatné odchylky - funkce std(). Sám jsem byl překvapen, jak rychle nyní výpočet probíhá.

Teď už se můžeme databáze v klidu dotazovat např.:

SELECT std(nazev_soupce) FROM nazev_tabulky WHERE podminka;

a SQLite nám bude rozumět.

       

Komentáře