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