l est des cas où l'on doit analyser une table par rapport à elle-même. Par exemple, étudier les relations hiérarchiques dans une table d'employés. Je vais étudier un autre cas, dénombrer les chemins à trois pages d'un même internaute dans un site. Cet article trouve son origine dans une réponse que j'ai faite sur un forum de discussion.

Nous allons d'abord décrire nos objectifs, ceux-ci nous dicterons les informations que nous avons besoin de stocker et nous terminerons par la requête complète.

Objectifs

Nous allons étudier les parcours d'un internaute sur un site. Les études classiques portent sur des comptages bruts : nombre de pages vues, nombre de visites à une page etc..

Nous avons les traces des visites des internautes sur un site, et nous voulons étudier la fréquence des différents parcours : quel est le nombre de parcours "page2, page5, page1'. Ici c'est un parcours sur trois pages, on peut étudier plus mais nous verrons que cela devient vite lourd à gérer.

Nous allons procéder par étapes :

• Définir les informations dont nous avons besoin ;
• Expliciter l'algorithme de requêtes et écrire la requête ;
• Améliorations possibles.

Définir les informations dont nous avons besoin

Nous devons bien sûr stocker les informations des visiteurs sur chaque page : nom de la page vue.

Afin de pouvoir dire que avant la pageX, le visiteur a vu la pageY, nous devons donc aussi enregistrer la page précédente, c'est la REFERER.

Pour être sûr que c'est le même internaute, on va enregistré son IP. Si l'on veut ête rigoureux, ce n'est pas suffisant ; en effet si cette IP est venue hier voir pageY puis pageW, et qu'elle revient voir pageW puis pageX, on ne peut pas dire que le parcours pageY->pageW->pageX ait été réalisé. Et pourtant, nous verrons que cela est le cas.

Un parcours est effectué suivant un ordre chronologique. Il faut que le passage de pageY->pageW soit antérieur à celui de pageW->pageX pour avoir le parcours pageY->pageW->pageX.

Si tel n'est pas le cas nous pourrons obtenir ce parcours avec des traces comme celles-ci pageW->pageX - pageX->page1 - page1->pageY et pageY->pageW. Nous y reviendrons après avoir écrit la requête.

Notre table de nom 'trace' contient donc (au moins) les informations suivantes:

Nous allons chercher les couples de lignes de la table tels que la 'pageApres' de la première soit la 'pageAvant' de la seconde. Nous allons donc jointurer notre table sur elle-même, pour réaliser cela nous devons utiliser les alias :

FROM trace AS tAvant, trace AS tApres

la clause de jointure est le champ IP :

WHERE tAvant.IP = tApres.IP

La traduction de l'ordre chronologique est une clause WHERE supplémentaire :

tAvant.idTrace < tApres.idTrace

N'oublions la clause sur la recherche de parcours :

tAvant.pageApres = tApres.pageAvant

Nous voulons compter les parcours, nous allons donc grouper les lignes sur de tels chemins :

GROUP BY tAvant.pageAvant, tAvant.pageApres, tApres.pageApres

Et enfin la clause SELECT avec le comptage et l'affichage du chemin :

SELECT count(*), tAvant.pageAvant, tAvant.pageApres, tApres.pageApres

Ce qui donne la requête suivante :

SELECT count(*), tAvant.pageAvant, tAvant.pageApres, tApres.pageApres
    FROM trace AS tAvant, trace AS tApres
    WHERE tAvant.IP = tApres.IP
          AND tAvant.idTrace < tApres.idTrace
          AND tAvant.pageApres = tApres.pageAvant
    GROUP BY tAvant.pageAvant, tAvant.pageApres, tApres.pageApres

Améliorations

Si un internaute effectue ce parcours en deux jours, c'est à dire les deux premières pages à J-1 et les deux suivantes à J, on ne peut pas dire que cela soit un vrai parcours et pourtant nous allons le comptabiliser comme tel. Plutôt que l'IP, il faudrait travailler avec des sessions.

Mais même avec cela, nous compterons les pseudo parcours au milieu desquels l'internaute aura fait une boucle : pageY->pageW->oage1->page2->PageW->pageX, les conditions de paragrpahe précédent sont toutes remplies et pourtant notre visiteur n'a pas fait le parcours direct !

Il faut aussi faire attention aux pages de tAvant.idAvant qui ne sont pas dans le site, c'est à dire que l'utilisateur vient d'ailleurs, ces enregistrements là faussent tout car alors ce ne sont plus des parcours à trois pages dans le site mais à deux simplement. C'est facile à régler, il suffit de mémoriser le nom de domaine des pages et d'ajouter que le domaine de toutes les pages manipulées est celui du site qui nous intéresse.

Conclusion

On arrive vite à des requêtes pas trop complexes mais embrouillées si l'on n'y prend pas garde. Vous imaginez maintenant ce que cela donne pour le comptage des visites à 4 pages ;-))

<<< Lire un autre article sur les jointures

Daniel Lucazeau
ajornet.com
Chef de projet Internet