Mysql: get 'last' value inside a group for view-CodePudding

I have a table of RT data from devices (IOT), like this (say 't1'):

 time*                  device   key       value
2022-08-20 12:00:12     a1       inp01     0.123
2022-08-20 12:00:15     a2       12        false
2022-08-20 12:00:25     a1       dev01     0.128
2022-08-20 12:00:50     a3       xk4       412  
more...

My objectif (on MySQL 5.1.62) is to group and decimate the t1 records, making a view like this, with a more usable record every 5 min (300s):

 time*                inp01      xk4    ...*
2022-08-20 12:00:12   0.124      412    ...
2022-08-20 12:05:03   0.113      NULL   ...
2022-08-20 12:10:05   NULL       312    ...
more...

I use a QUERY like this to get it:

SELECT `time`,
 AVG(case when (`key` = 'inp01') then `value` end) AS `inp01`,
 MAX(case when (`key` = 'xk4') then `value` end) AS `xk4`,
  ...more...
FROM `t1`
WHERE ((`device` = 'a1') OR (`device` = 'a3')) 
 GROUP BY (UNIX_TIMESTAMP(`time`) DIV 300) 
 ORDERED BY `time`;

that works as expected.

Now the problem: on groups I can use MAX(), AVG()... but in some cases I would need a FIST() and a LAST() (of course order by 'time' or maybe an HEAD() and TAIL() if the set is ordered by ORDER BY).

I couldn't find a valid inspiration in the examples I found... does anyone have an idea ? (n.b.: I can not change the MySQL version, user-variables use is restricted in views).

CodePudding user response：

You may try something like

SELECT FROM_UNIXTINE(300 * (UNIX_TIMESTAMP(`time`) DIV 300)) AS `time`,
 AVG(case when (`key` = 'inp01') then `value` end) AS `inp01`,
 MAX(case when (`key` = 'xk4') then `value` end) AS `xk4`,
  ...more...

,SUBSTRING_INDEX(GROUP_CONCAT(case when (`key` = 'inp01') then `value` end ORDER BY `time` ASC), ',', 1) AS first_value_per_group_for_inp01
,SUBSTRING_INDEX(GROUP_CONCAT(case when (`key` = 'inp01') then `value` end ORDER BY `time` DESC), ',', 1) AS last_value_per_group_for_inp01
  ...more for another `key` values...

FROM `t1`
WHERE ((`device` = 'a1') OR (`device` = 'a3')) 
GROUP BY (UNIX_TIMESTAMP(`time`) DIV 300) 
ORDER BY `time`;