sql - PostgreSQL - 列值已更改 - 选择查询优化

Question

假设我们有一张 table :

CREATE TABLE p
(
   id serial NOT NULL, 
   val boolean NOT NULL, 
   PRIMARY KEY (id)
);

填充了一些行:

insert into p (val)
values (true),(false),(false),(true),(true),(true),(false);

ID  VAL1   12   03   04   15   16   17   0

I want to determine when the value has been changed. So the result of my query should be:

ID  VAL2   04   17   0

I have a solution with joins and subqueries:

select min(id) id, val from
(
  select p1.id, p1.val, max(p2.id) last_prev
  from p p1
  join p p2
    on p2.id < p1.id and p2.val != p1.val
  group by p1.id, p1.val
) tmp
group by val, last_prev
order by id;

但是效率很低，对于行数多的表会非常慢。
我相信使用 PostgreSQL 窗口函数可能会有更有效的解决方案？

SQL Fiddle

Answer 1

这就是我使用分析的方式:

SELECT id, val
  FROM ( SELECT id, val
           ,LAG(val) OVER (ORDER BY id) AS prev_val
       FROM p ) x
  WHERE val <> COALESCE(prev_val, val)
  ORDER BY id

更新(一些解释):

分析函数作为后处理步骤运行。查询结果被分成多个分组(partition by)，并且在分组的上下文中应用分析函数。

在这种情况下，查询是从 p 中选择的。所应用的分析函数是 LAG。由于没有 partition by 子句，因此只有一个分组:整个结果集。此分组按 id 排序。 LAG 使用指定顺序返回分组中上一行的值。结果是每一行都有一个附加列(别名为 prev_val)，它是前一行的 val。那就是子查询。

然后我们寻找 val 与前一行 (prev_val) 的 val 不匹配的行。 COALESCE 处理第一行没有先前值的特殊情况。

解析函数起初可能看起来有点奇怪，但对解析函数进行搜索会发现很多示例来说明它们的工作原理。例如:http://www.cs.utexas.edu/~cannata/dbms/Analytic%20Functions%20in%20Oracle%208i%20and%209i.htm请记住，这是一个后处理步骤。除非您对其进行子查询，否则您将无法对分析函数的值执行过滤等操作。