对于每一行，查找以前的行是否包含更高的值

JonasPedersen 发表于 Dev

乔纳斯·佩德森（JonasPedersen）

我正在尝试在PostgreSQL和PostGIS中使用SQL解决视线（LoS）问题。为此，我有一个表pitch_at_point，其中包含一个id，一个点几何图形和一个螺距。

pitch_at_point(id integer,geom geometry,degrees float)

id列的最小值是LoS的起点，而最大值是最远的。对于这些点中的每一个，我想确定是否存在具有较低id的点，它们也具有较高的音高（度）。如果真是这样，那么这点就看不到了。

我一直在努力寻找解决方案。我已经尝试使用递归查询，如下面的SQL所示：

WITH RECURSIVE 
walk_points AS 
(
  SELECT ARRAY[id] AS idlist,geom,degrees,id
  FROM pitch_at_point
  WHERE degrees = (SELECT max(degrees) FROM pitch_at_point)
  UNION ALL
  SELECT array_append(w.idlist, n.id) AS idlist,n.geom,n.degrees,n.id 
  FROM pitch_at_point n, walk_points w
  WHERE n.degrees < any(SELECT n.degrees FROM pitch_at_point WHERE NOT       
  w.idlist @> ARRAY[n.id])
)
SELECT * FROM walk_points

我希望得到所有点的返回值，然后再返回音高较高的点，但是我只会得到一个点的结果，即使使用也会得到相同的点WHERE n.degrees > any(。我很难弄清楚PostgreSQL递归CTE，所以如果有人可以按我的方式帮助我，我将不胜感激。

亚历克斯

这听起来像可以使用窗口函数来解决。

对于先前度数字段（按id排序）大于当前行的度数的记录，这将为您提供true / false标志：

    case  when degrees < max( degrees ) over( 
            order by id 
            rows between unbounded preceding and 1 preceding 
          ) 
          then true 
          else false 
    end as higher_value_present

在此处查看SQLfiddle：http ://sqlfiddle.com/#!15/23196/1

从您的问题中，我不清楚您是否要将此逻辑应用于每个几何点的一组ID？在这种情况下，您可以按geom对窗口函数进行分区：

    case  when degrees < max( degrees ) over( 
            partition by geom
            order by id 
            rows between unbounded preceding and 1 preceding 
          ) 
          then true 
          else false 
    end as higher_value_present

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。