Oracle Computing Method

　　
The period-over-period ratio is the nth month of the year and the nth month of the year, or the nth month of the year. Create a test table. Assume It is a sales quantity table: create table t (dt date, cnt number). insert some test data as needed. The content of the final table is as follows: dt cnt ---------- 2012-02-09 15 2012-02-21 2 2012-03-23 1 2012-03-23 5 2012-04-23 2 2012-05-12 20 2012-07-01 20 2012-07-21 20 display period-over-period comparison, if there is no sales in a month, it is a bit of trouble. First, find a way to show that there is a very skillful technology to achieve this year's 1 to December: SQL> set pagesize 20 SQL> with m as (select '1970-'| lpad (rownum, 2, '0') v from dual connect by level <= 12) select * from m; V --------- 2012-01 2012-02 2012-03 2012-04 2012-05 2012-06 2012-07 2012-08 2012-09 2012-10 2012-11 2012-12 next easy, use the lag statistical function to connect two tables. With m as (select '2012-'| lpad (rownum, 2, '0') v from dual connect by level <= 12) select m. v "month", nvl (sum (t. cnt), 0) "sales quantity", lag (nvl (sum (t. cnt), 0), 1) over (order by m. v) "sales volume of the previous month", nvl (sum (t. cnt), 0)-lag (nvl (sum (t. cnt), 0), 1) over (order by m. v) "yoy increase", round (case when nvl (sum (t. cnt), 0) = 0 then null else (nvl (sum (t. cnt), 0)-lag (nvl (sum (t. cnt), 0), 1) over (order by m. v)/nvl (sum (t. cnt), 0) end) *, 1) "increase Ratio (%)" from m left outer join t on m. v = to_char (t. dt, 'yyyy-mm') group by m. v order by sales volume in January the sales volume in the previous month increased by a month-on-month increase (%) --------- ---------- ------------ ---------- --------------- 2012-01 0 2012-02 17 0 17 100 2012-03 6 17-11-183.3 2012-04 2 6-4-200 2012-05 20 2 18 90 2012-06 0 20-20 2012-07 40 0 40 100-08 0 40-40 2012-09 0 0 0 2012-10 0 0 0 2012-11 0 0 0 0 0 2012-12 0 0 0 author wallimn