Discussion :

[Good Bear]

Bonjour, voici mon problème.

Dans une base de données (SQL Server 2005) se trouve une table Visites servant à comptabiliser les visites sur un site web. Cette table contient les colonnes suivantes:
ID - format INT (identifiant unique auto incrémenté)
LogDate - format DATETIME (date de la visite)
IDWebPage - format INT (numéro unique de la page web visitée, les pages web sont recensées dans une autre table mais seul l'ID nous intéresse ici)
IPAddress - format NVARCHAR(15) (adresse IP du visiteur pour distinguer les visites uniques)

Je souhaite compter les visites par mois sur chacune des 3 pages web numérotées 1, 2 et 3 puis remplir un graphique grâce aux valeurs retournées.

Pour ce faire mes requêtes de comptage sont les suivantes:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT DATENAME(MONTH,LogDate) AS Mois, 
COUNT(IPAddress) AS [Visites page 1] 
FROM OFFVisitStats 
WHERE IDWebPage=1 
GROUP BY DATENAME(MONTH,LogDate)

puis la même chose avec IDWebPage=2 puis 3 pour les 2 autres pages.

Le problème est le suivant:

Pour remplir mon graphique, chacune des 3 requêtes dois impérativement retourner le même nombre de lignes.
Mais en admettant que la page 1 n'ai pas été visitée en février, la page 2 n'ai pas été visitée en février, mars et juillet et la page 3 n'ai pas été visitée en avril et octobre, je vais obtenir seulement 11 lignes pour la page 1, 9 pour la page 2 et 10 pour la page 3.

Hors je souhaiterais toujours obtenir 12 lignes (dans le même ordre) avec un comptage de 0 si aucune visite n'a été effectuée pour un mois donné.

Donc pour la requête de la page 1, au lieu de:
Janvier - 28
Mars - 12
Avril - 47
etc.

je souhaiterais:
Janvier - 28
Février - 0
Mars - 12
Avril - 47
etc.

J'espère avoir été clair.

[SQLpro]

SQL ne saurait pas inventer des données qui n'existe pas.

Pour résoudre ce genre de requête il faut ajouter à votre modèle un table de calendrier comme je l'ai indiqué dans cet article :
http://sqlpro.developpez.com/cours/gestiontemps/
et faire une jointure externe droite sur An/mois ce qui vous donnera tous les mois de toutes les années.

A +

[Good Bear]

Tout d'abord merci pour votre réponse.
Sachez que votre (immense) article "Le SQL de A à Z" est mon premier lien de référence sur SQL et que sa lecture m'a maintes fois permis de me débloquer et que j'ai grâce à lui appris la majorité de ce que j'utilise aujourd'hui en SQL.
Je suis d'ailleurs très agréablement surpris que vous preniez personnellement le temps de répondre aux questions de ce forum

Pour en revenir à nos moutons, j'avais espéré pouvoir me passer d'une table annexe et effectivement pensé à une jointure que j'avais essayé de faire sur la table elle même genre ceci (qui ne fonctionne absolument pas car le COUNT compte plusieurs fois chaque valeur et les mois sans visites ne génèrent toujours pas de ligne)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT DATENAME(MONTH,A.LogDate) AS Mois,
COUNT(A.IPAddress) AS [Visites]
FROM Visites A
RIGHT OUTER JOIN Visites B
ON DATENAME(MONTH,A.LogDate) = DATENAME(MONTH,B.LogDate)
WHERE A.IDWebPage = 1
GROUP BY DATENAME(MONTH,A.LogDate)

J'ai donc créé selon vos conseil une table supplémentaire nommée Mois contenant les champs:
ID - format INT (identifiant unique auto incrémenté)
NomDuMois - format NVARCHAR(10) (nom du mois)
et 12 lignes (1 - Janvier ... 12 - Décembre)

La requête avec jointure sur cette table est:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT NomDuMois,
COUNT(IPAddress) AS [Visites page 1]
FROM Visites
RIGHT OUTER JOIN Mois
ON DATEPART(MONTH,Visites.LogDate) = Mois.ID
WHERE IDWebPage = 1
GROUP BY NomDuMois

mais j'ai du me tromper quelque part car le résultat est le même que si je fais

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT DATENAME(MONTH,LogDate) AS Mois, 
COUNT(IPAddress) AS [Visites page 1] 
FROM OFFVisitStats 
WHERE IDWebPage=1 
GROUP BY DATENAME(MONTH,LogDate)

à savoir que pour les mois où aucune visite n'est enregistrée aucune ligne n'est générée.

Mon raisonnement est probablement faux mais je ne trouve pas où...

[Waldar]

L'ID de votre nouvelle table est-il significatif ?
Car vous l'utilisez pour joindre votre table Mois et votre table Visites.

[Good Bear]

Oui, l'ID de la table Mois correspond au numéro de chaque mois et prend les valeurs 1 à 12.

Ceci étant et après plusieurs tests, je me suis aperçu que la requête avec jointure sur cette nouvelle table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT NomDuMois,
COUNT(IPAddress) AS [Visites page 1]
FROM Visites
RIGHT OUTER JOIN Mois
ON DATEPART(MONTH,Visites.LogDate) = Mois.ID
WHERE IDWebPage = 1
GROUP BY NomDuMois

donne le résultat attendu en enlevant la clause WHERE.
Je récupère alors bien 12 lignes (1 par mois) avec le comptage des visites pour chaque mois et 0 lorsqu'aucune visite n'est comptabilisée.

Malheureusement je perds la possibilité de filtrer la page sur laquelle se font ces visites.
Le résultat n'est pas meilleur si je transforme la clause WHERE en HAVING.

Je pense que l'on s'approche du résultat mais j'ai encore besoin d'aide pour aboutir...

[SQLpro]

Il faut impérativement une table MoiS/AN car un mois tout seul ne veut rien dire... SI je vous donne, rendez-vous en septembre, s'agit-il de septembre 2009, 2010, 2597 ?

Autrement dit votre table doit être :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
CREATE TABLE T_MOISAN_MSA
(MSA_AN    SMALLINT NOT NULL CHECK(MSA_AN BETWEEN 2000 AND 9999),
 MSA_MOIS SMALLINT NOT NULL CHECK(MSA_AN BETWEEN 1 AND 12,
 CONSTRAINT PK_MSQ PRIMARY KEY (MSA_AN, MSA_MOIS);

En la remplissant de tous les mois depuis le janvier 2000 jusqu'à décembre 9999

A +

[Waldar]

Que donne la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT M.NomDuMois, COUNT(V.IPAddress) AS [Visites page 1]
FROM Mois AS M LEFT OUTER JOIN Visites AS V
    ON DATEPART(MONTH,V.LogDate) = M.ID
   AND V.IDWebPage = 1
GROUP BY M.NomDuMois

[Good Bear]

Merci à tous les 2 pour vos réponses rapides.
Le problème est résolu

SQLPro, merci pour cette méthode de création de table de dates, je vais l'utiliser. Et merci encore pour tous ces cours et ces articles en ligne sur Développez.com, c'est une mine d'informations extrêmement bien faite et utile.

Waldar, merci pour la dernière requête, c'est effectivement une solution qui fonctionne !

En fait le problème de ma dernière requête venait de la clause WHERE, il suffisait de remplacer le WHERE par un AND suite à la jointure. Je ne savais pas que cette syntaxe existait.

Pour résumer et éclairer ceux qui auront le même problème que moi voici donc 2 solutions au problème et retournant le même résultat soit une ligne par mois (triées dans l'ordre des 12 mois de l'année) et le chiffre 0 si aucune visite n'est enregistrée pour un mois donné.

Première solution en jointure droite:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT M.NomDuMois, 
COUNT(V.IPAddress) AS [Visites page 1]  
FROM Visites AS V 
RIGHT OUTER JOIN Mois AS M 
ON DATEPART(MONTH,V.LogDate) = M.ID 
AND V.IDWebPage=1 
GROUP BY M.NomDuMois, M.ID 
ORDER BY M.ID

Deuxième solution en jointure gauche:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT M.NomDuMois, 
COUNT(V.IPAddress) AS [Visites page 1]
FROM Mois AS M 
LEFT OUTER JOIN Visites AS V
ON DATEPART(MONTH,V.LogDate) = M.ID
AND V.IDWebPage = 1
GROUP BY M.NomDuMois, M.ID
ORDER BY M.ID

Encore merci à Waldar et SQLPro