当表中没有所有天数时，如何选择每工作日/周/月/60天/90天的平均值

Question

好吧，所以经过大量研究，我仍然停滞不前。我得到了简单的基础：

我可以使用group-by语句来选择所有值的平均值。按照我想要的平均值对日期在我范围内的地方进行分组。

Select AVG(Qty)
From (
            Select Sum(ItemQty) As Qty
            From ReceivedInfo
            Where DateUsed >= '2014-04-01' 
                         AND DateUsed < '2014-04-30'
            Group By Date(DateUsed)
)

这里的问题是，我也需要包括表中没有的日期。所有工作日都应计入没有条目的工作日。我不知道如何从一个数据范围中获得每天(或每月，或60天等(的平均数量，包括所有工作日。排除周末。

做这样的事情就足够了吗：

Select Sum(QTy) / (julianday('2014-04-30') - julianday('2014-04-01' )) - (((julianday('2014-04-30') - julianday('2014-04-01' )) / 7 ) * 2) As Average
From (
            Select Sum(ItemQty) As Qty
            From ReceivedInfo
            Where DateUsed >= '2014-04-01' 
                         AND DateUsed < '2014-04-30'          
)

或者我必须确定查询之外的工作天数吗？

Answer 1

不久前，我看到有人用CTE做了一些有趣的事情，解决了一个类似的问题：也许这对你有用？

WITH DateRange(Date) AS
     (
         SELECT '2014-04-01' Date
         UNION ALL
         SELECT DATEADD(day, 1, Date) Date
         FROM DateRange
         WHERE Date < '2014-04-30'
     )
     SELECT CONVERT(VARCHAR(10),Date, 121) -- either SELECT from or JOIN to this table
     FROM DateRange
     WHERE DatePart(dw, Date) NOT IN (1,7) -- this could limit it to Mon-Fri only

编辑：我认为以下将是您将其包含在结果集中的链接：

Select AVG(Qty)
From (
            Select Sum(ItemQty) As Qty
            From 
                DateRange 
                 LEFT JOIN 
                ReceivedInfo ON 
                    DateRange.Date = ReceivedInfo.DateUsed
            Where DateRange.Date >= '2014-04-01' 
                             AND DateRange.Date < '2014-04-30'
            Group By DateRange.Date
)

编辑2：根据下面的注释删除了变量。谢谢你的提醒！

Answer 2

如果您希望丢失的值被视为0，那么您的最终查询应该可以正常工作。然而，你可以简化它。因此，接近

Select Sum(QTy) / (5.0/7 * (julianday('2014-04-30') - julianday('2014-04-01' ) ) )
From ReceivedInfo
Where DateUsed >= '2014-04-01' AND DateUsed < '2014-04-30';  

当然，这只是工作日数的近似值(与您提出的解决方案类似(。一个完整的解决方案将使用繁琐的一周中的算术。