SQL Count und Join

[Jakob]

Hallo,

ich habe eine Log-Tabelle der Art
Stunde des Tages | Herkunftsland
vereinfacht sähen die Einträge so aus:
17 | de
17 | en
18 | en
18 | en
18 | de
(2 Menschen sind um 17 Uhr auf die Seite gegangen, einer deutsch, der andere englisch, 3 um 18 Uhr usw.)

Nun möchte ich per SQL-Abfrage folgendes erstellen:

Uhrzeit | de | en
17 | 1 | 1
18 | 2 | 1

Die Länder stehen nun in den Spalten, pro Uhrzeit eine Zeile. Wie bekomme ich das hin? Es müsste ja irgendwie sowas sein wie:

SELECT Uhrzeit, COUNT(?) as de, COUNT(?) as en
GROUP BY Uhrzeit

Leider fehlen mir die Fragezeichen…
Vielen Dank im Voraus für einen Tipp! Meine in dem Zusammenhang mal was von einem Self Join gehört zu haben, bringt das dort was? Kann's nicht drauf anwenden.

 

[timbajr]

wenn ichs richtig versteh müsste es mit select count(*) from ... group by uhrzeit, land

ok falsch verstanden *sorry*

 

[Jakob]

Leider nicht, man bekommt dann wieder so viele Uhrzeiteinträge, wie es zu dieser Stunde Länder gab. Wäre also noch ein „19 | de“ in der Tabelle ergibt dein Code:
Uhrzeit | Land
17 | 1
17 | 1
18 | 1
18 | 2
19 | 1

Das ist zwar nach Ländern aufgeteilt, aber leider nicht nach Zeilen und nicht Spalten. d.h. die Uhrzeit kommt mehrmals vor, was sie nicht sollte.

 

[norbi]

http://www.techonthenet.com/sql/index.php

No.

 

[Jakob]

http://www.techonthenet.com/sql/index.php

No.

Danke für den heißen Tipp .

Nen einfaches Stichwort wie Self Join, View oder sonstwas hätte gereicht.

 

[norbi]

Wüßte ich es selbst, hätte ich's Dir sofort gesagt...
Leider weiß ich es nicht, aber glaube, wo es steht.
Drum der Link.

Bitte, gerne, kein Problem.

No.

 

[Jakob]

Ah ok, hatte das „No.“ als Auskunftsverweigerung verstanden. Habe erst letztens ein 500-Seiten SQL-Buch gelesen, das Problem scheint mir nur leider nicht trivial. Die richtige URL wäre aber auch http://dev.mysql.com/doc/refman/5.0/en/index.html gewesen

 

[DeineMudda]

SELECT Uhrzeit, Count(*) AS DE FROM xyz WHERE Herkunftsland = 'de' GROUP BY Uhrzeit
UNION
SELECT Uhrzeit, Count(*) AS EN FROM xyz WHERE Herkunftsland = 'en' GROUP BY Uhrzeit;

 

[timbajr]

Geh ich richtig davon aus, dass du MySQL verwendest ?
Ich frage deswegen , weil es - soweit ich weiß - zB unter bestimmten MySQL Versionen noch keine subselects gibt. Auch UNIONS gehen da net

Mit Selfjoins ? ich denk mal drüber nach so spät schon neben Fußball ..

 

[DeineMudda]

Ich glaub, seit 4.1 alpha kann MySQL die meisten "Ferkeleien". Und 5.0 sowieso.

 

[Jakob]

Bin auf 5.1, da geht das alles. Hab die Lösung gefunden! Viel einfacher als von mir gedacht:

SELECT Uhrzeit, Land, Count(Sprache = "de" OR NULL) as de, Count(Sprache = "en" OR NULL) as en
FROM tabelle
GROUP BY Uhrzeit

Toll

Danke an den ersten Eintrag von hier http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html und natürlich Euch allen bei der schnellen und eifrigen Unterstützung.

 

[timbajr]

Ich glaub, seit 4.1 alpha kann MySQL die meisten "Ferkeleien". Und 5.0 sowieso.

Soweit mir bekannt - aber bitte steinigt mich nicht, wenn ich unrecht hab - sind subselects und unions erst ab 5.0 implementiert ... und viele ham ja noch 4.**** laufen

 

