Tajuk semula: Bagaimana untuk mengisih jadual berdasarkan bilangan suka dan korelasi masa dalam MySQL?

Question

Saya mempunyai masalah mudah, terdapat dua jadual mysql, satu menyimpan foto dan kedai lain suka. Strukturnya adalah seperti berikut:

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

Kini mudah untuk mengisih foto mengikut bilangan suka dan mendapatkan 3 teratas, anda boleh menggunakan pertanyaan berikut:

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;

Tetapi saya ingin menambah korelasi temporal, iaitu, suka baharu mempunyai "berat" yang lebih tinggi, walaupun sesetengah foto mempunyai lebih suka, ia lebih lama, jadi ia berada di kedudukan yang lebih rendah daripada suka Foto yang lebih sedikit tetapi lebih baharu.

Adakah mungkin untuk menyelesaikan masalah ini hanya menggunakan MySQL? Atau adakah ia memerlukan pemprosesan tambahan dalam PHP?

Answer 1

Pertimbangkan kes di mana likes。 timestamp ialah medan UNIXTIME, jadi jika kita menggunakan SUM(timestamp) dan bukannya mengira bilangan suka baharu, lebih berat akan diberikan:

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;

Sesetengah pekali boleh digunakan (cth. 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;