Como montar essa consulta

[bigue 0]

Olá,

 

tenho que fazer uma consulta em uma tabela que possui atualmente cerca de 250 mil registros (usuários) com a seguinte situação.

 

Quero trazer os 10 usuários com a maior média nas notas.

 

Tenho uma tabela usuario e uma avaliacao.

 

Na tabela usuario quero trazer os valores do id_usuario e nome_completo.

 

Na tabela avaliacao tenho notas de usuários, comentários, etc. Identifico o registro pelo campo id_tabela e tabela.

 

Já tentei da seguinte forma:

 

SELECT u.id_usuario, u.nome_completo, AVG(a.nota) AS media

FROM usuario u

LEFT JOIN avaliacao a ON a.tabela = 'usuario' AND a.id_tabela = u.id_usuario

WHERE u.status = 1

GROUP BY u.id_usuario

ORDER BY media DESC

LIMIT 0, 10

 

---

 

Outra coisa que tentei foi criar uma VIEW com essas 3 informações e quando eu preciso faço um select simples:

 

SELECT id_usuario, nome_completo, media

FROM usuario_view

ORDER BY media DESC

LIMIT 0, 10

 

---

 

Das duas formas está demorando muito para me retornar.

Alguém poderia me dizer se tenho outra forma de fazer isso ou otimizar a maneira como eu fiz.

 

Obrigado!

[Motta 645]

avaliacao tem índice por id_tabela ?

 

id_tabela é a a fk para usuario.id_usuario ?!

[bigue 0]

O id_tabela não é fk para id_usuario pq nessa tabela de avaliação pode entrar nota de diversos itens (tabelas diferentes), mais criei um indice para ele.

 

Exemplo:

 

id_avaliacao | id_tabela | tabela | nota

-----------------------------------------------

1 | 1 | usuario | 7

2 | 1 | comentario | 8

3 | 1 | usuario | 4

 

 

Usuário 1 recebeu nota 7 e nota 4.

Comentário 1 recebeu nota 8.

[Motta 645]

tente :

 

SELECT u.id_usuario, u.nome_completo, AVG(a.nota) AS media
FROM usuario u
LEFT JOIN avaliacao a ON a.tabela = 'usuario' AND a.id_tabela = u.id_usuario
WHERE u.status = 1
AND U.TABELA = 'usuario'
GROUP BY u.id_usuario
ORDER BY media DESC
LIMIT 0, 10

[bigue 0]

SELECT u.id_usuario, u.nome_completo, AVG(a.nota) AS media

FROM usuario u

LEFT JOIN avaliacao a ON a.tabela = 'usuario' AND a.id_tabela = u.id_usuario

WHERE u.status = 1

AND U.TABELA = 'usuario'

GROUP BY u.id_usuario

ORDER BY media DESC

LIMIT 0, 10

 

Como a tabela usuario u não possui o campo tabela, imaginei que você quis dizer para colocar a.tabela = 'usuario' no where também.

 

A query executou bem rápido, porém só trouxe 2 usuários que são os que tem nota na minha simulação, não os 10 que eu preciso.

 

Nesse caso precisaria que ele retornasse outros 8 usuários sem nota ordenados por nome por exemplo, para completar os 10.

[Motta 645]

Terá de usar a opção OUTER JOIN tratando os nulos.

 

A síntaxe do outer eu nunca lembro pois no Oracle é diferente, e a função que trata os nulos creio ser a COASLECE.