Verständnisproblem bei SQL Abfrage

[Kirby.exe]

Also ich habe die folgende Aufgabe:



Soweit habe ich verstanden, dass wir mittels den Tabellen Aufnahme, CD und Stueck zählen sollen, wie viele Stuecke auf einer CD sind und dann sollen wir diese Anzahl durch den Wert anz_cds (von der Tabelle CD) dividieren und in eine Tabelle schreiben. Aus dieser Tabelle werden dann die obigen Werte ermittelt. Ich verstehe aber nicht ganz wie ich diese Unteranfrage aufbauen soll... So werden die Tabellen gelinked:

Select s1.snr,
        s1.knr,
        s1.titel,
        s1.tonart,
        s1.opus,
        c1.cdnr,
        c1.name,
        c1.hersteller,
        c1.anz_cds,
        c1.gesamtspielzeit
From Stueck s1,
    Aufnahme a1,
    CD c1
Where a1.cdnr = c1.cdnr
        and s1.snr = a1.snr

 

[mihe7]

Ich verstehe aber nicht ganz wie ich diese Unteranfrage aufbauen soll...

Was genau verstehst Du daran nicht? Du brauchst die Anzahl der Stücke gruppiert nach cd_nr.

 

[Kirby.exe]

Was genau verstehst Du daran nicht? Du brauchst die Anzahl der Stücke gruppiert nach cd_nr.

Danke das hilft schonmal sehr 🙂 Aber wie teile ich die Counts für jede CD dann? Also kann ich einfach (count(s1.snr)/c1.anz_cds) machen und er zählt dann erst alle und dividiert dann?


Also so:

With res as (Select c1.cdnr,
                    (count(s1.snr)/c1.anz_cds)
            From Stueck s1,
                    Aufnahme a1,
                    CD c1
            Where a1.cdnr = c1.cdnr
                    and s1.snr = a1.snr
            Group By c1.cdnr)

 

[Kirby.exe]

Habe es jetzt einfach so gemacht:

With res as (Select c1.cdnr,
                    count(s1.snr) as anz
            From Stueck s1,
                    Aufnahme a1,
                    CD c1
            Where a1.cdnr = c1.cdnr
                    and s1.snr = a1.snr
            Group By c1.cdnr)
Select round(min(res.anz/c2.anz_cds),1),
        round(max(res.anz/c2.anz_cds),1),
        round(avg(res.anz/c2.anz_cds),1)
From CD c2, res
Where res.cdnr = c2.cdnr

Diese Anfrage liefert das richtige Ergebnis 🙂

 

[mihe7]

Ja, oder den Subselect direkt in der FROM-Clause angeben. Die Division könntest Du gleich in res durchführen. Ist es eigentlich vorgegeben/angedacht, ohne INNER JOIN zu arbeiten?

 

[Kirby.exe]

Ja, oder den Subselect direkt in der FROM-Clause angeben. Die Division könntest Du gleich in res durchführen. Ist es eigentlich vorgegeben/angedacht, ohne INNER JOIN zu arbeiten?

Naja not really, aber wird hier nicht intern sowieso ein Join durchgeführt?

 

[mihe7]

Das wird der Query-Optimizer richten, aber im Prinzip sagst Du: hey, bilde erstmal das Kreuzprodukt und filter mir die raus, für die gilt: ...

 

[Kirby.exe]

Yes you're right xD Ich hatte noch ein Problem zu einer ähnlichen Aufgabe....Also die Aufgabenstellung ist die folgende:


Ich habe ein "fertiges" Query, welches mir auch teile der erwarteten Aufgaben liefert. Jedoch jetzt zu meinem Problem...Die Filter Anfrage, welche sich die Anzahl der gesamten Stücken mit einer Tonart verschieden von Null holt gibt mir die korrekten Personen und Anzahlen her. Nun habe ich noch die anderen Tabellen mit eingebunden für Dur und Moll und nun fehlen plötzlich einträge...Ich sehe nicht ganz warum das passiert...Hier ist der Code:

