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

Question

说我们有一张桌子：

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);



身份验证
1 1
2 0
3 0
4 1
5 1
6 1
7 0

我想确定何时更改了值。所以我的查询结果应该是：

身份验证
2 0
4 1
7 0

我有一个连接和子查询的解决方案：

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小提琴

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列是前一行的。那就是子查询。

然后，我们查找与上一行（prev_val）val不匹配的val行。该COALESCE句柄处理不具有先前值的第一行的特殊情况。

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