PostgreSQL-列值已更改-选择查询优化 [英] PostgreSQL - column value changed - select query optimization

问题描述

说我们有一个表：

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  VAL
1   1
2   0
3   0
4   1
5   1
6   1
7   0

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

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

ID  VAL
2   0
4   1
7   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窗口函数可能会有更有效的解决方案？

But it is very inefficient and will work extremely slow for tables with many rows.
I believe there could be more efficient solution using PostgreSQL window functions?

SQL小提琴

推荐答案

这就是我要通过解析来做到的方式：

This is how I would do it with an analytic:

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

更新（一些解释） ：

分析功能是后处理步骤。查询结果分为多个分组（分区为），并且在分组的上下文中应用了分析功能。

Analytic functions operate as a post-processing step. The query result is broken into groupings (partition by) and the analytic function is applied within the context of a grouping.

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

In this case, the query is a selection from p. The analytic function being applied is LAG. Since there is no partition by clause, there is only one grouping: the entire result set. This grouping is ordered by id. LAG returns the value of the previous row in the grouping using the specified order. The result is each row having an additional column (aliased prev_val) which is the val of the preceding row. That is the subquery.

然后我们寻找 val 与 val 上一行（prev_val）。 COALESCE 处理不具有先前值的第一行的特殊情况。

Then we look for rows where the val does not match the val of the previous row (prev_val). The COALESCE handles the special case of the first row which does not have a previous value.

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

Analytic functions may seem a bit strange at first, but a search on analytic functions finds a lot of examples walking through how they work. For example: http://www.cs.utexas.edu/~cannata/dbms/Analytic%20Functions%20in%20Oracle%208i%20and%209i.htm Just remember that it is a post-processing step. You won't be able to perform filtering, etc on the value of an analytic function unless you subquery it.

这篇关于PostgreSQL-列值已更改-选择查询优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！