✔ [db2] SQL-Anfrage

[oraclin25]

Hallo zusammen,

ich habe (schon wieder) eine Frage bzgl SQL-Anfrage:

Ausgangstabelle ist folgendes(redundante Records sind erlaubt):

Schule        Spendenbetrag
---------------------------
 
Schule_01     15
Schule_01     20
Schule_01     30
Schule_01     30
Schule_02     15
Schule_02     100
Schule_03     15

Ich möchte gerne eine Auflistung von Schulen erstellen; jede Schule soll die Anzahl der Spendenvorgänge ANDERER Schulen ermittelt bekommt. Bis hierhin habe ich hinbekommen:

SELECT Tabelle1.Schule, (SELECT COUNT(*) FROM Tabelle1)-SUM(1) AS Anzahl
FROM Tabelle1
GROUP BY Tabelle1.Schule;

Was ich nicht hinbekomme ist, eine weitere Einschränkung zu implementieren: Es sollen lediglich jene Spendenvorgänge aufgezählt werden, die in Höhe von EUR 15 sind. Also, das Ergebnis für obige Tabelle sieht dann so aus:

Schule       Anzahl
-------------------
 
Schule_01    2
Schule_02    2
Schule_03    2

Schule_01 hat als Anzahl die 2, einmal der Spendenvorgang von Schule_02, einmal von Schule_03. Die beiden Spendenvörgänge sind ja jeweils in Höhe von EUR 15.

Bisher habe ich lediglich folgendes geschafft:

SELECT Tabelle1.Schule, (SELECT COUNT(*) FROM Tabelle1 WHERE Spendenbetrag = '15') AS Anzahl
FROM Tabelle1
GROUP BY Tabelle1.Schule;

Also, damit kriege ich ja schon mal die Anzahl der Datensätze mit Spendenbetrag = EUR 15:

Schule        Anzahl
--------------------
 
Schule_01    3
Schule_02    3
Schule_03    3

Ich muss nur noch ein "minus" einbauen:
Für Schule_01 --> 3 - 1 (die 1 kommt daraus, dass Schule_01 einen Spendenvorgang in Höhe von EUR 15 erhielt)
Für Schule_02 --> 3 - 1 (analog)
Für Schule_03 --> 3 - 1 (analog)

Habt Ihr vielleicht eine Idee, wie ich das hinkriege?

Vielen lieben Dank.

Schöne Grüße aus Rheinland,

Eure Ratna

 

[BaseBallBatBoy]

SELECT
  s.name,
  t.tot - COUNT(s.betrag) AS cnt
FROM
  schule s,
  (SELECT COUNT(*) AS tot FROM schule WHERE betrag = 15) t
WHERE 
  s.betrag = 15
GROUP BY 
  s.name, t.tot
ORDER BY 
  s.name;

 

[oraclin25]

Hallo BaseBallBatBoy,

vielen Dank erstmal für die rasche Antwort.

leider ist es bei dem obigen Code noch nicht das erwünschte Ergebnis, da von vorne rein bereits ein paar Zeilen durch die 2. WHERE-Bedingung gefiltert werden. Es sollen alle Schulen mitberücksichtigt werden. Zum Beispiel, bei folgender Ausgangstabelle:

Schule        Spendenbetrag
---------------------------
 
Schule_01     15
Schule_01     20
Schule_01     30
Schule_02     30

soll die Ergebnistabelle so aussehen:

Schule        Anzahl
---------------------------
 
Schule_01     0
Schule_02     1

Und mit dem obigen Code wird Schule_02 ja durch die Where-Bedingung von vorne rein rausgefiltert.

Schwieriger Fall oder?

Schöne Grüße aus Rheinland,

Eure Ratna

 

[BaseBallBatBoy]

Hässlich, aber naja, mir kam gerade nichts anderes in den Sinn

SELECT 
  a.name,
  s.tot_cnt_15 - a.own_cnt_15 AS foreign_cnt_15
FROM
  (
    SELECT
      school.name,
      CASE 
        WHEN has_15.cnt IS NULL THEN 0 
        ElSE has_15.cnt 
      END AS own_cnt_15 
    FROM
    -- alle schulen
    (
    SELECT DISTINCT
      name
    FROM
      schule2
    ORDER BY
      name
    ) school
    LEFT JOIN
    -- alle schulen mit 15
    (
    SELECT 
      name,
      COUNT(*) AS cnt 
    FROM 
      schule2 
    WHERE 
      betrag = 15
    GROUP BY 
      name
    ORDER BY 
      name
    ) has_15
    ON
    has_15.name = school.name
  ) a,
  (
    SELECT COUNT(*) AS tot_cnt_15 FROM schule2 WHERE betrag = 15
  ) s
ORDER BY
  a.name;

 

[j2se]

In der Regel werden deratige Probleme/Zählen innerhalb eines Attributs mit der Formel SUM(CASE WHEN Attribut_trifft_zu THEN 1 ELSE -1 END) summe

