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 Amiga-OS 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.