Hi!
I have various tables more or less similar to this :
mysql> desc disk;
+--------------+-----------------------+------+-----+---------------------+-
------+
| Field | Type | Null | Key | Default |
Extra |
+--------------+-----------------------+------+-----+---------------------+-
------+
| timecode | datetime | | | 0000-00-00 00:00:00 |
|
| system_id | smallint(5) unsigned | | MUL | 0 |
|
| devicename | char(8) | | | |
|
| device_id | char(8) | | | |
|
| read_count | mediumint(8) unsigned | YES | | NULL |
|
| read_kb | mediumint(8) unsigned | YES | | NULL |
|
| write_count | mediumint(8) unsigned | YES | | NULL |
|
| write_kb | mediumint(8) unsigned | YES | | NULL |
|
| servicetime | float(4,2) | YES | | NULL |
|
| waittime | float(4,2) | YES | | NULL |
|
| active_queue | float(4,2) | YES | | NULL |
|
| wait_queue | float(4,2) | YES | | NULL |
|
| busy | float(4,2) | YES | | NULL |
|
+--------------+-----------------------+------+-----+---------------------+-
------+
13 rows in set (0.00 sec)
Lots of data is inserted into these tables and allways (well, more or less)
later selected with a ordering by timecode.
I would like to reduce the size of old data by averaging the values by hour
instead of the current where I usually have 12 samples per hour.
The goal is to end up with high resolution data for the last couple of
monts, while older and more unimportant data is averaged by hour.
If I do a select like this :
select
substring_index(timecode,':',1),system_id,devicename,device_id,avg(read_coun
t),avg(read_kb),avg(write_count),avg(write_kb),avg(servicetime),avg(waittime
),avg(active_queue),avg(wait_queue),avg(busy) from disk where system_id='1'
group by substring_index(timecode,':',1) order by
substring_index(timecode,':',1)
...I will get all the data averaged by hour, but how do I get it back into a
table ???
In Oracle my guess is that I can do this with a combination of a select and
insert, but I don't think this is supported in MySQL. As far as I know this
is more a less a subselect thing, and MySQL doesn't support subselects.
Do I need to make some external scripting (Perl with DBI) or can everything
be done inside MySQL ???
--
Un saludo / Venlig hilsen / Regards
Jesper Frank Nemholt
Unix System Manager
Compaq Computer Corporation
Phone : +34 699 419 171
E-Mail: JesperFrank.Nemholt@stripped
