Komplexe SQL Abfrage

[Martin-FFB]

Hallo zusammen,

ich habe ein kleines Problem, an welchem ich scheitere...

ich habe 3 Tabellen:

[BENUTZER]
userid
username

[DATEIEN]
userid
fileid
fileyear

[MIGRATIONSJOB]
userid
fileid
status

Ich habe aktuell eine Auswertung, welche mir die Anzahl aller Dateien eines Benutzers anzeigt.

SELECT [BENUTZER].USERID, [BENUTZER].USERNAME, COUNT(*) AS MESSAGES FROM BENUTZER INNER JOIN DATEIEN ON [BENUTZER].USERID = [DATEIEN].USERID GROUP BY [BENUTZER].USERID, [BENUTZER].USERNAME

Soweit, so gut...

nun benötige ich aber zusätzlich noch die Angabe der Summer aller Dateien, bei welcher der Wert in "fileyear" größer als 2012 ist UND die summer aller Werte in MIGRATIONSJOB für diesen User, bei welchen der Status 99 ist und bei welchen der Status 0 ist...

Also im Ergebnis:
BENUTZER.USERID | BENUTZER.USERNAME | SUMME(DATEIEN) | SUMME(DATEIEN Wenn fileyear => 2012) | SUMME(MIGRATIONSJOB Wenn Status = 0) | SUMME(MIGRATIONSJOB Wenn Status = 99)

Hat da jemand einen Tipp für mich? Ich komme schon über die 2. Summe nicht hinaus.

Vielen Dank
Herzliche Grüße
Martin

 

[akretschmer]

sieht nach kaputtem Design aus. DATEIEN und MIGRATIONSJOB haben gemeinsam userid und fileid.

Mal als Schnellschuß aus der Hüfte, mache eine Join dieser beiden Tabellen

select d.userid, d.fileid, d,year, m.status from dateien d cross join migrationsjob m where (d.userid,d.fileid)=(m.userid, m.fileid);

und joine die Benutzertabelle via userid mit dieser. Die unterschiedlichen counts kannst Du via FILTER machen.

test=*# select * from benutzer ;
 userid |  name  
--------+--------
      1 | name 1
      2 | name 2
(2 rows)

test=*# select * from dateien ;
 userid | fileid | year
--------+--------+------
      1 |      1 | 2011
      1 |      2 | 2012
      1 |      3 | 2013
(3 rows)

test=*# select * from migrationsjob ;
 userid | fileid | status
--------+--------+--------
      1 |      1 |      0
      1 |      2 |     10
(2 rows)

test=*# select b.userid,b.name, count(foo.fileid), count(foo) filter (where status in (0,99)) as Null_oder_99, count(foo) filter (where year >= 2012) as anzahl_ueber_2012 from benutzer b left join (select d.userid, d.fileid, d,year, m.status from dateien d cross join migrationsjob m where (d.userid,d.fileid)=(m.userid, m.fileid)) foo on b.userid=foo.userid group by b.userid, b.name;
 userid |  name  | count | null_oder_99 | anzahl_ueber_2012
--------+--------+-------+--------------+-------------------
      1 | name 1 |     2 |            1 |                 1
      2 | name 2 |     0 |            0 |                 0
(2 rows)

Falls FILTER nicht geht zu case when ... umbauen.

 

[Martin-FFB]

