逐月计算增长率

Question

我有一个零售商店的基本数据库(4个表)，并试图运行一个查询，该查询将逐月计算商店收入的百分比增长(或下降)。ORDERS表保存每个月的OrderPrice信息。

我很难找到一个能做到这一点的查询，有人知道是否存在这样的查询吗？

数据库在这里：https://www.db-fiddle.com/f/qS515ShyFGxgj4h6Ga6HA3/0#&togetherjs=009rG6HwUe

到目前为止，我已经想出了SUM的收入总额，一个月一个月：

SELECT * FROM (
SELECT Coalesce(MONTHNAME(ORDERS.OrderDate), 'Grand Total') AS OrderDate, 
SUM(ORDERS.OrderPrice) as SumOrderPrice  
FROM ORDERS
WHERE OrderDate BETWEEN '2018-01-01' AND '2018-12-31'
GROUP BY MONTH(ORDERS.OrderDate) with ROLLUP)
AS T;

Answer 1

好吧，你已经很接近了：

我修改了你的陈述，想出了以下几点。我做了一个值得注意的假设--您希望输出按月份编号排序，而汇总值则是最后一个。如果您最终需要显示一份从一年的十月到次年九月的列表，您将需要投入更多的工作。

SELECT Coalesce(OrderDate, 'Grand Total') AS OrderDate
      ,SumOrderPrice
FROM (
      SELECT MONTHNAME(ORDERS.OrderDate) as OrderDate
            ,SUM(ORDERS.OrderPrice) as SumOrderPrice
            ,MONTH(ORDERS.OrderDate) as MonthNum
      FROM ORDERS
      WHERE OrderDate BETWEEN '2018-01-01' AND '2018-12-31'
      GROUP BY
            MONTHNAME(ORDERS.OrderDate)
           ,MONTH(ORDERS.OrderDate) with ROLLUP
     ) AS T
ORDER BY COALESCE(MonthNum,13);

下面是修改后的链接，包括我的查询版本。

我改变了什么，为什么：

• 首先，DB顶部的USE GUITARWORLD使设置代码无法运行。在未来，请确保您在DB中发布的内容确实成功运行--仅仅是失败是一个糟糕的开始:-)。
• 您的GROUP BY没有按SELECT列表中的值进行分组。这并不总是一个错误，但这意味着您的COALESCE(MONTHNAME())没有工作。我仅将GROUP BY更改为GROUP BY MONTHNAME()结果，并将COALESCE移动到外部查询，以确保来自该查询的结果不会导致分组问题。因为我没有使用SELECT *，所以我还需要显式地返回SumOrderPrice。

最初，结果列出了四月份，然后是三月，然后是大总计。我相当肯定您希望看到按数字顺序排列的月份，而不是字母(或不确定的)顺序，因此我在内部查询中添加了MONTH(Orders.OrderDate)作为新列；将其添加到GROUP BY中(结果不变，因为给定的月份号始终具有匹配的给定名称)，并按MonthNum (添加COALESCE以强制大道达尔最后显示)对结果进行排序。

在下一步中，我们将使用一个变量：

SET @Last_Month_SumOrderPrice := 0;

SELECT OrderDate, SumOrderPrice, Monthly_Pct_Change
FROM (
      SELECT OrderDate
            ,SumOrderPrice
            ,CASE WHEN @Last_Month_SumOrderPrice = 0
               THEN NULL
               ELSE (SumOrderPrice - @Last_Month_SumOrderPrice) * 100.00 / @Last_Month_SumOrderPrice
             END as Monthly_Pct_Change
            ,@Last_Month_SumOrderPrice := SumOrderPrice
            ,MonthNum
      FROM (
            SELECT MONTHNAME(ORDERS.OrderDate) as OrderDate, 
                   SUM(ORDERS.OrderPrice) as SumOrderPrice,
                   MONTH(ORDERS.OrderDate) as MonthNum
            FROM ORDERS
            WHERE OrderDate BETWEEN '2018-01-01' AND '2018-12-31'
            GROUP BY MONTHNAME(ORDERS.OrderDate),
                     MONTH(ORDERS.OrderDate)
           ) AS T
      ORDER BY MonthNum) as SQ
ORDER BY MonthNum;

我们使用您已经拥有的基本查询，并在遍历数据时添加一个变量@Last_Month_SumOrderPrice来跟踪上个月的SumOrderPrice。在处理SELECT列表数据时，变量将保留最后一行的值。

这使我们可以访问当前和前几个月的SumOrderPrice值。从那里计算百分比是微不足道的。我将值设置为NULL，以避免除以零错误。

我们将另一个查询放在现有查询之外；否则，分配给变量的值将在每一行中输出。

请注意，我已经在ORDER BY子查询和外部查询中都包含了SQ子查询，这两个查询都在MonthNum上。子查询中的一个保证按正确的顺序处理行；在外部查询中的一个保证以正确的顺序显示最终结果。在没有外部ORDER BY的情况下，可能会正确地排序最终结果；但是，只有在最后结果上指定ORDER BY时，才能保证规则为order。

在这种情况下，总金额将是误导/不相关的，我已经放弃了。如果您有更多的数据，并且希望看到比一年多的百分比变化，我会考虑单独的查询。

这是最后一个链接，显示了正在运行的这两个查询。