[DeineMudda]

Ich lern ja immer gerne dazu: Warum denn das "OR NULL" in beiden Count()s?

Edit: Subselects gehen definitiv seit 4.1. Unions - keine Ahnung - hab ich noch nie produktiv benötigt

 

[dpr]

Bei solchen Datenbankentwürfen rollen sich mir regelmäßig die Fußnägel hoch

Hier mal eine standard-konforme Lösung:

SQL> select r1.hour, de, en from (select hour, count(country) as de from r where country='de' group by hour) r1, (select hour, count(country) as en from r where country='en' group by hour) r2 where r1.hour=r2.hour;

      HOUR         DE         EN
---------- ---------- ----------
        17          1          1
        18          1          2

 

[Jakob]

@dpr: Die Tabelle ist eigentlich viel umfangreicher. Eine Art Logfile, nur als DB-Tabelle. Zufälligerweise ist aber ein Timestamp und eine IP->Länderherkunft mit drin, dadurch kann ich jetzt dieses tolle Diagramm erstellen:

@Mudda: Keine Ahnung wegen der OR NULLs, aber ohne funktioniert's nicht, bzw. er zeigt in jeder Spalte immer die gesamten, also nicht Länderspezifischen Einträge an (jede Spalte pro Reihe ist somit gleich).

Interessant, dass die Resteuropäer anscheinend viel später surfen als die Deutschen, vor allen Dingen gar nicht gegen 9, wo DE sein Maximum hat. Habe letztens gelesen, über die Hälfte der Deutschen arbeitet mittlerweile an einem Computer mit Internetanschluss. Ein Schelm, wär nun Böses denkt. (Statistik ist von einem Produktkatalog)

 

[DeineMudda]

Bei solchen Datenbankentwürfen rollen sich mir regelmäßig die Fußnägel hoch

Hier mal eine standard-konforme Lösung:

Das ist ein UNION umständlich formuliert

 

[dpr]

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

Do not use this feature if the columns you omit from the GROUP BY part are not constant in the group. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

Autsch! Dazu sage ich jetzt lieber nichts...

 

[dpr]

Das ist ein UNION umständlich formuliert

Nö, das ist ein natürlicher Verbund (der strukturell ein Schnitt ist). Eine Vereinigung ist etwas anderes.

EDIT: Das hättest Du auch gesehen, wenn Du Dein UNION-Beispiel

SELECT Uhrzeit, Count(*) AS DE FROM xyz WHERE Herkunftsland = 'de' GROUP BY Uhrzeit
UNION
SELECT Uhrzeit, Count(*) AS EN FROM xyz WHERE Herkunftsland = 'en' GROUP BY Uhrzeit;

einmal ausprobiert hättest. Abgesehen davon, das das Zielschema einer Vereinigung durch den linken Operanden bestimmt wird, kommt Deine "Lösung" auch inhaltlich nicht ansatzweise hin:

SQL> select hour, count(*) as de from r where country='de' group by hour union select hour, count(*) as en from r where country='en' group by hour;

      HOUR         DE
---------- ----------
        17          1
        18          1
        18          2

 

[Jakob]

Hallo dbr,

vielen Dank für den Hinweis mit den unvorhersehbaren Resultaten. Habe es jetzt mehrfach nachgeprüft, doch es funktioniert.

Zu Deiner sehr guten Lösung:
Bei mir gibt er da immer nur die erste Zeile aus des gewünschten Resultsets aus. Hast Du das Skript bei Dir getestet?

 

[dpr]

vielen Dank für den Hinweis mit den unvorhersehbaren Resultaten. Habe es jetzt mehrfach nachgeprüft, doch es funktioniert.

Das ist Zufall und kann sich gleich morgen ändern, wenn Du es nicht automatisch überwachst.

Hast Du das Skript bei Dir getestet?

Selbstverständlich. Was Du da siehst, kommt so aus Oracle und sollte bei jedem System, das sich nur ansatzweise an den Standard hält, auch exakt so aussehen. Ich suche nochmal ein DB2, das wird aber dasselbe ergeben.