Перейти к разрыву SQL по конкретному условию и правильному использованию Lead()

(PostgreSQL 8.4) Продолжая мой предыдущий пример, я хочу углубить свое понимание обработки промежутков и островов с помощью оконных функций. Рассмотрим следующую таблицу и данные:

CREATE TABLE T1
(
  id SERIAL PRIMARY KEY,
  val INT,   -- some device
  status INT -- 0=OFF, 1=ON
);

INSERT INTO T1 (val, status) VALUES (10, 0);
INSERT INTO T1 (val, status) VALUES (11, 0);
INSERT INTO T1 (val, status) VALUES (11, 1);
INSERT INTO T1 (val, status) VALUES (10, 1);
INSERT INTO T1 (val, status) VALUES (11, 0);
INSERT INTO T1 (val, status) VALUES (10, 0);

Как объяснялось ранее, устройства включаются и выключаются, и на этот раз я хочу извлечь определенную последовательность:

  • показать все новые записи состояния ON, которые не дублируются (одно и то же устройство дважды подряд)
  • показать соответствующий OFF статус текущего ON устройства

Самое близкое, что я мог получить, это:

SELECT * FROM  (
  SELECT *
       ,lag(val, 1, 0) OVER (PARTITION BY status ORDER BY id) last_val
       ,lag(status, 1, -1) OVER (PARTITION BY val ORDER BY id) last_status
  FROM   t1
  ) x
WHERE  (last_val <> val OR last_status <> status)
AND    (status = 1 OR last_status <> -1)
ORDER BY id

Это отфильтровывает больше фиктивных данных, которые не включены в образец, но, по сути, речь идет об удалении последующих дубликатов (независимо от статуса) и первых OFF записей, которые не совпадают. Записи 3, 4, 5 и 6 возвращаются, но мне не нужна пятая, это OFF, которая пришла после новой ON. Поэтому мне нужно перепрыгнуть через этот пробел и найти следующий правильный OFF для текущего активного устройства.

  1. 10 выключается - в этом контексте ложно, но портит отставание ()
  2. 11 выключается — в этом контексте ложно, но портит отставание ()
  3. 11 включается -- OK, новая последовательность, включить в SELECT
  4. 10 включено -- OK, новая последовательность, включить в SELECT
  5. 11 выключается -- сообщение пришло с опозданием, нужно игнорировать пробел
  6. 10 выключается -- ОК, правильное OFF для строки 4, необходимо включить в SELECT

После правильной фильтрации я хотел бы использовать lead() сверху всего этого, чтобы получить идентификатор следующей строки (представьте себе отметку времени), а также отфильтровать все записи, которые не являются ON статусами. Я предполагаю, что для этого потребуются три встроенных оператора SELECT. Это дало бы мне четкое представление о том, как долго устройство было активным, до состояния другого ON или правильного поворота OFF.


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

Ответы (1)


arrow_upward
3
arrow_downward

Запрос с оконными функциями

SELECT *
FROM  (
   SELECT *
         ,lag(val, 1, 0)    OVER (PARTITION BY status ORDER BY id) AS last_val
         ,lag(status, 1, 0) OVER w2 AS last_status
         ,lag(next_id)      OVER w2 AS next_id_of_last_status
   FROM  (
      SELECT *, lead(id) OVER (PARTITION BY status ORDER BY id) AS next_id
      FROM   t1
      ) AS t
   WINDOW w2 AS (PARTITION BY val ORDER BY id)
  ) x
WHERE (last_val <> val OR last_status <> status)
AND   (status = 1 
       OR last_status = 1
          AND ((next_id_of_last_status > id) OR next_id_of_last_status IS NULL)
      )
ORDER  BY id

В дополнение к тому, что у нас уже было, нам нужны действующие переключатели OFF.

Переключатель OFF действителен, если устройство было переключено ON до (last_status = 1), а следующая операция ON после этого происходит после рассматриваемого переключателя OFF (next_id_of_last_status > id).

Мы должны предусмотреть особый случай, когда была последняя операция ON, поэтому мы дополнительно проверяем NULL (OR next_id_of_last_status IS NULL).

next_id_of_last_status происходит из того же окна, из которого мы берем last_status. Поэтому я ввел дополнительный синтаксис для явного объявления окна, поэтому мне не нужно повторяться:

WINDOW w2 AS (PARTITION BY val ORDER BY id)

И нам нужно получить следующий id для последнего статуса в подзапросе ранее (подзапрос t).

Если вы все поняли это, у вас не должно возникнуть проблем с добавлением lead() поверх этого запроса, чтобы добраться до конечного пункта назначения. :)

Функция PL/pgSQL

Как только он получит этот комплекс, пора переключиться на процедурную обработку.

Эта сравнительно простая функция plpgsql снижает производительность сложного запроса оконной функции по той простой причине, что она должна сканировать всю таблицу только один раз.

CREATE OR REPLACE FUNCTION valid_t1 (OUT t t1)  -- row variable of table type
  RETURNS SETOF t1 LANGUAGE plpgsql AS
$func$
DECLARE
   _last_on int := -1;  -- init with impossible value
BEGIN

FOR t IN
   SELECT * FROM t1 ORDER BY id
LOOP
   IF t.status = 1 THEN
      IF _last_on <> t.val THEN
         RETURN NEXT;
         _last_on := t.val;
      END IF;
   ELSE
      IF _last_on = t.val THEN
         RETURN NEXT;
         _last_on := -1;
      END IF;
   END IF;
END LOOP;

END
$func$;

Вызов:

SELECT * FROM valid_t1();
person Erwin Brandstetter    schedule 19.09.2012
comment
Это потрясающе. Куда отправить чек? А если серьезно, то один дополнительный вопрос. Почему EXPLAIN ANALYZE для запроса выполняется дольше (в 3 раза дольше), чем фактическое выполнение? Для справки, в таблице около 8 миллионов записей. - person denpanosekai; 20.09.2012
comment
@denpanosekai: EXPLAIN ANALYZE должен делать все, что делает запрос, плюс дополнительная работа, поэтому он может работать медленнее. Я думаю, что это улучшилось с версии 8.4. Также: когда он наконец закончился, каковы были результаты? Я ожидаю, что функция будет примерно в 3-4 раза быстрее, чем запрос..? - person Erwin Brandstetter; 20.09.2012
comment
На самом деле оба выполняются за 13 секунд, но функция возвращает 381 тыс. записей, а запрос возвращает 327 тыс. записей. Черт, должно же быть какое-то дополнительное второстепенное условие, которое я не учел... Сейчас я в основном сортирую мусор. Я думаю, что это связано с дублированием записей ON/OFF с одной и той же временной меткой (на самом деле не используется поле идентификатора, как в моем примере). Но ваша помощь была неоценимой, и я вам очень благодарен. - person denpanosekai; 20.09.2012
comment
Более того, при тестировании чистых наборов данных (то же количество записей, без повторяющихся временных меток) запрос работает намного быстрее (может получить 500 записей из 8 миллионов за 20 мс, а не за 35 мс по функциям). Из того, что я вижу, нет из них используют индексы, поэтому я не могу сказать, в чем разница. - person denpanosekai; 20.09.2012
comment
@denpanosekai: Это неожиданно. В быстром тестировании с небольшим набором данных на PostgreSQL 9.1.5 эта функция оказалась явно лучше. Это всегда зависит от многих факторов. Круто в любом случае, я одинаково люблю обоих своих детей. ;) - person Erwin Brandstetter; 20.09.2012