Didn't see a response to this so here's some ideas.
You can do an INSERT INTO tablename SELECT ... statement or if you are
creating a new table to put these values into CREATE TABLE tablename
(SELECT ...). Check the manual for the exact syntax and for which version
of mysql you'll need for this to work (especially if you have an older one,
I know it has worked for me for a while).
hope that helps,
cheers,
noel
On Friday, July 13, 2001 5:45 AM, Nemholt, Jesper Frank
[SMTP:[EMAIL PROTECTED]] wrote:
> 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(wait
time
> ),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: [EMAIL PROTECTED]
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
