利用Oracle分析函數LAG求時間段內的本期、同比、環比值

with time_dim as 
(select add_months(add_months(date'2013-7-1',-12),(rownum-1)) dim_month
 from dual connect by level <=months_between(date'2014-1-1',date'2012-7-1')+1)
select t.dim_month,
 t.c_count,
 nvl(t.ly_count,0) ly_count,
 nvl(t.lm_count,0) lm_count
 from
 (select t1.dim_month, 
 nvl(t2.c_count,0) c_count,
 lag(t2.c_count,1,0) over(order by t1.dim_month) ly_count,
 lag(t2.c_count,12,0) over(order by t1.dim_month) lm_count 
 from time_dim t1
 left join test_lzc t2
 on t1.dim_month = t2.dim_month) t
 where t.dim_month between date'2013-7-1' and date'2014-1-1'
order by 1 desc;