sql计算增长率

港控/mmm° 2021-09-24 15:02 509阅读 0赞

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_stackoverrun.com_cn_q_9333493]

[https://blog.csdn.net/johnf\_nash/article/details/78681060][https_blog.csdn.net_johnf_nash_article_details_78681060]

[https_stackoverrun.com_cn_q_9333493]: https://stackoverrun.com/cn/q/9333493
[https_blog.csdn.net_johnf_nash_article_details_78681060]: https://blog.csdn.net/johnf_nash/article/details/78681060