Filtern von Datensätzen mit mehreren Bedingungen

[umutos]

Hallo,

Ich hab da ein Filter-Problem in MS SQL 2008 und komme schon seit über einem Monat nicht weiter Bin für jede Hilfe Dankbar!
Nehmen wir die fiktive Tabelle „Daten“ sieht wie folgt aus:

ID	Alter	Ort	Geburtsdatum	Augen	Sex
12321	11	Ulm	01.02.2008	Grün	Mann
12311	12	München	02.09.1999	Blau	Mann
I2343	34	Berlin	01.03.2001	Braun	Frau
…	…	…	…	…	…	…

Die Tabelle geht dann noch so weiter mit vielen weiteren Zeilen.

Ich brauche nun nur diejenigen Zeilen in der Tabelle „Daten“ die bei gegebenem Wert in der Spalte „Alter“, „Ort“, „Geburtsdatum“ und „Augen“ sowohl „Mann“ als auch „Frau“ in Spalte „Sex“ haben. D.h. wenn z.B. in der ganzen Tabelle "Daten" die 12 jährigen Münchner die am 2.9.1999 geboren sind und blaue Augen haben alles nur Männer sind, dann sollen diese Zeilen ausgeblendet werden.

Ich habe ursprünglich die Daten aus MS SQL 2008 exportiert und habe den Filteralgorithmus in Matlab geschrieben. Doch dieser hat es aufgrund der Größe der Tabellen nicht geschafft (200 Tabellen mit jeweils mehr als 1 Mio. Zeilen). In SQL kenne ich mich leider überhaupt nicht aus und bin wirklich für jede Hilfe dankbar!

Bernd

[hwoess]

Mein Vorschlag:

select * from Daten d1 where exists (
  select 1 from daten d2 where
  d2.Ort = d1.Ort and
  d2.Geburtsdatum = d1.Geburtsdatum and
  d2.Augen = d1.Augen and
  d2.Sex = (case when d1.Sex = 'Mann' then 'Frau' else 'Mann' end)
)

... auf Alter habe ich nicht geprüft, denn wenn das Geburtsdatum gleich ist ...

bye,
Helmut

[umutos]

Danke Helmut! Ich hätte da noch 2 Fragen zu dem Code:

1. Ich würde die gefilterte Tabelle weiter filtern und mit anderen Tabellen verbinden. Nehmen wir an es gibt noch eine Tabelle "TabelleAutodaten" und eine Tabelle "TabelleEinkommensdaten". Ich hätte mir gedacht dass man die gefilterte Tabelle wie folgt mit den anderen Tabellen verbinden kann:

select Daten.ID, Alter, Ort, Geburtsdatum, Augen, Sex,
Automarke,KMStand,Einkommen 
from Daten d1,TabelleAutodaten, TabelleEinkommensdaten  where exists (
  select 1 from daten d2 where
  d2.Ort = d1.Ort and
  d2.Geburtsdatum = d1.Geburtsdatum and
  d2.Augen = d1.Augen and
  d2.Sex = (case when d1.Sex = 'Mann' then 'Frau' else 'Mann' end))
  AND TabelleAutodaten.ID=Daten.ID AND TabelleEinkommensdaten.ID=Daten.ID

Dieser Code funktioniert leider nicht und es kommt die Fehlermeldung dass nicht gebunden werden konnte. Kann mir da jemand weiterhelfen?

2. Durch das d1 und d2 duplizierst du anscheinend fiktiv die Tabelle. Wie nennt man dieses Verfahren in SQL bzw. nach was muss ich unter der Hilfe oder in Google suchen damit ich mehr zu dieser Methodik lesen kann?

Vielen Dank nochmals für die Hilfe!

[hwoess]

Was ich mit d1 und d2 mache findet man unter ALIAS (kann man auch für Spalten verwenden, siehe zB hier).
Und das Einbinden weiterer Tabellen macht man besser mit JOIN:

