mysql插值数据 [英] mysql interpolate data

问题描述

是否有一种简便的方法"从表中获取两行数据，并添加具有中间值"的行?

Is there an 'easy way' to grab two rows of data from a table, and add rows with values 'in-between'?

我想从每行中获取纬度，经度和时间戳.将时间戳与上一行的时间戳进行比较，如果时间戳大于我的最小值，则插入新行...将两行间隔为1分钟，每10秒添加一行...

I want to grab a latitude, a longitude and a timestamp from each row. Compare the timestamp to the one from the previous row, and interpolate new rows if the timestamp is bigger than my minimum...grab two rows 1 minute apart and add rows for every 10 seconds...

使用存储过程是解决此问题的最佳方法吗?最简单?

Is using a stored procedure the best way to go about this? Easiest?

当前正在使用mySql和PHP ...

Currently using mySql and PHP...

推荐答案

我只是获取数据并在PHP中进行数学运算. SQL并没有那么多用途，您将不为所动.

I would just grab the data and do the math in PHP. SQL isn't all that versatile, and you'd be saving yourself a headache.

编辑:实际上，仅出于乐趣，您可以通过左键连接到日历表来简化数学运算.

Actually, just for the fun of it, you could make the math easier by left-joining to a calendar table.

首先，您需要具有值0-9的表ints.然后，您可以执行以下操作:

First you need a table ints with the values 0-9. Then you can do something like:

SELECT cal.t, lat, lng FROM (
    SELECT {start_time} + INTERVAL (t.i*1000 + u.i*100 + v.i*10) SECOND as t
    FROM ints as t
    JOIN ints as u
    JOIN ints as v
    WHERE t <= {end_time}
) LEFT JOIN locations ON (cal.t = locations.stamp)

这将返回一个表，该表具有lat和lng的NULL值，其中在10秒标记上没有 条目，因此您可以遍历并进行数学运算对于那些.请记住，这仅在您拥有的所有数据点(起始和结束除外)恰好在10秒标记处时才起作用.

This would return a table with NULL values for lat and lng where there isn't an entry on the 10 second mark, so you could iterate through and do the math for just those. Keep in mind, this only works if you all the datapoints you do have (other than the start and end) land right on a 10-second mark.

这篇关于mysql插值数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！