sql - 如何获得 DATEDIFF() 的平均值？

Question

我有以下查询:

SELECT 
    DATEDIFF(day, DateUsed, DateExpires) AS DaysBetweenExpirationAndUse
FROM tblOffer

如何从 DaysBetweenExpirationAndUse 列中获取平均天数？

Answer 1

这应该可以正常工作:

SELECT AVG(DATEDIFF(d, DateUsed, DateExpires)) AS AvgDaysBetweenExpirationAndUse 
FROM tbl

如果你想在你的 AVG 中保留小数位:

SELECT AVG(DATEDIFF(d, DateUsed, DateExpires)*1.0) AS AvgDaysBetweenExpirationAndUse 
FROM tbl

如果你想同时选择 AVG 和其他字段，而不分组:

SELECT *
      ,AVG(DATEDIFF(d, DateUsed, DateExpires)*1.0) OVER() AS AvgDaysBetweenExpirationAndUse 
FROM tbl