mega komplizierter query...

[yves030]

Hallo Experten,

ich breche mir die Finger bei der Lösungen des folgenden Problems:

Ich habe eine Tabelle mit einigen Spalten, von denen eine ein
TimeStamp ist und eine andere sowas ähnliches, diese enthält das
Unixdatum in millisekunden und speichert die Erstellungszeit des
Records. Sinn und Zweck des ganzen ist, eine Häufigkeitsverteilung
(Histogramm) zu erzeugen.

Ich möchte nun einen (schnellen) query bauen, der mir JEWEILS die
ANZAHL aller Zeilen zurückgibt, die innerhalb eines Anfangs- und
Endzeitpunktes liegen, aber gruppiert nach einem frei bestimmbaren
Intervall.
Klingt kompliziert? Ist es auch... hier sei ein Beispiel der
Tabelle (created seien hier der einfachheit halber mal sekunden):

id created data1 data2
 1     10    a     k
 2     20    b     l
 3     30    c     m
 4     40    d     n
 5     80    e     o
 6     83    f     p
 7    250    g     q
 8    370    h     r
 9    388    i     s
10    409    j     t

Wie muß der Query aussehen, wenn ich die Häufigkeiten (Anzahl der
Zeilen) innerhalb eines beliebigen Anfangs- und Endzeitpunktes
z.b. 30 <= created <= 500 unter Angabe des Gruppier-Intervalls
(z.b. 60 Sekunden) haben will (Im Klartext: "gib mir die Anzahl
der Datensätze, die jeweils innerhalb von 60 Sekunden nach dem
Zeitpunkt created=30 und vor dem Zeitpunkt created=500 angelegt
wurden)?
Obiges Beispiel müßte mir als Ergebnis bringen (id=nummer des
intervalls [in den Klammern steht zur Veranschauung der Bereich
des Intervalls]):

id           count(*)
 0( 30- 89)   4
 1( 90-149)   0
 2(150-209)   0
 3(210-269)   1
 4(270-329)   0
 5(330-389)   2
 6(390-449)   1

schick wäre zudem ein Limit, was mir die Anzahl der zurückgegebenen
count-zeilen begrenzt... Mit temporären Tabellen oder Programmlogik
wäre das kein Problem, aber die Frage ist, ob man das nicht auch
alles mit einem Select hinkriegt.... Ich bin für jeden Hinweis
oder gar ein funktionierendes Beispiel dankbar! (Die Tabelle ist
seeeeeeehr groß, ca. 1mio Zeilen..., und Performance spielt eine
große Rolle...). Ich weiß, wir sind hier nicht beim Wunschkonzert,
aber ich habe überhaupt keine Idee...
Danke schonmal an alle, die bis hierher gelesen haben!

yves030

[Hamstar]

Ich weiß, wir sind hier nicht beim Wunschkonzert,
aber ich habe überhaupt keine Idee...
Danke schonmal an alle, die bis hierher gelesen haben!

In der Regel kommt man da mit Aggregatfunktionen, der WHERE- und HAVING-Klausel, etwas "GROUP BY" (wg. der Gruppierung) und eventuell Unterabfragen. - Schon _irgendwas_ probiert?

Vorsichtshalber noch die Frage: Wie "heisst" die Datenhaltung (das RDBMS)?

[dedlfix]

echo $begrüßung;

Ich möchte nun einen (schnellen) query bauen, der mir JEWEILS die
ANZAHL aller Zeilen zurückgibt, die innerhalb eines Anfangs- und
Endzeitpunktes liegen, aber gruppiert nach einem frei bestimmbaren
Intervall.

Ob er schnell ist, müsstest du messen. Bei der Lösung, die mir einfällt, muss für jeden Datensatz innerhalb deines Intervalls der Gruppierungswert berechnet werden. Das gibts nicht kostenlos.

Wie muß der Query aussehen, wenn ich die Häufigkeiten (Anzahl der
Zeilen) innerhalb eines beliebigen Anfangs- und Endzeitpunktes
z.b. 30 <= created <= 500 unter Angabe des Gruppier-Intervalls
(z.b. 60 Sekunden) haben will

Die "Anzahl der Zeilen" bekommt ein COUNT(*) in der SELECT-Klausel hin.
"Innerhalb eines beliebigen Anfangs- und Endzeitpunktes" kann durch eine einfache WHERE-Klausel sichergestellt werden. (x BETWEEN a AND b oder x >= a AND x <= b)
Die "Angabe des Gruppier-Intervalls" müsste berechnet werden.

