Calcul d'écart-type avec sqlite

Par Nicolas, . Lien permanent Développement

SQLite est une bibliothèque qui propose un moteur de base de données. Le grand intérêt de SQLite est que toute la base tient dans un unique fichier. SQLite implémente en grande partie le standard SQL 92 mais certaines fonctions sont manquantes. Par exemple les fonctions mathématiques sont simples et il n'y a pas de fonction de calcul d'écart type.

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 : Formule écart type

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.

Haut de page