MySQL在打开和关闭时间戳之间选择最大值，再加上3秒 [英] MySQL to select max value between on and off time stamps plus 3 seconds

查看：76
本文介绍了MySQL在打开和关闭时间戳之间选择最大值，再加上3秒的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

有一个运输机进气门传感器，该数据被收集到一个MySQL DB表中，该表显示带有时间戳的进气门打开和关闭周期.还有一个活重传感器，该数据从相关的运输工具收集到MySQL DB表中，显示随着时间变化的活重值.

There's a transporter inlet valve sensor which data is collected to a MySQL DB table showing the intake valve open and close cycles with the time stamps. There's also a live weight sensor which data is collected to a MySQL DB table from the correlated transporter showing the live weight value as it changes with a time stamp.

我已经针对此表数据的一小部分示例构建了以下简化的示例查询，以显示需要进一步使用其他子查询等操作的数据，以便将数据放入临时表中.

I've built the below simplified sample query against a small sample of this table data to show the data I need to further manipulate with another subquery, etc. to put the data in a temp table.

CREATE TABLE TempBatch

SELECT  TimeStr, 
        CASE 
            WHEN Value = 'On' THEN 'On'
            WHEN Value = 'Off' THEN 'Off' 
            ELSE Null END Status, 
        CASE 
            WHEN Value = 'On' THEN Null
            WHEN Value = 'Off' THEN Null 
            ELSE Value END Weight
FROM
    (
        SELECT TimeStr, If(Value = 1, 'Off', 'On') Value FROM transporter2_inlet_valve_closed WHERE Value = 1 OR Value = 0
        UNION ALL
        SELECT TimeStr, Value FROM transporter2_liveweight
    ) x 
    WHERE TimeStr BETWEEN '2019-06-29 18:51:00' AND DATE_ADD('2019-06-29 19:27:18', INTERVAL 3 SECOND);

挣扎于...

我正在努力寻找一种显示记录的方法，在该记录中，我只能看到每个打开和关闭周期之间的最大重量，但是要在关闭周期的时间戳记后3秒内添加，以找到该周期的最大重量.

Struggling With...

I'm struggling to easily figure out a way to show the records where I see only the maximum weight between each on and off cycle but add in 3 seconds after the off cycle's time stamp to find the maximum weight for the cycle.

也许正在澄清SQL ... 

(SELECT MAX(Value) Value FROM transporter2_liveweight WHERE TimeStr BETWEEN StopTime AND StartTime)

我想要...

基本上我想省略Null状态，但在每个On和Off周期(Off时间戳记后+3秒)之间显示最大权重值，而在Weight列中显示最大权重Off状态记录，但在Weight列中保留On记录Null.

What I want...

Basically I want to omit the Null statuses but show the maximum weight value between each On and Off cycle (+3 seconds after Off timestamp) with the maximum weight in the Weight column of the Off status records but leave On records Null for the Weight column.

所需结果示例 

+---------------------+--------+---------+
|       TimeStr       | Status |  Weight |
+---------------------+--------+---------+
| 2019-06-29 18:51:00 |   On   |   Null  |
| 2019-06-29 18:51:16 |   Off  |   315   |
| 2019-06-29 19:00:57 |   On   |   Null  |
| 2019-06-29 19:01:13 |   Off  |   311   |
+---------------------+--------+---------+

注意:创建下表并插入数据，这可用来尝试获得我要的期望结果.

Note: Create the below table and insert the data, this is what can be used to try to get the desired results I'm asking about.

