SQL函数表插值

Question

我有一个包含(x,y)值的SQL表。

x     y
0.0   0.0
0.1   0.4
0.5   1.0
5.0   2.0
6.0   4.0
8.0   4.0
10.0  5.0

x列已被索引，我正在使用sqlite。

我的最终目标是针对任何x值获得y(x)。我将使用表格值进行线性插值。如下图所示。

有没有一种方法可以直接使用选择查询执行线性插值？否则，获取x所属的区间值就足够了。

是否有一个查询可以给出给定x的最后较小和第一个较大的一对，以便我可以计算出插值的y(x)值？

例如，如果x=2.0，则为：

0.5   1.0
5.0   2.0

如果x不在表格中，则获取前两个/最后两个值来进行外推。

例如，如果x=20.0，则应获取：

8.0   4.0
10.0  5.0

- ztik

---

如果插值不可行，获取两个对即可。 - ztik

@CL 我编辑了问题，使其更加清晰明了。 - ztik

@JoeTaras 我觉得你没有理解他的意思... ;) 但如果我没有漏掉什么，对于x=2.0，返回的一对应该是：((0.5,1.0),(5.0,2.0))... 对吧？ - T.Z.

@JoeTaras 这些示例从问题开头的表中提取行。 - ztik

@JoeTaras 我刚刚注意到我的 x=2.0 的例子是错误的。我已经在问题中进行了修正。 - ztik

Answer 1

如果不使用分析函数，要在普通的SQLLite中完成这个任务会比较困难。在更复杂的SQL引擎中，您可以使用 LEG和LEAD分析函数轻松获取所需的一组对。

但是在SQLLite中，我会创建两个游标，如下所示：

游标C1：

SELECT 
    x,y
FROM
    table
WHERE
    x>=2
ORDER BY
    x asc
;

光标 C2：

SELECT 
    x,y
FROM
    table
WHERE
    x<=2
ORDER BY
    x desc
;

然后在其他语言中执行其余操作 - 从两个中获取一次，或者如果一个光标不返回值，则从另一个中获取两次。还需要处理一些额外的异常情况 - 如果您的集合少于两个值怎么办。或者如果您在集合中给定了X - 您根本不需要插值……等等。

- T.Z.

---

谢谢，我想把这个放在一个选择器里。这可行吗？ - ztik

我也不认为这个要求有任何简单的解决方案。如果X被索引了，从这些游标中获取数据应该是相当快的。 - T.Z.

我在考虑速度问题。如果表很大，那么用一个查询获取一对数据将会减少时间。 - ztik

这取决于您需要构建的查询，以一次获取这两个对。我认为这并不会更快（在SQLite中实际上可能会更慢），而且肯定很难阅读、理解和维护。保持简单。;) - T.Z.

Answer 2

我会选择简单的减法。

您正在寻找两个最近的输入，因此：

SELECT x, y 
FROM my_table
ORDER BY Abs(:val - x)
LIMIT 2

然而，这将导致全表扫描。

- Nemeros

---

不错的想法。但他的应用程序仍需要一些逻辑来检查这是否应该是插值还是外推，并检查他是否需要执行任何操作等等。全表扫描在性能方面听起来并不好。;) 但这确实是一个简单而美丽的解决方案！ - T.Z.

表扫描本身并不是问题。如果表的资源过大，则可能会成为问题。现在我们不知道表的确切结构，也不知道是否有另一选择列（如graph_id），可以避免进行完全扫描。 - Nemeros

我已经考虑过这个解决方案。我的主要担忧是它并不总是给出正确的区间。例如，如果 x=0.6，那么我将得到 0.1 和 0.5 行。 - ztik

是的，你说得对。它只会给出前两个最近的点。如果有的话，不会给出前一个和后一个中的第一个。 - Nemeros