Suchfunktion mit "Gruppierung"

[Matze]

Hi zusammen,

ich habe in einer Tabelle "tab_entries" mehrere Text-Einträge u.a. mit den Spalten "f_hits" (= Anzahl Aufrufe), "f_cat_id" (= zugehörige Kategorie-ID) und "f_status_id" (= ID des Status eines Eintrags wie "erledigt").
In einer weiteren Tabelle "tab_search_index" befindet sich ein Suchindex mit den Spalten "f_word" (= Wort im Text) und "f_entry_id" (= ID des Eintrags, in dem das Wort enthalten ist).

Nun möchte ich nach einem der indizierten Wörter suchen und die zugehörigen Einträge bekommen.
Prinzipiell funktioniert das wie folgt:

SQL-Code:

SELECT e.* FROM tab_entries e
   JOIN tab_search_index i
      ON i.f_entry_id = e.id
   WHERE i.f_word = 'Suchbegriff'

Das nur zum verständlichen Einstieg in die Thematik.
Nun gibt es noch 2 weitere Tabellen "tab_entries_status" (= Status eines Eintrags wie "erledigt", "nicht erledigt") und "tab_categories" (= sämtliche Kategorien, denen Einträge zugeordnet werden können).

Da ich diese Daten auch haben möchte, würde sich die SQL-Abfrage wie folgt erweitern:

SQL-Code:

SELECT e.*, s.f_status_name, c.f_name FROM tab_entries e
   JOIN tab_categories c
      ON c.id = e.f_cat_id
   JOIN tab_search_index i
      ON i.f_entry_id = e.id
   JOIN tab_entries_status s
      ON s.id = e.f_status_id
   WHERE i.f_word = 'Suchbegriff'

Aber das wäre natürlich viel zu einfach so.

Ich hätte gerne die Suchergebnisse gruppiert nach Kategorien und pro Kategorie sollen die 2 am häufigsten aufgerufenen Einträge angezeigt werden. Schön wäre hier eine Lösung über "LIMIT", sodass ich die Anzahl der angezeigten Treffer (hier: 2) leicht verändert kann.
Zusätzlich soll die Anzahl aller Treffer zurückgegeben werden.

Später soll die Ausgabe so aussehen:

Code:

Kategorie 1:
- Treffer mit den häufigsten Aufrufen        erledigt
- Treffer mit den zweithäufigsten Aufrufen   nicht erledigt
Anzahl Treffer in der Kategorie insgesamt: 12

Kategorie 2:
- Treffer mit den häufigsten Aufrufen        erledigt
- Treffer mit den zweithäufigsten Aufrufen   erledigt
Anzahl Treffer in der Kategorie insgesamt: 25

...

Aktuell durchlaufe ich alle Kategorien und führe pro Kategorie zwei SQL-Abfragen aus: Eine ermittelt mir die 2 am häufigsten angeklickten Einträge und die Andere über "COUNT(e.id)" die Anzahl der Treffer.
Beides zusammen in einer Abfrage lieferte bei mir nicht das gewünschte Ergebnis.

Das Ganze soll auch mit mehreren Suchbegriffen funktionieren, also mit ... WHERE i.f_word = 'Suchbegriff1' OR i.f_word = 'Suchbegriff2' Von der Performance sollte das auch bei einigen tausend Einträge nicht zu langsam werden.
Ich könnte mir auch vorstellen, eine Abfrage zu haben und das Ergebnis per PHP zu zerlegen, falls das einfacher ist.
Gut wäre auch, wenn die Abfrage leicht auf andere DBMS übertragen werden kann. Also nicht unbedingt MySQL-spezifische Befehle enthält.

Habt ihr hierfür eine Idee/Lösung?

PS: Die Abfragen habe ich hier hinein getippt. Daher könnten diese evtl. Syntaxfehler enthalten.

Grüße
Matze

[jobo]

Am Ende ist vom Status aber nichts mehr zu sehen, weder als Anzeige noch Einschränkung oder ist der bloße Join die Einschränkung?
Hast Du richtige Tabellendefinition?

[Matze]

Hallo,

doch, den Status lasse ich mit anzeigen. Nur in dem kleinen Auszug habe ich den weg gelassen. Ich ergänze ihn kurz.

[jobo]

also allgemein ist es nicht, da die genutzte Funktion oracle spezifisch ist und ich nicht weiß, wie weit das einem ANSI Standard folgt.

SQL-Code:

select o.*, s.*
      from (select id,
                   f_hits,
                   f_cat_id,
                   f_word,
                   f_status_id,
                   row_number() over(partition by f_cat_id order by f_hits) as TopOf
              from (SELECT e.id, e.f_hits, e.f_cat_id, f_word, e.f_status_id
                      FROM tab_entries e
                      JOIN tab_search_index i
                        ON i.f_entry_id = e.id
                     WHERE i.f_word = 'Stadt' --'Fluss'
                    ) i) o,
           tab_entries_status s
     where o.TopOf < 3
       and s.id = o.f_status_id
     order by o.f_cat_id, o.f_hits desc

