sql 环比and同比

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;