SGBD : SQL La requête fictive qui compte zéro l’absence d’enregistrements dans une Base de Données

Mis à jour le

Le mardi 18 janvier 2011, je décide d’améliorer l’extension StatPress Reloaded pour lui faire afficher pour chaque page et article le graphique du nombre de visites quotidiennes, pages vues, flux rss et visites de robots. Cela me permettra d’avoir une vue de la fréquentation du site.

Pour ce faire, je copierai la présentation déjà existante dans StatPress Reloaded et montrerai pour chaque article du site le graphique par des rectangles de couleurs différentes comme StatPress :

  1. du nombre de lecteurs (visiteurs),
  2. de lectures (pages vues),
  3. d’abonnements aux flux RSS
  4. et de visites de robots de recherche

de tous les jours affichés.

Graphique stapress
Graphique StatPress

Alors, j’ai regardé comment StatPress comptabilise les visiteurs dans son graphique de la page principale et j’ai l’impression que la méthode choisie pour ce faire est bien trop compliquée.

En effet, pour afficher le nombre quotidien de visites, de visiteurs, de flux RSS, de robots le programme fait 4 requêtes SQL par jour d’affichage (une pour les pages vues, une pour les robots, une pour les flux RSS, une pour les visiteurs, rectangles de couleurs différentes).

Ainsi, pour afficher un graphique :

  • des 20 derniers jours, StatPress et tous ses dérivés font 20 fois plus de requêtes, soit 20 x 4 = 80 requêtes SQL.
  • Pour les 30 derniers jours, ils font 30 fois plus de requêtes, soit 30 x 4 = 120 requêtes.
  • Pour les 50 derniers jours, 50 fois plus de requêtes, soit 50 x 4 = 200 requêtes.

Ce grand nombre de requêtes ralenti l’exécution de StatPress et interdit le comptage des lecteurs, lectures, flux RSS et robots pour chaque page et article du site car cela entraine un nombre démentiel de requêtes SQL !

En effet, imaginez :

  • si le site a 40 pages (le mien en a 40), et que l’on veuille afficher la fréquentation des 30 derniers jours de ces 40 pages, donc 40 graphiques, cette technique de comptage entraine 40 x 30 x 4 = 4800 requêtes.
  • Si le site a 40 pages et que l’on veuille afficher la fréquentation des 50 derniers jours, cela entraine 40x50x4 = 8 000 requêtes,
  • en limitant l’affichage a 10 pages, cela fait 10 x 50 x 4 = 2000 requêtes, autant dire que cela est vraiment interminable pour un serveur gratuit comme Free !

Je suis persuadé que l’on peut faire chaque graphique en 4 requêtes SQL uniquement indépendamment du nombre de jours comptés.

Et donc comptabiliser le nombre de lecteurs, lectures, flux RSS et robots de tous les jours du graphique en 4 requêtes (une par comptage) :

  • une requête pour le nombre des visiteurs des 20 derniers jours,
  • une autre pour les pages vues,
  • etc.

LE PROBLEME qui a contraint le programmeur de Statpress à faire autant de requêtes est le suivant :

  • Quand on demande le nombre quotidien de visiteurs des 30 derniers jours, la requête SQL renvoie le nombre de visiteurs de chaque jour.
  • Mais si un jour, il n’y a aucun visiteur, la requête SQL ne renvoie rien du tout, même pas zéro : ce jour est ignoré.

    En effet, StatPress n’a rien enregistré dans la base de données puisque personne n’est venu.

    Et cette absence d’enregistrement dans la base de données induit un ENSEMBLE de jours sans les jours avec aucun visiteur !

    C’est à dire un ensemble de jours où il y a eu réellement des visiteurs et uniquement ceux-là.

Ainsi, la requête SQL ne renvoit rien du tout pour les jours sans visiteurs puisque la table de données de statpress est vide pour ce jour là !

J’ai séché pendant 3 jours sur le problème des requêtes, la nuit je me réveillais avec une solution, j’allais l’écrire et cela ne marchait pas. À force d’essayer des solutions qui ne marchaient pas, je me suis plongé un peu plus profondément dans l’apprentissage du langage SQL. C’est à dire, comprendre quelles sont ses possibilités pour pouvoir les utiliser.

