计算上个月(同年)的百分比 [英] Calculate percentage on previous month (same year)

问题描述

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

How can i calculate and show the percentage of growth or loss, on the previous month (comparing the figure from the previous month, not previous year).

即Jan 是 500，因此结果将为 0%.然后因为2月是150，百分比损失是-70%，3月将显示-86.67，因为他们的结果只有20(与2月150相比)

i.e. Jan is 500, so results will be 0%. Then as Feb is 150, the percentage loss is -70%, March will show -86.67 as their results were only 20 (compared to Febs 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 |
+----------+------+------------+

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

My script below produces: (I just need to add another percentage column

+----------+-------------------+
| 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

推荐答案

您可能会在月数减 1 时将查询加入到自身中.然后您将当月和最后一个月的总和放在一行中，并且可以计算百分比.类似于以下内容:

You might left join the query to itself on the number of the month minus 1. Then you had the sum of the month an the last month in one row and could calculate the percentage. Similar to the following:

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 子句中是必需的.

Note A and B being your query, just modified so that the month is numeric as this is needed in the ON clause.

这篇关于计算上个月(同年)的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！