Обнаружение острова SQL по нескольким параметрам и условиям

(PostgreSQL 8.4) Я получил отличное представление о пробелах и островах SQL здесь, в Stack Overflow, но у меня все еще есть вопрос. Многие CTE обнаружения островов основаны на порядке выполнения временной метки и некоторого флага, который прерывает последовательность при ее изменении. Но что, если условие разрыва немного сложнее?

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

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

В этом случае val представляет устройство, а status — либо ON, либо OFF. Я хочу выбрать записи 1, 3, 6, 7 и 9 со следующей логикой.

  1. 10 включено -- OK, новая последовательность, включить в SELECT

  2. 10 поворотов OFF -- правильно завершает последовательность, игнорируя строку

  3. 11 включается -- OK, новая последовательность, включить в SELECT

  4. 11 включается -- дублировать, игнорировать строку

  5. 10 выключено -- #10 не было включено, игнорировать

  6. 12 включается -- OK, неявно выключается #11, включается в SELECT

  7. 13 включается -- ОК, неявно выключается #12, включается в SELECT

  8. 13 выключается -- правильно завершает последовательность, игнорируя строку

  9. 13 включается -- OK, новая последовательность, включить в SELECT

По сути, одновременно может быть включено только одно устройство, и условие прерывания таково:

  • new.val = running.val AND new.status = 0
  • new.val ‹› running.val AND new.status = 1

Я ищу что-нибудь в виде CTE, без курсоров, пожалуйста.


postgresql sql gaps-and-islands
person Jeff    schedule 17.09.2012    source источник
comment
Учитывая нужные вам строки, первое условие — это шум. Похоже, вас вообще не интересуют OFF строки.   -  person Clockwork-Muse    schedule 18.09.2012
comment
Добавлены две дополнительные строки (8, 9) вместе с объяснением.   -  person Jeff    schedule 18.09.2012

Ответы (1)


arrow_upward
2
arrow_downward

Ответ на обновленный вопрос

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

Как?

То же, что и раньше, но на этот раз объедините две оконные функции. Включение устройства считается допустимым, если ..
1. последнее включенное устройство было другим.
2. или это же устройство было выключено в его последней записи. Крайний случай с NULL для первой строки раздела не имеет значения, потому что тогда строка уже квалифицирована в 1.

Ответ на исходную версию вопроса.

Если я правильно понимаю вашу задачу, этот простой запрос выполнит задание:

SELECT *
FROM  (
   SELECT *
         ,lag(val, 1, 0) OVER (ORDER BY id) last_on
   FROM   t1
   WHERE  status = 1
   ) x
WHERE  last_on <> val

Возвращает строки 1, 3, 6, 7 по запросу.

Как?

Подзапрос игнорирует все выключения, так как это просто шум, согласно вашему описанию. Оставляет записи, где устройство включено. Среди них дисквалифицируются только те записи, где это же устройство уже было включено (включение последней записи). Для этого используйте оконную функцию lag(). . В частности, я предоставляю 0 по умолчанию, чтобы покрыть особый случай первой строки - при условии, что нет устройства с val = 0.
Если есть, выберите другое невозможное число.
Если нет невозможного числа, оставьте специальное case как NULL с lag(val) OVER ... и во внешнем запросе проверьте с помощью:

WHERE last_on IS DISTINCT FROM val
person Erwin Brandstetter    schedule 17.09.2012
comment
Это отлично работает, но мне не хватало примера, чтобы проиллюстрировать, почему я считаю статус важным. Рассмотрим дополнительные две строки, которые добавляют OFF из 13, а затем еще один ON. Один и тот же девайс два раза подряд, который лаг игнорирует (надо подцепить 9-й ряд) - person Jeff; 18.09.2012
comment
@Jeff: я добавил еще одно решение. К вашему сведению, если у вас есть обновления для вашего вопроса, которые сделают недействительными существующие ответы, написанные добросовестно, рекомендуется начать новый вопрос. Мы можем просто откатить такие правки. Однажды я добавил новый ответ. - person Erwin Brandstetter; 18.09.2012