我如何获得一个月的日均值？

问题描述：

select count(distinct people_id) 
from #enrollments_PreviousMonth 
where program_modifier_id = 'E1AA7A36-0500-4BAE-A0AA-D9E0BC91A6F3' 
AND actual_date between '4/1/13' and '5/1/13'

上面是我当前代码的一部分。我想在四月份的每一天获得明显的people_id数。那么我想要将这些数字计算在四月份。例如，如果4月1日的计数为764，4月2日的计数为763，那么我将总计764和763 = 1527。同样，我会在4月的每一天进行总计。最后，我会用四月的天数除以得到我的日常平均值。什么是最有效的TSQL来完成这一点？有没有可以用于这个或其他标准SQL运算符的CTE？

答

你可能想使用这样的

SELECT round(Cast(count(people_id)as float)/Cast(DateDiff(day, '4/1/13', '4/6/13')as float), 2) as average 
    FROM #enrollments_PreviousMonth 
    WHERE program_modifier_id = 'E1AA7A36-0500-4BAE-A0AA-D9E0BC91A6F3' 
     AND actual_date between '4/1/13' and '5/1/13' 
    GROUP BY people_id

答

WITH list AS (
select actual_date,count(distinct people_id) cnt 
from #enrollments_PreviousMonth 
where program_modifier_id = 'E1AA7A36-0500-4BAE-A0AA-D9E0BC91A6F3' 
AND actual_date between '4/1/13' and '5/1/13' 
GROUP BY actual_date) 
SELECT AVG(cnt) FROM list

确保你施放CNT平均或平均会返回一个int，而不是一个浮动时，浮动。 – 2013-05-10 17:15:09

答

SELECT AVG(CONVERT(FLOAT, distinctPeopleByDate)) 
FROM 
(
    select actual_date, count(distinct people_id) as distinctPeopleByDate 
    from #enrollments_PreviousMonth 
    where program_modifier_id = 'E1AA7A36-0500-4BAE-A0AA-D9E0BC91A6F3' 
    and actual_date between '4/1/13' and '5/1/13' 
    group by actual_date 
) x

我如何获得一个月的日均值？

相关推荐