主页 / dba / 问题 / 290034
Accepted
Pantea
Asked: 2021-04-19 22:34:13 +0800 CST

为什么更改窗口函数“MAX() OVER()”的 ORDER BY 部分中的列会影响最终结果？

  • 772

我有一个具有以下结构的表，它是数据：

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-2I useditem_name列中按部分顺序排列。我认为这两个查询必须生成相同的结果because I used item_index in both queries for partitioning!我现在完全糊涂了！为什么按列排序会影响最终结果？

QUERY-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

谁能解释这两个查询是如何执行的以及为什么每个查询都会产生不同的结果？

提前致谢

sql-server t-sql

4 个回答

  1. SQL Server 关于窗口函数的文档中给出了不同结果的解释，该ORDER BY部分：

    ORDER BY

    定义结果集的每个分区内行的逻辑顺序。也就是说，它指定了执行窗口函数计算的逻辑顺序。

    如果指定了它，而ROWS/RANGE没有指定 a，则默认值 被可以接受可选 规范（例如或）RANGE UNBOUNDED PRECEDING AND CURRENT ROW的函数用作窗口框架的默认值。ROWS/RANGEminmax

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

    如果没有这样的规范，他们会计算整个分区的 MIN 和 MAX。如果有，他们会计算指定范围内的 MIN 或 MAX。由于您的两个查询指定了不同的订单/范围，因此它们会产生不同的结果。

    如果您希望 MAX 覆盖整个分区，请删除ORDER BY范围：

    如果未指定，则默认顺序为 ASC ，窗口函数将使用 partition 中的所有行。

    • 5
  2. Best Answer

    max(t.Item_name)over(partition by t.item_index order by item_index) new_column

    让我们取一个t.item_index= 0 的组。它是

    项目索引 项目名
    0 一个
    0 C
    0 乙

    应用时order by item_index，所有行都具有相同的值，因此所有行都包含在框架中，并且所有行值都用于 MAX() 选择。所以'E'返回所有行的值。

    max(t.Item_name)over(partition by t.item_index order by item_name)

    让我们采取同一组。

    项目索引 项目名
    0 一个
    0 C
    0 乙

    现在排序键不同了，当RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW应用窗口时，不同的行被包含到要调查的框架中。

    对于第一行，只有这一行包含在框架中，并且'A'是框架中的唯一值，所以它被返回。

    对于第 2 行，前 2 行包含在帧中，值'A'和'C'进行比较，并'C'作为帧中的最大值返回。

    对于第 3 行，所有 3 行都包含在框架中，值'A'和'C'进行'E'比较，并'E'作为框架中的最大值返回。

    • 3

  3. 您使用的窗口子句（在这种情况下默认为“无界前行和当前行之间的范围”）对您订购的内容进行操作。这有一些逻辑：为了能够知道什么是“之前”或“之后”，你必须谈论有序数据。

    在 query-1 中，您按 item_index 排序，这也是您分区的依据。所以让我们看一下 item_index = 0 的分区。分区中的每一行都有一个 item_index 0。这意味着对于每一行，窗口实际上是“所有行 item_index <= 0”。所以，基本上分区中的所有行。而其中的最大值（item_name）是'E'。

    在查询 2 中，您按 item_name 订购。对于同一个分区，这意味着“A”行的窗口将是“所有带有 item_name <= 'A' 的行”这只是“A”行，因此 max(item_name) 是“A”。'C' 行的窗口将是 "All rows with an item_name <= 'C'" 那是 'A' 和 'C' 行，所以 max(item_name) 是 'C' 。'E' 行的窗口将是 "All rows with an item_name <= 'E'" 那是 'A', 'C' 和 'E' 行，所以 max(item_name) 是 'E' 。

    希望这有助于想象发生了什么。

    • 3

  4. 对于窗口函数，您需要记住一些事情：它们在每一行上执行。因此，像滞后和领先这样的窗口函数适用于前一行和下一行。通过添加 order by，您基本上是在说“在这个分组中，直到并包括当前行，列 item_name 的最大值是多少”。

    当在您订购的同一列上使用 max 时（假设为 asc），这没有意义，因为它总是与当前列相同，但对于 min 和 avg 是有意义的。让它为 max 做任何其他事情都太复杂了。

    • 1

相关问题