Re: Group by time periods.

From: The Natural Philosopher <tnp_at_invalid.invalid>
Date: Mon, 29 Apr 2019 22:12:36 +0100
Message-ID: <qa7pc5$mkk$1_at_dont-email.me>

On 29/04/2019 21:26, J.O. Aho wrote:
> On 29/04/2019 20.49, The Natural Philosopher wrote:

>>
[Quoted] [Quoted] >> 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?

-- 
"If you don’t read the news paper, you are un-informed. If you read the 
news paper, you are mis-informed."

Mark Twain

Received on Mon Apr 29 2019 - 23:12:36 CEST