跳过特定条件和适当的lead（）使用情况的SQL差距

小编典典

跳过特定条件和适当的lead（）使用情况的SQL差距

sql

（PostgreSQL 8.4）
继续前面的示例，我希望进一步了解使用Window函数进行间隙和孤岛处理的理解。请考虑下表和数据：

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为当前处于活动状态的设备寻找下一个合适的设备。

10关闭-在这种情况下是虚假的，但会使lag（）混乱
11设置为OFF-在这种情况下是假的，但会使lag（）混乱
11接通-确定，新序列，包括在SELECT中
10接通-确定，新序列，包括在SELECT中
11设为OFF-消息来晚了，需要忽略间隙
10档关闭-确定，将第4行正确关闭，需要包含在SELECT中

正确过滤后，我想首先使用lead()它来获取下一行的ID（想象一个时间戳），并过滤掉所有不是ON状态的记录。我想这将需要三个嵌入式SELECT语句。这将使我清楚地了解设备处于活动状态的时间，直到发生另一次ON或适当的转弯OFF。

阅读 187

2021-04-07

共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

除了我们已经拥有的之外，我们还需要有效的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)

并且我们需要更早地获得子查询（subquery t）中最后一个状态的下一个ID 。

如果您已经了解了所有 这些内容 ，那么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();

2021-04-07