[PHP/MYSQL]min,avg,max waarde uit tabel + bijbehorende tijd

[radial]

Als eerste, ben een echt mysql noob, afgelopen week toch maar eens begonnen om te kijken hoever het een en ander wil lukken.

Ik heb een database waar gegevens instaan mbt het weer, deze wordt elke 5 min geupdate.

code:

"timestamp","rec_date","rec_time","temp_in","temp_out","dewpoint","rel_hum_in","rel_hum_out","windspeed","wind_direction","wind_angle","wind_chill","rain_1h","rain_24h","rain_1w","rain_1m","rain_total","rel_pressure","tendency","forecast"
"20060803165123","2006-Aug-03","16:51:23","23.8","20.0","14.1","53","69","83.6","0.0","0.0","71.0","0.0","1.5","15.5","16.0","27.9","1008.5","Falling","Rainy
"
"20060803194929","2006-Aug-03","19:49:29","24.4","18.3","13.3","52","73","83.6","0.0","0.0","71.0","0.0","0.0","15.5","16.0","27.9","1009.7","Falling","Rainy
"
"20060803210504","2006-Aug-03","21:05:04","24.4","16.6","13.1","52","80","83.6","0.0","0.0","71.0","0.0","0.0","15.5","16.0","27.9","1010.4","Rising","Cloudy
"
"20060803212103","2006-Aug-03","21:21:03","24.4","16.3","13.0","53","81","83.6","0.0","0.0","71.0","0.0","0.0","15.5","16.0","27.9","1010.7","Rising","Cloudy
"
"20060803212503","2006-Aug-03","21:25:03","24.4","16.3","13.0","53","81","83.6","0.0","0.0","71.0","0.0","0.0","15.5","16.0","27.9","1010.6","Rising","Cloudy
"
"20060803213004","2006-Aug-03","21:30:04","24.3","16.1","13.0","53","82","83.6","0.0","0.0","71.0","0.0","0.0","15.5","16.0","27.9","1010.7","Rising","Cloudy
"
"20060803213504","2006-Aug-03","21:35:04","24.3","16.2","13.1","53","82","83.6","0.0","0.0","71.0","0.0","0.0","15.5","16.0","27.9","1010.8","Rising","Cloudy
"
"20060803214002","2006-Aug-03","21:40:02","24.3","16.1","13.0","53","82","83.6","0.0","0.0","71.0","0.0","0.0","15.5","16.0","27.9","1010.9","Rising","Cloudy
"
"20060803215503","2006-Aug-03","21:55:03","24.4","15.8","12.9","54","83","83.6","0.0","0.0","71.0","0.0","0.0","15.5","16.0","27.9","1011.1","Rising","Cloudy
"
"20060803220003","2006-Aug-03","22:00:03","24.4","15.8","12.9","55","83","83.6","0.0","0.0","71.0","0.0","0.0","15.5","16.0","27.9","1011.2","Rising","Cloudy
"

De bedoeling is om van elke dag het gemiddelde (min,avg en max) te berekenen met het daarbij behorende tijdstip. Dat wil dus nog niet lukken.

De gemiddelde waarden krijg ik er al wel uit, alleen de tijd die daarbij hoort niet.

code:

# Geeft de waardes weer voor de buitentemperatuur: Minimum, Gemiddeld, Maximum
$return_temp_in = "SELECT DATE_FORMAT(timestamp, '%d-%m-%Y')as datum,rec_date,MIN(temp_out) as min,AVG(temp_out) as avg,MAX(temp_out) as max FROM data GROUP BY rec_date";
$result = mysql_query($return_temp_in) or die(mysql_error());
while($row = mysql_fetch_array($result)){
    echo "B U I T E N  T E M P E R A T U U R  O P : ";
    echo $row['datum']."<br>";
    echo "Minimum: ".$row['min']." &deg;C"."<br>"."Gemiddeld: ".round($row['avg'],1)." &deg;C"."<br>"."Maximum: ".$row['max']." &deg;C";
    echo "<br /><br />";
}

Aangezien ik al de hele dag hiermee bezig ben om een antwoord te vinden via zoeken google en het nog niet gelukt is, toch maar een bericht geplaatst met de hoop op een antwoord zodat ik weer verder kan.

[NMe]

In een group by moet je alle velden uit je select opnemen die niet het resultaat zijn van een aggregate function, zoals je ook in onze SQL-FAQ kan lezen. Je vergeet dus een veld in je groepering.

[Pyrus]

DATE_FORMAT wil een date en geen timestamp, maak er eens DATE_FORMAT(DATE(timestamp), '%d-%m-%Y') van.

[T.T.]

Ik zie het probleem niet? Ik krijg netjes een datum wanneer ik jouw query uitvoer.

@Pyrus, volgens mij werkt DATE_FORMAT() in zijn geval prima. Het betreft immers een datum + tijd (zonder spaties)

Wat NME zegt klopt wel, maar bij MySQL mag het gewoon
Programming FAQ - SQL

[ Voor 255% gewijzigd door T.T. op 07-08-2006 06:40 ]

[radial]

De datum krijg ik er wel uit dat is het probleem niet, de bedoeling is:

