sql - 每月值的总和除以每月的天数

Question

我有一个销售表:

id_item, quantity, date_time

我需要对一个月内售出的商品求和，然后将它们除以所选月份期间的月份天数。

示例 - 用户选择 10 月 1 日到 12 月 31 日的日期。我需要显示 items_sold/days_of_month:

Month  Items sold  Days of month  Items/Day
Sep        25           30           0.83333
Oct        36           31           1.16
Dec        15           31           0.4838

我必须按元素种类指定。该种类是从另一个名为 Items 的表中获得的。我使用日期格式 dd/mm/yy。

select
   month(date_time),
   sum(quantity) / (select(datepart(dd,getdate())))
from
   sales v
   join items a on v.id_item=a.id_item
where
   a.kind='Kind of Item'
   and cast(Convert(varchar(10), date_time, 112) as datetime)
      between '01/10/2012' and '31/12/2012'
group by
   month(date_time)

我的问题是选择月份的天数，如何选择 x 个月份并将每个月的总和(数量)除以每个月的天数？

我知道这部分代码只选择当月的几天:

(select(datepart(dd,getdate())))

Answer 1

试穿这个尺寸:

DECLARE
   @FromDate datetime,
   @ToDate date; -- inclusive

SET @FromDate = DateAdd(month, DateDiff(month, 0, '20121118'), 0);
SET @ToDate = DateAdd(month, DateDiff(month, 0, '20121220') + 1, 0);

SELECT
    Year = Year(S.date_time),
    Month = Month(S.date_time),
    QtyPerDay =
       Sum(s.quantity) * 1.0
       / DateDiff(day, M.MonthStart, DateAdd(month, 1, M.MonthStart))
FROM
    dbo.Sales S
    INNER JOIN dbo.Items I
       ON S.id_item = I.id_item
    CROSS APPLY (
       SELECT MonthStart = DateAdd(month, DateDiff(month, 0, S.date_time), 0)
    ) M
WHERE
    I.kind = 'Kind of Item'
    AND S.date_time >= @FromDate
    AND S.date_time < @ToDate
GROUP BY
    Year(S.date_time),
    Month(S.date_time),
    M.MonthStart

它将选择任何被 FromDate 部分包围的完整月份和 ToDate . * 1.0如果 quantity 部分是必需的column 是一个整数，否则你会得到一个整数结果而不是一个小数。

一些风格注释:

• 不要在列上使用字符串日期转换以确保获得整日。这将完全阻止使用任何索引，需要更多的CPU，而且不清楚(样式112又做了什么!？!？)。要包含完整的日期期间，请使用我在 DateCol >= StartDate 查询中显示的内容和 DateCol < OneMoreThanEndDate .搜索“sargable”以了解这里的一个非常关键的概念。一条非常安全且有值(value)的一般规则是，如果可以重写条件以避免出现这种情况，则永远不要将列放在表达式中。

• 为表设置别名很好，但您应该在整个查询过程中为每一列使用这些别名，就像我在查询中所做的那样。我认识到别名 V 和 A 来自另一种语言，因此它们在那里有意义——只是通常尝试使用与表名匹配的别名。

• 请务必在您的对象中包含架构名称。不这样做并不是一个很大的禁忌，但确实有好处，而且这是最佳做法。

• 当您提出问题时，解释所有逻辑很有帮助，这样人们就不必猜测或询问您——如果您知道(例如)用户可以输入月中日期，但您需要whole months 然后请在你的问题中指出并说明需要做什么。

• 提供 SQL Server 的版本有助于我们将所需的语法归零，因为以前的版本表达能力较差。通过告诉我们版本，我们可以为您提供最佳查询。

注意:将日期计算数学放在查询本身(而不是使用 SET 来做)中没有错。但我认为您会在存储过程中对此进行编码，如果是这样，使用 SET 就可以了。