Mit [...] Programmlogik wäre das kein Problem,

Wie sähe denn deine Programmlogik aus? Meine nähme zur Ermittlung des Gruppierungswertes den created-Spaltenwert, zöge den Wert des Anfangszeitpunkts ab und ermittelte dann durch eine Integerdivision mit dem Intervall-Wert eine Zahl ohne Komma und Rest.

schick wäre zudem ein Limit, was mir die Anzahl der zurückgegebenen
count-zeilen begrenzt...

Solange da das DBMS mitspielt ist das kein Problem. Wenn es keine LIMIT-Klausel oder dergleichen kennt, gibt es sicherlich Workarounds.

(Die Tabelle ist seeeeeeehr groß, ca. 1mio Zeilen..., und Performance spielt eine
große Rolle...).

Tja... Da wird sich so einfach nicht viel machen lassen. Vielleicht ist es besser, wenn du ein Extra-Feld mit dem bereits berechneten Gruppierungswert anlegst. Doch ich befürchte, dass sich das aufgrund variabler Ausgangsparameter nicht machen lässt.

echo "$verabschiedung $name";

[Ilja]

yo,

grundsätzlich hilft es, immer das dbms und die version mit anzugeben, weil je nach verwendung unterschiedliche funktionalitäten zur verfügung stehen. ich nehme man an, wir reden von mysql ?

nun aber zu deiner abfrage. der erste teil ist einfach und ich plediere für den operator BETWEEN, den auch fedlfix erwähnte, da er "sprechender" ist.

SELECT COUNT(*)
FROM tabelle
WHERE created BETWEEN start AND ende

nun zu der gruppierung. in prinizip sollte es nicht so schwer sein, wie es im ersten augenblick aussieht. du musst halt eine gruppierung aus einer berechnung heraus bilden sprich:

SELECT berechnung, COUNT(*)
FROM tabelle
WHERE created BETWEEN start AND ende
GROUP BY berechnung

wobei ich die berechnung offen gelassen ist, da ich deine version und dein dbms nicht kenne. aber fachlich musst du den wert der spalte created durch das intervall teilen und die nachkommastellen wegschneiden. in etwa so:

funktion_nachkommastellen_abschneiden(created/intervall)

das alles anstelle von berechnung eingestzt sollte dir das gewünschte ergebnis liefern.

Ilja

[yves030]

Wow..

das war der Kick in die richtige Richtung.

Die Lösung liest sich einfacher als erwartet:

_I_ = gewünschtes Intervall
_S_ = Startzeit
_E_ = Endzeit
_L_ = max. Anzahl Zeilen

SELECT CAST(created/_I_ AS UNSIGNED) AS g,count(*) FROM test WHERE created BETWEEN _S_ AND _E_ GROUP BY g LIMIT _L_

Allerdings hat diese Lösung eine (leider) entscheidende Schwäche...
sie liefert keine Zeilen, in denen ein count(*) den Wert 0 ergeben
würde... d.h. es wendet die Intervalle auf die Daten an, und
schaut nicht welche Daten auf alle Intervalle zwischen Start und
Stop... deswegen hatte ich das Beispiel so gewählt , daß auch Zeilen
entstehen, wo Intervalle mit 0 Einträgen vorkommen...

Da ich nicht wüßte, wie man einen Select quasi als Schleife miß-/oder
gebrauchen könnte, wußte ich mir jetzt erst mal nur mit einer
weiteren Tabelle zu helfen, die nix weiter als nur IDs in
aufsteigender Reihenfolge enthält, mit deren Hilfe ich die Intervall-
grenzen erzeuge. Die Anzahl der Zeilen in dieser Hilfstabelle muß
mind. die Anzahl der gewünschten Ergebnisse sein. Mit Hilfe eines
Joins komme ich auf mein gewünschtes Ergebnis. Interessanterweise
bekomme ich als count nie 0, sondern immer 1, daher mein Test
auf NULL auf eine beliebige Datenspalte des Tabelle.
Das ganze funktioniert dann so:

nums ist die Hilfstabelle, die nur eine Spalte 'id' mit den Werten
1 bis 400 enthält (ich brauche immer nur max. 400 Werte..)