Gruppiert man nun nach schule, wirst du pro Schule die Anzahl gemäss Formel erhalten. Die Berechnung wird innerhalb von SUM() gemacht, indem für das zutreffende Kriterium dynamisch eine 1 zurückgeliefert wird und für das unzutreffende eine -1. Ich habe gerade unter MySQL eine Testtabelle mit einigen Testdaten erzeugt, und die Abfrage funktioniert korrekt. Da ich beruflich unter DB2 entwickle, kann ich bestätigen, dass DB2 CASE/WHEN ebenfalls unterstützt. Überraschend war für mich festzustellen, dass MySQL dies ebenfalls untersützt.

Die Testdaten:



und die Abfrage mit den Ergebnisse. Man beachte, alle Werte grösser 10 werden mit +1 addiert, die übrigen mit -1.



Ich hoffe, der Meccano ist verständlich.

 

[BaseBallBatBoy]

In der Regel werden deratige Probleme/Zählen innerhalb eines Attributs mit der Formel SUM(CASE WHEN Attribut_trifft_zu THEN 1 ELSE -1 END) summe

Stimmt. Wahr wohl reichlich spät gestern, als dass noch anständiger Code dabei herausgekommen wäre

Nun etwas kompakter:

    SELECT 
      a.name,
      s.tot_cnt_15 - a.own_cnt_15 AS foreign_cnt_15
    FROM
      (
        SELECT
          name,
          SUM(CASE WHEN betrag = 15 THEN 1 ELSE 0 END) AS own_cnt_15 
        FROM
          schule2 
        GROUP BY
          name
      ) a,
      (
        SELECT COUNT(*) AS tot_cnt_15 FROM schule2 WHERE betrag = 15
      ) s
    ORDER BY
      a.name;

 

[j2se]

Mit Verlaub, aber selbst die kompaktere Variante bleibt nicht nur in der Syntax, sondern in der Ausführung hässlich. Bei sehr kleinen Tabellen mag das vernachlässigbar sein, bei einer Tabelle mit mehreren Millionen Rows nicht mehr.

 

[oraclin25]

Hallo j2se und BaseBallBatBoy,

vielen lieben Dank für Eure Hilfestellungen. Der Code von BaseBallBatBoy hat funktioniert. Ursprünglich hatte ich folgenden Code:

SELECT DISTINCT t2.schule,
  (SELECT COUNT(*) FROM tabelle AS t1 WHERE  t1.schule != t2.schule)
FROM tabelle AS t2

Ich habe diesen dann auch letzte Nacht getestet; bei einer kleinen Menge von Datensätze hat er wunderbar funktioniert. Aber auf der tatsächlichen Datenbank(ich habe mehrere hundert von Millionen von Datensätzen und zehntausende von Schulen), war der Code überfordert, er läuft läuft und läuft ohne Ende. Ich vermute mal, der COUNT-Klausel überfordert den DBMS.

Dank der Idee von j2se hat mir BaseBallBatBoy einen völlig anderen Code vorgeschlagen; statt COUNT wird hierbei SUM verwendet, um die Hauptarbeit zu leisten:

SELECT 
      a.name,
      s.tot_cnt_15 - a.own_cnt_15 AS foreign_cnt_15
    FROM
      (
        SELECT
          name,
          SUM(CASE WHEN betrag = 15 THEN 1 ELSE 0 END) AS own_cnt_15 
        FROM
          schule2 
        GROUP BY
          name
      ) a,
      (
        SELECT COUNT(*) AS tot_cnt_15 FROM schule2 WHERE betrag = 15
      ) s
    ORDER BY
      a.name;

Die Ausführungszeit von dem Code war "gut" --> 1,5 Stunden.

Nun weiss ich natürlich nicht, ob im Bezug auf die Ausführungszeit dies schon der optimale Code ist. Aber ich bin ehrlich gesagt schon mal froh, dass ein lauffähiger Code da ist. Von daher, bitte um Vorschläge, wenn welche einfallen. Vielen lieben Dank für Eure Hilfestellungen, bin echt dankbar.

Schöne Grüße aus Rheinland,

Eure Ratna

 

[j2se]

Selbst der COUNT ist Non Sense und obsolet, da man diese Abfrage im ersten SUM erledigen kann. Die Ausführungszeit für eine deratige Abfrage ist viel zu lange und frisst unnötige Ressourcen. Frage doch Deine DBAs für eine Optimierung.

 

[oraclin25]

Hallo j2se,

vielen Dank für die rasche Antwort.

da man diese Abfrage im ersten SUM erledigen kann

Hättest Du vielleicht einen Vorschlag, wie man dies schaffen kann? Bei dem Einzeiler:

select schule, sum(case when spendenbetrag > 10 then 1 else -1 end) from tabelle group by schule;

funktioniert der Code so leider nicht, da eigentlich die Anzahl der Spendenvörgänge bzgl. der anderen Schulen ist, die vom Interesse ist. Also, bei diesem Einzeiler wird ja immer nur die betroffene Schule in Betracht gezogen (wegen GROUP BY).

Vielen Dank.

Schöne Grüße aus Rheinland,

Eure Ratna