With res as (Select k1.knr,
                    count(s1.tonart) as anz_tonart
            From Komponist k1,
                    Stueck s1
            Where k1.knr = s1.knr
            Group By k1.knr),
    dur as (Select k3.knr,
                count(s3.tonart) as anz_tonart
            From Komponist k3,
                Stueck s3
            Where k3.knr = s3.knr
                    and s3.tonart Like '%-dur'
            Group By k3.knr),
    moll as (Select k4.knr,
                    count(s4.tonart) as anz_tonart
             From Komponist k4,
                    Stueck s4
            Where k4.knr = s4.knr
                    and s4.tonart Like '%-moll'
            Group By k4.knr)
Select k2.name,
        k2.vorname,
        res.anz_tonart as anzahl_tonarten,
        round((dur.anz_tonart*100.0)/res.anz_tonart) as prozent_dur,
        round((moll.anz_tonart*100.0)/res.anz_tonart) as prozent_moll
From Komponist k2,
    res,
    dur,
    moll
Where k2.knr = res.knr
        and k2.knr = dur.knr
        and k2.knr = moll.knr
        and res.anz_tonart >= 3
Group By k2.name, k2.vorname, res.anz_tonart, dur.anz_tonart, moll.anz_tonart

 

[mihe7]

Ich sehe nicht ganz warum das passiert...

Ich auch nicht 🙂 Lass mal Zeile 34 weg.

Die Behandlung von res kannst Du mit HAVING vereinfachen:

SELECT
  k.knr,
  count(s.tonart) as stuecke_ges
FROM Komponist k
  INNER JOIN Stueck s ON k.knr = s.knr
GROUP BY k.knr
HAVING count(s.tonart) >= 3

Damit liefert der Subselect schon mal von Haus aus nur noch die Komponisten mit wenigstens drei Stücken, für die die Tonart bekannt ist und die Gesamtzahl dieser Stücke (count(s.tonart) liefert nicht die Zahl verschiedener Tonarten sondern die Zahl der Datensätze, deren Tonart nicht NULL ist und somit die Anzahl der gesuchten Stücke).

Kannst Du mal ein SQL-Dump posten (CREATE TABLE, INSERTs)? Was für eine DB verwendest Du?

 

[Kirby.exe]

Puhh also verwendet wird POSTGreSQL xD Das ist eine Uni Interne DB aber ich kann man Screenshots von den Tables machen

Hier sind die beiden Tabellen:#

 

[mihe7]

So viel zum Thema OpenData 🙂

OK, dann andersrum:

Die Filter Anfrage, welche sich die Anzahl der gesamten Stücken mit einer Tonart verschieden von Null holt gibt mir die korrekten Personen und Anzahlen her. Nun habe ich noch die anderen Tabellen mit eingebunden für Dur und Moll und nun fehlen plötzlich einträge..

Fehlen Datensätze oder Zahlen?

 

[Kirby.exe]

So viel zum Thema OpenData 🙂

OK, dann andersrum:



Fehlen Datensätze oder Zahlen?

Datensätze XD Wenn ich die Anfrage ausführt um nur die Anzahl der Tonarten zu bekommen (also egal ob dur oder moll) dann erhalte ich 9 Datensätze. Wenn ich aber alles zusammen verwende bekomme ich nur 7 Datensätze...xD Was mich verwirrt ist, dass ich doch gar nichts einschränke oder? Ich linke nur die res tabelle mit der dur und moll tabelle zusammen oder nicht ? xD

 

[mihe7]

Wenn ich die Anfrage ausführt um nur die Anzahl der Tonarten zu bekommen (also egal ob dur oder moll) dann erhalte ich 9 Datensätze.

Nochmal: das ist die Anzahl der Stücke, nicht der Tonarten. Sind da zufälligerweise Sätze dabei, für die anz_tonart < 3 gilt?

 

[Kirby.exe]

Nope alle haben mindestens 3 Stück:

 

[mihe7]

Ich bezog mich auf die nicht verknüpfte Abfrage, wo Du 9 Datensätze bekommst 🙂

 

[Kirby.exe]

Ahhh ich glaube ich habe eine vermutung....Bei den beiden welchen Fehlen ist die Anzahl von Moll Tonarten = 0...XD Kann es sein, dass diese dann gar nicht im Query als in der Tabelle Moll gelistet werden?

 

