PostgreSQL - 列值更改 - select查询优化

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

我希望确定数值何时被更改。因此我的查询结果应为：

ID  VAL
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 Fiddle

- Nailgun

---

你认为第一行的值从之前的“未知”或“空白”变为“已更改”了吗？ - Erwin Brandstetter

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只需记住它是后处理步骤。您无法在分析函数的值上执行过滤等操作，除非将其作为子查询。

- Glenn

---

为了方便那些对窗口函数不太熟悉的未来读者，您能否解释一下这段代码是如何工作的/它在做什么？ - Clockwork-Muse

@Clockwork-Muse 当然，已经添加了一些解释。 - Glenn

没有使用COALESCE也能正常工作，难道我漏掉了什么吗？http://sqlfiddle.com/#!15/30044/8 - Nailgun

@Nailgun：如果您的列可以为NULL，则“COALESCE”才有用。在这种情况下，“COALESCE”仅在第一行起作用-在那里它不会改变任何内容。无论是“val <> val”还是“val <> NULL”，都不会评估为“TRUE”-这是“WHERE”子句中唯一重要的结果。因此，您可以在此处删除“COALESCE”。我在我的答案中写得更多。 - Erwin Brandstetter

@Glenn：Postgres从不使用“分析函数”一词来表示窗口函数 - 对于这类函数所做的事情来说，这是一个相当奇怪的术语，至少在我听来是这样。你可能来自Oracle背景。 - Erwin Brandstetter

Answer 2

窗口函数

与其调用COALESCE，您可以直接从窗口函数lag()提供默认值。在此情况下，这是一个细节问题，因为所有列都定义为NOT NULL。但这可能对区分“没有前一行”和“前一行中的NULL”至关重要。

SELECT id, val
FROM  (
   SELECT id, val, <b>lag(val, 1, val)</b> OVER (ORDER BY id) <b><> val AS changed</b>
   FROM   p
   ) sub
WHERE  changed
ORDER  BY id;

立即计算比较结果，因为之前的值本身并不重要，只是可能发生了变化。更短，可能会稍微快一点。

如果您认为第一行已经“更改”（不像您的演示输出所示），那么您需要观察NULL值 - 即使您的列被定义为NOT NULL。基本的 lag() 在没有前一行的情况下返回NULL：

SELECT id, val
FROM  (
   SELECT id, val, lag(val) OVER (ORDER BY id) IS DISTINCT FROM val AS changed
   FROM   p
   ) sub
WHERE  changed
ORDER  BY id;

或者再次使用lag()的附加参数：

SELECT id, val
FROM  (
   SELECT id, val, <b>lag(val, 1, NOT val)</b> OVER (ORDER BY id) <b><> val AS changed</b>
   FROM   p
   ) sub
WHERE  changed
ORDER  BY id;

递归公共表达式 (Recursive CTE)

这只是一个概念证明。 :) 性能无法与已发布的替代方案相比。

WITH RECURSIVE cte AS (
   SELECT id, val
   FROM   p
   WHERE  NOT EXISTS (
      SELECT 1
      FROM   p p0
      WHERE  p0.id < p.id
      )
  
   UNION ALL
   SELECT p.id, p.val
   FROM   cte
   JOIN   p ON p.id   > cte.id
           AND p.val <> cte.val
   WHERE NOT EXISTS (
     SELECT 1
     FROM   p p0
     WHERE  p0.id   > cte.id
     AND    p0.val <> cte.val
     AND    p0.id   < p.id
     )
  )
SELECT * FROM cte;

在@wildplasser的帮助下有所改进。

SQL Fiddle演示所有内容。

- Erwin Brandstetter

---

你错过了我的极简主义方法。（还是我应该尝试使用递归CTE解决方案？） - wildplasser

也许我会尝试一下rCTE解决方案。我认为最简单（在数学术语上...）的解决方案应该是首选的。 - wildplasser

@wildplasser：之前我一直在关注窗口函数。就我所知，你的这段SQL代码已经无法再改进了（+1）。不过可能会让一些毫无准备的用户感到头疼。至于rCTE……给你。 :) - Erwin Brandstetter

