I stumbled upon a question (in a test) about which aggregate functions are applicable to DATE type columns. So, as I understand it, COUNT will just count the number of rows, and MIN and MAX return the earliest/latest date. However, I'm a bit confused about SUM and AVG functions. Will they just convert the DATE values to ints and calculate sum/avg on those ints? Or am I wrong here? Anyway, is this behaviour consistent across all implementations of SQL? Thanks in advance.
我偶然发现了一个关于哪些聚合函数适用于日期类型列的问题(在一个测试中)。所以，据我所知，COUNT只计算行数，MIN和MAX返回最早/最近的日期。但是，我对SUM和AVG函数有点困惑。他们会将日期值转换为int并计算它们的sum/avg吗?还是我说错了?无论如何，这种行为在所有SQL实现中都是一致的吗?提前谢谢。
1 个解决方案
#1
5
In MS SQL Server You cannot call the SUM operator on datetime types, nor can you call the AVG operator.
在MS SQL Server中，不能调用datetime类型上的SUM运算符，也不能调用AVG运算符。
MSDN lists the return types of the SUM operator here:
http://msdn.microsoft.com/en-us/library/ms187810.aspx
MSDN在这里列出SUM运算符的返回类型:http://msdn.microsoft.com/en-us/library/ms187810.aspx
It is also a valid reference for the types on which you may invoke the SUM operator.
它也是可以调用SUM运算符的类型的有效引用。
EDIT: In response to your comment, you may use a site like sqlfiddle to test various implementations
编辑:为了响应您的评论，您可以使用sqlfiddle这样的站点来测试各种实现
http://www.sqlfiddle.com/#!3/22cee/1
http://www.sqlfiddle.com/ ! 3/22cee / 1
Considering the backend storage of datetime is not standardized, I contend that it should not be depended upon for any database to return SUM or AVG results in predictable ways.... better not to do it at all...
考虑到后端存储的datetime不是标准化的,我认为它不应该依赖任何数据库返回总和或AVG结果以可预测的方式....最好不要做那件事……
#1
5
In MS SQL Server You cannot call the SUM operator on datetime types, nor can you call the AVG operator.
在MS SQL Server中，不能调用datetime类型上的SUM运算符，也不能调用AVG运算符。
MSDN lists the return types of the SUM operator here:
http://msdn.microsoft.com/en-us/library/ms187810.aspx
MSDN在这里列出SUM运算符的返回类型:http://msdn.microsoft.com/en-us/library/ms187810.aspx
It is also a valid reference for the types on which you may invoke the SUM operator.
它也是可以调用SUM运算符的类型的有效引用。
EDIT: In response to your comment, you may use a site like sqlfiddle to test various implementations
编辑:为了响应您的评论，您可以使用sqlfiddle这样的站点来测试各种实现
http://www.sqlfiddle.com/#!3/22cee/1
http://www.sqlfiddle.com/ ! 3/22cee / 1
Considering the backend storage of datetime is not standardized, I contend that it should not be depended upon for any database to return SUM or AVG results in predictable ways.... better not to do it at all...
考虑到后端存储的datetime不是标准化的,我认为它不应该依赖任何数据库返回总和或AVG结果以可预测的方式....最好不要做那件事……