sql-server – 聚合分组单调函数的冗余排序

CREATE TABLE [dbo].[Example]
(
    [x] FLOAT NOT NULL,[y] FLOAT NOT NULL,PRIMARY KEY CLUSTERED 
    (
        [x] ASC
    )
);

SELECT FLOOR([x]),AVG([y])
FROM [dbo].[Example]
GROUP BY FLOOR([x]);

这里我有(x,y)对已经按x排序(由于聚集主键),我对每个整数x求平均值y(通过FLOOR函数截断).我希望该表已经适合于聚合,因为FLOOR是单调函数.不幸的是,SQL Server决定需要重新排序这些数据,这是执行计划：

SQL Server是否能够对已经适当排序的列的单调函数分组的数据执行流聚合？

是否有一般方法来重写此类查询,以便SQL Server将看到订单被保留？

[更新]
我发现了一篇关于主题Things SQL needs: sargability of monotonic functions的文章,正如标题所示,似乎这是SQL Server尚未做的优化(在大多数情况下).

这里甚至比[dbo]更简单的查询.[示例]证明了这一点：

SELECT [x],[y]
FROM [dbo].[Example]
ORDER BY FLOOR([x]) --sort performed in execution plan

SELECT [x],[y]
FROM [dbo].[Example]
ORDER BY 2*[x] --NO sort performed in execution plan

SELECT [x],[y]
FROM [dbo].[Example]
ORDER BY 2*[x]+1 --sort performed in execution plan

在任何单个添加或乘法中,查询优化器都会理解数据已经具有相同的顺序(当您按这样的表达式进行分组时也会看到这种情况).因此,似乎优化器可以理解单调函数的概念,而不是通常应用的.

我现在正在测试计算列/索引解决方案,但似乎这会大大增加持久数据的大小,因为我需要几个索引来覆盖可能的间隔范围.

解决方法

>当表为空时看到的计划和表有X行的计划可以是完全不同的计划
>我不认为在X字段上拥有主键是正确的.可以有两个具有相同X值的点吗？

如果您执行以下操作,我认为您将获得最佳查询性能：

create table Point
(
    PointId int identity(1,1)
        constraint PK_Example_Id primary key,X float not null,Y float not null,FloorX as floor(x) persisted
)

create index IX_Point_FloorX_Y on Point(FloorX,Y)

添加一些行：

declare @RowCount int = 10000
while(@RowCount > 0)
begin
    insert Point
    values (cast(crypt_gen_random(2) as int),cast(crypt_gen_random(2) as int))
    set @RowCount -= 1
end

查询：

select floor(X),avg(Y)
from Point
group by floor(X)

要么

select FloorX,avg(Y)
from Point
group by FloorX

两者都有相同的计划

计划：没有排序

另一个选项 – 您可以创建索引视图.在这种情况下,您将不得不直接查询视图,除非您有Enterprise Edition,即使您直接查询表,它也会使用索引视图索引.

[编辑]刚才意识到我没有明确回答你的问题.你问为什么如果X是集群主键,SQL会执行排序. SQL不对X执行排序,它在floor(x)上执行排序.换句话说,如果x已经排序,那么f(x)不一定具有相同的顺序,对吧？

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!