LAG( )
LAG is an analytic function which provides access to a row following Nth position of current row within window of ordered set of rows.
CREATE TABLE employee_data
(
eid NUMBER(4),
ename VARCHAR2(30),
depno NUMBER(3),
salary NUMBER(8)
);
BEGIN
INSERT INTO employee_data VALUES(1000,'Name01',90,30000);
INSERT INTO employee_data VALUES(1001,'Name02',90,6000);
INSERT INTO employee_data VALUES(1002,'Name03',90,23000);
INSERT INTO employee_data VALUES(1003,'Name04',60,35000);
INSERT INTO employee_data VALUES(1004,'Name05',60,60000);
INSERT INTO employee_data VALUES(1005,'Name06',60,30000);
INSERT INTO employee_data VALUES(1006,'Name07',80,36000);
INSERT INTO employee_data VALUES(1007,'Name08',80,29000);
INSERT INTO employee_data VALUES(1008,'Name09',80,37000);
INSERT INTO employee_data VALUES(1009,'Name10',80,41000);
COMMIT;
END;
/
SELECT * FROM employee_data;
SYNTAX : LAG( arg1,arg2,arg3)
arg1 = column name
arg2 = offset value
arg2 = Default value
SELECT ed.eid,
ed.ename,
ed.depno,
ed.salary,
LAG(ed.salary,1,0) OVER (PARTITION BY ed.depno ORDER BY ed.salary) AS "LAG"
FROM employee_data ed;
Output
----------------------------------------------
Eid Ename Depno salary LAG
----------------------------------------------
1005 Name06 60 30000 0
1003 Name04 60 35000 30000
1004 Name05 60 60000 35000
1007 Name08 80 29000 0
1006 Name07 80 36000 29000
1008 Name09 80 37000 36000
1009 Name10 80 41000 37000
1001 Name02 90 6000 0
1002 Name03 90 23000 6000
1000 Name01 90 30000 23000
----------------------------------------------
SELECT ed.eid,
ed.ename,
ed.depno,
ed.salary,
LAG(ed.salary,2,0) OVER (PARTITION BY ed.depno ORDER BY ed.salary) AS "LAG"
FROM employee_data ed;
Output
----------------------------------------------
Eid Ename Depno salary LAG
----------------------------------------------
1005 Name06 60 30000 0
1003 Name04 60 35000 0
1004 Name05 60 60000 30000
1007 Name08 80 29000 0
1006 Name07 80 36000 0
1008 Name09 80 37000 29000
1009 Name10 80 41000 36000
1001 Name02 90 6000 0
1002 Name03 90 23000 0
1000 Name01 90 30000 6000
----------------------------------------------
LEAD( )
LEAD is same as LAG analytic function but it provides access to a row prior to Nth position of current row within window of ordered set of rows.
SELECT ed.eid,
ed.ename,
ed.depno,
ed.salary,
LEAD(ed.salary,1,0) OVER (PARTITION BY ed.depno ORDER BY ed.salary) AS "LEAD"
FROM employee_data ed;
Output
----------------------------------------------
Eid Ename Depno salary LEAD
----------------------------------------------
1005 Name06 60 30000 35000
1003 Name04 60 35000 60000
1004 Name05 60 60000 0
1007 Name08 80 29000 36000
1006 Name07 80 36000 37000
1008 Name09 80 37000 41000
1009 Name10 80 41000 0
1001 Name02 90 6000 23000
1002 Name03 90 23000 30000
1000 Name01 90 30000 0
----------------------------------------------
FIRST_VALUE( ) and LAST_VALUE( )
FIRST_VALUE( )
The FIRST_VALUE returns the first result of an ordered set from a window.
SELECT ed.eid,
ed.ename,
ed.depno,
ed.salary,
FIRST_VALUE(ed.salary) OVER (PARTITION BY ed.depno ORDER BY ed.salary) AS "FIRST_VALUE"
FROM employee_data ed;
Output
----------------------------------------------
Eid Ename Depno salary FIRST_VALUE
----------------------------------------------
1005 Name06 60 30000 30000
1003 Name04 60 35000 30000
1004 Name05 60 60000 30000
1007 Name08 80 29000 29000
1006 Name07 80 36000 29000
1008 Name09 80 37000 29000
1009 Name10 80 41000 29000
1001 Name02 90 6000 6000
1002 Name03 90 23000 6000
1000 Name01 90 30000 6000
----------------------------------------------
LAST_VALUE( )
The LAST_VALUE returns the last result of an ordered set from a window
SELECT ed.eid,
ed.ename,
ed.depno,
ed.salary,
LAST_VALUE(ed.salary) OVER (PARTITION BY ed.depno ORDER BY ed.salary) AS "LAST_VALUE"
FROM employee_data ed;
Output
----------------------------------------------
Eid Ename Depno salary LAST_VALUE
----------------------------------------------
1005 Name06 60 30000 60000
1003 Name04 60 35000 60000
1004 Name05 60 60000 60000
1007 Name08 80 29000 41000
1006 Name07 80 36000 41000
1008 Name09 80 37000 41000
1009 Name10 80 41000 41000
1001 Name02 90 6000 30000
1002 Name03 90 23000 30000
1000 Name01 90 30000 30000
----------------------------------------------
DROP TABLE employee_data;
Example:
--Transpose data in rows in to column
create table test_insert (data varchar2(30));
create table ins_data(col1 varchar2(30), col2 varchar2(30), col3 varchar2(30));
BEGIN
insert into test_insert values('Revision 1');
insert into test_insert values('txt1');
insert into test_insert values('col1');
insert into test_insert values('txt2');
insert into test_insert values('col3');
insert into test_insert values('col4');
insert into test_insert values(NULL);
insert into test_insert values('Revision 1');
insert into test_insert values('txt1');
insert into test_insert values('col1');
insert into test_insert values('txt2');
insert into test_insert values('col3');
insert into test_insert values('col4');
Commit;
END;
/
SELECT * FROM ins_data;
DECLARE
v_data VARCHAR2(30);
v_required1 VARCHAR2(30);
v_required2 VARCHAR2(30);
v_required3 VARCHAR2(30);
v_required4 VARCHAR2(30);
v_required5 VARCHAR2(30);
CURSOR all_data
IS
SELECT *
FROM (SELECT data,
lead(data,
1,
6) over(ORDER BY 1) AS required1,
lead(data,
2,
6) over(ORDER BY 1) AS required2,
lead(data,
3,
6) over(ORDER BY 1) AS required3,
lead(data,
4,
6) over(ORDER BY 1) AS required4,
lead(data,
5,
6) over(ORDER BY 1) AS required5
FROM test_insert)
WHERE data LIKE 'Revision%';
BEGIN
OPEN all_data;
LOOP
FETCH all_data
INTO v_data,v_required1, v_required2, v_required3, v_required4, v_required5;
EXIT WHEN all_data%NOTFOUND;
INSERT INTO ins_data
VALUES
(v_required1,
v_required3,
v_required5);
END LOOP;
CLOSE all_data;
Commit;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
SELECT * FROM ins_data;
DROP TABLE ins_data;
DROP TABLE test_insert;
No comments:
Post a Comment