Die Einschränkung (Suchwort) habe ich extra "innen" gemacht, was am schnellsten sein dürfte. Ist trotzdem aber mitgeschleift, wäre also view tauglich, die Einschränkung müsste dann aber außen erfolgen.
Restliche Felder kann man m.E. nach belieben außen weglassen oder anfügen /joinen.

Edit: Ich hab vergessen, dass mehrere Suchworte möglich sein sollen.
Wenn ID bzw. SuchwortFeld mitgeschleift werden, ist das natürlich möglich. Geht es um mehrere tausend Einträge, wäre wie gesagt die innere Einschränkung vorteilhaft. Das ginge nur über eine Prozedur/Funktion, wo zunächst das Suchwort gesetzt wird und in der Where Bedinungung dann abgefragt wird.

[Matze]

Hallo,

vielen Dank.
Leider habe ich es noch nicht hinbekommen, das unter MySQL zum Laufen zu bekommen. Gibt es hier MySQL-Gurus, die das übersetzen können?

[mirage228]

Zitat von Matze:

Hallo,

vielen Dank.
Leider habe ich es noch nicht hinbekommen, das unter MySQL zum Laufen zu bekommen. Gibt es hier MySQL-Gurus, die das übersetzen können?

Over und PartitionBy ist schwierig. Diese ganzen OLAP-Erweiterungen (auch sowas wie CUBE) werden von MySQL quasi gar nicht unterstützt.

[Matze]

Hm, wie könnte ich das dann lösen?

[jobo]

Hier sind meine Daten /Ergebnisse. Vielleicht ist es nicht was Du haben wolltest, dann brauchst Du das SQL ja so auch nicht

Code:

SELECT e.id, e.f_hits, e.f_cat_id, f_word, e.f_status_id
  FROM tab_entries e
  JOIN tab_search_index i
    ON i.f_entry_id = e.id

Ergebnis

Code:

ID   F_HITS   F_CAT_ID   F_WORD   F_STATUS_ID
1   20   1   Stadt   1
2   22   3   LAnd   1
1   5   2   Stadt   2
2   9   3   LAnd   2
1   11   3   Stadt   2
1   33   4   Stadt   2
1   12   4   Stadt   1
1   13   4   Stadt   2
1   9   3   Stadt   2
1   12   1   Stadt   1
1   13   1   Stadt   2

Code:

      select o.*, s.*
        from (select id,
                     f_hits,
                     f_cat_id,
                     f_word,
                     f_status_id,
                     row_number() over(partition by f_cat_id order by f_hits) as TopOf
                from (SELECT e.id, e.f_hits, e.f_cat_id, f_word, e.f_status_id
                        FROM tab_entries e
                        JOIN tab_search_index i
                          ON i.f_entry_id = e.id
                       WHERE i.f_word = 'Stadt' --'Fluss'
                      ) i) o,
             tab_entries_status s
       where o.TopOf < 3
         and s.id = o.f_status_id
       order by o.f_cat_id, o.f_hits desc

Code:

ID   F_HITS   F_CAT_ID   F_WORD   F_STATUS_ID   TOPOF   ID   F_STATUS_NAME
1   13   1   Stadt   2   2   2   unfertich
1   12   1   Stadt   1   1   1   fertich
1   5   2   Stadt   2   1   2   unfertich
1   11   3   Stadt   2   2   2   unfertich
1   9   3   Stadt   2   1   2   unfertich
1   13   4   Stadt   2   2   2   unfertich
1   12   4   Stadt   1   1   1   fertich

Für MySQL gibts aber doch einige Seiten zum Thema,
Analytic Functions nachbilden. Das Suchwort wäre also "Analytic Functions mySQL"

Die Formatierung sieht doof aus, keine Ahnung ob das besser geht. Als Screenshot ist es besser lesbar, aber so als Text kann man die Zahlen noch weiter nutzen.

[Elvis]

Zitat von Matze:

Hm, wie könnte ich das dann lösen?

Du gruppierst nachträglich - zum Beispiel über ein Dictionary - in deinem Anwendungscode. (Auch wenn das bei dir wohl *brrr* PHP sein wird)

[Sir Rufo]

Zitat von Elvis:

Zitat von Matze:

Hm, wie könnte ich das dann lösen?

Du gruppierst nachträglich - zum Beispiel über ein Dictionary - in deinem Anwendungscode. (Auch wenn das bei dir wohl *brrr* PHP sein wird)

Wozu das denn? Geht doch alles mit SQL

Code:

Select
  a
From (
Select
  foo As a
From
  TabA
Union
Select
  foo
From
  TabB ) As TempQry
Group By
  a