SELECT IF(isNull(test.data1),0,count(*)) AS anzahl FROM nums
 LEFT JOIN test ON
  test.ts BETWEEN _S_ AND _E_ AND
  test.ts>=(nums.id-1)*_I_ AND
  test.ts<(nums.id)*_I_
  GROUP BY nums.id

Falls da jemand einen Denkfehler sieht... bitte melden!

Danke an Euch, insbes. Ilja für das Auslegen der richtigen Spur.

yves

p.s.
RDBMS ist mysql 4.1 unter linux

[yves030]

p.s.
die Spalte "ts" ist die Spalte "created" aus meinem ersten Beispiel...
Um bei diesem Beispiel zu bleiben, hätte es eigentlich heißen müssen:

SELECT IF(isNull(test.data1),0,count(*)) AS anzahl FROM nums
  LEFT JOIN test ON
   test.created BETWEEN _S_ AND _E_ AND
   test.creatd>=(nums.id-1)*_I_ AND
   test.created<(nums.id)*_I_
   GROUP BY nums.id

SELECT IF(isNull(test.data1),0,count(*)) AS anzahl FROM nums
LEFT JOIN test ON
  test.ts BETWEEN _S_ AND _E_ AND
  test.ts>=(nums.id-1)*_I_ AND
  test.ts<(nums.id)*_I_
  GROUP BY nums.id

[dedlfix]

echo $begrüßung;

SELECT CAST(created/_I_ AS UNSIGNED)

MySQL kennt einen Integerdivisionsoperator (DIV), der ohne hinterher casten oder runden zu müssen bereits einen Ganzzahlwert liefert.

Allerdings hat diese Lösung eine (leider) entscheidende Schwäche...
sie liefert keine Zeilen, in denen ein count(*) den Wert 0 ergeben
würde... d.h. es wendet die Intervalle auf die Daten an, und
schaut nicht welche Daten auf alle Intervalle zwischen Start und
Stop... deswegen hatte ich das Beispiel so gewählt , daß auch Zeilen
entstehen, wo Intervalle mit 0 Einträgen vorkommen...

Das ist im Allgemeinen so üblich. Eine Datenbank liefert nur Daten die sie auch kennt.

SELECT IF(isNull(test.data1),0,count(*)) AS anzahl FROM nums
LEFT JOIN test ON
  test.ts BETWEEN _S_ AND _E_ AND
  test.ts>=(nums.id-1)*_I_ AND
  test.ts<(nums.id)*_I_
  GROUP BY nums.id
Falls da jemand einen Denkfehler sieht... bitte melden!

Nur die Verknüpfungsbedingungen sollen in die ON-Klausel. Wenn nums Werte von 1 bis 10 enthält und du aus test Intervall-Werte berechnest ist, sollten nur diese beiden die Verknüpfungsbedingung sein.

Ich würde hier versuchen, dein lückenbehaftetes Zähl-Statement als Subquery mit der nums-Tabelle zu verknüpfen.

echo "$verabschiedung $name";

[yves030]

Hallo zusammen,

merci für den Hinweis.
Leider musste ich feststellen, daß die Lösung zwei zwar
funktioniert, jedoch grottenlangsam ist.. dauert bereits
bei 100000 Zeilen fast 1 Minute...
Also habe ich mir nochmal Lösung 1 angeschaut (da wo
die Nullen fehlten) und bei genauem hinsehen erkannt, daß
ich dort auf die fehlenden Zeilen schließen kann und dieser
Request bewegt sich ausführungstechnisch im Bereich weniger
Millisekunden... (mit cast oder div gab keinen Unterschied...)

Ich bekomme ja in der ersten Spalte des Selects (g) als ganz-
zahligen wert... aber der ist ja nicht fortlaufend! Da wo Lücken
sind, gabs auch keine Objekte in entsprechenden Intervall...

Bekomme ich als Ergebnis des Querys

SELECT CAST(created/_I_ AS UNSIGNED) AS g,count(*) FROM test
WHERE created BETWEEN _S_ AND _E_ GROUP BY g LIMIT _L_
z.B.:

g  count(*)
 1    4
 4    1
 5    2
 7    1

und schaue mir die Werte von g an, sehe ich Lücken zwischen
(1 und 4) sowie zwischen (5 und 7). Das 2. und 3. sowie das
6. Intervall ergaben somit 0 Objekte...
Somit kann ich also bereits mit dem ersten Query und "geschicktem"
Auslesen ein vollständiges Verteilungsschema errechnen.

Grüße,
yves030