bugfix: event cache stats

[rockus]

Hi there!

http://geocaching.rockus.org/caches_event.html hatte nicht alle Event Caches gelistet, sondern nur die, bei denen Leute schon waren. Ist wohl bedingt sinnvoll. Auszerdem war die available-Anzeige nicht korrekt.

Beides ist jetzt gefixt.

Falls jemandem was Simpleres als das Folgende einfaellt, bitte her damit:

select id as cacheID, name, sum(logs) as logsum, max(day) as day, available from ((select id, name, '' as logs, '' as day, available from cache where type='event') union (select cacheid, '' as name, count(*) as logs, min(date) as day, '' as available from logs inner join cache on logs.cacheid=cache.id where (logs.type='attended' or logs.type='find') and cache.type='event' group by cacheid) order by id, name desc) as events group by id order by logsum desc

Viel Spasz :)
rockus

[RedHunters]

Mit einem Outer Join ginge das einfacher falls ich die Problemstellung richtig verstanden habe.
...
from caches ca
left join
logs l
on l.cacheid = ca.cacheid
where ca.available = ...

lg
Stefan

[rockus]

Mit einem Outer Join ginge das einfacher falls ich die Problemstellung richtig verstanden habe.
...
from caches ca
left join
logs l
on l.cacheid = ca.cacheid
where ca.available = ...

lg
Stefan

Danke. Problemstellung hast richtig verstanden, Umsetzung funktioniert so aber leider nicht, weil:

select id, name, date, count(ca.id), l.type from cache ca left join logs l on l.cacheid=ca.id where ca.type='event' group by ca.id;

liefert *alle* logs zu den jeweiligen caches. Somit auch zwei (derzeit) zum April, April, bei dem aber noch keiner war.

Wennst das Ganze dann wieder einschraenkst auf ... and (l.type='attended' or l.type='find') in der where-clause, dann kommen Events, bei denen noch keiner war, wieder gar nicht vor.

Machst die Einsschraenkung mit having l.type='attended' or l.type='find' hinten dran (jn dem Gedanken, dasz er dann ja eigentlich nur die results filtern sollte damit), kommen ueberhaupt nur mehr zwei caches retour. Die dafuer mit korrekter Anzahl...

*gruebel*
rockus

[EarlGrey]

liefert *alle* logs zu den jeweiligen caches. Somit auch zwei (derzeit) zum April, April, bei dem aber noch keiner war.

rockus

ich bin ja jetzt nicht der sql-guru, aber kann man zukünfige caches nicht mit einem (sinngemäßen) "where datum_heute < datum_cache" oder so filtern

???
nur so eine idee ....

[RedHunters]

hmmm. Ich denk nochmal drüber nach.

Stefan

[rockus]

liefert *alle* logs zu den jeweiligen caches. Somit auch zwei (derzeit) zum April, April, bei dem aber noch keiner war.

rockus

ich bin ja jetzt nicht der sql-guru, aber kann man zukünfige caches nicht mit einem (sinngemäßen) "where datum_heute < datum_cache" oder so filtern

???
nur so eine idee ....

An sich richtig. So kriegst aber *nur* die zukuenftigen Caches, und ich will ja alle event-Caches drinnen haben und dazu die Summe der 'attended' und 'find' logs fuer jeden. Wennst das mit einem "where type='attended' or type='find'" einschraenkst, kriegst aber die zukuenftigen nimmer mit, weil dort diese beiden types eben noch nicht in den logs vorkommen.

Obige Loesung mit einem union und einem group darueber ist bis jetzt die einzige Moeglichkeit, wie ich diese Aufgabenstellung loesen konnte.

An sich sind unions ja genau dazu da, results aus zwei Abfragen aneinanderzuhaengen. Funktionieren tuts ja, als Perfektionist bin ich aber immer auf der Suche nach aesthetischeren Loesungen.

[RedHunters]

