Re-titre : Comment trier un tableau en fonction du nombre de likes et de la corrélation temporelle dans MySQL ?

Question

J'ai un problème simple, il y a deux tables MySQL, l'une stocke les photos et l'autre les likes. La structure est la suivante :

表 `photos` - id, name, image_id
表 `likes` - id_photos, timestamp

Il est désormais facile de trier les photos par nombre de likes et d'obtenir le top 3, vous pouvez utiliser la requête suivante :

SELECT photos.*
    , COUNT(likes.id) AS likes_count 
 FROM photos 
 LEFT JOIN likes ON photos.id = likes.id_photos 
 GROUP BY photos.id 
 ORDER BY likes_count DESC LIMIT 3;

Mais je veux ajouter une corrélation temporelle, c'est-à-dire que les nouveaux likes ont un "poids" plus élevé, même si certaines photos ont plus de likes, elles sont plus anciennes, donc elles sont classées plus bas que les likes. Moins de photos mais plus récentes.

Est-il possible de résoudre ce problème en utilisant uniquement MySQL ? Ou cela nécessite-t-il un traitement supplémentaire en PHP ?

Answer 1

Considérons un cas où likes。 timestamp est un champ UNIXTIME, donc si nous utilisons SUM(timestamp) au lieu de compter le nouveau nombre de likes, plus de poids sera attribué :

SELECT photos.*
    , COUNT(likes.id) AS likes_count 
    , SUM(likes.timestamp) AS likes_weight 
 FROM photos 
 LEFT JOIN likes ON photos.id = likes.id_photos 
 GROUP BY photos.id 
 ORDER BY likes_weight DESC LIMIT 3;

Certains coefficients peuvent être utilisés (par exemple UNIX_TIMESTAMP())

SELECT photos.*
    , COUNT(likes.id) AS likes_count 
    , SUM(likes.timestamp)/UNIX_TIMESTAMP() AS likes_weight 
 FROM photos 
 LEFT JOIN likes ON photos.id = likes.id_photos 
 GROUP BY photos.id 
 ORDER BY likes_weight DESC LIMIT 3;