SQL 查询 - 销售额的逐年增长 [英] SQL Query - Year by Year Growth of Sales

问题描述

我从 SQL 查询获得了这个结果集.这个结果是通过对租户每年的销售额进行分组以获得年度总销售额而得出的.表名为 TENANTSALES，其中包含以下列:租户、日期、销售额等.

I have this result set from an SQL query. This result has come up by grouping the sales of tenant per year to get the total sales in yearly basis. Table is named TENANTSALES with columns: Tenant, date, sales etc.

TENANT      YEAR    SALES   
tenant 1    2014    2000    
tenant 1    2015    5000       
tenant 2    2013    1000    
tenant 2    2014    1500       
tenant 2    2015    800    

我用这个SQL查询代码实现了上面的结果

I used this SQL query code to achieve the above result

select tenant, year(date), SUM(sales)
from tenantSales
group by tenant, YEAR(date)

我需要完成的任务是添加一个列名 Yearly growth ，它将比较和计算每个租户的逐年销售额增长.这是示例正确/所需的输出

What I need to complete the task is to add a column name Yearly growth where it will compare and compute for year by year growth of sales per tenant. Here's the sample correct / desired output

TENANT      YEAR    SALES    YEARLY GROWTH
tenant 1    2014    2000    
tenant 1    2015    5000       150%
tenant 2    2013    1000    
tenant 2    2014    1500       50%
tenant 2    2015    800       -46.67%

计算公式为:((Latest Year - Previous Year)/Previous Year) * 100

The formula is: ((Latest Year - Previous Year) / Previous Year) * 100

租户 1 的示例:

((2015 年销售额 - 2014 年销售额)/2014 年销售额)* 100 = 150%

((2015 sales - 2014 sales) / 2014 sales) * 100 = 150%

我试过这样做，在上一年的行中添加下一年，以便我轻松计算两年的销售额，但我无法将最近一年的销售额加起来，只能加起来年份本身.有什么方法或适当的方法可以做到吗?

Ive tried to do this, adding the next year in the previous year's row to make it easy for me to compute for the two years sales, but I can't add up the sales of the latest year, only the year itself. Is there any way or proper way to do it?

select tenantcode, year(date), SUM(gsc), year(date) + 1
from dailymod
where tenantcode = 'cmbina13'
group by tenantcode, YEAR(date)

您的专家建议将不胜感激.谢谢

Your expert advice will be highly appreciated. Thanks

推荐答案

试试这个查询:

SELECT t1.tenant, t1.YEAR, t1.SALES,
    CASE WHEN t2.YEAR IS NOT NULL THEN
        FORMAT(
            CONVERT(DECIMAL(10, 2), (t1.SALES - t2.SALES)) /
            CONVERT(DECIMAL(10, 2), t2.SALES), 'p')
    ELSE NULL END AS "YEARLY GROWTH"
FROM
(
    SELECT tenant, YEAR(date) AS YEAR, SUM(sales) AS SALES
    FROM tenantSales
    GROUP BY tenant, YEAR(date)
) t1
LEFT JOIN
(
    SELECT tenant, YEAR(date) AS YEAR, SUM(sales) AS SALES
    FROM tenantSales
    GROUP BY tenant, YEAR(date)
) t2
ON t1.tenant = t2.tenant AND t2.YEAR = t1.YEAR - 1

点击下面的链接查看工作演示:

Click the link below for a working demo:

SQLFiddle

后期更新:

您也可以使用通用表表达式尝试相同的方法.下面是使用这种方法的上述查询的样子:

You could also try the same approach using Common Table Expressions. Here is what the above query would look like using this approach:

WITH cte AS(SELECT tenant, YEAR(date) AS YEAR, SUM(sales) AS SALES
            FROM tenantSales
            GROUP BY tenant, YEAR(date))
SELECT c1.*, CONVERT(varchar,
                 CONVERT(DECIMAL(10,2),
                     CONVERT(DECIMAL(10, 2), (c1.SALES - c2.SALES)) /
                     CONVERT(DECIMAL(10, 2), c2.SALES))) + '%' AS "YEARLY GROWTH"
FROM cte c1
LEFT JOIN cte c2 ON c1.tenant = c2.tenant AND c2.YEAR = c1.YEAR - 1

这是另一个 Fiddle，您可以在其中进行测试:

And here is another Fiddle where you can test this out:

SQLFiddle

这篇关于SQL 查询 - 销售额的逐年增长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！