sql - 在特定条件和正确的 lead() 用法上跳过 SQL 差距

Question

(PostgreSQL 8.4) 继续我的 previous example ，我希望通过窗口函数进一步理解间隙和孤岛处理。考虑下表和数据:

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 状态记录(同一设备连续两次)
• 显示当前ON 设备的适当OFF 状态

我能得到的最接近的是:

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 关闭——在这种情况下是假的，但会弄乱 lag()
2. 11 关闭——在这种情况下是假的，但搞乱了滞后()
3. 11 打开 -- OK，新序列，包含在 SELECT 中
4. 10 次开启 -- OK，新序列，包含在 SELECT 中
5. 11 关闭 -- 消息来晚了，需要忽略间隙
6. 10 次关闭 -- OK，正确关闭到第 4 行，需要包含在 SELECT 中

正确过滤后，我想使用 lead()最重要的是获取下一行的 ID(想象一个时间戳)并过滤掉所有不是 ON 状态的记录。我想这将需要三个嵌入式 SELECT 语句。这将使我清楚地了解设备处于事件状态的时间，直到另一个 ON 或正确打开 OFF 的条件。

Answer 1

使用窗口函数查询

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;

除了what we already had ，我们需要有效的 OFF 开关。

OFF 如果设备在 (last_status = 1) 之前和下一个 ON 之前切换为 ON 则有效code> 之后的操作发生在有问题的 OFF 开关之后 (next_id_of_last_status > id)。

我们必须提供最后一个 ON 操作的特殊情况，所以我们另外检查 NULL (OR next_id_of_last_status IS NULL)。

next_id_of_last_status 来 self 们获取 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();