par
Hubert Roksor » 09 mars 2007, 21:55
J'espere que c 'est clair, pour moi mon raisonnement sur papier l'est mais le retranscrire en sql c'est autre chose ^^
J'ai relu ton message, et si je comprends bien tu cherches à évaluer l'affinité entre les tags. Pour ce faire, tu récupères les identifiants des tous les articles taggés "musique" et tu établis un classement des autres tags.
Récupérer tous les articles taggés "musique"
Code : Tout sélectionner
SELECT article_id
FROM table_normalisee
WHERE tag = 'musique'
On réutilise la requête du précédent message, en ajoutant un WHERE pour la limiter aux articles dont on a précédemment récupéré les identificateurs. (pour ma table de test ce sont les articles 1,2,3,4,5,6 et 7)
Code : Tout sélectionner
SELECT COUNT(*) AS total, tag
FROM table_normalisee
WHERE article_id IN (1,2,3,4,5,6,7)
AND tag <> 'musique'
GROUP BY tag
ORDER BY total DESC
Maintenant, si tu as beaucoup d'articles tu n'auras peut-être pas envie d'avoir une clause WHERE qui fait dix lignes, donc tu peux aussi chercher d'autres solutions basées sur une jointure. Donc je rappelle l'algorithme "pour chaque article associé au tag 'musique' compte le tags associé au même article et je groupe les résultat tag par tag". En SQL ça ce traduit par "je cherche dans la table 1 tous les articles taggés "musique", je joins la table 2 article par article et je compte les tags de la table 2 à condition que ce ne soit pas le même tag" (sinon le tag le plus souvent associé à "musique" serait... "musique")
Code : Tout sélectionner
SELECT COUNT(*) AS total, t2.tag
FROM table_normalisee t1
JOIN table_normalisee t2 USING (article_id)
WHERE t1.tag = 'musique'
AND t2.tag <> t1.tag
GROUP BY t2.tag
ORDER BY total DESC
Si tu as tout suivi, cette requête remplace les deux premières. Et je sais que si tu crées un index PRIMARY sur les colonnes (article_id, tag) ainsi qu'un index simple sur (tag) elle est très performante. Évidemment, ce n'est valable que si tu normalises tes données.
En dernier, pour la postérité voici donnat le même résultat pour ta table pas normalisée. Attention, les performances sont
astronomiquement mauvaises.
Code : Tout sélectionner
SELECT COUNT(*) AS total, tag
FROM (
SELECT TAG1 AS tag FROM table_pas_normalisee WHERE TAG1 > '' AND 'musique' IN (TAG2, TAG3, TAG4, TAG5, TAG6)
UNION ALL
SELECT TAG2 AS tag FROM table_pas_normalisee WHERE TAG2 > '' AND 'musique' IN (TAG1, TAG3, TAG4, TAG5, TAG6)
UNION ALL
SELECT TAG3 AS tag FROM table_pas_normalisee WHERE TAG3 > '' AND 'musique' IN (TAG1, TAG2, TAG4, TAG5, TAG6)
UNION ALL
SELECT TAG4 AS tag FROM table_pas_normalisee WHERE TAG4 > '' AND 'musique' IN (TAG1, TAG2, TAG3, TAG5, TAG6)
UNION ALL
SELECT TAG5 AS tag FROM table_pas_normalisee WHERE TAG5 > '' AND 'musique' IN (TAG1, TAG2, TAG3, TAG4, TAG6)
UNION ALL
SELECT TAG6 AS tag FROM table_pas_normalisee WHERE TAG6 > '' AND 'musique' IN (TAG1, TAG2, TAG3, TAG4, TAG5)
) AS tmp
GROUP BY tag
ORDER BY total DESC
[Edit: en y réfléchissant il y a beaucoup de solutions différentes pour la table pas normalisée, mais ce n'est pas évident de trouver la plus performante. Si je devais faire une comparaison je dirais que c'est comme chercher le meilleur moyen de creuser un tunnel avec une petite cuillère. On ne le fait que lorsqu'on est vraiment vraiment obligé. Cette requête est la troisième itération et ne nécessite de lire la table entière "que" 6 fois d'affilée... la première version la lisait 12 fois d'affilée]
[quote="worm1"]J'espere que c 'est clair, pour moi mon raisonnement sur papier l'est mais le retranscrire en sql c'est autre chose ^^[/quote]
J'ai relu ton message, et si je comprends bien tu cherches à évaluer l'affinité entre les tags. Pour ce faire, tu récupères les identifiants des tous les articles taggés "musique" et tu établis un classement des autres tags.
[b]Récupérer tous les articles taggés "musique"[/b]
[code]SELECT article_id
FROM table_normalisee
WHERE tag = 'musique'[/code]
[b]Récupérer le classement des tags[/b]
On réutilise la requête du précédent message, en ajoutant un WHERE pour la limiter aux articles dont on a précédemment récupéré les identificateurs. (pour ma table de test ce sont les articles 1,2,3,4,5,6 et 7)
[code]SELECT COUNT(*) AS total, tag
FROM table_normalisee
WHERE article_id IN (1,2,3,4,5,6,7)
AND tag <> 'musique'
GROUP BY tag
ORDER BY total DESC[/code]
Maintenant, si tu as beaucoup d'articles tu n'auras peut-être pas envie d'avoir une clause WHERE qui fait dix lignes, donc tu peux aussi chercher d'autres solutions basées sur une jointure. Donc je rappelle l'algorithme "pour chaque article associé au tag 'musique' compte le tags associé au même article et je groupe les résultat tag par tag". En SQL ça ce traduit par "je cherche dans la table 1 tous les articles taggés "musique", je joins la table 2 article par article et je compte les tags de la table 2 à condition que ce ne soit pas le même tag" (sinon le tag le plus souvent associé à "musique" serait... "musique")
[code]SELECT COUNT(*) AS total, t2.tag
FROM table_normalisee t1
JOIN table_normalisee t2 USING (article_id)
WHERE t1.tag = 'musique'
AND t2.tag <> t1.tag
GROUP BY t2.tag
ORDER BY total DESC[/code]
Si tu as tout suivi, cette requête remplace les deux premières. Et je sais que si tu crées un index PRIMARY sur les colonnes (article_id, tag) ainsi qu'un index simple sur (tag) elle est très performante. Évidemment, ce n'est valable que si tu normalises tes données.
En dernier, pour la postérité voici donnat le même résultat pour ta table pas normalisée. Attention, les performances sont [b]astronomiquement mauvaises[/b].
[code]SELECT COUNT(*) AS total, tag
FROM (
SELECT TAG1 AS tag FROM table_pas_normalisee WHERE TAG1 > '' AND 'musique' IN (TAG2, TAG3, TAG4, TAG5, TAG6)
UNION ALL
SELECT TAG2 AS tag FROM table_pas_normalisee WHERE TAG2 > '' AND 'musique' IN (TAG1, TAG3, TAG4, TAG5, TAG6)
UNION ALL
SELECT TAG3 AS tag FROM table_pas_normalisee WHERE TAG3 > '' AND 'musique' IN (TAG1, TAG2, TAG4, TAG5, TAG6)
UNION ALL
SELECT TAG4 AS tag FROM table_pas_normalisee WHERE TAG4 > '' AND 'musique' IN (TAG1, TAG2, TAG3, TAG5, TAG6)
UNION ALL
SELECT TAG5 AS tag FROM table_pas_normalisee WHERE TAG5 > '' AND 'musique' IN (TAG1, TAG2, TAG3, TAG4, TAG6)
UNION ALL
SELECT TAG6 AS tag FROM table_pas_normalisee WHERE TAG6 > '' AND 'musique' IN (TAG1, TAG2, TAG3, TAG4, TAG5)
) AS tmp
GROUP BY tag
ORDER BY total DESC[/code]
[Edit: en y réfléchissant il y a beaucoup de solutions différentes pour la table pas normalisée, mais ce n'est pas évident de trouver la plus performante. Si je devais faire une comparaison je dirais que c'est comme chercher le meilleur moyen de creuser un tunnel avec une petite cuillère. On ne le fait que lorsqu'on est vraiment vraiment obligé. Cette requête est la troisième itération et ne nécessite de lire la table entière "que" 6 fois d'affilée... la première version la lisait 12 fois d'affilée]
