Actualites | Forum |Archives
Le magazine des décideurs et webmasters qui gagnent !
Inscription | Livre d'or | Plan du site | 15 visiteurs actifs
   
A la Une
Actualité
Dossiers
Communiqués
Coin Technique
Agenda des salons
Emploi
Echange de liens

Archives
Sélection
Expérience qui parle
Internet quotidien
Tous les dossiers

Forum
Forum SAM-MAG

Guides
Check-list de la promotion des sites
Promouvoir et référencer les sites web

Contact
Nous contacter
Newsletter
La protection des données personnelles


 
Jointure d'une table sur elle-même
Dossier "SAM l'Informaticien" du 10/09 au 23 Septembre 2001 par Daniel Lucazeau

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:

idTrace
IP
pageAvant
pageApres

Identifiant unique, en auto_increment, par exemple. On pourrait mettre la date et heure. C'est ce champ qui gère la chronologie

Comme son nom l'indique. Ce peut-être aussi le nom du HOST.

Nom de la page REFERER

Page vue actuellement

 

Expliciter l'algorithme de requêtes

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

 

 
 
Google
 
Web www.sam-mag.com
 

Copyright © ACORUS 2004. All Rights Reserved

- Sam-Mag.com Referencement-Sur-mesure - Referencer-Site-Web.com
Visibilite-Internationale.com - Referencement-Immobilier.net