Discussion :

[Kijer]

Bonjour,

Je bosse actuellement sur un site de vidéos où chaque vidéo incrémente son compteur de vues à chaque visite unique de cette page.

Je souhaite faire une page TOP100 par jour / semaine / mois /année et detouslestemps.

Ce même classement pourra être toutes catégories confondues ou sur une catégorie précise.

Je n'ai jamais implémenté de système de classement et n'est donc pour l'instant qu'un simple champ "nbre_vues" en INT sur ma table videos.

Vous, par expérience, quelle structure mettriez-vous en place pour réussir cette mise en place ?

C'est à dire :
- le TOP 100 par période
- Et que je puisse à tout moment afficher le classement global de la vidéo sur sa fiche.


Merci d'avance.

[rawsrc]

Salut

Le plus simple, serait que tu horodates l'incrémentation du compteur de vues pour chaque vidéo

[Kijer]

Merci de ta réponse.
Donc ce qui sous entend une table "videos_vues" ?

avec

id INT
video_id INT
date DATETIME

c'est ça ?

Et une REQ du genre :

Code sql :

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

SELECT videos.*, COUNT(vues.video_id) AS nbre_vues FROM videos JOIN videos_vues vues ON vues.video_id = videos.id ORDER BY nbre_vues LIMIT 100

?

[rawsrc]

Oui.
Par contre n'utilise pas une colonne nommée date, c'est généralement un mot réservé par le moteur SGBD.
Evite le SELECT * FROM, liste plutôt les colonnes.
Après à toi de jouer sur les périodes pour parvenir à tes fins.

[Kijer]

Ok niquel.
Pour la requête, j'ai effectivement raccourci mon écriture pour l'exemple.

Me reste 2 questions :

- Si ya 1000,10000,100 000, 1 000 000 de pages vues, ca risque pas de réduire considérablement le calcul/chargement de la page/site ?
- Et comment faire "Cette vidéo est classé x/123 dans la cat x, et x/1452 sur tout le site".
Merci !

EDIT : J'ai peut-être un semblant de solution, au lieu de faire une insertion par page vue, rajouter plutot un champ "vues" sur cette table, et il n'y aura que 365 entrées/page par an. Champ que j''update ou ajoute si le jour est différent.

[rawsrc]

Une table avec 1 million d'enregistrements du style id | id_video | datetime c'est ridicule avec les index qui vont bien.

Je ne pense pas que ton approche d'associer un calendrier vide à chaque vidéo pour chaque année soit tip top. Il n'y a aucun intérêt à réserver de l'espace pour des données qui ne seront peut-être jamais renseignées (une semaine sans vues par exemple).
Je procéderai autrement : pour éviter de tout recalculer (et si vraiment tu constates un ralentissement significatif), je créerai une table de stats standard pour les périodes passées avec le cumul des compteurs.
Ensuite toujours pour améliorer les performances, tu pourrais aussi purger régulièrement dans une autre table les anciennes données de la table courante.

Après, ne connaissant pas quel SGBD tu utilises ni le format des tables, je te dirais que COUNT(*) est généralement très lent. Parfois, en passant par un simple SUM() tu pourrais y gagner en célérité.

Mets d'abord en place ce que tu souhaites faire et penches toi ensuite à l'usage sur les optimisations.
Pour les aspects dynamiques des stats, je te conseille très fortement de les coder sous forme de procédure/fonction stockée et de profiter ainsi des divers optimisations que t'apporte un SGBD (tables temporaires, chargement en mémoire vive, compilation...).

[Kijer]

Merci pour ta réponse détaillée.

De mon coté j'ai pas mal avancé dans l'aprem et j'ai testé l'idée d'avoir une table videos_vues contenant video_id, date_ajout, nbre_vues

Ca marche tip top, j'ai mon TOP 100 pour n'importe quelle période que je souhaite grace aux fonctions natives mySQL (oui c'est mon SGBD).

Je n'ai qu'un seul frein restant, c'est la fameuse position dans le classement pour tel ou tel vidéo.

J'ai trouvé un peu partout de partir sur ce genre de requête :

Code sql :

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

1
2
SELECT count(*) as position FROM table t1 
WHERE t1.points > (SELECT points FROM table t2 WHERE t2.user = xxxx)

Cf : http://www.developpez.net/forums/d67...t/#post3964708

Ma seule nuance est que j'ai plusieurs fois le même video_id (avec la date qui diffère), j'ai donc besoin de faire un SUM(). C'est ce que j'ai fait mais le résultat est incorrect quand je le compare au top 100.

Voila ma requête actuelle :

Code sql :

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

1
2
3
4
SELECT COUNT(*) FROM `videos_vues` 
WHERE `video_id` <> 7 
GROUP BY `video_id` 
HAVING SUM(nbre_vues) >= 91

Le 91 correspond au résultat d'un SUM(nbre_vues) de la video 7.
Et j'ai du mettre un HAVING car j'avais un message d'erreur si j'utilisai WHERE SUM(nbre_vues) >=

Je ne sais donc pas si ma requête est bonne (ce que je ne pense pas puisque je n'ai pas le bon résultat).

Merci encore, et en espérant avoir été clair.

[rawsrc]

Est-ce qu'une vidéo peut faire partie de plusieurs catégories ?

Après je ne comprends pas trop l'intérêt de ta requête...
Tu ne peux pas faire de COUNT() dans la mesure où tu additionnes déjà les vues dans nbre_vues, c'est un non-sens.

En partant avec deux tables simples et en considérant qu'une vidéo puisse avoir plusieurs catégories :

t_video_categ (#id, categ, #video_id)
t_video_vue   (#id, #video_id, date_ajout, nbre_vues)

une requête du genre devrait faire tes classements

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 
  `t_video_categ`.`categ`,
  `t_video_categ`.`video_id`,
  SUM(`t_video_vue`.`nbre_vues`) AS `total_vues`
FROM
  `t_video_vue`
  INNER JOIN `t_video_categ` ON (`t_video_vue`.`video_id` = `t_video_categ`.`video_id`)
WHERE
  `t_video_vue`.`date_ajout` BETWEEN '2013-01-01' AND '2013-01-05'
GROUP BY
  `t_video_categ`.`categ`,
  `t_video_categ`.`video_id`
ORDER BY
  `t_video_categ`.`categ`,
  `total_vues` DESC

[Kijer]

Est-ce qu'une vidéo peut faire partie de plusieurs catégories ?

Non, le category_id est sur la table videos

Après je ne comprends pas trop l'intérêt de ta requête...
Tu ne peux pas faire de COUNT() dans la mesure où tu additionnes déjà les vues dans nbre_vues, c'est un non-sens.

Il ne me semble pas qu'il s'agisse d'un non sens puisque que j'ai cette structure de donnée :


Je suis donc obligé d'additionner tous les résultats d'un même video_id sur les dates correspondantes (pour le SUM) et le COUNT() pour compter cb de vidéos ont plus de vues, si ca répond 1, je suis donc en seconde position. L'idée était là, mais c'est pas forcément efficace.