CREATE TABLE `tempbatch` (
  `TimeStr` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Status` varchar(3) DEFAULT NULL,
  `Weight` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:16',NULL,'311');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:15',NULL,'310');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:14',NULL,'309');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:13','Off',NULL);
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:12',NULL,'307');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:11',NULL,'305');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:10',NULL,'304');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:09',NULL,'305');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:07',NULL,'306');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:06',NULL,'308');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:05',NULL,'284');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:04',NULL,'256');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:03',NULL,'168');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:02',NULL,'106');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:01',NULL,'15');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:00:57','On',NULL);
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:57:30',NULL,'1');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:55:21',NULL,'0');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:55:11',NULL,'2');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:54:25',NULL,'1');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:53:55',NULL,'0');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:53:50',NULL,'2');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:53:34',NULL,'1');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:53:19',NULL,'0');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:53:16',NULL,'2');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:53:06',NULL,'1');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:49',NULL,'0');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:47',NULL,'-5');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:46',NULL,'-4');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:44',NULL,'-1');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:42',NULL,'0');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:41',NULL,'5');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:40',NULL,'6');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:39',NULL,'7');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:38',NULL,'8');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:37',NULL,'9');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:36',NULL,'10');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:35',NULL,'12');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:34',NULL,'14');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:32',NULL,'17');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:31',NULL,'19');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:30',NULL,'22');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:29',NULL,'25');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:28',NULL,'29');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:27',NULL,'32');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:26',NULL,'33');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:25',NULL,'34');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:24',NULL,'36');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:23',NULL,'41');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:22',NULL,'45');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:21',NULL,'47');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:20',NULL,'52');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:18',NULL,'53');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:17',NULL,'56');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:16',NULL,'63');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:15',NULL,'68');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:14',NULL,'73');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:13',NULL,'78');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:12',NULL,'84');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:11',NULL,'87');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:10',NULL,'90');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:09',NULL,'92');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:08',NULL,'94');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:07',NULL,'99');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:06',NULL,'104');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:05',NULL,'109');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:04',NULL,'113');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:03',NULL,'111');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:02',NULL,'114');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:01',NULL,'126');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:00',NULL,'132');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:59',NULL,'139');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:58',NULL,'145');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:56',NULL,'152');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:55',NULL,'158');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:54',NULL,'163');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:53',NULL,'165');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:52',NULL,'167');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:51',NULL,'173');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:50',NULL,'183');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:49',NULL,'192');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:47',NULL,'194');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:46',NULL,'196');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:45',NULL,'204');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:44',NULL,'202');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:43',NULL,'212');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:42',NULL,'219');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:41',NULL,'229');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:40',NULL,'232');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:39',NULL,'235');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:38',NULL,'237');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:37',NULL,'242');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:36',NULL,'246');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:34',NULL,'249');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:33',NULL,'252');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:32',NULL,'256');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:31',NULL,'267');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:29',NULL,'277');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:28',NULL,'283');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:27',NULL,'286');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:26',NULL,'285');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:24',NULL,'286');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:23',NULL,'300');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:22',NULL,'303');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:21',NULL,'309');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:20',NULL,'308');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:19',NULL,'314');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:18',NULL,'315');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:17',NULL,'311');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:16','Off',NULL);
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:14',NULL,'308');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:13',NULL,'310');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:12',NULL,'312');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:11',NULL,'311');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:10',NULL,'309');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:09',NULL,'305');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:08',NULL,'319');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:07',NULL,'260');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:06',NULL,'193');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:05',NULL,'108');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:04',NULL,'8');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:02',NULL,'-5');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:01',NULL,'20');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:00','On',NULL);
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:00',NULL,'-17');

推荐答案

下面的查询选择状态不为空的所有行.

The query below selects all rows where the status isn't null.

对于状态为关"的行，它使用子查询返回最近的开"与当前关"之间的最大权重+ 3秒

For the rows where the status is 'Off' it uses a subquery to return the maximum weight between the most recent 'On' and the current 'Off' + 3 seconds

select t.TimeStr, t.Status,
  case
    when status = 'Off'
    then (select max(cast(Weight as signed)) from tempbatch t2 
          where t2.TimeStr between 
            (select max(TimeStr) 
                 from tempbatch t3 
                 where status = 'On' and t3.TimeStr < t.TimeStr)
          and date_add(t.TimeStr, interval 3 second)
    )
    else Weight
  end as Weight
from tempbatch t
where t.Status is not null
order by TimeStr

http://sqlfiddle.com/#!9/f27fb/6

这篇关于MySQL在打开和关闭时间戳之间选择最大值，再加上3秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