sqlite - 如何在窗口中找到计算值的最大值？

Question

我有一个简单的数据库表，其中包含三列：id，x，y。 x和y只是直线上点的坐标。我想使用SQLite Window函数使用三行滑动窗口对表进行分区，然后从窗口中第一个坐标（行）的y值中获取最远的y值。

一个例子：

| id | x | y |
|----|---|---|
|  1 | 1 | .5|
|  2 | 2 | .9|
|  3 | 3 | .7| 
|  4 | 4 |1.1|
|  5 | 5 | 1 |

因此，第一个分区将包括：

| id | x | y |
|----|---|---|
|  1 | 1 | .5|
|  2 | 2 | .9|
|  3 | 3 | .7| 

预期的结果将是：

| id | x | y | d |
|----|---|---|---|
|  1 | 1 | .5| .4|
|  2 | 2 | .9|
|  3 | 3 | .7| 

由于以 id = 1作为 CURRENT ROW的窗口的最大变化为.4；分区中第一行的 y值 .5和 .9之间的最大距离为 .4。

最终的预期结果：

| id | x | y | d |
|----|---|---|---|
|  1 | 1 | .5| .4|
|  2 | 2 | .9| .2|
|  3 | 3 | .7| .4|
|  4 | 4 |1.1| .1|
|  5 | 5 | 1 |   | 

我试过使用像这样的窗口函数： WINDOW win1 AS (ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING可以给我正确的窗口。

在定义窗口之后，我尝试执行以下操作：

SELECT
    max(abs(y - first_value(y) OVER win1)) AS d
FROM t
WINDOW win1 AS (ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)

但是我误用了 first_value报错。

我认为我的问题是，这不是在分区的每一行上进行计算的正确方法，但是我找不到与我在此尝试的方法相匹配的其他解决方案或方法。

Answer 1

对于表的每一行，您都定义一个窗口，从当前行到下两行。
在您的代码中，y是当前行中的值，而first_value()是当前窗口中y的第一个值，也是当前行y的值。
因此，即使您的代码在语法上正确无误，您计算出的差也将始终返回0。
使用LEAD()窗口函数可以更轻松地解决您的问题：

WITH cte AS (
  SELECT *,
    LEAD(y, 1) OVER () AS y1,
    LEAD(y, 2) OVER () AS y2
  FROM tablename
)  
SELECT 
  id, x, y, 
  MAX(ABS(y - y1), COALESCE(ABS(y - y2), 0)) d 
FROM cte

请参见 demo。
结果：

id  x   y   d
1   1   0.5 0.4
2   2   0.9 0.2
3   3   0.7 0.4
4   4   1.1 0.1
5   5   1.0