sql计算增长率

1、现有数据

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

该sql为

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

2、期望结果

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

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

3、最终sql

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

  或

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

注：https://stackoverrun.com/cn/q/9333493

       https://blog.csdn.net/johnf_nash/article/details/78681060