Statistik mit spaltenweiser Ausgabe der Monate

[ck123]

Hallo liebe Community,

ehrlich gesagt, ist es mir etwas peinlich, die folgende Frage zu stellen, aber momentan stehe ich gewaltig auf dem Schlauch und komme nicht weiter.
Zum besseren Verständnis ein bisschen Background:
Ich arbeite in einer Bibliothek und möchte eine Statistik erstellen, die die Zugriffe auf Onlinezeitschriften darstellt. Diese Statistik soll folgenden Aufbau haben.

Zeitschrift | Zugriffe im Januar | Zugriffe im Februar | Zugriffe im März | … | Zugriffe 2012

Die Daten lese ich aus dem Access-Log des Proxys (squid) und speichere sie in eine MySQL DB. Die Tabelle, auf die es ankommt, ist wie folgt aufgebaut.

proxyLog.jpg

Die Spalte journal enthält eine eindeutige ID, die die Zeitschrift kennzeichnet. Die Spalte Zeit speichert die Zugriffszeit.

Meine erste Idee war folgende Query

select journal, count(month(zeit) = '1') as Januar, … , count(zeit) as Gesamt from proxylog where year(zeit) = '2012' group by journal;

Als Ergebnis erhielt ich in jeder Spalte (außer der Ersten natürlich) die Anzahl aller Einträge einer Zeitschrift, nicht aber die Anzahl der Zugriffe des Monats.

Das Erweitern der group by Klausel um den Ausdruck month(zeit)

select journal, count(month(zeit) = '1') as Januar, … , count(zeit) as Gesamt from proxylog where year(zeit) = '2012' group by journal, month(zeit);

bringt mich meinem Ziel zwar schon näher, listet die Monate aber zeilen- und nicht spaltenweise.

Kann mir jemand, bevor ich noch verzweifle, einen kleinen Tipp bzw. Denkanstoß geben?

MfG

ck123

[akretschmer]

Kann mir jemand, bevor ich noch verzweifle, einen kleinen Tipp bzw. Denkanstoß geben?

MfG

ck123

Deine Briefmarke ist nicht wirklich lesbar, aber ich denke, Du suchst etwas in der Art, wie ich es mal hier erklärt habe: http://www.pg-forum.de/h-ufig-gestel...u-spalten.html

Andreas

[ck123]

Hallo Andreas,

vielen Dank für den Hinweis. Aber was heist denn hier Briefmarke? Wenn man den Aufbau der Tabelle kennt, ist es doch gar nicht so schwer zu lesen
Aber du hast recht, es ist ein bisschen klein. Für alle, denen es ähnlich geht, habe ich hier noch mal das CREATE TABLE der Tabelle:

CREATE TABLE proxyLog(
	zeit datetime not null, 
	elapsed int, 
	remotehost varchar(600) not null, 
	codeA varchar(90), 
	codeB int, 
	bytes bigint, 
	method varchar(90),
	url varchar(698) not null, 
	rfc931 varchar(90), 
	peerstatus varchar(90), 
	contenttype varchar(90),	
	journal varchar(50),
	PRIMARY KEY (zeit,remotehost,url), 
	FOREIGN KEY (journal) REFERENCES journals(issn) ON UPDATE CASCADE);

Hier noch Mal die Lösung:

select
	titel,
	sum(case when extract(month from zeit) = 1 then 1 else 0 end) as "Januar",
	sum(case when extract(month from zeit) = 2 then 1 else 0 end) as "Februar",
	sum(case when extract(month from zeit) = 3 then 1 else 0 end) as "März",
	sum(case when extract(month from zeit) = 4 then 1 else 0 end) as "April",
	sum(case when extract(month from zeit) = 5 then 1 else 0 end) as "Mai",
	sum(case when extract(month from zeit) = 6 then 1 else 0 end) as "Juni",
	sum(case when extract(month from zeit) = 7 then 1 else 0 end) as "Juli",
	sum(case when extract(month from zeit) = 8 then 1 else 0 end) as "August",
	sum(case when extract(month from zeit) = 9 then 1 else 0 end) as "September",
	sum(case when extract(month from zeit) = 10 then 1 else 0 end) as "Oktober",
	sum(case when extract(month from zeit) = 11 then 1 else 0 end) as "November",
	sum(case when extract(month from zeit) = 12 then 1 else 0 end) as "Dezember",
	count(journal) as "2012"
from proxylog
where extract(year from zeit) = 2012
group by journal;