我有一个简单的表odd_nums
,其中包含列num
和odd
:
create table odd_nums
(
num numeric,
odd numeric
);
INSERT INTO odd_nums VALUES (0, 0);
INSERT INTO odd_nums VALUES (1, 1);
INSERT INTO odd_nums VALUES (2, 0);
INSERT INTO odd_nums VALUES (3, 1);
我正在玩弄窗口函数。该查询产生预期结果:
SELECT num,
FIRST_VALUE(num) OVER (PARTITION BY odd) a,
FIRST_VALUE(num) OVER (PARTITION BY odd) b
FROM odd_nums;
编号 | A | 乙 |
---|---|---|
0 | 0 | 0 |
2 | 0 | 0 |
1 | 1 | 1 |
3 | 1 | 1 |
当我在列中订购分区时,b
结果会发生如下变化:
SELECT NUM,
FIRST_VALUE(num) OVER (PARTITION BY odd) a,
FIRST_VALUE(num) OVER (PARTITION BY odd ORDER BY num DESC) b
FROM odd_nums;
编号 | A | 乙 |
---|---|---|
2 | 2 | 2 |
0 | 2 | 2 |
3 | 3 | 3 |
1 | 3 | 3 |
列b
是我所期望的,但为什么列a
也发生变化?
有人可以帮助我理解为什么ORDER BY num DESC
在第二个窗口函数中添加该子句会导致第一个窗口函数发生变化吗?是否有一些特定的数据库实现细节或优化可能会影响结果?
我浏览了 PostgreSQL 文档但没有成功。
将您的数据视为一堆衣服,其中
odd
列存储一种衣服的 ID(衬衫、裤子、袜子等)。子句相当于将衣服按每种类型分成不同的堆PARTITION
。odd
所以一堆衣服只是衬衫,另一堆只是袜子。然后,FIRST_VALUE()
窗口函数本身就像有人对你说,给我每堆衣服中的第一件。一堆衣服杂乱无章,那么每堆衣服中哪件衣服是第一个呢?第一次,您可以随机选择一件蓝色衬衫和红色袜子。如果你把这些衣服放回各自的堆里,然后再做一遍,你最终可能会得到一件绿色的衬衫和紫色的袜子。
这是你的数据。除非您在获取该数据的查询中显式指定一个顺序,否则它没有顺序。如果没有子句,大多数窗口函数就没有意义
ORDER BY
。如果没有,返回的数据是半随机的,称为非确定性的。当您将
ORDER BY
子句添加到查询中时,就像取出一堆衣服并将衣服排成一行,按尺寸排序。现在,每当有人向您询问每一系列衣服的第一件衣服时,您总是会反复拉最小的衬衫、最小的袜子等。这称为确定性。对于您的数据也是如此,请确保在使用依赖于排序的窗口函数(例如FIRST_VALUE()
)时明确对其进行排序。至于为什么当列的窗口函数不使用子句时,
a
列最终会遵循与列相同的顺序:数据库系统是惰性的,又是高效的。这在不同的数据库系统之间会略有不同,并且不能保证您总是会看到基于特定数据库系统的 SQL 引擎如何工作的大量因素的相同行为。但正如 jjanes 所指出的,为了最有效,当它看到没有为该列提供特定的排序时,它可能会重新使用该列中已排序的数据列表,以达到最有效的效果。对某件事物进行一次排序比以两种不同的方式对同一件事物进行排序更有效。b
a
ORDER BY
a
b
如果没有 ORDER BY,则表达式不确定。有多种可能的答案,它返回最方便计算的答案。一旦一个表达式获得 ORDER BY,那么将相同的排序也应用于另一个表达式就变得最方便。