前言
业务场景:原始表以天存储数据,以月维度统计数据及其对应的环比数据
历史时间环比:(上月数据-上上月数据)/上上月数据
当前时间环比:(当前时间数据-上月同期数据)/上月同期数据
上月同期概念:如今天是2023年3月26日,2023年3月的统计时间为3月1日-3月25日,故该时间区间的上月同期数据应为2023年2月1日-2月25日,而非整个2月
原始数据
表tmp存储每日数据,字段t_date为日期,t_value为测试值(可以是购买人数、访问量等任何有现实意义的数据),样例数据如图所示:
方法一
关键词:①一张表实现 ②add_months() ③date_add()
Step1:以月维度统计当月每天数据
难点:由上述上月同期概念可知,二月,作为当前月(三月)的环比月,不仅要保留整月数据作为二月当月的值,也要选取部分数据作为三月同期环比的值,靠字段t_month无法区分两种二月数据的差别,所以在group by时保留t_date
知识点:函数add_months(date,int),其中第一个参数为日期,第二个为按月增加的幅度
上月和同期两个条件通过add_months()实现,整体代码如下:
-- 每月数据
tmp2 as
(
-- 历月数据
select
substr(t_date,1,7) as t_month,
max(t_date) as t_date,
sum(t_value) as t_value
from tmp
group by substr(t_date,1,7)
union all
-- 上月同期数据
select
substr(t_date,1,7) as t_month,
max(t_date) as t_date,
sum(t_value) as t_value
from tmp
where substr(t_date,1,7)=substr(add_months(current_date(),-1),1,7) -- 上月
and t_date<=add_months(date_add(current_date(),-1),-1) -- 同期
group by substr(t_date,1,7)
)
汇总得到每月数据如图所示:
Step2:匹配环比数据,计算环比值。注意剔除上月同期数据
-- 方法一
select
t_month,
t_value,
hb_t_month,
hb_t_value,
case
when hb_t_value is null then null
else round((COALESCE(t_value,0)-COALESCE(hb_t_value,0))/COALESCE(hb_t_value,0),2)
end as hb
from
(select
t1.t_month as t_month,
t1.t_date as t_date,
t1.t_value as t_value,
t2.t_month as hb_t_month, -- 环比月
t2.t_date as hb_t_date, -- 环比日
t2.t_value as hb_t_value -- 环比值
from
tmp2 t1
left join
tmp2 t2
on add_months(t1.t_date,-1)=t2.t_date
) t
where t_date=add_months(date_add(concat(t_month,'-01'),-1),1) -- 剔除上月同期数据
or t_date=date_add(current_date(),-1) -- 保留当前月不被剔除
order by t_month desc
limit 10
得到结果如图所示:
注意点:假设current_date()为2023-03-31,当前月统计时间范围3月1日-3月30日,上月同期应为2月1日-2 月28日。
注意Step1,同期用了t_date<=add_months(date_add(current_date(),-1),-1),而不是t_date<add_months(current_date(),-1)。两者的区别如表所示:
语法 | 平常日current_date() 如2023-03-26 | 月末日current_date() 如2023-03-31 | 结论 |
t_date<= add_months (date_add(current_date(),-1),-1) | t_date<=’2023-02-25’ | t_date<=’2023-02-28’ | 平常日和月末日都能满足 |
t_date< add_months(current_date(),-1) | t_date<’2023-02-25’ | t_date<’2023-02-28’ | 平常日能满足,月末日无法满足 |
注意Step2,使用了t1.t_date=add_months(t2.t_date,1),而不是add_months(t1.t_date,-1)=t2.t_date。两者的区别如表所示:
语法 | 平常日 | 月末日 | 结论 |
t1.t_date=add_months(t2.t_date,1) | t2.t_date为2023-02-26,t1.t_date=add_months(‘2023-02-26’,1)=’2023-03-26’ | t2.t_date为2023-02-28,能匹配上2023-03-31,但是2023-03-30的数据将无法匹配 | 平常日能满足,月末日会丢失数据 |
add_months(t1.t_date,-1)=t2.t_date | t1.t_date为2023-03-26,t2.t_date=add_months(‘2023-03-26’,-1)=’2023-02-26’ | 无论t1.t_date为2023-03-31还是2023-03-30,都可以匹配上2023-02-28的数据 | 平常日和月末日都能满足 |
方法二
关键词:①同一张表② lead() over()
Step1 :同方法一的Step1
Step2 :知识点:开窗函数lead() over()。lead(expr,N,default)
expr:它可以是列或任何内置函数
N:正值,当前行向下偏移的行数,可忽略,默认值为1
default:函数返回的默认值,默认为NULL。当前行偏移N行后没有数据时显示。
-- 方法二
select
t_month,
t_value,
hb_t_value,
case
when hb_t_value is null then null
else round((COALESCE(t_value,0)-COALESCE(hb_t_value,0))/COALESCE(hb_t_value,0),2)
end as hb
from
(select
t1.t_month,
t1.t_date,
t1.t_value,
lead(t_value) over (order by t_month desc,t_date asc) as hb_t_value
from tmp2 t1
) t2
where t_date=add_months(date_add(concat(t_month,'-01'),-1),1) -- 剔除上月同期数据
or t_date=date_add(current_date(),-1) -- 保留当前月不被剔除
order by t_month desc
limit 10
方法三
关键词:①两张表
Step1:建一张历月数据表和一张环比月数据表,匹配环比数据,计算环比值。有别于方法一,这里可以直接用月进行匹配
-- 方法三
select
t.t_month,
t.t_value,
t_hb.t_month as hb_t_month,
t_hb.t_value as hb_t_value,
case
when t_hb.t_value is null then null
else round((COALESCE(t.t_value,0)-COALESCE(t_hb.t_value,0))/COALESCE(t_hb.t_value,0),2)
end as hb
from
-- 历月数据
(select
substr(t_date,1,7) as t_month,
max(t_date) as t_date,
sum(t_value) as t_value
from tmp
group by substr(t_date,1,7)
) t
left join
-- 环比月数据
(select
substr(t_date,1,7) as t_month,
max(t_date) as t_date,
sum(t_value) as t_value
from tmp
where t_date<=add_months(date_add(current_date(),-1),-1)
group by substr(t_date,1,7)
) t_hb
on t.t_month=substr(ADD_MONTHS(concat(t_hb.t_month,'-01'),1),1,7)
order by t.t_month desc
limit 10
求同比亦然,把add_months()中的1或-1改成12或-12即可