Re: Group by time periods.
From: Luuk <luuk_at_invalid.lan>
Date: Mon, 29 Apr 2019 23:25:43 +0200
Message-ID: <5cc76bd6$0$22355$e4fe514c_at_news.xs4all.nl>
>> On 29/04/2019 20.49, The Natural Philosopher wrote:
>>>
>>> I have a database with 5 minute samples of various quantities.
>>>
>>> e.g. consider a simplified table with
>>> id, value, timestamp
>>>
>>> where 'id' is integer, 'value' is a float and 'timestamp' is datetime.
>>>
>>>
>>> I would like to select in groups averaged by hour so that e,g., the
>>> output could be the average of 12 consecutive timestamps, plus a
>>> timestamp of the starting hour.
>>>
>>> wring procedural code to do it is not hard but I wondered if in fact
>>> there was a way to do it in Mysql.
>>>
>>> I thought of of 'group by hour', but that would be just 24 samples!
>>
>> Not sure if I misunderstood you, but something like
>>
>>
>> select
>> count(*) as amount
>> , avg(value)
>> , cast(UNIX_TIMESTAMP(timestamp) / 60 * 15 as unsigned) as gdate
>> , min(timestamp)
>> from tablename
>> group by gdate;
>>
>> This would make things grouped in section of 15 minutes, giving you
>> the average value, number of rows and the time of the first event in
>> that time segment.
>>
>> As we use an alias for the calculation of the time frame, we don't
>> have to repeat the calculation group by but use the alias. As we need
>> to do the calculation of the time before we can group things, this
>> will take some toll on the database server if you have a lot of rows.
>>
Date: Mon, 29 Apr 2019 23:25:43 +0200
Message-ID: <5cc76bd6$0$22355$e4fe514c_at_news.xs4all.nl>
On 29-4-2019 23:12, The Natural Philosopher wrote:
> On 29/04/2019 21:26, J.O. Aho wrote:
>> On 29/04/2019 20.49, The Natural Philosopher wrote:
>>>
>>> I have a database with 5 minute samples of various quantities.
>>>
>>> e.g. consider a simplified table with
>>> id, value, timestamp
>>>
>>> where 'id' is integer, 'value' is a float and 'timestamp' is datetime.
>>>
>>>
>>> I would like to select in groups averaged by hour so that e,g., the
>>> output could be the average of 12 consecutive timestamps, plus a
>>> timestamp of the starting hour.
>>>
>>> wring procedural code to do it is not hard but I wondered if in fact
>>> there was a way to do it in Mysql.
>>>
>>> I thought of of 'group by hour', but that would be just 24 samples!
>>
>> Not sure if I misunderstood you, but something like
>>
>>
>> select
>> count(*) as amount
>> , avg(value)
>> , cast(UNIX_TIMESTAMP(timestamp) / 60 * 15 as unsigned) as gdate
>> , min(timestamp)
>> from tablename
>> group by gdate;
>>
>> This would make things grouped in section of 15 minutes, giving you
>> the average value, number of rows and the time of the first event in
>> that time segment.
>>
>> As we use an alias for the calculation of the time frame, we don't
>> have to repeat the calculation group by but use the alias. As we need
>> to do the calculation of the time before we can group things, this
>> will take some toll on the database server if you have a lot of rows.
>>
> Thanks. That looks promising...yes I do have a lot of rows. > > > 8 years worth > > what is the count (*) for? >
for counting ?
;)
-- LuukReceived on Mon Apr 29 2019 - 23:25:43 CEST