为什么按窗口函数"MAX() OVER()“部分的顺序更改列会影响最终结果？

Question

我有一张表，它的结构和数据如下：

create table test_table
(
Item_index   int,
Item_name    varchar(50)
)

insert into test_table (Item_index,Item_name) values (0,'A')
insert into test_table (Item_index,Item_name) values (1,'B')
insert into test_table (Item_index,Item_name) values (0,'C')
insert into test_table (Item_index,Item_name) values (1,'D')
insert into test_table (Item_index,Item_name) values (0,'E')

我想知道为什么更改查询的order by部分中的列，会更改结果？在QUERY-1中，我使用了item_index，在QUERY-2中，我按顺序使用了item_name列。我认为这两个查询都必须生成相同的结果-- because I used item_index in both queries for partitioning!，我现在完全糊涂了！为什么按列排序会影响最终结果？

查询-1：

select t.*,
       max(t.Item_name)over(partition by t.item_index order by item_index) new_column
from test_table t;

结果：

Item_index  Item_name     new_column
----------- --------------------------
0           A                E
0           C                E
0           E                E
1           D                D
1           B                D

查询-2：

select t.*,
       max(t.Item_name)over(partition by t.item_index order by item_name) new_column
from test_table t;

结果：

Item_index  Item_name  new_column
----------- -----------------------
0           A             A
0           C             C
0           E             E
1           B             B
1           D             D

有人能解释这两个查询是如何执行的吗?为什么每个查询产生不同的结果？

提前感谢

Answer 1

对于不同结果的解释见Server关于窗口函数的文档，ORDER BY部分：

ORDER BY定义结果集中每个分区中行的逻辑顺序。也就是说，它指定执行窗口函数计算的逻辑顺序。如果它是指定的，并且没有指定一个ROWS/RANGE，那么可以接受可选的ROWS/RANGE规范(例如min或max)的函数将默认的RANGE UNBOUNDED PRECEDING AND CURRENT ROW用作窗口框架的缺省值。

注意，MIN()和MAX()窗口聚合接受可选的ROWS或RANGE规范。

当没有这样的规范时，他们计算整个分区的最小和最大。如果存在，则计算指定范围内的最小值或最大值。由于两个查询指定了不同的顺序/范围，因此它们产生不同的结果。

如果您想要整个分区上的MAX，那么删除ORDER BY范围：

如果未指定，则默认顺序为ASC，而window函数将使用分区中的所有行。

Answer 2

您使用的windowing子句(在本例中为“在无界的前一行和当前行之间的范围”的默认值)对您所订购的内容进行操作。这有一些逻辑:要想知道什么是“前面”或“跟随”，就必须谈论有序的数据。

在query-1中，您可以通过item_index进行定购，这也是分区的基础。让我们看一下item_index = 0的分区。分区中的每一行都有一个item_index 0。这意味着，对于每一行，窗口实际上都是“具有item_index <= 0的所有行”。基本上，分区中的所有行。最大(Item_name)值是'E‘。

在query-2中，您可以通过item_name订购。对于相同的分区，这意味着'A‘行的窗口将是“具有item_name <= 'A’的所有行”，也就是'A‘行，因此max(item_name)是’A‘。“C”行的窗口将是“具有item_name <= 'C‘的所有行”，即'A’和'C‘行，因此最大值(Item_name)为'C’。“E”行的窗口将是“具有item_name <= 'E‘的所有行”，即'A’、'C‘和'E’行，因此最大值(Item_name)为'E‘。

希望这有助于想象发生的事情。

Answer 3

对于窗口函数，您需要记住的一点是:它们在每一行上都执行。因此，像延迟和引导这样的窗口函数应用于前一行和下一行。通过添加order，您基本上是在说“在这个分组中，直到并包括当前行，列item_name的最大值是多少”。

当在同一列上使用max (假设为asc)时，这是没有意义的，因为它总是指向当前列，但对于min和avg来说是有意义的。让它为麦克斯做任何事都太复杂了。