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.

[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

[Elvis]

Zitat von Sir Rufo:

Wozu das denn? Geht doch alles mit SQL

Je nach verwendetem DBMS kann das simulieren von analytic functions schnell ziemlich lahm werden.
Wenn Matze also anfängt komplexes Reporting zu implementieren wird er früher oder später an die Grenzen von mySQL kommen, und DBMS-unabhängig gehen dann nur duplizierte SQLs wie du sie gezeigt hast.
Das kann natürlich absolut in Ordnung sein, solange man nicht die halbe Logik der Abfrage 5-mal kopieren muss. Oder plötzlich 300 Zeilen SQL an mySQL schickt...
Denn wehe demjenigen, der bei einer Anpassung die 4 Duplikate nicht mit anpasst.

[jobo]

Ich habs mal in mySql versucht.

Wirklich gruppiert wird ja in meinem oracle sql nicht. Man muss nur Gruppen zählen. Das geht mit mySql mit Variablen.
Ist wieder nicht allgemein, dafür mySQL.

Die Suchgeschichte und das drumrum hab ich mal weggelassen.

Code:

mysql> select
    ->   if (@cat = f_cat_id, @rn := @rn + 1, @rn := 1) rownum,
    ->      (@cat := f_cat_id) f_cat_id,
    ->      e.Id, f_hits, f_Status_id
    ->   from
    ->     (select * from tab_entries
    ->       order by id, f_cat_id, f_hits desc ) e,
    ->     (select (@cat := 0)) as x
    -> ;
+--------+----------+------+--------+-------------+
| rownum | f_cat_id | Id   | f_hits | f_Status_id |
+--------+----------+------+--------+-------------+
|      1 |        1 |    1 |     20 |           1 |
|      2 |        1 |    1 |     13 |           2 |
|      3 |        1 |    1 |     12 |           1 |
|      1 |        2 |    1 |      5 |           2 |
|      1 |        3 |    1 |     11 |           2 |
|      2 |        3 |    1 |      9 |           2 |
|      1 |        4 |    1 |     33 |           2 |
|      2 |        4 |    1 |     13 |           2 |
|      3 |        4 |    1 |     12 |           1 |
|      1 |        3 |    2 |     22 |           1 |
|      2 |        3 |    2 |      9 |           2 |
+--------+----------+------+--------+-------------+
11 rows in set (0.00 sec)

mysql>



mysql> select * from (
    -> select
    ->   if (@cat = f_cat_id, @rn := @rn + 1, @rn := 1) rownum,
    ->      (@cat := f_cat_id) f_cat_id,
    ->      e.Id, f_hits, f_Status_id
    ->   from
    ->     (select * from tab_entries
    ->       order by id, f_cat_id, f_hits desc ) e,
    ->     (select (@cat := 0)) as x
    ->     ) o
    ->  where o.rownum<3;
+--------+----------+------+--------+-------------+
| rownum | f_cat_id | Id   | f_hits | f_Status_id |
+--------+----------+------+--------+-------------+
|      1 |        1 |    1 |     20 |           1 |
|      2 |        1 |    1 |     13 |           2 |
|      1 |        2 |    1 |      5 |           2 |
|      1 |        3 |    1 |     11 |           2 |
|      2 |        3 |    1 |      9 |           2 |
|      1 |        4 |    1 |     33 |           2 |
|      2 |        4 |    1 |     13 |           2 |
|      1 |        3 |    2 |     22 |           1 |
|      2 |        3 |    2 |      9 |           2 |
+--------+----------+------+--------+-------------+
9 rows in set (0.00 sec)

mysql>