Umtitel: Wie sortiere ich eine Tabelle basierend auf der Anzahl der Likes und der Zeitkorrelation in MySQL?

Question

Ich habe ein einfaches Problem: Es gibt zwei MySQL-Tabellen, eine speichert Fotos und die andere speichert Likes. Der Aufbau ist wie folgt:

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

Jetzt ist es ganz einfach, Fotos nach der Anzahl der Likes zu sortieren und die Top 3 zu erhalten. Sie können die folgende Abfrage verwenden:

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;

Aber ich möchte eine zeitliche Korrelation hinzufügen, das heißt, neue Likes haben ein höheres „Gewicht“, auch wenn einige Fotos mehr Likes haben, sind sie älter und werden daher niedriger eingestuft als Likes. Weniger, aber neuere Fotos.

Ist es möglich, dieses Problem nur mit MySQL zu lösen? Oder ist eine zusätzliche Verarbeitung in PHP erforderlich?

Answer 1

考虑一种情况，即 likes。 timestamp 是 UNIXTIME 字段，因此如果我们使用 SUM(timestamp) 而不是计算新的 likes 数量，将会给予更多的权重：

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;

可以使用一些系数（例如 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;