Sql:计算客户在一段时间内订购产品的平均时间

Question

你如何计算一个产品在一周、一个月、一年中平均销售了多少次？

我感兴趣的不是数量，而是客户购买了给定产品的次数。

OrderLine

 OrderNo | ProductNo | Amount |
----------------------------------------
       1 |         1 |     10 |
       1 |         4 |      2 |
       2 |         1 |      2 |
       3 |         1 |      4 |

订单

     OrderNo | OrderDate
  ----------------------------------------
           1 | 2012-02-21
           2 | 2012-02-22
           3 | 2012-02-25

这是我正在寻找的输出

ProductNo | Average Orders a Week | Average Orders a month |
------------------------------------------------------------
        1 |                     3 |                     12 |
        2 |                     5 |                     20 |

Answer 1

这是DRapp答案的一个副本，但是为SQL Server编写的(它太大了，无法进行注释！)

SELECT PreCount.ProductNo,
       PreCount.TotalCount / PreCount.CountOfYrWeeks AS AvgPerWeek,
       PreCount.TotalCount / PreCount.CountOfYrMonths AS AvgPerMonth,
       PreCount.TotalCount / PreCount.CountOfYears AS AvgPerYear
FROM   (SELECT OL.ProductNo,
               Count(*) TotalCount,
               Count(DISTINCT Datepart(wk, O.OrderDate)) AS CountOfYrWeeks,
               Count(DISTINCT Datepart(mm, O.OrderDate)) AS CountOfYrMonths,
               Count(DISTINCT Year(O.OrderDate)) AS CountOfYears
        FROM   OrderLine OL  JOIN [Order] O
                 ON OL.OrderNo = O.OrderNo
        GROUP  BY OL.ProductNo) PreCount

Answer 2

你必须首先按照你想要的平均方法对它进行分组和计数。为了区分第1年和第2年，我会将事务的year()添加到分组限定符中以进行区分。例如2010年1月的销售额与2011年的销售额与2012年的销售额...同样，2010年的第1周，2011和2012年的第1周，而不是将所有3年都算作一周。

如果您使用的是MySQL，则可以执行以下操作

select
      PreCount.ProductNo,
      PreCount.TotalCount / PreCount.CountOfYrWeeks as AvgPerWeek,
      PreCount.TotalCount / PreCount.CountOfYrMonths as AvgPerMonth,
      PreCount.TotalCount / PreCount.CountOfYears as AvgPerYear
   from
      ( select
              OL.ProductNo,
              count(*) TotalCount,
              count( distinct YEARWEEK( O.OrderDate ) ) as CountOfYrWeeks,
              count( distinct Date_Format( O.OrderDate, "%Y%M" )) as CountOfYrMonths,
              count( distinct Year( O.OrderDate )) as CountOfYears
           from
              OrderLine OL
                 JOIN Order O
                    on OL.OrderNo = O.OrderNo
           group by
              OL.ProductNo ) PreCount