1、同比和环比
同比:同去年的今天进行比较
环比:同上个月的今天进行比较
2、add_months函数介绍
-1 代表减一个月;
+1 代表加一个月;
select add_months('2020-09-04',-1);
2020-08-04
select add_months('2020-03-31',-1);
2020-02-29
3、环比实战
select d.spotid,d.times,d.totalnumber,d.last_month_day,d.last_month_day_num,
concat(nvl(round((d.totalnumber - d.last_month_day_num) / d.last_month_day_num * 100,2),0),'%') as ratio
from
(
select a.spotid,a.times,a.totalnumber,
if(b.times is null,0,b.times) as last_month_day,
if(b.totalnumber is null,0,b.totalnumber) as last_month_day_num from
(select spotid,times,totalnumber from bas_cgj_ssrlsj1 where date_id = '20200904') a
left join
(select spotid,times,totalnumber from bas_cgj_ssrlsj1 where date_id = '20200804') b
on a.spotid = b.spotid
and day(b.times) = day(a.times)
and month(b.times) = month(add_months(a.times,-1))
and year(b.times) = year(add_months(a.times,-1)))d;
这部分是以左表a 作为主表,为本月数据,b为上个月的数据
上月数据与本月数据关联不上 填充为0
关联条件为 day = day, month = month, year = year 再用left 连接,关联不上的就被0填充,保证数据关联的准确性;
4、同比
同比和环比一样的,就需要把add_months -1 改为 减 12个月就可以了
参考:https://blog.csdn.net/weixin_42279393/article/details/108405912