在SELECT中过滤出重复的后续记录

Question

（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”可以，因为介于两者之间。

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

Answer 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。

这是Sql Server MVP Deep
Divs的第5章，其中提供了一些解决孤岛和空白问题的方法。