sql-server - ORDER BY中的字段影响窗口函数的结果

Question

我有简单的 T-SQL 查询，它计算所有记录的行号、行数和总数量:

DECLARE @t TABLE
(
    id        varchar(100),
    volume    float,
    prev_date date
);

INSERT INTO @t VALUES
('0318610084', 100, '2019-05-16'),
('0318610084', 200, '2016-06-04');

SELECT
    row_num    = ROW_NUMBER() OVER (PARTITION BY id ORDER BY prev_date),
    rows_count = COUNT(*) OVER (PARTITION BY id ORDER BY prev_date),
    vol_total  = SUM(volume) OVER (PARTITION BY  id ORDER BY prev_date),
    *
FROM @t;

我得到以下结果:

但是，这不是我所期望的:在所有两行中，rows_count 必须为 2，vol_total 必须为 300:

解决方法是在 UNBOUNDED PRECEDING 和 UNBOUNDED FOLLOWING 之间添加 ROWS。但是，我认为一定有另一种方式。

最终我发现 ORDER BY 子句必须使用 id 字段而不是 prev_date 字段:

row_num    = ROW_NUMBER() OVER (PARTITION BY id ORDER BY id),
rows_count = COUNT(*) OVER (PARTITION BY id ORDER BY id),
vol_total  = SUM(volume) OVER (PARTITION BY id ORDER BY id)

进行此更改后，查询的输出符合预期。但!我不明白为什么会这样？顺序如何影响分区？

Answer 1

对于聚合函数，通常不需要在窗口定义中具有顺序，除非您想以有序的方式一次进行一个聚合，这就像运行总计。只需删除订单即可解决问题。

如果我想从另一种方式解释它，它就像一个窗口，当您移动到另一行时，它会逐行扩展。它从第一行开始，计算从之前(第一行中只是当前行!)到行位置的所有行的聚合。

如果删除顺序，将为窗口定义中的所有行计算聚合，并且应用窗口的顺序不会生效。

您可以更改窗口定义中的顺序以查看其效果。

当然，排名函数需要顺序，这一点仅适用于聚合。

DECLARE @t TABLE
(
    id        varchar(100),
    volume    float,
    prev_date date
);

INSERT INTO @t VALUES
('0318610084', 100, '2019-05-16'),
('0318610084', 200, '2016-06-04');

SELECT
   row_num    = ROW_NUMBER() OVER (PARTITION BY id ORDER BY prev_date),
   rows_count = COUNT(*) OVER (PARTITION BY id),
   vol_total  = SUM(volume) OVER (PARTITION BY  id),
   *
FROM @t;

在 SqlServer 2012 之后添加的聚合窗口中启用顺序，但它不是 2005 年该功能第一个版本的一部分。

对于聚合上窗口函数的顺序的详细解释，这是一个很大的帮助: Producing a moving average and cumulative total - SqlServer Documentation