[Kirby.exe]

Hier ist die nicht verknüpfte Anfrage:

 

[mihe7]

Hä, kann es sein, dass die moll-Subquery nur 7 Sätze liefert?!?

 

[mihe7]

Ah, das sind diese kleinen Fallstricke. Das Problem ist, dass wir Komponisten mit Stuecke joinen und dann nach der Tonart einschränken. Wenn ein Komponist kein Stück dieser Tonart hat, dann ist der Join leer und damit liefert moll auch keinen Satz für diesen Komponisten... Analog für dur.

 

[Kirby.exe]

Ah, das sind diese kleinen Fallstricke. Das Problem ist, dass wir Komponisten mit Stuecke joinen und dann nach der Tonart einschränken. Wenn ein Komponist kein Stück dieser Tonart hat, dann ist der Join leer und damit liefert moll auch keinen Satz für diesen Komponisten... Analog für dur.

Ja gut xD Wie kann ich da jetzt nen Default Case machen? Also wenn Zeile nicht existiert dann einfach auf 0 setzen xD oder so?

 

[mihe7]

Alle Komponisten und die dazu passenden Sätze aus moll (bzw. dur). Wonach schreit das?

 

[Kirby.exe]

Alle Komponisten und die dazu passenden Sätze aus moll (bzw. dur). Wonach schreit das?

Ehm sorry ich stehe gerade auf dem Schlauch xD Maybe ein Join ? xD

 

[mihe7]

LEFT JOIN.

 

[Kirby.exe]

Soo jetzt habe ich schonmal die einträge, aber wie mache ich jetzt aus dem NULL eine 0 xD

 

[mihe7]

aber wie mache ich jetzt aus dem NULL eine 0

COALESCE 🙂

 

[Kirby.exe]

Alright xD Ich habe jetzt sowas wie Cases gefunden, aber das kommt vom JOIN, das knr ambigous ist xD Keine Ahnung wie man das umgehen könnte 🙂

Select ks.knr,
       sum(case when ks.tonart is not null then 1 else 0 end) as gesamt,
       sum(case when ks.tonart Like '%-dur' then 1 else 0 end) as dur,
       sum(case when ks.tonart Like '%-moll' then 1 else 0 end) as moll
From (Komponist
      JOIN Stueck ON Komponist.knr = Stueck.knr) as ks
GROUP BY ks.knr

Edit:

Ich sollte mehr Kaffe trinken xD
So funktionierts:

Select Komponist.knr,
       sum(case when Stueck.tonart is not null then 1 else 0 end) as gesamt,
       sum(case when Stueck.tonart Like '%-dur' then 1 else 0 end) as dur,
       sum(case when Stueck.tonart Like '%-moll' then 1 else 0 end) as moll
From Komponist
        JOIN Stueck ON Komponist.knr = Stueck.knr
GROUP BY Komponist.knr

 

[Kirby.exe]

Update:

Ich habe es gelöst xD Meine gute heute war einfach nicht mein Tag xD

With gesamt as (Select Komponist.knr,
                sum(case when Stueck.tonart is not null then 1 else 0 end) as stuecke_ges,
                sum(case when Stueck.tonart Like '%-dur' then 1 else 0 end) as anz_tonart_dur,
                sum(case when Stueck.tonart Like '%-moll' then 1 else 0 end) as anz_tonart_moll
            From Komponist JOIN Stueck ON Komponist.knr = Stueck.knr
            GROUP BY Komponist.knr)
Select name,
        vorname,
        stuecke_ges,
        round((anz_tonart_dur*100.0)/stuecke_ges) as prozent_dur,
        round((anz_tonart_moll*100.0)/stuecke_ges) as prozent_moll
From Komponist JOIN gesamt on Komponist.knr = gesamt.knr
Where stuecke_ges >= 3
Group By name, vorname, stuecke_ges, anz_tonart_dur, anz_tonart_moll

 

[mihe7]

Ich habe jetzt sowas wie Cases gefunden

Geht auch. Du hättest aber einfach nur noch COALESCE() verwenden müssen 🙂