mysql实现同比环比的计算

最新推荐文章于 2025-08-20 20:55:16 发布

转载最新推荐文章于 2025-08-20 20:55:16 发布 · 3.2k 阅读

mysql 专栏收录该内容

12 篇文章

订阅专栏

本文详细介绍了如何使用SQL查询来计算同比和环比增长率，通过具体的SQL语句展示了如何对比当前月与上一年同期及上一月的数据变化，适用于数据分析场景。

同比：

select 
    t5.m,FORMAT(((t5.newhan-t5.oldhan)/t5.oldhan)*100,2) 
from 
(
select t3.month as m,t3.avg_han as newhan,t2.avg_han1 as oldhan
from
(select date_format(time, '%Y-%m') as month,avg(han) as avg_han 
from hanni group by month) t3
left join
(
select date_add(t1.month,INTERVAL 1 YEAR) as last_month,t1.avg_han as avg_han1
from
(select date_format(time, '%Y-%m') as month,avg(han) as avg_han 
 from hanni group by month) t1
) t2
on t3.month=t2.last_month
) t5

环比：

select 
    t5.m,FORMAT(((t5.newhan-t5.oldhan)/t5.oldhan)*100,2) 
from 
(
select t3.month as m,t3.avg_han as newhan,t2.avg_han1 as oldhan
from
(select date_format(time, '%Y-%m') as month,avg(han) as avg_han 
from hanni group by month) t3
left join
(
select date_add(t1.month,INTERVAL 1 MONTH) as last_month,t1.avg_han as avg_han1
from
(select date_format(time, '%Y-%m') as month,avg(han) as avg_han 
 from hanni group by month) t1
) t2
on t3.month=t2.last_month
) t5;