Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Etwas eigenwillige Abfrage gesucht

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von andre_scheringer, 26 Februar 2014.

  1. andre_scheringer

    andre_scheringer Benutzer

    Hallo Datenbank-Auskenner.

    Ich habe eine Frage:
    Ich habe eine Tabelle des Formats
    Spalte1 Spalte2
    Set1 A
    Set1 C
    Set1 X
    Set2 D
    Set2 X
    ... ...

    Die erste Spalte sind also Sets, die aus Dingen bestehen, Spalte 2 sind Dinge, die Sets bilden. Ich möchte jetzt die Dinge auflisten, die zusammen die meisten kompletten Sets bilden. Ich möchte also Dinge am oberen Ende der Liste haben, wenn sie in vielen Sets enthalten sind, die nicht Dinge enthalten, die nur in wenigen Sets enthalten sind. Dinge die nur einmal in einem Set vorkommen, möchte ich gar nicht in der Liste haben.

    Die Frage lautet nun: kann ich sowas mit MSSQL machen? Wenn ja, wie?

    Ich bitte um Verzeihung, dass ich vor meinem ersten Post nicht die Suchfunktion bemüht habe, aber ich weiß nicht, wie ich dieses Thema überhaupt suchen soll. Gibt es einen Begriff für diese Art Suche?

    Viele Grüße,
    André
     
  2. akretschmer

    akretschmer Datenbank-Guru

    Was sollte bei Deinem Beispiel rauskommen?

    Code:
    test=*# select * from foobar ;
      s1  | s2
    ------+----
    set1 | a
    set1 | c
    set1 | x
    set2 | d
    set2 | x
    (5 rows)
    
    Time: 0,096 ms
    test=*# select s1, count(s2) from foobar group by s1 order by count(s2) desc;
      s1  | count
    ------+-------
    set1 |  3
    set2 |  2
    (2 rows)
    
    Time: 0,224 ms
    test=*# select s1, count(s2) from foobar group by s1 having count(s2) > 2 order by count(s2) desc;
      s1  | count
    ------+-------
    set1 |  3
    (1 row)
    
    Falls das richtige schon dabei ist: bitte ankreuzen.
     
  3. andre_scheringer

    andre_scheringer Benutzer

    Sorry, kann noch nichts ankreuzen. Das Beispiel ist vielleicht auch nicht so hilfreich, bei nochmaligem drüber nachdenken, weil sehr wenig Datensätze drin sind. Das Ergebnis soll halt so aussehen:
    X | 27
    C | 15
    A | 9
    D | 7
    sprich, X kommt in 27 Sets vor, die mit den anderen gelisteten Dingen zusammen komplett sind, und so weiter.
    Nehmen wir mal als anderes Beispiel die Situation eines Dinges Q. Q kommt in 10 Sets vor. Zwei davon sind aber nur komplett, wenn jeweils ein Ding F bzw. L enthalten ist. F und L sind aber nur jeweils in diesem einen Set enthalten, daher fallen diese Sets weg. Also wird Q am Ende gelistet als:
    Q | 8
    Wenn alle 10 Sets, in denen Q enthalten ist, je ein einzigartiges Ding enthalten würden, würde Q auch herausfallen.

    Hilft das bei der Klärung?
     
  4. akretschmer

    akretschmer Datenbank-Guru

    Mit Deinen Daten:

    Code:
    test=*# select s2, count(s1) from foobar group by s2;
     s2 | count
    ----+-------
     c  |  1
     x  |  2
     a  |  1
     d  |  1
    (4 rows)
    
    Ähm, laß mich kurz nachdenken ... neee.
     
  5. akretschmer

    akretschmer Datenbank-Guru

    Du hast 10 Sets. Zwei davon können nur komplett sein, wenn da auch F oder (und?) enthalten ist.

    Die anderen 8 sind komplett, auch wenn F und/oder L da fehlen? Bahnhof.
     
  6. andre_scheringer

    andre_scheringer Benutzer

    Erstmal danke für deine Geduld.
    Soweit richtig. Deshalb war das Beispiel auch ein bisschen zu kurz bzw nur gedacht, um den Tabellenaufbau zu illustrieren. Alle Einträge mit count 1 interessieren mich nicht. Alle Einträge, die sich ein Set mit diesen Einträgen teilen, interessieren mich auch nicht, und sollen nicht mitgezählt werden. Deshalb das verwirrende Beispiel mit Q. Es gibt 10 Sets, in denen Q enthalten ist. Es gibt nur ein Set, in dem L, und eines, in dem F enthalten ist. L und F fallen damit raus, und weil Q in den beiden Sets enthalten ist die L bzw. F enthalten, fallen zwei Zählungen von Q auch raus. Damit wird Q 8-mal gezählt, L und F gar nicht.
    Ich suche also nur Sets, die keine einzigartigen Dinge enthalten.
    Ich möchte die Dinge in diesen Sets zählen, nach Set gruppiert.
    Ich möchte diese Zahlen absteigend sortieren.
    Der erste Schritt ist eigentlich das größte Problem. Die beiden anderen sind mit count, group by und desc ja leicht erschlagen.
     
  7. andre_scheringer

    andre_scheringer Benutzer

    Mir ist ein praktisches Alltagsbeispiel eingefallen:
    Ich habe eine Liste mit Rezepten und Zutaten. Weil der Platz in meinem Küchenschrank begrenzt ist, will ich keine Zutaten einkaufen, die nur in einem Rezept vorkommen. Diese Rezepte streiche ich von meinem regulären Speiseplan und kaufe eben ein, wenn ich sie kochen will. Von den übriggebliebenen Rezepten will ich alle Zutaten im Schrank haben, und darüber hinaus wissen, in wie viele Rezepten sie vorkommen, damit ich sie entsprechend sortieren kann (Reis muss griffbereit, Kurkuma kann irgendwo hinten in die Ecke).
     
  8. akretschmer

    akretschmer Datenbank-Guru

    Angenommen, du hast:

    Code:
    test=*# select * from foobar ;
      s1  | s2
    ------+----
     set1 | a
     set1 | c
     set1 | x
     set2 | d
     set2 | x
    (5 rows)
    
    'd' soll rausfallen, nun suchst alle s1, wo alle s2 (außer 'd') vorkommen:

    Code:
    test=*# select * from (select s1, array_agg(s2) as zutaten from foobar group by s1) foo where zutaten @> (select array_agg(distinct s2) from foobar where s2 not in ('d'));
      s1  | zutaten
    ------+---------
     set1 | {a,c,x}
    (1 row)
    

    Probleme:
    • möglicherweise hab ich Dein Problem noch immer nicht geschnallt
    • ich habe PostgreSQL. Das ist sowas wie Mercedes 'S' - Klasse unter den Datenbanken. Sehr wahrscheinlich kann M$SQL das so entweder nur mit Änderungen oder gar nicht.
     
  9. andre_scheringer

    andre_scheringer Benutzer

    Ja, irgendwie kommen wir noch nicht zusammen. Ich brauche nur die Zutaten und die Anzahl der Rezepte, minus die Rezepte in denen eine einmalige Zutat drin ist*. Das Array-Ding übersteigt glaube ich wirklich die Fähigkeiten von MSSQL. Nochmal Beispiel:
    Rezept | Zutat
    Suppe | Zwiebeln
    Suppe | Salz
    Suppe | Pfeffer
    Suppe | Möhren
    Eintopf | Erbsen
    Eintopf | Möhren
    Eintopf | Zwiebeln
    Eintopf | Salz
    Eintopf | Pfeffer
    Gemüsepfanne | Salz
    Gemüsepfanne | Pfeffer
    Gemüsepfanne | Zwiebeln
    Gemüsepfanne | Möhren

    Dann kriege ich:
    Salz | 2
    Pfeffer |2
    Zwiebeln | 2
    Möhren | 2

    Der Eintopf fällt raus und wird nicht mehr erwähnt, weil er das einzige Gericht ist, in dem Erbsen drin sind. Es werden also auch Möhren, Salz, Pfeffer und Zwiebeln nicht nochmal gezählt. In der Ergebnistabelle habe ich jetzt alle nicht-einzigartigen Zutaten, und die Information, dass ich mit diesen Zutaten 2 vollständige Rezepte kochen könnte. Ich möchte halt nur beim Einkaufen schon sicherstellen, dass ich Dinge einkaufe, die nachher eine komplette Mahlzeit ergeben, und die nach Bedarf auch zu verschiedenen Mahlzeiten kombiniert werden können.

    *Bei näherem Nachdenken wäre es glaube ich nett, wenn man die Schwelle festlegen könnte, also auch Zutaten (und die Rezepte die diese Zutaten brauchen) streichen könnte, wenn sie zwei- oder dreimal vorkommen.
     
  10. akretschmer

    akretschmer Datenbank-Guru

    Ja, ich glaub, ich hab es. Sicherlich machbar mit PG und Arrays (das ist das, was mir so einfällt...), bringt aber nix, hilft Dir nix. Vielleicht fällt mir oder jemanden anders 'ne andere Lösung ein...
     
  11. akretschmer

    akretschmer Datenbank-Guru

    Code:
    test=*# select * from kochen ;
     rezept  |  zutat
    ---------+----------
     suppe  | zwiebeln
     suppe  | pfeffer
     suppe  | moehren
     eintopf | erbsen
     eintopf | moehren
     eintopf | zwiebeln
     pfanne  | salz
     pfanne  | pfeffer
     pfanne  | zwiebeln
     pfanne  | moehren
     suppe  | salz
     eintopf | salz
     eintopf | pfeffer
    (13 rows)
    
    test=*# select zutat, count(rezept) from kochen where rezept not in ( select rezept from kochen where zutat in (select zutat from kochen group by zutat having count(rezept) < 2)) group by zutat;
      zutat  | count
    ----------+-------
     zwiebeln |  2
     pfeffer  |  2
     salz  |  2
     moehren  |  2
    (4 rows)
    

    select 'kaffee' into /me;
     
  12. ukulele

    ukulele Datenbank-Guru

    Ich verstehe wohl die Erklärung und ich glaube die letzte Lösung von akretschmer ist auch richtig. Ich habs nochmal selbst geschrieben mit Spalte1=sets und Spalte2=Dinge:
    Code:
    SELECT    Spalte2,
            count(*) AS Anzahl
    FROM    tabelle
    WHERE    Spalte1 NOT IN (    SELECT    Spalte1
                                FROM    tabelle
                                WHERE    Spalte2 IN (    SELECT    Spalte2
                                                        FROM    tabelle
                                                        GROUP BY Spalte2
                                                        HAVING count(*) = 1 ) )
    GROUP BY Spalte2
    ORDER BY count(*) DESC
     
  13. andre_scheringer

    andre_scheringer Benutzer

    Danke Leute, das funktioiert. Vielen Dank für Eure Bemühungen. Eine Kleinigkeit ist mir noch aufgefallen: ich habe jetzt auch Rezepte drin, die nur eine Zutat haben (z. B: "Möhrenrohkost | Möhren"). Solche Rezepte möchte ich auch ignorieren. Ich habe versucht die Formel umzudrehen
    Code:
    select rezept from kochen group by rezept having count(zutat) < 2
    aber dadurch ändern sich die Resultate nicht.
     
  14. akretschmer

    akretschmer Datenbank-Guru


    Du mußt das dann noch im WHERE einbauen, via AND.
     
  15. andre_scheringer

    andre_scheringer Benutzer

    Das war ungeschickt ausgedrückt. Was ich gemacht habe, ist natürlich, diese Formel mit einem AND anzuknüpfen, so dass es dann so aussieht:
    Code:
    select zutat, count(rezept) from kochen where rezept not in ( select rezept from kochen where zutat in (select zutat from kochen group by zutat having count(rezept) < 2) AND rezept not in (select rezept from kochen group by rezept having count(zutat) < 2))
    
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden