plot an average value of groups of samples

[andrea1]

Hi all.

I have a table with a timestamp and a value, with hundreds of lines.

Timestamp                    value

23/03/2020 08:01:01    21.3

23/03/2020 08:01:02    20.4

23/03/2020 08:01:04    18.2

...

My purpose is take these samples in a group of 5 seconds, make the average of the values of the group, and then plot these average values over a period of an hour.
How could I do? any ideas?

Thanks in advance
Andrea

[andrea1]

Any news?

[eddie_wagt]

Hello @andrea1 

Can you be more specific? I have expand your table with more samples. Could you maybe illustrate a little bit more what you are trying to achieve? Do you want to group every 5 seconds, meaning that the first group is from line 1 to line 5  and a second group is from line 2 to line 7 ??

Regards 

Eddie

[andrea1]

Hi Eddie, thanks for your reply.

Yes, you're right. Original table (in which samples can be more than one every second) is divided into many small tables, every 5 second.
of each of these groups I calculate the average, and then graph these values.

[eddie_wagt · Accepted solution]

Hello @andrea1 ,

Maybe this is something you are looking for?

TimeBucket:
Load RecNo()									 as %KEYSECONDS
,	 num(num#(time(maketime(0,0,RowNo()),'ss'))) as Seconds 
autogenerate(60)
;
left join (TimeBucket)
LOAD %KEYSECONDS
,	 class(num(num#(Seconds)),5) as TimeBucket
Resident TimeBucket
;


Example:
LOAD num(num#(timestamp(Timestamp,'ss'))) as %KEYSECONDS
,	 *
;
LOAD * INLINE [Timestamp	value

23/03/2020 08:01:01	1.3

23/03/2020 08:01:02	20.4

23/03/2020 08:01:04	18.2

23/03/2020 08:01:05	6.3

23/03/2020 08:01:06	1.8

23/03/2020 08:01:07	18.7

23/03/2020 08:01:08	23.2

23/03/2020 08:01:09	29.1

23/03/2020 08:01:10	1.5

23/03/2020 08:01:11	17.4

23/03/2020 08:01:12	19.8
] (delimiter is '	')
;

[andrea1]

Wonderful!

many thanks

Andrea

[eddie_wagt]

@andrea1 , you are welcome. I see a made a mistake in the script. I forgot that you need also the timestamps where the seconds are equal to zero. 8:00:00

Therefore you have to replace 

,	 num(num#(time(maketime(0,0,RowNo()),'ss'))) as Seconds

,	 num(num#(time(maketime(0,0,RowNo()-1),'ss'))) as Seconds

Regards Eddie