mysql - 计算上年与本年的增长百分比

Question

我想计算上一年的增长百分比。

表结构:

CustomerName | Sum_1415 | Sum_1516 

我尝试过这个查询:

select CustomerName, SUM(Sum_1415), SUM(Sum_1516), 
    round(sum(Sum_1516)-Sum(Sum_1415)/(select sum(Sum_1415) from dummy),2) as Percentage 
from dummy 
group by CustomerName 
order by Sum_1415 desc 
limit 15

Answer 1

将子查询(select sum(Sum_1415) from dummy)替换为sum(Sum_1415)。否则，您计算的是每个客户的增长与上一年所有收入总额的比率，而不是该客户的总收入。

您还在 ROUND 调用中缺少一些括号，围绕减法 sum(Sum_1516)-Sum(Sum_1415)。

select CustomerName, SUM(Sum_1415) AS Sum_1415, SUM(Sum_1516) AS Sum_1516, 
    round((sum(Sum_1516)-Sum(Sum_1415))/SUM(Sum_1415),2) as Percentage 
from dummy 
group by CustomerName 
order by Sum_1415 desc 
limit 15

我可能会将其重构为子查询，这样就没有必要不断重复 SUM 表达式。

SELECT CustomerName, Sum_1415, Sum_1516, ROUND((Sum_1516-Sum_1415)/Sum_1415, 2) AS Percentage
FROM (SELECT CustomerName, SUM(Sum_1415) AS Sum_1415, SUM(Sum_1516) AS Sum_1516
      FROM dummy
      GROUP BY CustomerName
      ORDER BY Sum_1516 DESC
      LIMIT 15) AS x