Use the Oracle analysis function LAG to calculate the current period, year-on-year comparison, and period-over-period comparison values in a time period.

Withtime_dimas (selectadd_months (add_months (date2013-7-1,-12), (rownum-1) dim_monthfromdualconnectbylevelmonths_between (date2014-1-1, date2012-7-1) + 1) selectt. dim_month, t. c_count, nvl (t. ly_count, 0) ly_count, nvl (t. lm_count,

With time_dim as (select add_months (date '2017-7-1 ',-12), (rownum-1) dim_month from dual connect by level = months_between (date '2017-1-1 ', date '2014-7-1 ') + 1) select t. dim_month, t. c_count, nvl (t. ly_count, 0) ly_count, nvl (t. lm_count,

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;

The Oracle analysis function LAG is a function that is used to calculate the previous value in the result set as a new column and is highly efficient. This analysis function can be used to calculate the second-to-second ratio and the same-to-year ratio, but there are also some problems. The syntax structure of the LAG analysis function is as follows:

LAG (EXPRESSION, [OFFSET], [DEFAULT]) OVER ([patition by COLUMN1....] order by COLUMN1 ...);

Here, offset refers to the OFFSET of the forward nth value in the result set.

Problem:

1. If some months in the base table are missing, the missing month will be missed and the previous value will be directly searched.

2. If a time filter is added, the first value is worth the period-over-period comparison, which is not found in the plenary session of the same ratio of all result sets.

Solution:

Initialize a time dimension and associate it with the base table to complete all the months. The missing month value is zero. Take sales as an example. The SQL statement is as follows: