Oracle Computing period-over-period comparison example

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, which is assumed to be a sales quantity table:

Create table t (dt date, cnt number );

Insert some test data. The content of the last 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-

Display period-over-period comparison. If no sales are available in a month, it will be a little troublesome.
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 '2012-'| 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, it's easy to 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 last month ",
Nvl (sum (t. cnt), 0)-lag (nvl (sum (t. cnt), 0), 1) over (order by m. v) "period-over-period growth ",
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 1

Monthly sales volume the sales volume of the previous month increased by a month-on-month Ratio (%)
--------------------------------------------------------
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
2012-08 0 40-40
2012-09 0 0 0
2012-10 0 0 0
2012-11 0 0 0
2012-12 0 0 0

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12