Navicat 博客

在 SQL Server 中计算平均每日计数 2020 年 11 月 20 日,由 Robert Gravelle 撰写

计算平均每日计数似乎是经常会做的事情,但我从未做过。我问我的妻子是否有这样做过,她也是数据库支持的应用程序的程序员,而她也从来没机会这样做!因此,我今天充满热情地接受了这一个挑战。

要界定“平均每日计数”的含义,对于本篇文章而言,它说明在医生办公室的每月患者计数,一个月内制造的小部件数目或一个月内出售的产品数目。然后通过将每月计数除以一个月的天数来计算每日平均值,以便我们知道每日计数对每月总数的贡献。例如,如果一家汽车经销商在 5 月份销售了 10 辆本田思域,那么这 10 辆交易就意味着平均每天销售量为 0.32 辆。同时,如果经销商要在一个月内售出多达 50 辆本田思域,那么每日平均销售量将升至每天 1.61 辆本田思域。

查询

我们的 SELECT 语句将查询 Sakila 示例数据库,以以下格式列出每个月的电影租借量:

ID| MONTH | MONTHLY_COUNT | AVG_DAILY_COUNT 
------------------------------------------- 
 1| Jan   | 152           | 10.3 
 2| Jan   | 15000         | 1611 
 3| Jan   | 14255         | 2177 
 1| Feb   | 4300          | 113 
 2| Feb   | 9700          | 782 
 3| Feb   | 1900          | 97 
etc... 

上面的 AVG_DAILY_COUNT 列增加了查询的复杂性,因为我们需要首先获取月度计数。因此,查询由内部和外部 SELECT 语句组成。以下是内部查询和按年、月和 ventory_id 排序的结果:

inner_query (150K)

外部查询

从这些数据中,我们可以将平均每日计数制成如下的列表:

outer_query (165K)

我将每个月的天数包括在内以作参考,因为它在计算每日平均值中起着关键作用。这也很有启发性,因为需要每个月的天数才能计算出平均每日计数,因此如何获得一个月的天数也很有趣。这是从查询其余部分分离出的代码:

datediff(day, 
         datefromparts(rental_year, rental_month, 1), 
         dateadd(month, 1, datefromparts(rental_year, rental_month, 1))) days_in_month

datediff() 函数返回每月第一天到下个月第一天之间的天数。datefromparts() 函数用内部查询的 rental_year 和 rental_month 列创建一个日期。

我们可以在 daily_avg 的计算中看到相同的代码:

round(
    cast(cnt as FLOAT) / cast(datediff(day, 
                              datefromparts(rental_year, rental_month, 1), 
                              dateadd(month, 1, datefromparts(rental_year, rental_month, 1))) as FLOAT
                         ), 4
) daily_avg

请注意,被除数(cnt)和除数(月的天数)都必须转换为 FLOAT。否则,小数将在计算中被丢弃。我们希望尽可能地保持精度在舍入为 4 位小数位数。

总结

在今天的文章中,我们使用 Navicat for SQL Server计算了 SQL Server 中给定列的平均每日计数。如果你对 Navicat for SQL Server 感兴趣,可以免费试用 14 天!

Navicat 文章
频道条目
分享
文章归档