[MYSQL] Problemen met inner join / verschil rows

[Sponge]

Hmm, ik zit nu al een tijdje te klooien met een bepaalde query. Voor mijn weerstation ben ik allemaal grafiekjes aan het toveren, tot nu toe zijn de meeste grafieken allemaal gelukt (zoals gemiddelde temperatuur per week, of de temperatuur per uur, etc).

Echter, is er een query die ik graag werkend zou willen hebben. Een query die het verschil tussen de luchtdruk berekend, en kijkt of er in dezelfde interval (een dag) ook regen gevallen is. Ik kom er simpelweg niet uit. Heb al meerdere kerende een hangende mysqld-nt gehad, blijkbaar omdat m'n query niet zo leuk gevonden werd .

Ik heb dus een tabel 'weather' waar op dit moment de relevante velden zijn:

- timestamp
- rain_1h (is echter totale regen ooit gevallen, cumulatief)
- rel_pressure de luchtdruk.

Ik heb nu al iets wat semi goed is,

code:

SELECT a.timestamp AS "Date 1", a.rel_pressure AS "Pressure 1", 
a.rain_1h AS "Rain A", 
b.timestamp AS "Date 2", b.rel_pressure AS "Pressure 2", 
b.rain_1h AS "Rain B", b.rain_1h - a.rain_1h AS Difference
FROM weather AS a
INNER JOIN weather AS b ON datediff(
a.timestamp, b.timestamp
) =1
LIMIT 0 , 300

(de limit is om alles fatsoenlijk te houden, er zijn namelijk al heel veel records)

Dit geeft echter:

code:

Date 1  Pressure 1  Rain A  Date 2  Pressure 2  Rain B  Difference  
2004-04-26 00:00:00 1011.0 13.4 2004-04-25 13:30:00 1012.8 13.4 0.0 
2004-04-26 00:10:00 1011.1 13.4 2004-04-25 13:30:00 1012.8 13.4 0.0 
2004-04-26 00:20:00 1010.9 13.4 2004-04-25 13:30:00 1012.8 13.4 0.0 
2004-04-26 00:30:00 1010.8 13.4 2004-04-25 13:30:00 1012.8 13.4 0.0 
2004-04-26 00:40:00 1010.8 13.4 2004-04-25 13:30:00 1012.8 13.4 0.0 
2004-04-26 00:50:00 1010.8 13.4 2004-04-25 13:30:00 1012.8 13.4 0.0 
2004-04-26 01:00:00 1010.8 13.4 2004-04-25 13:30:00 1012.8 13.4 0.0

Zoals je ziet blijft de tweede datum op 13:30, de eerste tijd gaat wel gewoon in 10 minuten. Echter moeten ze beide gewoon een (evt. halve) dag zijn, ipv. 10 minuten interval.

ZO nu en dan probeer ook een "GROUP BY date_format(a.timestamp,'%j %Y')" om op dag/jaar te groupen, om een record/dag te krijgen , maar elke keer moet ik mysql platgooien omdat ie blijft hangen

Misschien is er hier iemand die meteen de juiste oplossing weet? Ik kom er echt even niet meer uit...

[ Voor 4% gewijzigd door Sponge op 04-07-2004 14:16 . Reden: Layout verpest door query. ]

[NMe]

Moet je bij datediff niet de het interval "d" aangeven?

[Sponge]

Nee, dat is het probleem helaas niet:

code:

DATEDIFF() returns the number of days between the start date expr and the end
date expr2. expr and expr2 are date or date-and-time expressions. 
Only the date parts of the values are used in the calculation.

Geeft standaard al het aantal dagen..

[Verwijderd]

Hij neemt de tijd helemaal niet mee, maar kijkt alleen naar de dag. Je moet dus wel iets aan die datediff doen. Of gewoon timestamps van mekaar aftrekken, moet ook lukken volgens mij.

[Sponge]

Hmm, weer 3x een shutdown van mysql onderhand.. ik kom er echt niet uit. Ik zal weer eens verdere googelen.

[Sponge]

Okee, stapje verder:

code:

SELECT  DISTINCT date( a.timestamp ) , a.timestamp AS  "Date 1", a.rel_pressure AS  "Pressure 1", a.rain_1h AS  "Rain A",
b.timestamp AS  "Date 2", b.rel_pressure AS  "Pressure 2", b.rain_1h AS  "Rain B", b.rain_1h - a.rain_1h AS Difference
FROM weather AS a
INNER  JOIN weather AS b ON datediff( a.timestamp, b.timestamp ) = 1
WHERE HOUR(a.timestamp) = 1 and minute(a.timestamp)  = 0
 and HOUR(b.timestamp) = 1 and minute(b.timestamp)  = 0

Deze is fixed op elke dag, 1:00. (misschien slordig, 't is even een test). Echter zijn er paar uitzonderingen geweest waardoor het niet precies 1:00 is geweest op een paar dagen... hoe los ik dat op? met BETWEEN heb ik weer kans dat er meerdere records komen van dezelfde dag...

[ Voor 27% gewijzigd door Sponge op 04-07-2004 23:03 ]

[Sponge]

Na de zoveelste crash geef ik het maar op. Geen regenvoorspelling

SELECT A.timestamp, B.Timestamp FROM weather as A
INNER JOIN weather as B ON DateDiff(A.timestamp, B.timestamp) = 2
GROUP BY date(A.timestamp)

Is al genoeg om mysql plat te krijgen, Kost me elke keer 10minuten om mysql te kunnen platgooien omdat 'ie 100% cpu inneemt...

[Verwijderd]

Ik haat het om in herhaling te vallen, maar vooruit, sponge, voor jou.

Je krijgt met die datediff een cartesisch product. De datediff kijkt namelijk niet naar tijd, waardoor voor alle records per dag in a, 144 records aan b krijgt. You do the math.

Je moet dus afstappen van die datediff functie. Je kunt, en zoiets zei ik ook al, de timestamps van elkaar aftrekken. Je houdt dan het aantal seconden over. Wederom een stukje math voor jou om uit te rekenen hoeveel seconden er in een dag zitten.

Om zo een tabel met zichzelf te laten joinen is per definitie een performance vreter imho

[ Voor 9% gewijzigd door Verwijderd op 06-07-2004 11:26 ]