过滤掉 SELECT 中重复的后续记录

（PostgreSQL 8.4）表“trackingMessages”存储移动设备（tm_nl_mobileid）和固定设备（tm_nl_fixedId）之间的跟踪事件。

CREATE TABLE trackingMessages
(
  tm_id SERIAL PRIMARY KEY,           -- PK
  tm_nl_mobileId INTEGER,             -- FK to mobile
  tm_nl_fixedId INTEGER,              -- FK to fixed
  tm_date INTEGER,                    -- Network time
  tm_messageType INTEGER,             -- 0=disconnect, 1=connect
  CONSTRAINT tm_unique_row
    UNIQUE (tm_nl_mobileId, tm_nl_fixedId, tm_date, tm_messageType)
);

这里的问题是，同一个移动设备可能会随后连接到同一个固定设备两次（或更多次）。我不想看到后续的内容，但是稍后看到移动设备连接到同一固定设备是可以的，前提是中间有到不同固定设备的连接。

我想我已经很接近了，但还不够。我一直在使用以下 CTE（在 Stack Overflow 上找到）

WITH cte AS 
(
  SELECT tm_nl_fixedid, tm_date, Row_number() OVER (
    partition BY tm_nl_fixedid
    ORDER BY tm_date ASC
  ) RN 
  FROM   trackingMessages
) 
SELECT * FROM cte 
  WHERE tm_nl_mobileid = 150 AND tm_messagetype = 1
  ORDER BY tm_date;

给我以下结果

32;1316538756;1
21;1316539069;1
32;1316539194;2
32;1316539221;3
21;1316539235;2

这里的问题是最后一列应该是 1, 1, 1, 2, 1，因为第三个“32”实际上是重复的跟踪事件（在同一固定位置连续两次）并且最后一个连接到“21” ” 可以，因为“32”位于两者之间。

请不要建议使用光标，这就是我目前正在努力摆脱的。游标解决方案确实有效，但考虑到我必须处理的记录量，它太慢了。我宁愿修复 CTE 并且只选择位置RN = 1...除非你有更好的主意！

好吧，你们还没有那么亲近，因为row_number()无法同时跟踪两个组的序列。PARTITION BY tm_nl_fixedid ORDER BY date RESTART ON GAP不存在，不存在这样的东西。

Itzik Ben-Gan 对于您面临的岛屿和间隙问题有一个解决方案（实际上有几个解决方案）。这个想法是按主要标准（日期）然后按分区标准+主要标准对行进行排序。序数之间的差异将保持不变，因为它们属于相同的分区标准和日期系列。

with cte as
(
  select *,
      -- While order by date and order by something-else, date
      -- run along, they belong to the same sequence
         row_number() over (order by tm_date)
       - row_number() over (order by tm_nl_fixedid, tm_date) grp
    from trackingMessages
)
select *,
    -- Now we can get ordinal number grouped by each sequence
       row_number() over (partition by tm_nl_fixedid, grp
                          order by tm_date) rn
  from cte
 order by tm_date

这是 Sql Fiddle 示例 http://sqlfiddle.com/#!11/4cd20/5.

这是 Sql Server MVP Deep Dives 的第 5 章，提供了孤岛和间隙问题的几种解决方案 http://www.manning.com/nielsen/SampleChapter5.pdf.