min-waarde: tijdstip
avg-waarde: tijdstip
max-waarde: tijdstip

Daar wil dus niet lukken en ik heb eerlijk gezegd geen idee hoe ik dit aan moet pakken,

[Voutloos]

En wat voor tijdstip moet er nou verschijnen bij de avg? De gemiddelde waarde hoeft niet letterlijk bij 1 rij voor te komen en bovendien vraag ik me af wat voor nut een tijdstip bij dat gemiddelde heeft.

Je weet hoe he een minimum en maximum bepaald, dus doe daar iets mee in de where clause als je alleen de rij op wilt halen welke de gevens met oa de min. resp. max. waarde bevat.

[Verwijderd]

radial schreef op maandag 07 augustus 2006 @ 07:09:
De datum krijg ik er wel uit dat is het probleem niet, de bedoeling is:

min-waarde: tijdstip
avg-waarde: tijdstip
max-waarde: tijdstip

Daar wil dus niet lukken en ik heb eerlijk gezegd geen idee hoe ik dit aan moet pakken,

En als de gemiddelde waarde nu niet precies voorkomt, hoe wil je daar dan een tijdstip van hebben?

Bij de min kan je een beetje creatief doen door de tabel met zichzelf te joinen en daarvan dan de waarde te kiezen die hetzelfde is als min

SQL:

SELECT MIN(d1.temp_out) as min 
FROM data d1, data d2 
WHERE d2.temp_out=min 
GROUP BY d1.rec_date

Syntax ben ik niet helemaal zeker van, maar dit zou moeten werken (voor max natuurlijk dezelfde methode).

Mocht je mysql 4.1 of hoger hebben, dan kan je beter gaan kijken naar een subquery

[Voutloos]

^^ Join weghalen en gewoon 'WHERE kolom = MIN(kolom)' doen.

[radial]

Dus als ik het even samenvat:

bv minimale waarde: 23.8 om 9.15
maximale waarde: 26.1 om 23.40

Bij de gemiddelde waarde kan inderdaad zoals hieronder al vermeld, geen tijdstip aan toe worden gekend.

Zal vanavond als ik thuis ben gelijk de geopperde mogelijkheden gaan uitproberen, alvast bedankt.

[NMe]

T.T. schreef op maandag 07 augustus 2006 @ 06:11:
IWat NME zegt klopt wel, maar bij MySQL mag het gewoon
Programming FAQ - SQL

Dat het mág wil niet zeggen dat het werkt zoals het hoort. Hoe weet MySQL immers welke van de gegroepeerde datums hij moet pakken?

[Janoz]

@voutloos: Ik neem aan dat dat een grapje is??

----
min en max zijn net zo berekend als avg. Net als je bij avg geen record hebt geldt dat ook voor min en max. Dit is vaak wat lastiger voor te stellen omdat je daar daadwerkelijk een waarde uit de lijst terug krijgt, maar wat wil je terug krijgen als er 2 records dezelfde maximale waarde hebben?

Random een keuze maken is iets dat je NIET wilt hebben (dat hebben de mysql ontwikkelaars echter niet begrepen).

[Voutloos]

O, misschien is dat wel een grapje

Maar goed, die tijdstippen lijken mij ook niet bijster nuttig. En random een keuze maken is idd not done.

[ACM]

Min en max (mits je kan leven met dat het niet feilloos is, zie Janoz) zijn redelijk triviaal:

SQL:

SELECT temp, datum FROM tabel ORDER BY temp DESC LIMIT 1;
SELECT temp, datum FROM tabel ORDER BY temp ASC LIMIT 1;

Of alternatief, langzamer maar universeler, maar weer niet mogelijk op een mysql < 4.1:

SQL:

SELECT temp, datum FROM tabel WHERE temp = (SELECT MAX(temp) FROM tabel)

Als je dat in één query wilt kan je een UNION gebruiken. Het gemiddelde is echter heel wat lastiger, omdat je helemaal niet weet of er uberhaupt een record met die specifieke waarde is.

[radial]

[quote]ACM schreef op maandag 07 augustus 2006 @ 16:15:
...Of alternatief, langzamer maar universeler, maar weer niet mogelijk op een mysql < 4.1:

SQL:

SELECT temp, datum FROM tabel WHERE temp = (SELECT MAX(temp) FROM tabel)

...
[quote]

Hiermee kom ik in elk geval iets verder, hij laat nu alleen de laagste waarde zien met bijbehorende datum/tijd en in het geval er meerder dagen zijn met deze waarde laat hij ze allemaal zien.

Is er geen mogelijkheid met mysql om per dag de laagste/hoogste waarde te laten zien met het bijbehorend tijdstip.

code:

$return_temp_in = "SELECT DATE_FORMAT(timestamp, '%d-%m-%Y %H:%i')as tijd,MIN(temp_in) as min FROM data WHERE temp_in = (SELECT MAX(temp_in) FROM data) GROUP BY rec_date";
$result = mysql_query($return_temp_in) or die(mysql_error());
while($row = mysql_fetch_array($result)){
    echo "B I N N E N  T E M P E R A T U U R<br>";
    echo "Minimum: ".$row['min']." &deg;C - ".$row['tijd']."<br>";
    echo "<br /><br />";