KeiruaProd

Même si les bases de données NoSQL ont la côte en ce moment, les bases de données SQL ont encore de beaux jours à vivre devant eux. En effet, dans beaucoup de situations, il est assez facile de sortir des statistiques avec quelques lignes de SQL. Voici quelques astuces que j’ai découvert il y a quelques temps, et qui sont très pratiques pour sortir des chiffres.

Assez rapidement, lorsqu’on fait du SQL, on a besoin de récupérer le nombre d’éléments dans la base qui correspondent à un certain critère. Pour faire des statistiques, comme par exemple extraire le nombre d’utilisateurs enregistrés dans la base par exemple. La réponse est classique :

select
	count(u.id) as total
from
	users u

Ok, c’était facile. Et si on veut le nombre d’utilisateurs qui ont été créés chaque mois ?

On peut utiliser un « group by date_trunc »

L’astuce, c’est d’utiliser date_trunc. date_trunc permet de ne conserver, dans une date, que la partie de la date qui nous intéresse. Si notre date est le 4 janvier 1981 à 17h23, lorsqu’on tronque au mois, date_trunc nous renverra le 1 janvier 1981 à 00h00. En combinant cela avec un group by, on peut obtenir le nombre d’utilisateur qui a été créé mois par mois :

select
	count(u.id) as total
	date_trunc ('months', u.created_at) as considered_month
from
	users u
group by date_trunc ('months', u.created_at)<br />
order by date_trunc ('months', u.created_at)<br />

Comme vous l’avez compris, le premier paramètre de date_trunc permet de définir où tronquer. En tronquant au jour ou bien à l’heure on obtient une granularité plus fine, mais également plus de données, ce qui n’est pas toujours ce dont on a besoin.

On obtient des résultats de ce genre, en supposant que nous ayons des utilisateurs entre avril et novembre 2013 :

En fait, maintenant, on aimerait bien connaitre, pour chaque mois, le pourcentage d’utilisateurs qui s’est connecté au moins 3 fois, par rapport au nombre total d’utilisateurs créés durant le mois. Ok, c’est un exemple tordu, mais c’est pour expliquer comment peut faire un pourcentage sur le nombre d’éléments d’un sous-ensemble de données d’un mois concerné par rapport au nombre d’éléments de l’ensemble de départ.

La structure sum (case …)

L’idée, c’est d’utiliser une structure avec SUM (case … ) pour gérer un compteur « à la main ». Dans l’exemple ci-dessous, vous pouvez voir qu’on compte le nombre d’éléments total avec count, et qu’on compte le nombre d’utisateurs ayant 3 connections en incrémentant un compteur nous même via SUM (case … ). Enfin, à la troisième ligne du select on calcule le pourcentage correspondant.

select
	count(u.id) as total,
	sum( case when u.nb_connection > 3 THEN 1 ELSE 0 END) as subset_count,
	sum( case when u.nb_connection > 3 THEN 1 ELSE 0 END)::float * 100/ count(u.id)::float as percentage,
    date_trunc ('months', u.created_at) as considered_month
from
	users u
group by date_trunc ('months', u.created_at)
order by date_trunc ('months', u.created_at)

Dans la vraie vie, au lieu de stocker un entier correspondant au nombre de connections, on stockerait plutôt la date de connection dans une table séparée, mais c’est pour simplifier.

On obtient des résultats de ce genre :

Il est à noter qu’avec PostgreSQL, on pourrait convertir directement le résultat de « u.nb_connection > 3 » entier pour faire la somme de manière plus concise, mais pas forcément plus simple à lire. De plus, l’exemple ci-dessus montre qu’on peut mettre plusieurs conditions (avec plusieurs « when » à l’intérieur du case), ce que la conversion d’un boolean ne montre pas forcément. Néanmoins, il est possible d’écrire la ligne qui suit :

select
	count(u.id) as total
	sum( (u.nb_connection > 3)::integer ) as subset_count
	...

Bref, on a vu dans cet articles 2 structures pratiques pour extraire des chiffres à partir de votre base, en utilisant group by date_trunc et sum (case …). Ca a l’air de rien car les sommes et les moyennes sont des outils simples, mais ce sont également des outils très pratiques pour étudier l’évolution d’un business, ou anticiper les évolutions de structure à prévoir.