Using SQLite, Leverage the create_aggregate(), and SQL's Between Operator to Create a Normal Probability Distribution Histogram, or What is More Commonly Referred to as a Bell Curve

One of the hats I wear at work is that of the performance analysis guy. I work at a multiplatform shop, so naturally, I have a vested interest in retaining technologies that can be reliably used on different platforms without too much fussing around, and Python was the logical choice. With minimal care taken, a Python script can be designed to run on any of the supported platforms from Andrioid to Windows. Further, dealing with different Python versions can also be dealt with dynamically at runtime as well.

Python, is SQLite3 ready right out of the box, and it’s desirable to use this combination to form a tool set for statistical processing common in performance analysis.

Unlike SQLite’s lustrous big brothers and sisters in the Enterprise Level Client-Server market, like Oracle, IBM DB2, Microsoft SQL Server, and MySQL, that offer a rich assortment of built in aggregates, SQLite offers just the basic aggregate functions like Average, Max, Min, Count, group_concat, Sum and Total. The more complex Statistical aggregate functions are sadly missing.

However, this is not an oversite on the part of the SQLite folks, but purposely designed into it. SQLite has been designed to have a small footprint, and in many applications, it is common place to be embedded in hardware, or resided in memory.

In my case, I need these statistical functions, especially Standard Deviation, as it’s the basis of a myriad computations.

Fortunately, SQLite allows the importing of custom aggregate functions, to the database at run time by means of the create_aggregate() method in what could be described as a kind of an à la carte fashion.

This is very advantageous as it allows for adding of custom adhoc functionality to the database without the need of the ugly paradigm of pulling, processing and pushing data back and forth, CPU intensive loops and iterations outside the data layer. Data functions are preformed intra-tier vs inter-tier The database can be thought of as a container object that processes data internally, that can be as smart as a whip, or as dumb as a post. The choice is yours based on your needs at runtime.

This script demonstrates the use of SQLite’s create_aggregate() as the base function to produce one of the most common Statistical Analysis tools, namely the distribution histogram, or what is more commonly referred to as a Bell Curve.

#!/usr/bin/python
# -*- coding: utf-8 -*-
import sqlite3
import math
import random
import os
import sys
import traceback
import random

class StdevFunc:
    def __init__(self):
        self.M = 0.0    #Mean
        self.V = 0.0    #Used to Calculate Variance
        self.S = 0.0    #Standard Deviation
        self.k = 1      #Population or Small 

    def step(self, value):
        try:
            if value is None:
                return None

            tM = self.M
            self.M += (value - tM) / self.k
            self.V += (value - tM) * (value - self.M)
            self.k += 1
        except Exception as EXStep:
            pass
            return None    

    def finalize(self):
        try:
            if ((self.k - 1) < 3):
                return None
            
            #Now with our range Calculated, and Multiplied finish the Variance Calculation
            self.V = (self.V / (self.k-2))

            #Standard Deviation is the Square Root of Variance
            self.S = math.sqrt(self.V)

            return self.S
        except Exception as EXFinal:
            pass
            return None 

