Re: Group by time periods.
From: J.O. Aho <user_at_example.net>
Date: Tue, 30 Apr 2019 06:54:01 +0200
Message-ID: <giq2naFiasgU1_at_mid.individual.net>
>> 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 4 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: Tue, 30 Apr 2019 06:54:01 +0200
Message-ID: <giq2naFiasgU1_at_mid.individual.net>
[Quoted] On 29/04/2019 23.13, Luuk wrote:
> On 29-4-2019 22: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 4 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.
>>
> > I think this is about the same as what you are doing? > > select > count(*) as amount > ,avg(value) > ,min(timestamp) > from tablename > group by date(timestamp),hour(timestamp)
[Quoted] Kind of, but no, yours will group by hour (which would be equal to
cast(UNIX_TIMESTAMP(timestamp) / 60 as unsigned)), while
cast(UNIX_TIMESTAMP(timestamp) / 60 * 15 as unsigned) will group things
in 4 minute intervals.
[Quoted] If you want 15 minutes intervals (as I originally wrote), then you need
cast(UNIX_TIMESTAMP(timestamp) / 60 * 4 as unsigned).
[Quoted] Sure yours is faster, but you can only group it in 1 hour intervals and next smaller would be 1 minute intervals...
-- //AhoReceived on Tue Apr 30 2019 - 06:54:01 CEST