计算上个月(同年)的百分比

Question

如何计算和显示上个月的增长或损失百分比(比较上个月的数字，而不是上一年的数字(。

即 1 月是 500，所以结果将是 0%。然后，由于 2 月是 150，损失百分比为 -70%，3 月将显示 -86.67，因为他们的结果只有 20(与 2 月 150 相比(

+----------+------+------------+
| Month    | Sale | Difference |
+----------+------+------------|
| January  |  500 |            |
| February |  150 |     -70.00 |
| March    |   20 |     -86.67 |
| April    |  250 |    1150.00 |
| May      |  280 |      12.00 |
| June     |  400 |      42.86 |
| July     |  480 |      20.00 |
+----------+------+------------+

我

下面的脚本产生：(我只需要添加另一个百分比列

+----------+-------------------+
| MONTH    | SUM(SALES_AMOUNT) |
+----------+-------------------+
| January  |               500 |
| February |               150 |
| March    |                20 |
| April    |               250 |
| May      |               280 |
| June     |               400 |
| July     |               480 |
+----------+-------------------+

SELECT coalesce(date_format(DATE_PURCHASED, '%M')) AS MONTH,
SUM(SALE_PRICE)
FROM SALE
WHERE YEAR(DATE_PURCHASED) = 2017
GROUP BY month

Answer 1

您可以将查询本身留给月份减去 1 的查询。然后，您将月份和上个月的总和放在一行中，并且可以计算百分比。类似于以下内容：

SELECT monthname(concat('1970-', lpad(A.MONTH, 2, '0'), '-01')) AS MONTH,
       A.SALE_PRICE,
       CASE
         WHEN A.SALE_PRICE IS NULL
           THEN NULL
         WHEN B.SALE_PRICE IS NULL
           THEN NULL
         WHEN A.SALE_PRICE = 0
           THEN NULL
         ELSE
           (B.SALE_PRICE / A.SALE_PRICE - 1) * 100
       END AS PERCENTAGE
       FROM (SELECT month(DATE_PURCHASED) AS MONTH,
                    sum(SALE_PRICE) AS SALE_PRICE,
                    FROM SALE
                    WHERE year(DATE_PURCHASED) = 2017
                    GROUP BY MONTH) A
            LEFT JOIN (SELECT month(DATE_PURCHASED) AS MONTH,
                              sum(SALE_PRICE) AS SALE_PRICE,
                              FROM SALE
                              WHERE year(DATE_PURCHASED) = 2017
                              GROUP BY MONTH) B
                      ON A.MONTH - 1 = B.MONTH
       ORDER BY A.MONTH;

请注意A和B是您的查询，只是修改为月份是数字，因为ON子句中需要这样做。