Etwas eigenwillige Abfrage gesucht

[andre_scheringer]

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é

 

[akretschmer]

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.

Was sollte bei Deinem Beispiel rauskommen?

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.

 

[andre_scheringer]

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?

 

[akretschmer]

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

Mit Deinen Daten:

test=*# select s2, count(s1) from foobar group by s2;
 s2 | count
----+-------
 c  |  1
 x  |  2
 a  |  1
 d  |  1
(4 rows)

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?

Ähm, laß mich kurz nachdenken ... neee.

 

[akretschmer]

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

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.

 

[andre_scheringer]

Erstmal danke für deine Geduld.

Mit Deinen Daten:

test=*# select s2, count(s1) from foobar group by s2;
s2 | count
----+-------
c  |  1
x  |  2
a  |  1
d  |  1
(4 rows)

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.

 

[andre_scheringer]

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).

 

[akretschmer]

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).

Angenommen, du hast:

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:

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.

 

[andre_scheringer]

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.

 

[akretschmer]

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.

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...

 

[akretschmer]

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...

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;

 

[ukulele]

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:

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

 

[andre_scheringer]

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

select rezept from kochen group by rezept having count(zutat) < 2

aber dadurch ändern sich die Resultate nicht.

 

[akretschmer]

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

select rezept from kochen group by rezept having count(zutat) < 2

aber dadurch ändern sich die Resultate nicht.

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

 

[andre_scheringer]

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:

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))