Hallo akretschmer.
Vielen Dank für deine Antwort... Leider bin ich wohl doch Zuviel noob :-( Ich verstehe nur Bahnhof...

Ich hab auch gesehen, das ich da nen Fehler in er Tabellen Struktur habe - es fehlt eine Wichtige. Bezüglich "Kaputtes" Design: Die Tabellen "Benutzer", "Dateien" und "Profile" sind von einer Softwarelösung. Die Tabelle "Migationsjob" habe ich hinzugefügt, weil wir die Dateien gerade auf ein anderes System Migrieren und ich ein kleines Tool gebastelt habe, das die Migration macht - bin aber leider kein SQL Guru sondern versuch mit meinen - nennen wir es mal erweiterten Grundkenntnissen das halt hinzubekommen...

Die Tabelle, welche ich übersehen habe ist:
[PROFILE]
fileid
filedate
Und das Fileyear ist bei Dateien nicht drinnen sondern kommt aus YEAR(filedate) von Profile :-(
[DATEIEN]
userid
fileid


Was ich brauche ist im Prinzip einmal pro Woche einen Report, der anzeigt, wieviele Dateien bereits Migriert wurden und wieviele fehlgeschlagen sind und das pro user. Außerdem brauche ich die Zahlen, wie viele insgesamt zu migrieren sind - das sind 2 Zahlen, einmal die, welche insgesamt vorhanden sind per user und dann die, welche neuer als 31.12.2011 sind...

Also das Ergebnis soll so aussehen:

[BENUTZER].USERID, [BENUTZER].USERNAME, COUNT(SELECT FROM DATEIEN WEHRE USERID= AKTUELLER USER) AS ALLEDATEIEN, COUNT(SELECT FROM DATEIEN WEHRE USERID= AKTUELLER USER AND YEAR(SELECT filedate from PROFILE WHERE fileid in (select fileid from DATEIEN where userid= AKTUELLER USER)>=2012) AS ALLENEUENDATEIEN, COUNT(SELECT FROM MIGRATONSJOB WHERE USERID = AKTUELLER USER AND STATUS=0) AS SUCESSFULL, COUNT(SELECT FROM MIGRATONSJOB WHERE USERID = AKTUELLER USER AND STATUS=9) AS FAILED


Wenn ich die von Dir geschriebene Query nehme, meckert er die Stelle "migrationsjob m where (d.userid,d.fileid)=(m.userid, m.fileid))" an und macht dann natürlich den cross join nicht

Sorry - ich weiß, ich sollte sowas den Profis überlassen :-()

Danke für die Hilfe...

 

[akretschmer]

Sorry - ich weiß, ich sollte sowas den Profis überlassen :-()

Genau. Reklamiere das, gut ist (oder wird) das. Oder wirf mit Geld um Dich, daß Euch das jemand macht. Ich kann zumindest mit M$SQL nicht viel helfen - und wenn das eine gekaufte & bezahlte Lösung ist, dann verliert ihr vermutlich Eure Garantierechte daran, wenn ihr selber da eingreift. Die Tatsache, daß Du da schon Dinge vergessen/falsch dargestellt hast, spicht Bände. Daher ist (für mich) hier auch "Ende Gelände". Man will ja nicht mitschuldig werden ...

Andreas

 

[castorp]

Wenn ich das richtig verstehe, dann suchst Du sowas:

SELECT
  b.userid,
  b.username,
  COUNT(d.userid) AS alledateien,
  COUNT(case when d.fileyear >= 2012 then 1 end) AS allen_euen_dateien,
  COUNT(case when m.status = 0 then 1 end) AS sucessful,
  COUNT(case when m.status = 90 then 1 end) AS failed
FROM benutzer b
  LEFT JOIN dateien d ON d.userid = b.userid
  LEFT JOIN migrationsjob j ON j.userid = d.userid and j.fileid = d.fileid
GROUP BY b.userid, b.username;

Ich weiß nicht was für ein Datentyp die Spalte "fileyear" ist, aber das obige Statement geht davon aus, dass es eine Zahl ist (vermute ich, basierend auf dem Namen der Spalte). Wenn es aber z.B. ein DATE oder DATETIME ist, dann musst Du stattdessen das Jahr mittels datepart() extrahieren: datepart(year, d.fileyear)

Der Trick ist das CASE innerhalb der count() Funktion. Wenn die Bedingung nicht erfüllt ist, dann liefert das einen NULL-Wert. Aggregatsfunktionen ignorieren aber NULL Werte, also werden nur die Zeilen gezählt in denen das Ergebnis des CASE Ausdrucks wahr ist.

 

[ukulele]

Grundsätzlich gibt es in akretschmers Code zwei Sachen die in MSSQL umformuliert werden müssen da Syntax unterschiedlich. castorp hat das schon aufgegriffen. Zum einen lassen sich 2 Werte in MSSQL nicht so abgleichen:
(d.userid,d.fileid)=(m.userid, m.fileid)
sondern nur so:
d.userid = m.userid AND d.fileid = m.fileid

Und FILTER gibt es in MSSQL nicht, dazu muss ein CASE herangezogen werden. Jetzt bin ich mir nicht sicher ob das mit count() so gut funktioniert, ich nutze immer sum(). Beispiel:
sum(case when d.fileyear >= 2012 then 1 else 0 end) AS allen_euen_dateien
Der Effekt ist dann der selbe wie bei einem count() nur das du eben genau bestimmst wann gezählt wird.

Inhaltlich habe ich den Thread jetzt nur überflogen aber mit dem CASE solltest du in der Lage sein alles zu zählen.

 

[castorp]

sum(case when d.fileyear >= 2012 then 1 else 0 end) und count(case when d.fileyear >= 2012 then 1 end) machen exakt das Gleiche, da count() (wie auch alle anderen Aggregatsfunktionen wie sum()) NULL Werte ignoriert.

Online Beispiel: nulls, Sql Server - rextester

Ich persönlich finde die Verwendung von count() zum Zählen intuitiver als eine Summierung via sum().
Letztendlich ist es eine Frage des persönlichen Geschmacks.