PROSAGA码农传奇-postgresql-PSQL循环表

<div class =“post-text”itemprop =“text”>
  
    我在假设
     <code>
 trips_id
 </code>
     是每个曲目的索引
     <code>
 trips
 </code>
    ，为此我想出了以下示例表结构：
  
   <pre class="lang-sql prettyprint-override">
 <code>
 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)
);

</code>
 </pre>
  
    基于此，不需要已经指出的功能：
  
   <pre class="lang-sql prettyprint-override">
 <code>
 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
;

</code>
 </pre>
  
    在线数据示例：
    <a href="http://sqlfiddle.com/#!17/925f5/1/0" rel="nofollow noreferrer">
      http://sqlfiddle.com/#!17/925f5/1/0
    </A>
  
  
    至少在我使用过的结构中，执行计划非常简洁：
  
   <pre>
 <code>
 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)

</code>
 </pre>
  
    没有排序和合并连接总是一个好兆头......;）
  
</DIV>