[SQL] Top 5 items berekenen

[Avalaxy]

Ik ben een beetje aan het stoeien met een query... Wat ik wil is eigenlijk heel simpel: ik wil de top 5 items weergeven... Echter moet ik hiervoor een rekensommetje doen die alles in de soep gooit.

Ik heb de volgende tabellen (die relevant zijn):



Wat ik precies wil: de titel, id en het saldo van de votes van de top5 items van de afgelopen 24 uur. Die votes moeten dus berekend worden door plusvotes - minvotes te doen. Een plusvote wordt in last_vote_type aangegeven door '0' en een minvote wordt in last_vote_type aangegeven door '1' (vraag me niet waarom het niet andersom is...).

Ik ben dus een aantal queries gaan bakken, maar geen daarvan doet wat ik wil. De volgende query bijvoorbeeld werkt alleen als ik een post_id opgeef... Als ik de post_id weghaal voegt ie alle resultaten samen waardoor ik de post_title van het populairste artikel krijg met een COUNT van alle votes van alle items die er zijn. Ik heb dit eventjes opgelost door de som van het aantal negatieve votes van het aantal positieve votes af te halen:

SQL:

SELECT `post_id`, COUNT(`last_vote_type`) - 
    (SELECT SUM(`last_vote_type`) 
    FROM `wp_votes_check` 
    WHERE `post_id`=1252 AND `last_vote` > 1298136966) AS `count`
FROM `wp_votes_check` 
WHERE `post_id`=1252 AND `last_vote_type`=0 AND `last_vote` > 1298136966

last_vote is hier een UNIX timestamp, die wordt normaal gesproken dynamisch berekend met PHP.

Zonder die berekening van (plusvotes - minvotes) zou het heel makkelijk zijn, want dan is het gewoon een simpele JOIN.

[krvabo]

Als je het hebt over een 'top 5', dan denk ik al snel aan 'Limit' en 'Order By'.
Waarom zie ik dat niet terug in jouw query? (Stille hint)

Protip: je kunt ook berekeningen doen in 'Order By'.

[RobIII]

SQL:

select post_id, title, sum(case when last_vote_type = 0 then 1 else -1 end) as total
from ...
where ...
group by post_id, title
order by total desc
limit 0,5

[ Voor 11% gewijzigd door RobIII op 20-02-2011 19:31 ]

[Avalaxy]

Ah, dat case kende ik niet. Ik ga het eens uitproberen, dat zou wel een oplossing zijn.

krvabo schreef op zondag 20 februari 2011 @ 19:29:
Als je het hebt over een 'top 5', dan denk ik al snel aan 'Limit' en 'Order By'.
Waarom zie ik dat niet terug in jouw query? (Stille hint)

Dat heb ik er uitgesloopt om de query te vereenvoudigen.

Edit: volgens mij is het nu gelukt:

SQL:

SELECT wp_posts.post_title AS `title`, wp_votes_check.`post_id`, SUM(CASE WHEN wp_votes_check.last_vote_type = 0 THEN 1 ELSE -1 END) AS `total`
FROM `wp_votes_check` 
JOIN wp_posts
ON wp_votes_check.post_id = wp_posts.ID
WHERE `last_vote` > 1298139925
GROUP BY wp_votes_check.post_id
ORDER BY total DESC
LIMIT 0,5

[ Voor 95% gewijzigd door Avalaxy op 20-02-2011 19:40 ]