SQL 查询 SUM 并除以非重复日期计数

Question

我帮助，我正在寻找数据字段的总和，然后想将其除以该字段中不同日期的数量。

SUM(CASE WHEN dateResolved IS NOT NULL 
         THEN 1 ELSE 0 
    END) / DISTINCT(dateResolved) AvgPerDay

如果 dateResolve 中有 32 个日期，有 5 个不同的日期，我希望它返回 6.4。

Answer 1

默认情况下，

它会执行您需要的整数除法：

SUM(CASE WHEN dateResolved IS NOT NULL 
         THEN 1 ELSE 0 
    END) * 1.0 / COUNT(DISTINCT dateResolved) AvgPerDay

但是简单地count也可以工作：

COUNT(dateResolved) * 1.0 / COUNT(DISTINCT dateResolved) AvgPerDay

COUNT(dateResolved)将忽略null值。

Answer 2

我会这样做：

SUM(CASE WHEN dateResolved IS NOT NULL 
         THEN 1.0 ELSE 0 
    END) / COUNT(DISTINCT dateResolved) as AvgPerDay

但这更简单地表述为：

COUNT(dateResolved) * 1.0 / COUNT(DISTINCT dateResolved) as AvgPerDay