postgresql - 如何在点之间的最大距离中找到最低和最高值(SQL)

Question

目前，我有一个 PostgreSQL 数据库(和一个结构几乎相同的 SQL Server 数据库)，其中有一些数据，如下例所示:

+----+---------+-----+
| ID | Name    | Val |
+----+---------+-----+
| 01 | Point A |   0 |
| 02 | Point B | 050 |
| 03 | Point C | 075 |
| 04 | Point D | 100 |
| 05 | Point E | 200 |
| 06 | Point F | 220 |
| 07 | Point G | 310 |
| 08 | Point H | 350 |
| 09 | Point I | 420 |
| 10 | Point J | 550 |
+----+---------+-----+

ID = PK (auto increment);
Name = unique;
Val = unique;

现在，假设我只有Point F (220)，我想找到数据之间最大距离小于100的最小值和最大值.

所以，我的结果必须返回:

• 最低:点 E (200)
• 最大:点 I (420)

逐步解释(因为英语不是我的主要语言):

• 寻找最低值:

  Initial value = Point F (220);
  Look for the lower closest value of Point F (220): Point E (200);
  200(E) < 220(F) = True; 220(F) - 200(E) < 100 = True;
  Lowest value until now = Point E (200)
  
  Repeat
  
  Look for the lower closest value of Point E (200): Point D (100);
  100(D) < 200(E) = True; 200(E) - 100(D) < 100 = False;
  Lowest value = Point E (200); Break;
  

• 寻找最大的值(value):

  Initial value = Point F (220);
  Look for the biggest closest value of Point F (220): Point G (310);
  310(G) > 220(F) = True; 310(G) - 220(F) < 100 = True;
  Biggest value until now = Point G (310)
  
  Repeat
  
  Look for the biggest closest value of Point G (310): Point H (350);
  350(H) > 310(G) = True; 350(H) - 310(G) < 100 = True;
  Biggest value until now = Point H (350)
  
  Repeat
  
  Look for the biggest closest value of Point H (350): Point I (420);
  420(I) > 350(H) = True; 420(I) - 350(H) < 100 = True;
  Biggest value until now = Point I (420)
  
  Repeat
  
  Look for the biggest closest value of Point I (420): Point J (550);
  550(J) > 420(I) = True; 550(J) - 420(I) < 100 = False;
  Biggest value Point I (420); Break;
  

Answer 1

这可以使用 Windows Functions 来完成和一些工作。

以循序渐进的方式，您将从拥有一个由该选择定义的表(我们称它为 point_and_prev_next)开始:

SELECT
    id, name, val, 
    lag(val) OVER(ORDER BY id) AS prev_val, 
    lead(val) OVER(ORDER BY id) AS next_val
FROM
    points 

产生:

| id |    name | val | prev_val | next_val |
|----|---------|-----|----------|----------|
|  1 | Point A |   0 |   (null) |       50 |
|  2 | Point B |  50 |        0 |       75 |
|  3 | Point C |  75 |       50 |      100 |
|  4 | Point D | 100 |       75 |      200 |
|  5 | Point E | 200 |      100 |      220 |
|  6 | Point F | 220 |      200 |      310 |
|  7 | Point G | 310 |      220 |      350 |
|  8 | Point H | 350 |      310 |      420 |
|  9 | Point I | 420 |      350 |      550 |
| 10 | Point J | 550 |      420 |   (null) |

lag and lead窗口函数用于从表中获取上一个和下一个值(按 ID 排序，不按任何分区)。

接下来，我们创建第二个表 point_and_dist_prev_next，它使用 val、prev_val 和 next_val 来计算距离到上一点和到下一点的距离。这将使用以下 SELECT 进行计算:

SELECT
    id, name, val, (val-prev_val) AS dist_to_prev, (next_val-val) AS dist_to_next
FROM
    point_and_prev_next

这是执行后得到的结果:

| id |    name | val | dist_to_prev | dist_to_next |
|----|---------|-----|--------------|--------------|
|  1 | Point A |   0 |       (null) |           50 |
|  2 | Point B |  50 |           50 |           25 |
|  3 | Point C |  75 |           25 |           25 |
|  4 | Point D | 100 |           25 |          100 |
|  5 | Point E | 200 |          100 |           20 |
|  6 | Point F | 220 |           20 |           90 |
|  7 | Point G | 310 |           90 |           40 |
|  8 | Point H | 350 |           40 |           70 |
|  9 | Point I | 420 |           70 |          130 |
| 10 | Point J | 550 |          130 |       (null) |

而且，在这一点上，(从点“F”开始)，我们可以通过以下查询获得第一个“向上错误的点”(第一个失败的“与前一个距离”<100 的点):

SELECT
      max(id) AS first_wrong_up
FROM
    point_and_dist_prev_next
WHERE
    dist_to_prev >= 100
    AND id <= 6     -- 6 = Point F

这只是寻找最接近我们引用点(“F”)的点，它与前一个点的距离<100。

结果是:

| first_wrong_up |
|----------------|
|              5 |

下行的第一个“错误点”以等效方式计算。

所有这些查询都可以使用 Common Table Expressions 放在一起，也称为 WITH 查询，您将获得:

WITH point_and_dist_prev_next AS
(
    SELECT
        id, name, val, 
        val - lag(val) OVER(ORDER BY id) AS dist_to_prev, 
        lead(val) OVER(ORDER BY id)- val AS dist_to_next
    FROM
        points 
),
first_wrong_up AS
(
SELECT
    max(id) AS first_wrong_up
FROM
    point_and_dist_prev_next
WHERE
    dist_to_prev >= 100
    AND id <= 6     -- 6 = Point F
),
first_wrong_down AS
(
SELECT
    min(id) AS first_wrong_down
FROM
    point_and_dist_prev_next
WHERE
    dist_to_next >= 100
    AND id >= 6     -- 6 = Point F
)
SELECT
    (SELECT name AS "lowest value"
       FROM first_wrong_up
       JOIN points ON id = first_wrong_up),
    (SELECT name AS "biggest value"
       FROM first_wrong_down
       JOIN points ON id = first_wrong_down) ;

它提供了以下结果:

| lowest value | biggest value |
|--------------|---------------|
|      Point E |       Point I |

可以在SQLFiddle查看.

---

注意:假设 id 列总是递增的。如果不是，则必须改用 val 列(显然，假设它始终保持增长)。