So jetzt hab ich nochmal gebastelt.
Das Select ist etwas einfacher als deines aber ob die Performance besser oder schlechter ist kann ich nicht sagen.
Ich hatte bei meinem Beispiel vorher das OUTER vergessen. Sollte ja auch ein Outer Join werden.

select c.id
, c.name
, count(l.cacheid) anz
, c.available
, min(l.date)
from cache c
left outer join
logs l
on l.cacheid = c.id
and (l.type = 'attended' or l.type = 'find')
where c.type ='event'
group by c.id, c.name
order by anz desc


Stefan

[EarlGrey]

ich meinte das mit "datum_heute OPERATOR datum_cache" so, je nachdem, was man eben haben will, d.h. mit ">", ">=" oder eben "<"

vielleicht kann man so mit zwei grossen "OR"-bäumen die jeweils interessanten "zweige" abfragen und gemeinsam darstellen.

welche normalformen werden bei der struktur erfüllt? wenn >3 nimmer dabei ist, muss man das wahrscheinlich in eine extratabelle auslagern und dort herum"join"en ...

oder :?::?::?:

[RedHunters]

Zusatz:
Du könntest min(l.date) gegen c.hidden austauschen.
Hatte den Spaltennamen vorher nicht im Kopf.

lg
Stefan

[rockus]

So jetzt hab ich nochmal gebastelt.
Das Select ist etwas einfacher als deines aber ob die Performance besser oder schlechter ist kann ich nicht sagen.
Ich hatte bei meinem Beispiel vorher das OUTER vergessen. Sollte ja auch ein Outer Join werden.

select c.id
, c.name
, count(l.cacheid) anz
, c.available
, min(l.date)
from cache c
left outer join
logs l
on l.cacheid = c.id
and (l.type = 'attended' or l.type = 'find')
where c.type ='event'
group by c.id, c.name
order by anz desc


Stefan

Super, danke. Das ist wesentlich simpler von der Logik und Verstaendlichkeit her. Das AND in der ON-clause bewirkt das gewünschte Verhalten. Dort hatte ich nicht gedacht, mit AND was zu verknuepfen.

Profiling brachte Folgendes:

Meine Variante:
25 rows in set (3.25 sec)
25 rows in set (3.25 sec)
25 rows in set (3.58 sec)
25 rows in set (3.27 sec)
25 rows in set (3.25 sec)
25 rows in set (3.26 sec)
25 rows in set (3.38 sec)

Deine Variante:
25 rows in set (5.39 sec)
25 rows in set (5.37 sec)
25 rows in set (0.00 sec)
25 rows in set (0.00 sec)
25 rows in set (0.00 sec)
25 rows in set (0.01 sec)
25 rows in set (0.00 sec)

Mir scheint, deine Variante ist cache-able, meine nicht. Das waere schon fein, wenn das ausnuetzbar ist.

[RedHunters]

Bitte gerne.

Ich weiß nicht ob das in MYSQL4 geht aber ich würde auf die Spalte logs.cacheid einen Foreign-Key zu cache.id anlegen.
Das würde bei joins zwischen cache und logs sicher was bringen.

Stefan

[rockus]

Zusatz:
Du könntest min(l.date) gegen c.hidden austauschen.
Hatte den Spaltennamen vorher nicht im Kopf.

lg
Stefan

Genau, hab ich jetzt eh gemacht. War mir nur net sicher, ob wirklich bei allen events als Hidden-Datum das korrekte eingetragen wird. Scheint aber so zu sein. Ich verwends jetzt jedenfalls mal...

rockus.

[rockus]

.. .wahrscheinlich in eine extratabelle auslagern und dort herum"join"en ...


Waere moeglich. Extratabellen sind aber ein Sicherheitrisiko, da dann wer anderer als nur das "wohldefinierte" skript schreibend auf die Datenbank zugreifen mueszte.

Is wohl machbar von der Granularitaet der Rechtevergaben unter SQL her, mag ich aber eher net.

Auszerdem isses die schoenere Loesung, wenns auch ohne geht :)

Danke aber fuers Mitdenken!

rockus