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?
推荐答案
这就是我要通过解析来做到的方式:
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屋!