是否有用于SQL聚合函数计算的标准？ [英] Is there a standard for SQL aggregate function calculation?

问题描述

是否在SQL实现上有一个标准，可以在同一查询中多次调用同一聚合函数？

Is there a standard on SQL implementaton for multiple calls to the same aggregate function in the same query?

例如，考虑以下示例，该示例基于示例架构：

For example, consider the following example, based on a popular example schema:

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)>1000

大概需要花费时间才能计算SUM（OrderPrice）的值。

Presumably, it takes computation time to calculate the value of SUM(OrderPrice). Is this cost incurred for each reference to the aggregate function, or is the result stored for a particular query?

或者，在这种情况下，没有针对SQL引擎实现的标准吗？ ？

Or, is there no standard for SQL engine implementation for this case?

推荐答案

尽管我使用过许多不同的DBMS，但仅向您显示在SQL Server上进行验证的结果。考虑一下该查询，该查询甚至在表达式中包含CAST。查看查询计划，仅使用一次表达式 sum（cast（number（bigint））），定义为 DEFINE：（[Expr1005 ] = SUM（[Expr1006]）。

Although I have worked with many different DBMS, I will only show you the result of proving this on SQL Server. Consider this query, which even includes a CAST in the expression. Looking at the query plan, the expression sum(cast(number as bigint)) is only taken once, which is defined as DEFINE:([Expr1005]=SUM([Expr1006])).

set showplan_text on
select type, sum(cast(number as bigint))
from master..spt_values
group by type
having sum(cast(number as bigint)) > 100000

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Filter(WHERE:([Expr1005]>(100000)))
       |--Hash Match(Aggregate, HASH:([Expr1004]), RESIDUAL:([Expr1004] = [Expr1004]) DEFINE:([Expr1005]=SUM([Expr1006])))
            |--Compute Scalar(DEFINE:([Expr1004]=CONVERT(nchar(3),[mssqlsystemresource].[sys].[spt_values].[type],0), [Expr1006]=CONVERT(bigint,[mssqlsystemresource].[sys].[spt_values].[number],0)))
                 |--Index Scan(OBJECT:([mssqlsystemresource].[sys].[spt_values].[ix2_spt_values_nu_nc]))

上面可能不是很明显，因为它没有显示SELECT结果，所以我在下面的查询中添加了 * 10 。注意，它现在包括一个额外的步骤 DEFINE：（[[Expr1006] = [Expr1005] *（10））（步骤从下至上），该步骤表明需要使用新表达式它执行额外的计算。但是，即使对此进行了优化，因为它不会重新计算整个表达式-只是采用Expr1005并将其乘以10！

It may not be very obvious above, since it doesn't show the SELECT result, so I have added a *10 to the query below. Notice that it now includes one extra step DEFINE:([Expr1006]=[Expr1005]*(10)) (steps run bottom to top) which demonstrates that the new expression required it to perform an extra calculation. Yet, even this is optimized, as it doesn't recalculate the entire expression - merely, it is taking Expr1005 and multiplying that by 10!

set showplan_text on
select type, sum(cast(number as bigint))*10
from master..spt_values
group by type
having sum(cast(number as bigint)) > 100000

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1006]=[Expr1005]*(10)))
       |--Filter(WHERE:([Expr1005]>(100000)))
            |--Hash Match(Aggregate, HASH:([Expr1004]), RESIDUAL:([Expr1004] = [Expr1004]) DEFINE:([Expr1005]=SUM([Expr1007])))
                 |--Compute Scalar(DEFINE:([Expr1004]=CONVERT(nchar(3),[mssqlsystemresource].[sys].[spt_values].[type],0), [Expr1007]=CONVERT(bigint,[mssqlsystemresource].[sys].[spt_values].[number],0)))
                      |--Index Scan(OBJECT:([mssqlsystemresource].[sys].[spt_values].[ix2_spt_values_nu_nc]))

这很可能是所有其他DBMS的工作方式，至少要考虑主要的数据库，例如PostgreSQL，Sybase，Oracle，DB2，Firebird，MySQL。

This is very likely how all the other DBMS work as well, at least considering the major ones i.e. PostgreSQL, Sybase, Oracle, DB2, Firebird, MySQL.

这篇关于是否有用于SQL聚合函数计算的标准？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！