Mais pour pallier au manque de fonction, une fonctionnalité bien pratique de SQLite est qu'on peut créer ou redéfinir des fonctions. C'est ce que nous allons faire pour pouvoir calculer l'écart type dans une requête.
Il y a de nombreuses formules de calcul d'écart type suivant les cas où on fait une estimation ou si on est sur un ensemble fini de valeurs. Dans le cas d'une requête SQL, on a un ensemble fini de valeurs et donc la formule suivante convient :
L'écart type est la racine carré de la variance. La variance est la moyenne est carrés des écarts à la moyenne. On peut scinder la somme et on obtient la moyenne de la somme des carré moins la moyenne au carré. En PHP, si on a un tableau $values qui contient les différentes valeurs, cela peut s'écrire comme ceci :
function ecart_type($values) {
if (count($values)<=1)
{
return 0;
}
$somme = 0;
$somme_carre = 0;
foreach ($values as $value)
{
$somme += $value;
$somme_carre += pow($value, 2);
}
$moyenn = $somme/count($values);
$variance = $somme_carre/count($values) - pow($moyenne, 2);
return sqrt($variance);
}
Nous allons mainteant créer la fonction d'aggrégation en utilisant la méthode SQLite3::createAggregate :
SQLite3::createAggregate('std', 'ecart_type_step', 'ecart_type_finalize');
function ecart_type_step(&$values, $row_number, $value) {
$values[] = $value;
return $values;
}
function ecart_type_finalize(&$values, $row_number) {
if (count($values)<=1)
{
return 0;
}
$somme = 0;
$somme_carre = 0;
foreach ($values as $value)
{
$somme += $value;
$somme_carre += pow($value, 2);
}
$moyenn = $somme/count($values);
$variance = $somme_carre/count($values) - pow($moyenne, 2);
return sqrt($variance);
}
La fonction ecart_type_step() se contente de stocker les différentes valeurs. La fonction ecart_type_finalize() est la même que tout à l'heure aux paramètres près.
On peut désormais calculer l'écart type sur un ensemble de valeur avec une requête du type :
select count(*), std(valeur) from ma_table
Le choix du nom de la fonction que l'on vient de créer (std) permet d'avoir la même requête qu'on utilise SQLite, Mysql ou PostgreSQL.