select d1.ID, d1.Alter, d1.Ort, d1.Geburtsdatum, d1.Augen, d1.Sex,
ta.Automarke, ta.KMStand, te.Einkommen from Daten d1
LEFT JOIN TabelleAutodaten ta on ta.ID = d1.ID
LEFT JOIN TabelleEinkommensdaten te on te.ID = d1.ID
where exists (
  select 1 from daten d2 where
  d2.Ort = d1.Ort and
  d2.Geburtsdatum = d1.Geburtsdatum and
  d2.Augen = d1.Augen and
  d2.Sex = (case when d1.Sex = 'Mann' then 'Frau' else 'Mann' end)
)

Joins sind schon etwas komplexer, vielleicht liest du mal das als Einführung und eventuell dann das, um einen Überblick über den Einsatz aber auch dessen Komplexität zu kriegen.

bye,
Helmut

PS: was ist, wenn jemand zwei Autos hat, gibt es in TabelleAutodaten zwei Datensätze mit derselben ID ? (ID klingt so nach PrimaryKey).

[umutos]

Super! Ich werde mir mal die Infos durchlesen bevor ich den Code dann anwende. Will ja auch verstehen was ich mache. Danke nochmals. Werd mich dann melden wenn es geklappt/nicht geklappt hat

Zu den ID's: Der Datensatz ist fiktiv ausgewählt. In meinen Tabellen handelt es sich bei ID's um Vertragsnummern die den Primary Key darstellen.

Schönen Tag noch!

[umutos]

Hallo, du danke nochmals für den Code. Der hat super geklappt. Hab mir auch die Texte durchgelesen.
Nun stehe ich aber vor einem weiteren Filterproblem das noch eine Stufe schwieriger ist und ich wieder mal nicht lösen kann. Ich glaube das ganze ist mit einigen GROUP BY Befehlen lösbar aber ich habe es bis jetzt nicht geschafft. Vielleicht kann man mir da ja auch jemand weiterhelfen. Das wär echt super!
Es sind einfach viel zu viele Paare („identische Mann und Frau Paare“) in meinem Datensatz und ich muss die Zusammenfassen damit ich weiter rechnen kann.

Dazu müssen 2 Schritte gemacht werden:
(nur eine kleine info: ich bin drauf gekommen das es in meinem Datensatz jedes „identische Paar“ nur aus 1 Mann und 1er Frau besteht. )

1.Zuerst werden die „identischen Pärchen“ (bestehend aus einem Mann und einer Frau) zusammengefasst:
Für jedes „identische Paar“ wird der Durchschnittliche KMStand berechnet („KM_AVG“): AVG(KMStand (vom Mann), KMStand (der Frau)). Diese Zahl fasst nun alle „identischen Pärchen“ zusammen. Somit kann die Hälfte des Datensatzes gelöscht werden (z.b. alle Männer oder alle Frauen)

2.Dann werden alle Pärchen (die jetzt nur mehr in einer einzelnen Zeile zusammengefasst sind) an einem bestimmten Ort mit einem bestimmten Geburtsdatum zusammengefasst:
Man berechnet die Summe der Spalte „KM_AVG“ für jeden bestimmten Ort mit einem bestimmten Geburtsdatum. Diese Zahl fasst dann alle Pärchen an einem bestimmten Ort mit einem bestimmten Geburtsdatum zusammen.

Wenn es z.B. an einem bestimmten Ort mit einem bestimmten Geburtsdatum 5 „identische Pärchen gibt“ (also 10 Zeilen, da es jeweils 1 Mann und 1 Frau gibt) wird im ersten Schritt der Datensatz auf 5 Zeilen reduziert und im zweiten Schritt auf nur 1e Zeile reduziert.

Hier ein kleines Bsp.:
Ausgangstabelle ist das Ergebnis aus dem zweiten Code, den Helmut geschrieben hat (hier sind die Daten nur so gruppiert damit man sieht was ich meine):

