The Lag and Lead functions can retrieve the data of the first N rows and the value of the last N rows of the same field in a query. This operation can be achieved through table join for the same table, but LAG and LEAD are more efficient.
Copy codeThe Code is as follows: create table salaryByMonth
(
EmployeeNo varchar2 (20 ),
YearMonth varchar2 (6 ),
Salary number
);
Insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY)
Values (1, '20140901', 200805 );
Insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY)
Values (1, '20140901', 200802 );
Insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY)
Values (1, '20140901', 200803 );
Insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY)
Values (1, '20140901', 200804 );
Insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY)
Values (1, '20140901', 200708 );
Commit;
SELECT EMPLOYEENO
, YEARMONTH
, SALARY
, MIN (SALARY) KEEP (DENSE_RANK first order by yearmonth) OVER (partition by employeeno) FIRST_SALARY -- base ratio analysis salary/first_salary
, LAG (SALARY, 1, 0) OVER (partition by employeeno order by yearmonth) AS PREV_SAL-- Period-over-period comparison with last month
, LAG (SALARY, 12, 0) OVER (partition by employeeno order by yearmonth) AS PREV_12_SAL-- Compare the year-on-year analysis 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