环比函数

Lag和Lead函数能够在三次询问中抽取同一字段的前N行的多寡和后N行的值。这种操作可以运用对相通表的表连接来落成,可是使用LAG和LEAD有更加高的频率。复制代码 代码如下:CREATE TABLE
salaryByMonth(employeeNo varchar2(20卡塔尔(قطر‎,yearMonth varchar2(6卡塔尔国,salary
number卡塔尔 ;insert into SALACR-VYBYMONTH (EMPLOYEENO, YEARMONTH, SALATiggoY卡塔尔values
(1, ‘201005’, 500卡塔尔(قطر‎;insert into SALAQX56YBYMONTH (EMPLOYEENO, YEARMONTH,
SALATucsonYState of Qatarvalues (1, ‘201002’, 150卡塔尔;insert into SALAOdysseyYBYMONTH (EMPLOYEENO,
YEARMONTH, SALA牧马人Y卡塔尔国values (1, ‘200903’, 200State of Qatar;insert into SALACRUISERYBYMONTH
(EMPLOYEENO, YEARMONTH, SALAGL450Y卡塔尔国values (1, ‘二〇一〇04’, 300卡塔尔(قطر‎;insert into
SALAXC60YBYMONTH (EMPLOYEENO, YEARMONTH, SALA奥迪Q5YState of Qatarvalues (1, ‘二零零五08’,
100State of Qatar;commit;

金沙js8331,SELECT EMPLOYEENO ,YEARMONTH ,SALARY ,MIN(SALARY) KEEP(DENSE_RANK FIRST
ORDER BY YEARMONTH) OVER(PARTITION BY EMPLOYEENO) FIRST_SALACRUISERY —
基比深入分析 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 SALARYBYMONTHORDER BY EMPLOYEENO ,YEARMONTH

发表评论

电子邮件地址不会被公开。 必填项已用*标注