J’ai fini par intégrer le fait que le langage est ENSEMBLISTE, que la requête SQL est un ENSEMBLE (non ordonné, non rangé pour ainsi dire) et non pas une LISTE ordonnée de tous les jours (numérotée par nombre de visites et rangée, classée, dans un ordre précis : celui de l’index de 1 à 30 ou 31 selon le nombre de jours du mois). Le langage SQL est construit avec la théorie des ensembles car les bases de données sont des ENSEMBLES de données non indexables, que l’on ne peut pas ranger dans un tableau. La donnée est présente ou absente, rien de plus.

Trouver une requête SQL qui compte zéro une absence d’enregistrement dans la base de donnée m’a bel et bien pris la tête pendant 3 jours, j’y pensais tout le temps, même en dormant, c’est dire ! Ce n’est que lorsque j’ai compris qu’une requête SQL est en fait un ENSEMBLE en elle-même et doit donc être pensée comme un ENSEMBLE et non pas comme une liste (ou tableau) que j’ai trouvé la solution ce samedi 22 janvier 2011 au matin.

POUR RESOUDRE CE PROBLEME de l’absence des jours sans visite de la table de données, j’ai codé une requête qui crée une ENSEMBLE fictif.

Cet ENSEMBLE fictif est la REUNION (au sens de la théorie des ensemble)

  • de l’ENSEMBLE des jours de visites existant réellement (le nombre de visite est nécessairement plus grand que zéro)
  • et de l’ENSEMBLE fictif de tous les jours tous comptés sans aucune visite, c’est à dire avec zéro visite.

Les jours où il n’y a aucune visite réelle, la REUNION des deux ensembles renvoie le nombre zéro puisque c’est le seul nombre de visiteurs du jour enregistré dans la table fictive.

Les jours avec visites, cela renvoie le nombre de visites réelles.

Du coup, quel que soit le nombre de jours affiché, il n’y a que 4 requêtes :

  • une pour les visiteurs,
  • une pour les flux RSS,
  • une pour les robots,
  • une pour les pages vues et ce indépendamment du nombre de jours affichés.

Cela a fonctionné immédiatement, j’ai eu juste à regarder la synthaxe exacte du SQL pour que la requête fonctionne. J’ai trouvé un bon cours, complété par quelques forums anglais et j’ai pu écrire correctement la requête.

La voici écrite pour WordPress :

