sql 查询 环比
and同比
环比和同比的计算公式
环比=(本期数-上期数)/上期数×100%
同比=(本期数-同期数)/同期数×100%
step1:获取每月的销售额
select month(sales_date) as 月份,
sum(unit_price*sales_quantity) as 销售额
from tb_sale1
group by month(sales_date);
step2:获取每月的销售额和上月的销售额
将获取到 每月的销售额 作为临时表,这个地方用到了一个窗口函数lag()
lag(对象,N) over ()...
-- 往回拿对象的N个
select 月份,
销售额 as 本月销售额,
lag(销售额,1) over (order by 月份 asc) as 上月销售额
--
from (select month(sales_date) as 月份,
sum(unit_price*sales_quantity) as 销售额
from tb_sale1
group by month(sales_date) ) as tmp;
step3:计算环比
-- 计算环比
select 月份,
本月销售额,
上月销售额,
(本月销售额-上月销售额)/上月销售额 as 环比
from (select 月份,
销售额 as 本月销售额,
lag(销售额,1) over (order by 月份 asc) as 上月销售额
from (select month(sales_date) as 月份,
sum(unit_price*sales_quantity) as 销售额
from tb_sale1
group by month(sales_date)) as tmp) as tmp;