sql-server - 计算发货时间超过 X 天的订单百分比的更好方法？

Question

我正在编写一个 T-SQL 查询来计算发货时间超过 X 天的订单的百分比。
我现在拥有的东西可以工作，但感觉很笨拙，我一直在寻求提高我的查询写作技巧。
第一个 CTE 是从一个时间范围内获取总订单。第二个 CTE 是获取发货日期早于(或少于)订单日期 3 天的任何订单。

;WITH TotalOrders
AS (
SELECT COUNT(*) AS TotalOrders
     , MONTH(OrderDate) AS Month
     , YEAR(OrderDate) AS Year
  FROM Orders
 WHERE OrderDate >= '2020-05-01'
GROUP BY MONTH(OrderDate), YEAR(OrderDate)
),
Under3
AS (
SELECT COUNT(*) AS Under3Days
     , MONTH(OrderDate) AS Month
     , YEAR(OrderDate) AS Year
  FROM SorMaster SM
 WHERE OrderDate >= '2020-05-01'
   AND DATEDIFF(D, OrderDate, ShipDate) <= 3
GROUP BY MONTH(OrderDate), YEAR(OrderDate)

)

SELECT ROUND(Under3Days * 100.0 / TPO.TotalOrders, 1) AS 'PercentOfTotal'
     , TPO.Month
     , TPO.Year
  FROM Under3
  JOIN TotalOrders TPO
    ON TPO.Month = Under3.Month
   AND TPO.Year = Under3.Year

我知道我也可以在临时表中执行此操作，但想知道在进行这些计算时是否有最佳实践可遵循

Answer 1

您可以使用条件聚合:

SELECT 
   ROUND(AVG(CASE WHEN ShipDate <= DATEADD(day, 3, OrderDate) THEN 100.0 ELSE 0 END), 1) AS PercentOfTotal
 , MONTH(OrderDate) AS Month
 , YEAR(OrderDate) AS Year
FROM Orders
WHERE OrderDate >= '2020-05-01'
GROUP BY MONTH(OrderDate), YEAR(OrderDate)