保持最新的一个字段值，直到它发生变化，然后保持其最新的字段值

I have a few tables that have millions of records where a sensor was sending multiple 0 and 1 values and this data was logged to the table even though we only needed it to keep the very first 1 or 0 per each 1 to 0 or 0 to 1 change.

我有一些表有数百万条记录，其中传感器发送多个0和1值，并且这些数据记录到表中，即使我们只需要它来保持第一个1或0每1到0或0到1改变。

Adjustments have been made so we only now get the 1 and 0 values on each change and not every one second or whatever but I need to cleanup the unnecessary records from the tables.

已经进行了调整，因此我们现在只在每次更改时获得1和0值，而不是每一秒或其他任何值，但我需要从表中清除不必要的记录。

I've done some research and testing and I'm having trouble figuring out what method to use here to delete the records not needed. I was trying to figure out how to retain the previous value record using variables and also created row numbers but it's not working as I need it to.

我做了一些研究和测试，我无法弄清楚在这里使用什么方法来删除不需要的记录。我试图弄清楚如何使用变量保留以前的值记录，并创建行号，但它不能正常工作。

I created an SQLFiddle here and tried some logic per the example post MySQL - How To Select Rows Depending on Value in Previous Row (Remove Duplicates in Each Sequence). I keep getting back no results from this and when I tried running it on a large local MySQL table, and I got an error wto I have to increase the MySQL Workbench read query timeout to 600 or it lost connection.

我在这里创建了一个SQLFiddle，并根据MySQL的示例尝试了一些逻辑 - 如何根据前一行中的值选择行（删除每个序列中的重复项）。我一直没有得到这样的结果，当我尝试在一个大的本地MySQL表上运行它时，我得到一个错误，我必须将MySQL Workbench读取查询超时增加到600或它失去连接。

I also found the "MySql - How get value in previous row and value in next row?" post and tried some variations of it and also "How to get next/previous record in MySQL?" and I've come up with total failure getting the expected results.

我还发现了“MySql - 前一行的获取值和下一行的值？”发布并试用了它的一些变体以及“如何获得MySQL中的下一个/上一个记录？”我得到了完全失败的预期结果。

The Data

The data in the tables has a TimeStr column and a Value column just as in the screen shot and on the SQLFiddle link I posted with a small sample of the data.

表中的数据有一个TimeStr列和一个Value列，就像在屏幕截图中一样，我在SQLFiddle链接上发布了一小部分数据样本。

Each record will never have the same TimeStr value but I really only need to keep the very first record time wise when the sensor either turned ON or OFF if that clarifies.

每个记录永远不会有相同的TimeStr值，但我真的只需要保持第一个记录时间，当传感器打开或关闭时，如果这澄清。

I'm not sure if the records will need an incremental row number added to get the expected results since it only has the TimeStr and the Value records otherwise.

我不确定记录是否需要添加增量行号以获得预期结果，因为它只有TimeStr和Value记录。

My Question

Can anyone help me determine a method that I can use on a couple large tables to delete the records from a table where there are subsequent and duplicate Value values so the tables only has the very first 1 or 0 records where those actually change from a 1 to 0 or 0 to 1?

任何人都可以帮我确定一个方法，我可以在几个大表上使用它来删除表中的记录，其中有后续和重复的值值，因此表只有最初的1或0记录，其中那些实际上从1更改到0或0到1？

I will accept an answer that also results in just the records needed—but any that perform fast would be even more greatly appreciated.

我会接受一个答案，这个答案也会产生所需的记录 - 但任何表现得很快的人都会更加欣赏。

I can easily put those into a temp table, drop the original table, and then create and insert the needed records only into the original table.
我可以很容易地将它们放入临时表，删除原始表，然后创建并将所需的记录仅插入到原始表中。

Expected Results

|              TimeStr | Value |
|----------------------|-------|
| 2018-02-13T00:00:00Z |     0 |
| 2018-02-13T00:00:17Z |     1 |
| 2018-02-13T00:00:24Z |     0 |
| 2018-02-13T00:00:28Z |     1 |

3 个解决方案

#1

Select t.timestr, t.value from (
 SELECT s.*,  @pv x1, (@pv := s.value) x2
 FROM sensor S, (select @pv := -1) x
 ORDER BY TimeStr ) t
where t.x1 != t.x2

See http://sqlfiddle.com/#!9/8d0774/122

请参阅http://sqlfiddle.com/#!9/8d0774/122

The Data

My Question

Expected Results

3 个解决方案

#1

更多相关文章

随机推荐