PGSQL循环表

Question

我是Postgresql的新手，我在循环中挣扎。我试着去了解其他关于这个话题的帖子，但我还是被困住了。

我有一个桌子“所有的旅行”与GPS位置(X，Y)和时间戳，从不同的轨道(列旅行)。每个曲目都有一个ID。我要做的是在每一组轨道上执行一个动作。

如果下一行具有相同的时间戳，我希望将doublon_timestamp列更新为True。但是，如果轨道的最后一个时间戳与下一个轨道的第一行具有相同的时间戳，则行不能为True。

我想这样做，因为我打算重复使用这个程序，在每个轨道的全球定位系统点之间画线，而不是将轨道的最后一点与下一个轨道的第一个点连接起来。

我试过这样的方法：

CREATE OR REPLACE FUNCTION public.testloop(
) RETURNS void
LANGUAGE 'plpgsql'
AS $BODY$

DECLARE
x alltrips.trips_id;
BEGIN
    FOR x IN (SELECT * FROM public.alltrips)
LOOP
    CASE WHEN alltrips.timedate = lead(alltrips.timedate) 
              OVER (ORDER BY alltrips.trips_id) 
            THEN UPDATE public.alltrips SET doublon_timestamp = true;
        END CASE;
END LOOP;
END;
$BODY$;

但不起作用。我知道我在DECLARE部分遇到了麻烦。我的错误是什么？

Answer 1

您的需求似乎可以通过一个带有派生表的UPDATE FROM语句来实现，而不是使用一个函数和不必要的循环。

你可以试试这样的东西(未经测试)。

UPDATE alltrips t 
SET    doublon_timestamp = d.doublon_timestamp 
FROM   (SELECT trips_id, 
               CASE 
                 WHEN timedate = LEAD(timedate) 
                                   OVER ( 
                                     ORDER BY trips_id) THEN true 
               END AS doublon_timestamp from alltrips) d 
WHERE  d.trips_id = t.trips_id; 

虽然您似乎认为在更新之前还有其他标准需要检查，但除非您在问题中提供了一些明显缺失的示例数据，否则很难为您提供一个有效的解决方案。

Answer 2

我假设trips_id是每个轨道的trips索引，为此我提出了下面的示例表结构：

CREATE TABLE alltrips (
    trips integer,
    trips_id integer not null,
    timedate timestamptz not null,
    doublon_timestamp boolean not null default false,
    primary key (trips, trips_id)
);

基于这一点，不需要像前面所指出的那样设立一个职能：

UPDATE alltrips AS u
SET doublon_timestamp = true
FROM (
    SELECT trips, trips_id, timedate, lead(timedate) OVER (ORDER BY trips, trips_id) AS next
    FROM alltrips
    ORDER BY 1, 2
) AS t
WHERE
    t.trips = u.trips
    AND
    t.trips_id = u.trips_id
    AND
    t.timedate = t.next
;

数据在线示例：http://sqlfiddle.com/#!17/925f5/1/0

至少在我所使用的结构已经就位之后，执行计划是非常整洁的：

                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Update on alltrips u  (cost=0.30..114.66 rows=1 width=63)
   ->  Nested Loop  (cost=0.30..114.66 rows=1 width=63)
         ->  Subquery Scan on t  (cost=0.15..99.60 rows=9 width=48)
               Filter: (t.timedate = t.next)
               ->  WindowAgg  (cost=0.15..76.98 rows=1810 width=24)
                     ->  Index Scan using alltrips_pkey on alltrips  (cost=0.15..45.30 rows=1810 width=16)
         ->  Index Scan using alltrips_pkey on alltrips u  (cost=0.15..1.67 rows=1 width=22)
               Index Cond: ((trips = t.trips) AND (trips_id = t.trips_id))
(8 rows)

没有排序和合并总是一个好迹象..。;)