sql 利用lead 和lag 求 同比和环比

1.源数据如下

        

2. 求同比

首先同比是指 不同年份的相同月份相比较

难点是取到上一年的相同月数据

SELECT
	date_str,
	date_year,
	date_month,
	curr_month_num,
	last_year_num,
	( curr_month_num - last_year_num ) / last_year_num AS year_basis 
FROM
	(
	SELECT
		date_str,
		date_year,
		date_month,
		curr_month_num,
		LEAD ( curr_month_num, 1 ) OVER 
                ( PARTITION BY date_month ORDER BY date_year DESC ) AS last_year_num 
	FROM
		owr_view 
	ORDER BY
		date_month DESC,
	date_year DESC 
	) T

结果如下

        

 解释：

LEAD ( curr_month_num, 1 ) OVER 
                ( PARTITION BY date_month ORDER BY date_year DESC ) AS last_year_num

 以月分组    按年倒序排   lead就是取自己前面第几条   这样就取到了上一年的相同月数据  其余的就是计算了

3.环比

环比是指 相邻月份相比较

难点是取到上一月数据

SELECT
	date_str,
	date_year,
	date_month,
	curr_month_num,
	last_month_num,
	( curr_month_num - last_month_num ) / last_month_num AS mom_ratio 
FROM
	(
	SELECT
		date_str,
		date_year,
		date_month,
		curr_month_num,
		LAG ( curr_month_num, 1 ) OVER
             ( PARTITION BY date_year ORDER BY date_month ASC ) AS last_month_num 
	FROM
		owr_view 
	ORDER BY
	date_str DESC 
	) T

执行结果如下

 解释：

LAG ( curr_month_num, 1 ) OVER
             ( PARTITION BY date_year ORDER BY date_month ASC ) AS last_month_num 

以年分组   月份升序排  lag 自己的前一条数据    

注：如果求不分年份的环比 就不需要 按年分组  但排序的一定要连续   代码如下

LAG ( curr_month_num, 1 ) OVER
             ( ORDER BY date_str ASC ) AS last_month_num