如何基于月度实际营业日计算日均销售额（SQL实现）

我们需要用SQL计算月度日均销售额，目前仅能获取全量数据的销售均值，无法按具体月份拆分统计。这里的「实际营业日」指有订单产生的日期，我们认为通过现有代码结合月度实际营业日计算即可得到目标结果，现寻求技术帮助。

现有代码及结果 #

当前代码仅能返回全量数据的平均订单数：

SELECT
  AVG(Orders.num)
  /*Need Help Here*/
FROM 
(
  SELECT
    DAY(DateTimeCreated) as day,
    MONTH(DateTimeCreated) as month,
    YEAR(DateTimeCreated) as year,
    COUNT(DISTINCT OrderID) AS num
  FROM OrderHeader 
  WHERE
    DateTimeCreated >= DATEADD(
      month,
      datediff(month, 0, DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)),
      0
    )  
    AND OrderType <> 2 
    AND Deleted <> 1 
    AND BranchID = 9
  GROUP BY
    YEAR(DateTimeCreated),
    MONTH(DateTimeCreated),
    DAY(DateTimeCreated)
)
AS Orders

运行结果：

AVG Orders
48

子查询代码及结果 #

单独运行子查询（按天统计每日订单数）：

SELECT
  DAY(DateTimeCreated) as day,
  MONTH(DateTimeCreated) as month,
  YEAR(DateTimeCreated) as year,
  COUNT(DISTINCT OrderID) AS num
FROM OrderHeader 
WHERE
  DateTimeCreated >= DATEADD(
    month,
    datediff(month, 0, DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)),
    0
  )  
  AND OrderType <> 2 
  AND Deleted <> 1 
  AND BranchID = 9
GROUP BY
  YEAR(DateTimeCreated),
  MONTH(DateTimeCreated),
  DAY(DateTimeCreated)
Order By
  YEAR(DateTimeCreated),
  MONTH(DateTimeCreated),
  DAY(DateTimeCreated)

返回结果（仅展示2个月数据）：

day month   year    num
18  7   2023    22
19  7   2023    12
20  7   2023    37
21  7   2023    50
22  7   2023    18
23  7   2023    1
24  7   2023    56
25  7   2023    56
26  7   2023    74
27  7   2023    68
28  7   2023    41
30  7   2023    1
31  7   2023    55
1   8   2023    88
2   8   2023    62
3   8   2023    123
4   8   2023    91
5   8   2023    10
6   8   2023    4
7   8   2023    84
8   8   2023    77
9   8   2023    65
10  8   2023    56
11  8   2023    57
12  8   2023    5
13  8   2023    5
14  8   2023    78
15  8   2023    75
16  8   2023    53
17  8   2023    59
18  8   2023    51
19  8   2023    11
20  8   2023    24
21  8   2023    62
22  8   2023    59
23  8   2023    60
24  8   2023    92
25  8   2023    71
26  8   2023    1
27  8   2023    9
28  8   2023    63
29  8   2023    63
30  8   2023    72
31  8   2023    67

我们尝试过多种方案，参考相关思路后认为子查询路径可行，但卡在最后一步的分组计算环节。

解决方案 #

只需在外层查询中按年、月分组，计算每月总订单数和实际营业日数，再做除法即可得到月度日均销售额：

SELECT
  year,
  month,
  SUM(num) AS monthly_total_orders,
  COUNT(*) AS actual_business_days,
  CAST(SUM(num) AS FLOAT) / COUNT(*) AS daily_average_sales
FROM 
(
  SELECT
    DAY(DateTimeCreated) as day,
    MONTH(DateTimeCreated) as month,
    YEAR(DateTimeCreated) as year,
    COUNT(DISTINCT OrderID) AS num
  FROM OrderHeader 
  WHERE
    DateTimeCreated >= DATEADD(
      month,
      datediff(month, 0, DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)),
      0
    )  
    AND OrderType <> 2 
    AND Deleted <> 1 
    AND BranchID = 9
  GROUP BY
    YEAR(DateTimeCreated),
    MONTH(DateTimeCreated),
    DAY(DateTimeCreated)
) AS Orders
GROUP BY year, month
ORDER BY year, month

代码说明 #

• 子查询保持原有逻辑，按天统计每日独立订单数
• 外层查询：
  • SUM(num)：累加当月所有日期的订单数，得到月度总订单数
  • COUNT(*)：统计当月有订单的日期数量，即实际营业日数
  • CAST(SUM(num) AS FLOAT) / COUNT(*)：将总订单数转为浮点型后做除法，避免整数除法导致的精度丢失，得到精确的月度日均销售额

内容的提问来源于stack exchange，提问作者user23449102