Komplizierte Durchschnittsberechnung

Hallo,

gegeben sei folgende Tabelle:

+----+---------+-----+------+---------------------+
| id | item_id | typ | user | datum |
+----+---------+-----+------+---------------------+
| 1 | 1 | a | 1 | 2007-11-07 12:00:00 |
| 2 | 1 | b | 1 | 2007-11-07 13:00:00 |
| 3 | 19 | a | 1 | 2007-11-07 11:00:00 |
| 4 | 19 | b | 1 | 2007-11-07 14:00:00 |
| 5 | 27 | a | 1 | 2007-11-07 12:00:00 |
| 6 | 27 | b | 1 | 2007-11-07 12:30:00 |
| 7 | 1 | a | 2 | 2007-11-07 12:00:00 |
| 8 | 1 | b | 2 | 2007-11-07 12:20:00 |
+----+---------+-----+------+---------------------+

Was ich nun möchte: geordnet nach usern möchte ich gerne den
Durchschnitt der Differenz der Daten aller Zeilen von typ b und b je
item_id.

Oder anders ausgedrückt: für jedes Zeilenpaar, das denselben user und
denselben item_id hat, möchte ich jeweils die Differenz zwischen dem
datum der Zeile mit typ b und der mit typ a.

Diese Differenzen sollen addiert und durch die Zahl der Zeilenpaare
geteilt werden.

Geht das irgendwie?

Zur Verfügung steht MySQL 5.0.45.

(id, item_id und user sind INTs, typ ist ein VARCHAR(20), datum ist ein
TIMESTAMP)

--
( ROT-13 if you want to email me directly: uvuc@ervzjrexre.qr )
"Das saarl. VwVfG läßt eine Interpretation deutscher Gesetze nur dann
zu, wenn sie nicht eindeutig sind." Manfred Saar, Präsident
Apothekerkammer d. Saarlandes. heute-journal v. 8. August 2006.

Re: Komplizierte Durchschnittsberechnung

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: Komplizierte Durchschnittsberechnung

In article <5qpi55F1139jnU32@mid.individual.net>,
Holger Pollmann writes:

> Was ich nun möchte: geordnet nach usern möchte ich gerne den
> Durchschnitt der Differenz der Daten aller Zeilen von typ b und b je
> item_id.

> Oder anders ausgedrückt: für jedes Zeilenpaar, das denselben user und
> denselben item_id hat, möchte ich jeweils die Differenz zwischen dem
> datum der Zeile mit typ b und der mit typ a.

> Diese Differenzen sollen addiert und durch die Zahl der Zeilenpaare
> geteilt werden.

> Geht das irgendwie?

Versuch's mal damit:

SELECT x1.user, sec_to_time(avg(timestampdiff(SECOND, x1.datum, x2.datum)))
FROM mytbl x1
JOIN mytbl x2 ON x2.user = x1.user AND x2.item_id = x1.item_id
AND x1.typ = 'a' AND x2.typ = 'b'
GROUP BY x1.user

Re: Komplizierte Durchschnittsberechnung

Holger Pollmann schrieb:

> [...]
> Oder anders ausgedrückt: für jedes Zeilenpaar, das denselben user und
> denselben item_id hat, möchte ich jeweils die Differenz zwischen dem
> datum der Zeile mit typ b und der mit typ a.
> [...]

Unabhängig von der konkreten Lösung: Du bist Dir bewußt, daß die Anzahl
der Differenzen für n Einträge pro User n über 2 beträgt? (Bei 100
Einträgen z.B. also 4950 Differenzen)
Je nach Größe der Datenbank kann das ein erheblicher Rechenaufwand werden...


Tschüs,

Sebastian

Re: Komplizierte Durchschnittsberechnung

Sebastian Suchanek wrote:

