portable - Desviación estándar para SQLite

sqlite studio

Question

Busqué en los documentos de SQLite y no pude encontrar nada, pero también busqué en Google y aparecieron algunos resultados.

¿SQLite tiene alguna función integrada de Desviación Estándar?

Answer 1

Agregó un poco de detección de errores en las funciones de Python

class StdevFunc:
"""
For use as an aggregate function in SQLite
"""
def __init__(self):
self.M = 0.0
self.S = 0.0
self.k = 0
def step(self, value):
try:
# automatically convert text to float, like the rest of SQLite
val = float(value) # if fails, skips this iteration, which also ignores nulls
tM = self.M
self.k += 1
self.M += ((val - tM) / self.k)
self.S += ((val - tM) * (val - self.M))
except:
pass
def finalize(self):
if self.k <= 1: # avoid division by zero
return none
else:
return math.sqrt(self.S / (self.k-1))

Answer 2

Implementé el método de Welford (igual que extension-functions.c ) como un UDF de SQLite:

$db->sqliteCreateAggregate(''stdev'',
function (&$context, $row, $data) // step callback
{
if (isset($context) !== true) // $context is null at first
{
$context = array
(
''k'' => 0,
''m'' => 0,
''s'' => 0,
);
}
if (isset($data) === true) // the standard is non-NULL values only
{
$context[''s''] += ($data - $context[''m'']) * ($data - ($context[''m''] += ($data - $context[''m'']) / ++$context[''k'']));
}
return $context;
},
function (&$context, $row) // fini callback
{
if ($context[''k''] > 0) // return NULL if no non-NULL values exist
{
return sqrt($context[''s''] / $context[''k'']);
}
return null;
},
1);

Eso está en PHP ( $db es el objeto PDO) pero debería ser trivial para portar a otro idioma.

SQLite es tan genial <3

Answer 3

Las funciones agregadas soportadas por SQLite están aquí:

http://www.sqlite.org/lang_aggfunc.html

STDEV no está en la lista.

Sin embargo, el módulo extension-functions.c en esta página contiene una función STDEV.

Answer 4

No, busqué en este mismo problema y terminé de hacer los cálculos con mi aplicación (PHP)

Answer 5

Puedes calcular la varianza en SQL:

create table t (row int);
insert into t values (1),(2),(3);
SELECT AVG((t.row - sub.a) * (t.row - sub.a)) as var from t,
(SELECT AVG(row) AS a FROM t) AS sub;
0.666666666666667

Sin embargo, todavía tiene que calcular la raíz cuadrada para obtener la desviación estándar.

Answer 6

Todavía no hay una función stdev incorporada en sqlite. Sin embargo, puede definir (como lo ha hecho Alix) una función agregadora definida por el usuario. Aquí hay un ejemplo completo en Python:

import sqlite3
import math
class StdevFunc:
def __init__(self):
self.M = 0.0
self.S = 0.0
self.k = 1
def step(self, value):
if value is None:
return
tM = self.M
self.M += (value - tM) / self.k
self.S += (value - tM) * (value - self.M)
self.k += 1
def finalize(self):
if self.k < 3:
return None
return math.sqrt(self.S / (self.k-2))
with sqlite3.connect('':memory:'') as con:
con.create_aggregate("stdev", 1, StdevFunc)
cur = con.cursor()
cur.execute("create table test(i)")
cur.executemany("insert into test(i) values (?)", [(1,), (2,), (3,), (4,), (5,)])
cur.execute("insert into test(i) values (null)")
cur.execute("select avg(i) from test")
print("avg: %f" % cur.fetchone()[0])
cur.execute("select stdev(i) from test")
print("stdev: %f" % cur.fetchone()[0])

Esto imprimirá:

avg: 3.000000
stdev: 1.581139

Compare con MySQL: http://sqlfiddle.com/#!2/ad42f3/3/0

Answer 7

un pequeño truco

select ((sum(value)*sum(value) - sum(value * value))/((count(*)-1)*(count(*))))
from the_table ;

entonces lo único que queda es calcular sqrt fuera.

Answer 8

#!/usr/bin/python
# -*- coding: utf-8 -*-
#Values produced by this script can be verified by follwing the steps
#found at https://support.microsoft.com/en-us/kb/213930 to Verify
#by chosing a non memory based database.
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(''ID'' INTEGER PRIMARY KEY AUTOINCREMENT, ''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(''ID'' INTEGER PRIMARY KEY AUTOINCREMENT, ''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):
#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))
#Add the More value
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:
#Theses are the actual bins where values fall within the distribution.
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)