Oracle for the year, the chain function (lag and lead) of the detailed _oracle

The lag and lead functions can fetch the first n rows of data from the same field and the values of the latter n rows in a single query. This can be done using table joins to the same table, but with lag and leads is more efficient.

Copy Code code as follows:

CREATE TABLE Salarybymonth
(
Employeeno varchar2 (20),
Yearmonth VARCHAR2 (6),
Salary number
) ;
Insert into Salarybymonth (Employeeno, Yearmonth, SALARY)
VALUES (1, ' 200805 ', 500);
Insert into Salarybymonth (Employeeno, Yearmonth, SALARY)
VALUES (1, ' 200802 ', 150);
Insert into Salarybymonth (Employeeno, Yearmonth, SALARY)
VALUES (1, ' 200803 ', 200);
Insert into Salarybymonth (Employeeno, Yearmonth, SALARY)
VALUES (1, ' 200804 ', 300);
Insert into Salarybymonth (Employeeno, Yearmonth, SALARY)
VALUES (1, ' 200708 ', 100);
Commit

SELECT Employeeno
, Yearmonth
, SALARY
, MIN (SALARY) KEEP (Dense_rank A/Yearmonth) over (PARTITION by Employeeno) first_salary--Gibe analysis Salary/first_sal ary
, LAG (SALARY, 1, 0) over (PARTITION by Employeeno Order by Yearmonth) as Prev_sal --chain analysis, compared with last month
, LAG (SALARY, 0) over (PARTITION by Employeeno Order by Yearmonth) as Prev_12_sal --year-on-year analysis, compared with the same month of the previous year
, SUM (SALARY) over (PARTITION by Employeeno, SUBSTR (Yearmonth, 1, 4) Order by Yearmonth RANGE unbounded preceding) LJ--Cumulative value
From Salarybymonth
ORDER BY Employeeno
, Yearmonth