>> Oder anders ausgedrückt: für jedes Zeilenpaar, das denselben user und
>> denselben item_id hat, möchte ich jeweils die Differenz zwischen dem
>> datum der Zeile mit typ b und der mit typ a.
>> [...]
>
> Unabhängig von der konkreten Lösung: Du bist Dir bewußt, daß die Anzahl
> der Differenzen für n Einträge pro User n über 2 beträgt? (Bei 100
> Einträgen z.B. also 4950 Differenzen)

Und bei 1000 sogar 499500.
Nur wenn n hier nicht 100 sondern 1 ist (*ein* Zeilenpaar pro
User/item_id), gibt die Formel (n*(n-1) / 2) genau 1 zurück :-)

Dieter

Re: Komplizierte Durchschnittsberechnung

Sebastian Suchanek schrieb:

> Holger Pollmann schrieb:
>
>> Oder anders ausgedrückt: für jedes Zeilenpaar, das denselben user und
>> denselben item_id hat, möchte ich jeweils die Differenz zwischen dem
>> datum der Zeile mit typ b und der mit typ a.
>
> Unabhängig von der konkreten Lösung: Du bist Dir bewußt, daß die Anzahl
> der Differenzen für n Einträge pro User n über 2 beträgt? (Bei 100
> Einträgen z.B. also 4950 Differenzen)

Ich verstehe nicht, was Du meinst. Pro User und Item gibt es genau
_eine_ Differenz.

Gruß. Claus

Re: Komplizierte Durchschnittsberechnung

Holger Pollmann schrieb:

> gegeben sei folgende Tabelle:
>
> +----+---------+-----+------+---------------------+
> | id | item_id | typ | user | datum |
> +----+---------+-----+------+---------------------+
> | 1 | 1 | a | 1 | 2007-11-07 12:00:00 |
> | 2 | 1 | b | 1 | 2007-11-07 13:00:00 |
> | 3 | 19 | a | 1 | 2007-11-07 11:00:00 |
> | 4 | 19 | b | 1 | 2007-11-07 14:00:00 |
> | 5 | 27 | a | 1 | 2007-11-07 12:00:00 |
> | 6 | 27 | b | 1 | 2007-11-07 12:30:00 |
> | 7 | 1 | a | 2 | 2007-11-07 12:00:00 |
> | 8 | 1 | b | 2 | 2007-11-07 12:20:00 |
> +----+---------+-----+------+---------------------+
>
> Was ich nun möchte: geordnet nach usern möchte ich gerne den
> Durchschnitt der Differenz der Daten aller Zeilen von typ b und b je
^ ^ ???
> item_id.

Soll wohl "a und b" heißen :-)

Unabhängig davon halte ich das DB-Design für kaputt: Statt _zwei_
Einträgen pro Benutzer und Item mit jeweils _einem_ Timestamp (und einer
zusätzlichen Kennung zur Unterscheidung dieser Einträge) wäre _ein_
Eintrag mit _zwei_ Timestamps (und ohne diese Kennung) IMHO sinnvoller:

+----+---------+------+---------------------+--------------- ------+
| id | item_id | user | start | ende |
+----+---------+------+---------------------+--------------- ------+
| 1 | 1 | 1 | 2007-11-07 12:00:00 | 2007-11-07 13:00:00 |
| 3 | 19 | 1 | 2007-11-07 11:00:00 | 2007-11-07 14:00:00 |
| 5 | 27 | 1 | 2007-11-07 12:00:00 | 2007-11-07 12:30:00 |
| 7 | 1 | 2 | 2007-11-07 12:00:00 | 2007-11-07 12:20:00 |
+----+---------+------+---------------------+--------------- ------+

Das würde nicht nur Platz sparen, sondern auch die SELECT-Abfrage
deutlich vereinfachen. So deutlich, dass sogar Du sie hinbekommen
dürftest ;-)

Gruß. Claus

Re: Komplizierte Durchschnittsberechnung