FROM p WHERE id = 1 -->> FROM p p1 WHERE NOT EXISTS (SELECT 1 FROM p px WHERE px.id < p1.id) - wildplasser

@wildplasser：当然。应用它让它发光。 - Erwin Brandstetter

如果有第三列，比如设备ID，我该怎么做呢？我想获取所有设备ID值的变化发生的值。 - EmirC

Answer 3

甚至可以在不使用窗口函数的情况下完成。

SELECT * FROM p p0
WHERE EXISTS (
        SELECT * FROM p ex
        WHERE ex.id < p0.id
        AND ex.val <> p0.val
        AND NOT EXISTS (
                SELECT * FROM p nx
                WHERE nx.id < p0.id
                AND nx.id > ex.id
                )
        );

更新：自连接非递归CTE（也可以是子查询而不是CTE）

WITH drag AS (
        SELECT id
        , rank() OVER (ORDER BY id) AS rnk
        , val
        FROM p
        )
SELECT d1.*
FROM drag d1
JOIN drag d0 ON d0.rnk = d1.rnk -1
WHERE d1.val <> d0.val
        ;

这种非递归的CTE方法速度惊人地快，尽管它需要一种隐式排序。

- wildplasser

---

如果我使用MySql，这将是被接受的答案 :) - Nailgun

好的，我试过了。在我的实际案例中，情况要稍微复杂一些，使用了PostGis点和地理区域，而不是布尔值，共有大约30000行数据。经过测试，被接受的解决方案性能更好。虽然我不是PostgreSql专家，但似乎被接受的解决方案的成本较低：http://sqlfiddle.com/#!15/962ac/5 http://sqlfiddle.com/#!15/30044/6 无论如何，感谢你提供的版本，比我的好多了。 - Nailgun

Answer 4

使用2个row_number()计算: 这也可以使用通常的“岛和间隔”SQL技术来实现（如果由于某种原因无法使用lag()窗口函数，则可能会有用：

with cte1 as (
    select
        *,
        row_number() over(order by id) as rn1,
        row_number() over(partition by val order by id) as rn2
    from p
)
select *, rn1 - rn2 as g
from cte1
order by id

这个查询将会给你所有的岛屿。

ID VAL RN1 RN2  G
1   1   1   1   0
2   0   2   1   1
3   0   3   2   1
4   1   4   2   2
5   1   5   3   2
6   1   6   4   2
7   0   7   3   4

你看，如何使用G字段将这些岛屿分组在一起：

使用cte1作为子查询，根据id排序得到rn1，根据val和id排序得到rn2。最后根据val和rn1-rn2分组，并按照id的最小值排序，就可以得到结果。

ID VAL
1   1
2   0
4   1
7   0

现在唯一需要做的就是删除第一条记录，这可以通过使用 min(...) over() 窗口函数来完成：

with cte1 as (
   ...
), cte2 as (
    select
        min(id) as id,
        val,
        min(min(id)) over() as mid
    from cte1
    group by val, rn1 - rn2
)
select id, val
from cte2
where id <> mid

结果如下：

ID VAL
2   0
4   1
7   0

- Roman Pekar

Answer 5

一个简单的内连接就可以实现。SQL Fiddle

select p2.id, p2.val
from
    p p1
    inner join
    p p2 on p2.id = p1.id + 1
where p2.val != p1.val

- Clodoaldo Neto

---

虽然这个解决方案是正确的，但在现实生活中，ID可能不会一个接一个地进行。 - Nailgun

@Nailgun 您的样本数据应反映您的环境条件。 - Clodoaldo Neto

就我而言，无论提供的样本数据是什么，我都始终假设真实数据中缺少ID编号。甚至，@Nailgun，我还假设这些值并不能反映实际插入顺序，更不用说与业务相关的顺序了！ID值真正有价值的原因是它们之间的关联，以及它们应该在源表中是唯一的。任何其他用途都试图在不存在意义的地方赋予意义。 - Clockwork-Muse