Mysql erhält die 5 besten Ergebnisse pro Gruppe bei innerer Verknüpfung

Question

Ich habe in der Vergangenheit Websites mit komplexen Abfragen erstellt (für mich jedenfalls), aber ich habe beschlossen, dass ich aufgrund meiner Art zu programmieren die erforderlichen Datenbankabfragen erstellen sollte, bevor ich meine Website aufbaue. Auf diese Weise kann ich Umstrukturierungen usw. vermeiden, aber ich habe nicht die tatsächlichen Daten, mit denen ich spielen kann. Auf meiner Website wird also ein Benutzer einen Beitrag schreiben. Dieser Beitrag kann dann von jedem anderen Benutzer erneut gepostet werden (ich nenne das einen "TRICKLE"). Ich muss die 5 besten Beiträge jedes Tags ermitteln, je nachdem, wie oft der Beitrag erneut gepostet wurde. Wenn also "Baseball" das Schlagwort war und 3000 Benutzer einen Beitrag mit diesem Schlagwort hatten, muss ich die 5 besten Beiträge ermitteln, die erneut gepostet wurden. Struktur:

POSTS
-post_id
-member_id
-tag
-title
-post
-date_posted
-is_active

TRICKLES
-post_id
-member_id
-date_trickled

Ich habe diese Abfrage, die ich denke, ist entlang der Linien ..

select p.post_id, p.tag, p.title, count(p.post_id) from (
select * from posts p inner join
trickles t on t.post_id=p.post_id
group by p.tag order by count(p.post_id) desc limit 5
) order by p.tag asc

Für mich bedeutet dies. Zuerst (innere Abfrage) alle Beiträge und ihre zugehörigen Rinnsale (Re-Posts) holen und gruppiere sie nach ihrem Tag und ordne sie dann nach der höchsten Anzahl pro Beitrag und begrenze sie auf 5. Dann die ID, den Tag, den Titel, die Anzahl (die Gesamtzahl der Beiträge, die getriggert wurden) und ordne sie alphabetisch nach Tag

Ich denke, dass ich so die Top 5 pro Gruppe in der Datenbank erhalte, aber es ist schwierig, dies ohne die tatsächlichen Daten herauszufinden, und das werde ich auch nicht, bis die Leute beitreten und anfangen zu posten. Jedes Mal, wenn ich anfange, gründlich darüber nachzudenken, habe ich das Gefühl, dass mein Verstand durcheinander gerät. Kann ich mit dieser Abfrage die Top 5 der Wiederveröffentlichungen für jedes gängige "Tag" in der Datenbank ermitteln? Ich danke Ihnen im Voraus!

Answer 1

Ich nehme an, dieser Trick mit der Aufzählung der Zeilen wird Ihnen helfen. Es ist ein wenig kompliziert, aber sollte funktionieren:

select 
    j.tag,
    j.post_id,
    j.title,
    j.cnt
from (
            select
                case when @b <> i.tag then (select @a := 0) end tmp1,
                case when @b <> i.tag then (select @b := i.tag) end tmp2,
                (select @a := @a + 1) num,
                i.*
            from (
                        select
                            p.title,
                            p.post_id, 
                            p.tag, 
                            count(*) cnt
                        from posts p
                        left join tickles t on t.post_id = p.post_id
                        group by 
                            p.post_id, p.tag, p.title
                        order by p.tag, count(*) desc
            ) i
            left join (select @a := 0, @b := '') x on 1=1 
) j
where j.num <= 5