Início / dba / Perguntas / 339565
Accepted
Bobby Redjeans
Asked: 2024-05-16 17:17:21 +0800 CST

MYSQL seleciona registros semelhantes classificados por campo de outra tabela

  • 772

eu tenho essas tabelas

pontuações_do_filme

eu ia filme_id ID do usuário pontuação
1 1 1 10
2 2 1 5

filmes

eu ia nome coleção
1 Interestelar 1
2 O Cavaleiro das Trevas 2
3 O brilho 1
4 Forrest Gump 2

Aqui sabemos que user_id 1 avaliou Interestelar e Cavaleiro das Trevas com 10 e 5 apropriadamente. Sabendo disso posso recomendar a este usuário 2 filmes - O Iluminado e Forrest Gump porque eles têm uma coleção semelhante aos dois primeiros filmes. Mas como ele avaliou Interestelar acima de O Cavaleiro das Trevas, posso adivinhar que ele gosta mais da coleção 1 do que da coleção 2 .

Então a ordem deveria ser:

  1. The Shining (mesma coleção de Interestelar que o usuário avaliou como 10)
  2. Forrest Gump (mesma coleção do Cavaleiro das Trevas que o usuário avaliou como 5)
mysql

1 respostas

  1. Best Answer
    SELECT name
FROM movies
JOIN (  -- subquery calculates average score for each collection, may use MAX instead
  SELECT movies.collection, AVG(movie_scores.score) avg_score
  FROM movie_scores
  JOIN movies ON movie_scores.movie_id = movies.id
             AND user_id = 1
  GROUP BY movies.collection
  ) avg_scores USING (collection)
WHERE NOT EXISTS (  -- subquery checks that the film is not scored yet
  SELECT NULL
  FROM movie_scores
  WHERE movie_scores.movie_id = movies.id
    AND user_id = 1
  )
ORDER BY avg_scores.avg_score DESC

    violino

    PS. A consulta não retornará os filmes se não houver nenhum filme pontuado na coleção. Para devolvê-los você usaria LEFT JOIN. Para esses filmes, a pontuação média será definida como NULL - você pode colocá-los no início ou no final da saída com a expressão de classificação adicional correspondente.

    • 1

relate perguntas