Ort	Geburtsdatum	Alter	Augen	KMStand	Sex
Ulm	02.03.2005	12	blau	12321	Mann
Ulm	02.03.2005	12	blau	1231	Frau
Ulm	02.03.2005	14	grün	4345	Mann
Ulm	02.03.2005	14	grün	2322	Frau
München	04.06.1988	14	grün	211	Mann
München	04.06.1988	14	grün	553	Frau
Paris	04.04.2001	34	blau	54654	Mann
Paris	04.04.2001	34	blau	4443	Frau
Paris	04.04.2001	22	blau	22	Mann
Paris	04.04.2001	22	blau	221	Frau

Nach dem 1.Schritt würd das Ergebnis so ausschauen:

Ort	Geburtsdatum	Alter	Augen	KM_AVG
Ulm	02.03.2005	12	blau	6776
Ulm	02.03.2005	14	grün	3333,5
München	04.06.1988	14	grün	382
Paris	04.04.2001	34	blau	29548,5
Paris	04.04.2001	22	blau	121,5

Und nachdem 2.Schritt sieht das Endergebnis so aus:

Ort	Geburtsdatum	SUM_KM_AVG
Ulm	02.03.2005	10109,5
München	04.06.1988	382
Paris	04.04.2001	29670

Vielen lieben Dank für die Hilfe!

[hwoess]

[gelöscht] ... Antwort war falsch ...

bye,
Helmut

[umutos]

hallo, nur eine kleine änderung. bin draufgekommen dass es leider doch pärchen gibt die aus mehr als 2 positionen besteht. daher muss man vorher noch einen schritt einfügen wo jedes pärchen dass aus mehr als 2 positionen besteht auf 2 positionen zusammengefasst wird.

Hier ein beispiel: nehmen wir an ein "identisches pärchen" (gleiches alter gebdatum, augen ort) sieht wie folgt aus:

	KMStand
Mann	1000
Mann	2000
Mann	2000
Frau	1000
Frau	2000

dann muss bevor die 2 summary schritte implementiert werden forgende summary erstellt werden:

	Volume_SUM
Mann	5000
Frau	3000

und dann muss man einfach nicht mehr von Volume den durchschnitt nehmen sondern von Volume_SUM.

Ich glaub den Code für die 2 Summary schritte hab ich eh schon. Irgendwas kleines passt da aber nicht. Es müsste mit 2 GROUP BY funktionen gehen....
ahh..das ganze will kein ende nehmen

[umutos]

Hallo Ich hab da bereits die Lösung. Danke für die Hilfe!
Was mir jetzt noch als letztes fehlt ist eine einfache Substraktion von Zeilen.

d.h. wenn ich die folgende Matrix habe:

Sp1	Sp2	Sp3
A	C	10
A	P	7
B	C	8
B	P	1
C	C	20
C	P	10

würde ich gerne die folgende Summary haben:

Sp1	Sp_X
A	3
B	7
C	10

A	3
B	7
C	10

Addieren wär ja einfach
select Sp_1, Summe=sum(Sp_X) from table group by Sp_1

aber wie geht da subtrahieren?
Danke

[hwoess]

Stehen in Spalte Sp1 immer genau zwei Einträge je Buchstabe und sind diese beiden Einträge in Spalte Sp2 immer einmal 'C' und einmal 'P' und wird immer 'P' von 'C' abgezogen?

bye,
Helmut

[umutos]

Hallo Helmut,
Ich bin gerade draufgekommen wie es geht. einfach nur mit einer case bedingung ( ja,es kommt immer 1 C und 1 P vor):

select Sp_1 SP_X=sum(Case if Sp_2='C' then Sp_3 else -Sp_3) from table group by SP_1

nochmals danke für die super hilfe!
wünsch dir noch einen schönen tag!