在SELECT过滤掉重复随后的记录(Filtering out duplicate subseque

（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（这里找到堆栈溢出）

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不存在，有没有这样的事情。

伊茨克奔甘对你所面临的岛屿和差距问题的解决方案（几种解决方案，实际上）。我们的想法是通过分割准则+主要标准订购通过的主要标准（日期），然后行。因为他们属于同一个分区的标准和日期序系列之间的区别将保持不变。

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拨弄例子。

这里是有几种解决方案的岛屿和差距问题的SQL服务器MVP深海潜水的第5章。

Answer 2:

这应该是与简单的窗函数lag()

WITH cte AS (
   SELECT *
         ,lag(tm_nl_fixedId) OVER (PARTITION BY tm_nl_mobileId
                                   ORDER BY tm_date) AS last_fixed
   FROM   trackingmessages
   )
SELECT *
FROM   cte
WHERE  last_fixed IS DISTINCT FROM tm_nl_fixedId
ORDER  BY tm_date