时间戳

首页 > 解决方案 > ORDER BY 中的字段影响窗口函数的结果

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

问题描述

我有一个简单的 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;

我得到以下结果：

IMG1

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

IMG2

解决方法是添加ROWS BETWEEN UNBOUNDED PRECEDING 和 UNBOUNDED FOLLOWING。但是，我认为必须有另一种方法。

最后我发现 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)

在此更改之后，查询的输出与预期的一样。但！我不明白为什么会这样？排序如何影响分区？

标签： sql-servertsql

解决方案

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

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

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

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

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

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 年该功能的第一个版本的一部分。

有关聚合窗口函数中顺序的详细说明，这是一个很大的帮助： 生成移动平均和累积总计 - SqlServer 文档

推荐阅读