def Histogram(Population):
    try:
        BinCount = 6 
        More = 0

        #a = 1          #For testing Trapping
        #b = 0          #and Trace Back
        #c = (a / b)    #with Detailed Info
        
        #If you want to store the Database
        #uncDatabase = os.path.join(os.getcwd(),"BellCurve.db3")
        #con = sqlite3.connect(uncDatabase)
        
        #If you want the database in Memory
        con = sqlite3.connect(':memory:')    

        #row_factory allows accessing fields by Row and Col Name
        con.row_factory = sqlite3.Row

        #Add our Non Persistent, Runtime Standard Deviation Function to the Database
        con.create_aggregate("Stdev", 1, StdevFunc)

        #Lets Grab a Cursor
        cur = con.cursor()

        #Lets Initialize some tables, so each run with be clear of previous run
        cur.executescript('drop table 
        if exists MyData;') #executescript requires ; at the end of the string
        cur.execute("create table IF NOT EXISTS MyData('Val' FLOAT)")
        cur.executescript('drop table 
        if exists Bins;')   #executescript requires ; at the end of the string
        cur.execute("create table IF NOT EXISTS Bins('Bin' 
        UNSIGNED INTEGER, 'Val' FLOAT, 'Frequency' UNSIGNED BIG INT)")

        #Lets generate some random data, and insert in to the Database
        for n in range(0,(Population)):
            sql = "insert into MyData(Val) values ({0})".format(random.uniform(-1,1))
            #If Whole Number Integer greater that value of 2, Range Greater that 1.5
            #sql = "insert into MyData(Val) values ({0})".format(random.randint(-1,1))
            cur.execute(sql)
            pass

        #Now let’s calculate some built in Aggregates, that SQLite comes with
        cur.execute("select Avg(Val) from MyData")
        Average = cur.fetchone()[0]
        cur.execute("select Max(Val) from MyData")
        Max = cur.fetchone()[0]
        cur.execute("select Min(Val) from MyData")
        Min = cur.fetchone()[0]
        cur.execute("select Count(Val) from MyData")
        Records = cur.fetchone()[0]

        #Now let’s get Standard Deviation using our function that we added
        cur.execute("select Stdev(Val) from MyData")
        Stdev = cur.fetchone()[0]

        #And Calculate Range
        Range = float(abs(float(Max)-float(Min)))

        if (Stdev == None):
            print("================================   Data Error ===================================")
            print("                 Insufficient Population Size, Or Bad Data.")   
            print("**********************************************************************************")
        elif (abs(Max-Min) == 0):
            print("================================   Data Error ===================================")
            print(" The entire Population Contains Identical values, Distribution Incalculable.")
            print("**********************************************************************************")            
        else:  
            Bin = []        #Holds the Bin Values
            Frequency = []  #Holds the Bin Frequency for each Bin

            #Establish the 1st Bin, which is based on (Standard Deviation * 3) 
             being subtracted from the Mean
            Bin.append(float((Average - ((3 * Stdev)))))
            Frequency.append(0)
            
            #Establish the remaining Bins, which is basically adding 1 Standard Deviation
            #for each interation, -3, -2, -1, 1, 2, 3             
            for b in range(0,(BinCount) + 1):
                Bin.append((float(Bin[(b)]) + Stdev))
                Frequency.append(0)

            for b in range(0,(BinCount / 1) + 1):
                #Lets exploit the Database and have it do the hard work calculating distribution
                #of all the Bins, with SQL's between operator, but making it left inclusive, 
                 right exclusive.
                sqlBinFreq = "select count(*) as Frequency from MyData 
                where val between {0} and {1} and Val < {2}". \
                             format(float((Bin[b])), float(Bin[(b + 1)]), float(Bin[(b + 1)]))

                #If the Database Reports Values that fall between the Current Bin, 
                 Store the Frequency to a Bins Table. 
                for rowBinFreq in cur.execute(sqlBinFreq):
                    Frequency[(b + 1)] = rowBinFreq['Frequency']
                    sqlBinFreqInsert = "insert into Bins (Bin, Val, Frequency) values ({0}, {1}, {2})". \
                                       format(b, float(Bin[b]), Frequency[(b)])
                    cur.execute(sqlBinFreqInsert)

                #Allthough this Demo is not likley produce values that
                #fall outside of Standard Distribution
                #if this demo was to Calculate with real data, we want to know
                #how many non-Standard data points we have. 
                More = (More + Frequency[b])

            More = abs((Records - More))
            
            sqlBinFreqInsert = "insert into Bins (Bin, Val, Frequency) values ({0}, {1}, {2})". \
                                format((BinCount + 1), float(0), More)
            cur.execute(sqlBinFreqInsert)
               
            #Now Report the Analysis
            print("================================ The Population ==================================")
            print("             {0} {1} {2} {3} {4} {5}". \
                  format("Size".rjust(10, ' '), \
                         "Max".rjust(10, ' '), \
                         "Min".rjust(10, ' '), \
                         "Mean".rjust(10, ' '), \
                         "Range".rjust(10, ' '), \
                         "Stdev".rjust(10, ' ')))
            print("Aggregates:  {0:10d} {1:10.4f} {2:10.4f} {3:10.4f} {4:10.4f} {5:10.4f}". \
                  format(Population, Max, Min, Average, Range, Stdev))
            print("================================= The Bell Curve =================================")  

            LabelString = "{0} {1}  {2}  {3}". \
                          format("Bin".ljust(8, ' '), \
                                 "Ranges".rjust(8, ' '), \
                                 "Frequency".rjust(8, ' '), \
                                 "Histogram".rjust(6, ' '))

            print(LabelString)
            print("----------------------------------------------------------------------------------")
            
            #Let's Paint a Histogram
            sqlChart = "select * from Bins order by Bin asc"
            for rowChart in cur.execute(sqlChart):
                if (rowChart['Bin'] == 7):
                    #Bin 7 is not really a bin, but where we place the values that did not fit into the
                    #Normal Distribution. This script was tested against Excel's Bell Curve Example
                    #https://support.microsoft.com/en-us/kb/213930
                    #and produces the same results. Feel free to test it.
                    BinName = "More"
                    ChartString = "{0:<6} {1:<10} {2:10.0f}". \
                                format(BinName, \
                                        "", \
                                        More)
                else:
                    BinName = (rowChart['Bin'] + 1)
                    #Scale the Chart
                    fPercent = ((float(rowChart['Frequency']) / float(Records) * 100))
                    iPrecent = int(math.ceil(fPercent))
                
                    ChartString = "{0:<6} {1:10.4f} {2:10.0f} {3}". \
                                  format(BinName, \
                                         rowChart['Val'], \
                                         rowChart['Frequency'], \
                                         "".rjust(iPrecent, '#'))
                print(ChartString)
                
            print("**********************************************************************************")

            #Commit to Database
            con.commit()

            #Clean Up
            cur.close()
            con.close()

    except Exception as EXBellCurve:
        pass
        TraceInfo = traceback.format_exc()       
        raise Exception(TraceInfo)  

            
print("**********************************************************************************")
print("Using SQLite, leverage the create_aggregate(), and SQL's Between Operator to")
print("calculate distribution, then Demonstrate and Store the results in a Database.\n")
print("This demo creates a Population of values between -1 and 1 generated with a")
print("Pseudo Random Number Generator. The Mean value should be very close to zero,")
print("with a Range value of about 2, contained within a Standard Distribution.")

PythonVersion = sys.version_info[0] 
  
#Population = 2             #To Test Insufficient Population Size 
Population = (16 ** 2)      #Population of 256
#Population = (64 ** 2)     #Population of 4096
#Population = (128 ** 2)    #Population of 16384
#Population = (256 ** 2)    #Population of 65536
#Population = (1024 ** 2)   #Population of 1048576

Histogram(Population)       #Do a Distribution Analysis

if (PythonVersion == 3):
    kb = input("Press Enter to continue...")
else:
    kb = raw_input("Press Enter to continue...")    

A few words about the script.

The script will demonstrate adding a Standard Deviation function to a SQLite3 database. For demonstration purposes, the script will 1^st run a Pseudo Random Number Generator to build a population of data to be analyzed.

This script has been tested on Windows, and Linux platforms, as well as Python 2.6, - 3.4.

A few words about the Bell Curve.

Since, distribution analysis is at the core what a Bell Curve speaks to, we will produce a population, of adequate size, with random seeded numbers ranging from -1 to 1. This will assure a Standard Distribution, and where results are easy to interpret.

Basically, if you produce a long series of numbers from -1 to 1, then the expectation would be for a population to produce a mean of close to 0, a range very close to 2. The result in essence simply displays just how random the Python Random Number Generator actually is.

There are no official Bin allocation rules for histograms. This demo utilizes Bin allocation rules based Excel’s Data Analysis Add-on, Histogram feature.
 

Download Script

https://support.microsoft.com/en-us/kb/213930