Claus Reibenstein schrieb:
> Sebastian Suchanek schrieb:
>> Holger Pollmann schrieb:
>>
>>> Oder anders ausgedrückt: für jedes Zeilenpaar, das denselben user und
>>> denselben item_id hat, möchte ich jeweils die Differenz zwischen dem
>>> datum der Zeile mit typ b und der mit typ a.
>> Unabhängig von der konkreten Lösung: Du bist Dir bewußt, daß die Anzahl
>> der Differenzen für n Einträge pro User n über 2 beträgt? (Bei 100
>> Einträgen z.B. also 4950 Differenzen)
>
> Ich verstehe nicht, was Du meinst. Pro User und Item gibt es genau
> _eine_ Differenz.

Stimmt. Ich hatte Holgers Angaben dahingehend mißinterpretiert,
daß er die Differenzen der Datensätze gruppiert nach User und Typ
untereinander analysieren möchte.


Tschüs,

Sebastian

Re: Komplizierte Durchschnittsberechnung

Claus Reibenstein <4spammersonly@web.de> schrieb:

> Unabhängig davon halte ich das DB-Design für kaputt: Statt _zwei_
> Einträgen pro Benutzer und Item mit jeweils _einem_ Timestamp (und
> einer zusätzlichen Kennung zur Unterscheidung dieser Einträge) wäre
> _ein_ Eintrag mit _zwei_ Timestamps (und ohne diese Kennung) IMHO
> sinnvoller:

Nun... wie man es nimmt. An und für sich ist das ganze eine Art Logging
von Handlungen. Typ a ist normalerweise der erste Aufruf einer Ressource,
Typ b ist dir Vornahme einer bestimmten Handlung; ich möchte jetzt für
jeden User wissen, wieviel Zeit bei ihm durchschnittlich zwischen erstem
Aufruf und Handlung Typ b vergeht.

Jede dieser Handlungen hat eben nur einen Zeitpunkt und nicht zwei.

Wenn, dann wäre es höchstens angebracht, zum zwecke dieser Analyse eine
Tabelle wie die von dir gewünschte anzulegen. Aber an und für sich ist das
Design glaube ich schon recht sinnvoll...

--
( ROT-13 if you want to email me directly: uvuc@ervzjrexre.qr )
"Das saarl. VwVfG läßt eine Interpretation deutscher Gesetze nur dann zu,
wenn sie nicht eindeutig sind." Manfred Saar, Präsident Apothekerkammer d.
Saarlandes. heute-journal v. 8. August 2006.

Re: Komplizierte Durchschnittsberechnung

Holger Pollmann schrieb:

> Claus Reibenstein <4spammersonly@web.de> schrieb:
>
>> Unabhängig davon halte ich das DB-Design für kaputt: Statt _zwei_
>> Einträgen pro Benutzer und Item mit jeweils _einem_ Timestamp (und
>> einer zusätzlichen Kennung zur Unterscheidung dieser Einträge) wäre
>> _ein_ Eintrag mit _zwei_ Timestamps (und ohne diese Kennung) IMHO
>> sinnvoller:
>
> Nun... wie man es nimmt. An und für sich ist das ganze eine Art Logging
> von Handlungen. Typ a ist normalerweise der erste Aufruf einer Ressource,
> Typ b ist dir Vornahme einer bestimmten Handlung; ich möchte jetzt für
> jeden User wissen, wieviel Zeit bei ihm durchschnittlich zwischen erstem
> Aufruf und Handlung Typ b vergeht.

Dein Design trägt also bei Typ a und Typ b einfach einen neuen Datensatz
ein. Mein Design verlangt dies nur bei Typ a, während bei Typ b der
zugehörige Datensatz, der bei Typ a angelegt wurde, per UPDATE um den
zweiten TIMESTAMP erweitert werden muss.

Dein Design mag den Vorteil haben, dass die Erfassung der beiden
Zeitpunkte einfacher ist, da Du keine Unterscheidung zwischen den Typen
a und b durchführen musst, sondern beide identisch abhandeln kannst.
Dafür ist mein Design platzsparender und die Auswertung einfacher zu
bewerkstelligen.

Gruß. Claus

Re: Komplizierte Durchschnittsberechnung

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)