🚀 go-pugleaf

Hi!

I'm using mySQL 3.22.32 on Debian GNU/Linux 2.2 for a temperature
logging system. I have several sensors whose values are read using SNMP
and inserted into a mySQL database table named "tbl_values". Now,
sometimes there are spikes in the measured values which I would like to
filter out.

My table "tbl_values", containing raw data, consists of 3 columns: label
(varchar(100)), value (float), timestamp (timestamp).

Example:

label	value		timestamp
A	23		20000802150000
B	22		20000802150010
C	24		20000802150021
A	22		20000802150100
B	22		20000802150111
A	21		20000802150200
B	23		20000802150209
C	23		20000802150219
A	22		20000802150300
B	22		20000802150311
C	22		20000802150329
A	23		20000802150400
B	22		20000802150409
C	24		20000802150421
A	22		20000802150500
B	21		20000802150513
C	23		20000802150522
...	...		... etc.

In a first step I'm looking for a SQL-query which will give me a list of
the 5 most current values for each label.

Example using the table above:

label	value		timestamp
A	22		20000802150500
A	23		20000802150400
A	22		20000802150300
A	21		20000802150200
A	22		20000802150100
B	21		20000802150513
B	22		20000802150409
B	22		20000802150311
B	23		20000802150209
B	22		20000802150111
C	23		20000802150522
C	24		20000802150421
C	22		20000802150329
C	23		20000802150219
C	23		20000802150125

In a second step I would like to filter these values in the following
fashion: Sum up all 5 values of each label and subtract the respective
maximum and minimum values. Divide the result by 3 to get an average.
For example, the formula for label 'A' would look like this:
(sum(A)-min(A)-max(A))/3 = (110-21-23)/3 = 22.

Example using the previous table:

label	value
A	22
B	22
C	23

At the moment I have Bash shell-scripts accessing the mysql frontend,
doing the filtering of the raw values and writing back the results, but
this method is really slow and certainly sub-optimal. I would like to
have a single proper SQL-query (or a set of queries) to perform this
sort of data filtering. I have experimented with various select
statements using LIMIT and LEFT JOIN, but to no avail. I would really
appreciate a few helpful hints.

Best wishes,
Albert.