導讀:1建站知識本篇文章是對oracle求同比,環比函數(LAG與LEAD)進行了詳細的分析介紹，需要的朋友參考下網站建設網站建設多少錢。

Lag和Lead函數可以在一次查詢中取出同一字段的前N行的數據和后N行的值。這種操作可以使用對相同表的表連接來實建設網站公司現，不過使用LAG和LEAD有更高的效率。

復制代碼 代碼如下:

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 FIRST ORDER BY YEARMONTH) OVER(PARTITION BY EMPLOYEENO) FIRST_SALARY -- 基比分析 salary/first_salary ,LAG(SALARY, 1, 0) OVER(PARTITION BY EMPLOYEENO ORDER BY YEARMONTH) AS PREV_SAL -- 環比分析，與上個月份進行比較 ,LAG(SALARY, 12, 0) OVER(PARTITION BY EMPLOYEENO ORDER BY YEARMONTH) AS PREV_12_SAL建設網站 -- 同比分析，與上個年度相同月份進行比較 ,SUM(SALARY) OVER(PARTITION BY EMPLOYEENO, SUBSTR(YEARMONTH, 1, 4) ORDER BY YEARMONTH RANGE UNBOUNDED PRECEDING) LJ --累計值 FROM SALARYBYMONTH ORDER BY EMPLOYEENO ,YEARMONTH

關鍵詞標簽: 函數 詳解 oracle求同比