Snippets for SQLite scripts.

stdev.py

"""Declare User-Defined Functions (UDF) for SQLite databases."""

# IMPORTS

import math
import typing as T

# CLASSES


class SQLiteSTDEVP:
    """Define the Population Standard Deviation function (STDEVP) for SQLite.

    Source: https://rosettacode.org/wiki/Cumulative_standard_deviation#Python"""

    NAME = "STDEVP"
    NUM_PARAMS = 1

    def __init__(self):
        self.sum = 0.0
        self.sum2 = 0.0
        self.count = 0

    def step(self, value: T.Optional[float]) -> None:
        """Define the initial action performed on each row (map)."""
        if value is None:
            return
        self.sum += value
        self.sum2 += value ** 2
        self.count += 1

    def finalize(self) -> float:
        """Define the final action that performs the aggregation (reduce)."""
        var = self.sum2 / self.count - self.sum ** 2 / self.count ** 2
        stdev = math.sqrt(var)
        return stdev