Фильтрация повторяющихся последующих записей в 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 (найденный здесь, в переполнении стека)

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 ... если у вас нет идеи получше!


postgresql sql window-functions gaps-and-islands
person denpanosekai    schedule 16.09.2012    source источник

Ответы (2)


arrow_upward
6
arrow_downward

Ну, вы не настолько близки, потому что 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 Fiddle с примером.

А вот глава 5 Sql Server MVP Deep Dives с несколькими решениями проблемы островов и пробелов .

person Nikola Markovinović    schedule 16.09.2012
comment
Именно то, что я искал! +1 за объяснение концепции и +inf за решение. - person denpanosekai; 17.09.2012

arrow_upward
3
arrow_downward

Это должно быть проще с функция окна 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

Объяснять
person Erwin Brandstetter    schedule 17.09.2012
comment
Это... ровно в два раза быстрее при работе с 8 миллионами записей. Почему? - person denpanosekai; 18.09.2012
comment
@denpanosekai: для этого требуется только один проход с одним порядком сортировки для одной оконной функции + 1 окончательная сортировка. Решение Николы имеет три оконные функции в разных окнах + 1 окончательную сортировку. Фактор 2 не удивителен. EXPLAIN ANALYZE может рассказать вам больше. - person Erwin Brandstetter; 18.09.2012