sql - 过滤掉 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”是可以的，因为“32”介于两者之间。

请不要建议使用游标，这是我目前正试图摆脱的东西。游标解决方案确实有效，但考虑到我必须处理的记录数量，它太慢了。我宁愿修复 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

Here is Sql Fiddle with example .

And here is chapter 5 of Sql Server MVP Deep Dives with several solutions to islands and gaps problem .