1
2
3
4
5
6
7
8
9
10
11
12
13
$wpdb->get_results("
SELECT total AS totalvisitors, date
FROM (
(SELECT count(distinct ip) AS total, date
FROM $table_name
WHERE feed=" AND spider=" AND date BETWEEN $limitdate AND $currentdate
GROUP BY date )
UNION
(SELECT 0 AS total, date
FROM $table_name
WHERE date BETWEEN $limitdate AND $currentdate)
) visitor
GROUP BY date "); // return 0 if 0 visit else real totalvisitors

Dès lors, la rapidité d’exécution a été fulgurante, l’affichage de la page de StatPress est instantané sur mon ordi quand la version originale met 10 secondes !

Avec la version originale de StatPress, je vois les graphiques se construire sous mes yeux. Les rectangles de couleurs qui symbolisent

  • le nombre de visiteurs (bleu foncé),
  • le nombre de pages vues (bleu clair),  
  • le nombre de robots (bleu pale)
  • et les flux RSS (rouge)

s’affichent les uns après les autres, de gauche à droite et de bas en haut !

En effet, pour afficher chaque rectangle de couleur, STATPRESS et toutes ses variantes calculent sa grandeur en créant une requête qui compte le nombre à afficher. Il y ainsi, autant de requêtes que de rectangles affichés.

Après avoir affiché 4 rectangles, c’est à dire un jour sur le graphique, le programme calcule la taille des 4 rectangles du jour suivant et ainsi de suite. Cela ralenti considérablement l’exécution de l’affichage de la page Web et est très coûteux en temps de mémoire vive et en temps de calcul du processeur. Désormais l’affichage est instantané, ce qui prouve que la mémoire RAM allouée par Free est moins sollicitée.

Le lendemain, je réalise que SI le site est en panne pendant 2 jours consécutifs, sans aucune entrée dans la table de données, ALORS, la technique ne fonctionnera pas.

La technique a besoin qu’au moins une entrée soit enregistrée dans la base de données pour chaque jour. Ce qui a toujours été le cas jusqu’à présent.

En effet, pour créer l’ensemble fictif des jours avec zéro visiteurs, la requête sélectionne tous les jours enregistrés et leur affecte d’autorité le nombre zéro visite. Donc, s’il n’y a aucune visite de quelque sorte que ce soit (visiteur, robot), la requête fictive ne peut pas affecter à ce jour sans visite le nombre 0.

Ainsi, s’il y a discontinuité dans les dates enregistrées, un jour complet de 0h à 24 h sans aucun enregistrement, la requête ne peut pas affecter pour ce jour là zéro visite car justement il n’y a eu aucune visite de quelque sorte que ce soit.

Il est nécessaire d’avoir au moins une visite chaque jour, rien de plus.

Dans la pratique, la discontinuité des dates ne se produit jamais. En effet, les robots de recherche viennent tous les jours et le site n’a été inaccessible qu’une seule fois car j’ai fait une connerie en oubliant le mot de passe de la base de données. À part cette durée de 29 heures d’inaccessibilité, le site a toujours été accessible. De plus ces 29 heures se sont réparties sur 2 jours et les deux jours ont enregistré des données, ce qui suffit pour que la technique fonctionne.

Pour que la technique fonctionne il faut qu’il y ait continuité dans l’enregistrement des dates.

S’il y a discontinuité de l’enregistrement des dates, alors la technique ne fonctionne pas. Maintenant, je n’ai plus qu’à écrire un petit programme qui vérifie la continuité des dates, en cas de discontinuité, ce programme crée une entrée dans la table de donnée en enregistrant uniquement la date manquante. Le problème de la discontinuité n’en est alors plus un.

Cela va nécessiter de comprendre comment on fait concrètement avec le langage SQL pour enregistrer une date dans la base de données. Je sais comment on lit dans la base de données, pas comment on enregistre une donnée dans la table.

Deux jours plus tard, le mardi 25 janvier 2011, j’ai fait le petit programme qui vérifie la continuité des dates, en cas de discontinuité, ce programme crée une entrée dans la table de donnée en enregistrant uniquement la date manquante. Le problème de la discontinuité des dates est réglé.

Je peux maintenant améliorer STATPRESS Reloaded en lui faisant afficher pour chaque page du site le nombre :

  • de visiteurs,
  • de pages vues,
  • de flux RSS,
  • de robots qu’elle a eu sur une période de temps donnée avec un joli graphique.

La méthode de comptage est identique à celle que j’ai programmée et je peux réutiliser ce code.

Il y aura alors, pour afficher la fréquentation de 20 pages, c’est à dire pour afficher 20 graphiques (quelque soit le nombre de jours affichés), 20 x 4= 80 requêtes SQL puisque cette technique de calcul est indépendante du nombre de jours affichés, soit autant que pour afficher la fréquentation globale du site sur 20 jours sur 1 seul graphique avec la version originale de StatPress.

Mise à jour : depuis le mois d’août 2011, sur les dernières versions de StatPress-Visitors, j’ai codé une autre solution proposée par Guy du forum français de WordPress (www.wordpress-fr.net).

Son idée est la suivante : coder l’union de 2 ensembles en PHP !

C’est à dire de créer en PHP un tableau des visites pour tous les jours, où tous les compteurs sont initialisés à zéro.

Puis, on fait une requête SQL qui renvoie le nombre réel de visite comme celle-ci :

SELECT count(distinct ip) AS total, date
FROM $table_name
WHERE feed=" AND spider=" AND date BETWEEN $limitdate AND $currentdate
GROUP BY date

Et donc ne renvoie rien lorsqu’il y a aucun visiteur. Ensuite, on met à jour le tableau PHP avec le résultat de la requête. Lorsqu’il n’y a aucun visiteur, le tableau reste à 0.

Cette procédure est deux fois plus rapide sur un site WordPress, même si PHP -qui est un langage interprété- est 1000 fois plus lent à son éxécution que SQL -qui est un langage compilé.

Si vous avez trouvé une faute d’orthographe, informez-nous en sélectionnant le texte et en appuyant sur Ctrl + Enter

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *