[MYSQL] query werkt niet

[Verwijderd]

Als intro:

Ik gebruik Mysql op OpenBSD 4 op Intel

De database opbouw is als volgt:

CREATE TABLE `weatherinfo` (
`timestamp` bigint(14) NOT NULL default '0',
`rec_date` char(13) NOT NULL default '00-00-0000',
`rec_time` time NOT NULL default '00:00:00',
`temp_in` decimal(3,1) NOT NULL default '0.0',
`temp_out` decimal(3,1) NOT NULL default '0.0',
`dewpoint` decimal(3,1) NOT NULL default '0.0',
`rel_hum_in` tinyint(3) NOT NULL default '0',
`rel_hum_out` tinyint(3) NOT NULL default '0',
`windspeed` decimal(3,1) NOT NULL default '0.0',
`wind_direction` char(3) NOT NULL default '',
`wind_angle` decimal(3,1) NOT NULL default '0.0',
`wind_chill` decimal(3,1) NOT NULL default '0.0',
`rain_1h` decimal(5,1) NOT NULL default '0.0',
`rain_24h` decimal(5,1) NOT NULL default '0.0',
`rain_1w` decimal(6,1) NOT NULL default '0.0',
`rain_1m` decimal(6,1) NOT NULL default '0.0',
`rain_total` decimal(7,1) NOT NULL default '0.0',
`rel_pressure` decimal(7,1) NOT NULL default '0.0',
`abs_pressure` decimal(7,1) NOT NULL default '0.0',
`tendency` varchar(10) NOT NULL default '',
`forecast` varchar(7) NOT NULL default '',
UNIQUE KEY `timestamp` (`timestamp`)
) TYPE=MyISAM;

Het model van de database heb ik niet zelf bedacht. Het hoort bij open3600.
Dit is een open source pakket om waardes uit een weerstation te lezen.

Het probleem:
Als ik de waardes opvraag zonder tijd dan gaat dit goed:

mysql> SELECT rec_date, max(temp_out) FROM weatherinfo GROUP BY rec_date order by max(temp_out) DESC limit 10;
+------------+---------------+
| rec_date | max(temp_out) |
+------------+---------------+
| 2007-05-05 | 31.3 |
| 2007-05-04 | 28.4 |
| 2007-06-07 | 28.4 |
| 2007-05-03 | 26.9 |
| 2007-06-08 | 25.2 |
| 2007-05-24 | 25.1 |
| 2007-05-25 | 24.8 |
| 2007-06-09 | 24.8 |
| 2007-06-06 | 24.3 |
| 2007-06-05 | 24.0 |
+------------+---------------+
10 rows in set (0.03 sec)

Maar ik wil eigenlijk het tijdstip erbij:

mysql> SELECT rec_date, rec_time, max(temp_out) FROM weatherinfo GROUP BY rec_date order by max(temp_out) DESC limit 10;
+------------+----------+---------------+
| rec_date | rec_time | max(temp_out) |
+------------+----------+---------------+
| 2007-05-05 | 00:00:08 | 31.3 |
| 2007-05-04 | 00:00:06 | 28.4 |
| 2007-06-07 | 00:10:08 | 28.4 |
| 2007-05-03 | 00:00:07 | 26.9 |
| 2007-06-08 | 00:10:07 | 25.2 |
| 2007-05-24 | 00:10:06 | 25.1 |
| 2007-05-25 | 00:10:07 | 24.8 |
| 2007-06-09 | 00:50:07 | 24.8 |
| 2007-06-06 | 00:10:07 | 24.3 |
| 2007-06-01 | 00:10:06 | 24.0 |
+------------+----------+---------------+
10 rows in set (0.03 sec)

HIER GAAT HET FOUT.

Hij pakt dus de eerste tijdstip waarde van die dag, en niet het tijdstip waarde dat bij die meeting hoort.

Ik heb geen idee hoe ik dit op moet lossen. Ik hoop dat jullie mij kunnen helpen om de query te finetunen....

Alvast bedankt!

[Verwijderd]

Volgens mij zit dit in je GROUP BY, ik zou dat gewoon eens weghalen en dan alleen even orderen en DESC-en.

[NMe]

Je GROUP BY klopt sowieso niet. Programming FAQ - SQL: Hoe werkt dat GROUP BY nu eigenlijk?

[Pete]

Dit is al veel vaker hier voorgekomen op GoT. Het probleem is dat de kolom rec_time nog een geaggregeerde, nog een gegroepeerde kolom is.

De eenvoudigste oplossing is een subquery te gebruiken om de goede tijd bij een datum en temp op te halen.

[Dido]

Een group-by gebruik je op alle kolommen zonder aggregate function!!!

Een van de redenen dat MySQL verboden zou moeten worden (die staat toe dat je dat niet doet).

Nu even kijken wat je wel precies wilt...

[Dido]

Iets als dit misschien?

SQL:

select a.rec_date, a.rec_time, a.temp_out
from weatherinfo a
where a.temp_out = (select max(b.temp_out) from weatherinfo b where a.rec_date = b.rec_date)

[Verwijderd]

With ^^ ! En Dido, jouw oplossing is netjes. Nu maar hopen dat TS een MySQL versie gebruikt die subselects ondersteunt...

[Verwijderd]

Er zitten zo'n 3300 records in mijn database...

mysql> select count(*), date_format(rec_date,'%Y-%m') as m_y from weatherinfo where year(rec_date)=2007 group by m_y;
+----------+---------+
| count(*) | m_y |
+----------+---------+
| 2830 | 2007-05 |
| 510 | 2007-06 |
+----------+---------+
2 rows in set (0.27 sec)

Het commando dat Dido geeft draait meer dan 4 minuten voordat het antwoord klaar is.
83 rows in set (4 min 8.64 sec)

Dit is geen query die ik realtime voor mijn website kan laten doen
Is de query nog te optimizen?

Dido, en alle anderen bedankt voor jullie reactie!

[Creepy]

De query misschien.. Je tabellen in elk geval ook nog. Een paar indexen kan wonderen doen. In dit geval op rec_date en temp_out.