Both
LAG
and LEAD
functions have the same usage, as shown below.LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause) LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
value_expression
- Can be a column or a built-in function, except for other analytic functions.offset
- The number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1.default
- The value returned if the offset is outside the scope of the window. The default value is NULL.
Looking at the
EMP
table, we query the data in salary (SAL
) order.
SELECT empno, ename, job, sal FROM emp ORDER BY sal; EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 7369 SMITH CLERK 800 7900 JAMES CLERK 950 7876 ADAMS CLERK 1100 7521 WARD SALESMAN 1250 7654 MARTIN SALESMAN 1250 7934 MILLER CLERK 1300 7844 TURNER SALESMAN 1500 7499 ALLEN SALESMAN 1600 7782 CLARK MANAGER 2450 7698 BLAKE MANAGER 2850 7566 JONES MANAGER 2975 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7839 KING PRESIDENT 5000 SQL>
LAG
The
LAG
function is used to access data from a previous row. The following query returns the salary from the previous row to calculate the difference between the salary of the current row and that of the previous row. Notice that the ORDER BY
of the LAG
function is used to order the data by salary.In LAG(sal, 1, 0), Second parameter '1' represents how many rows back. When there is no row left it will show default value '0' which is third parameter to this function.SELECT empno, ename, job, sal, LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev, sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff FROM emp; EMPNO ENAME JOB SAL SAL_PREV SAL_DIFF ---------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 800 0 800 7900 JAMES CLERK 950 800 150 7876 ADAMS CLERK 1100 950 150 7521 WARD SALESMAN 1250 1100 150 7654 MARTIN SALESMAN 1250 1250 0 7934 MILLER CLERK 1300 1250 50 7844 TURNER SALESMAN 1500 1300 200 7499 ALLEN SALESMAN 1600 1500 100 7782 CLARK MANAGER 2450 1600 850 7698 BLAKE MANAGER 2850 2450 400 7566 JONES MANAGER 2975 2850 125 7788 SCOTT ANALYST 3000 2975 25 7902 FORD ANALYST 3000 3000 0 7839 KING PRESIDENT 5000 3000 2000 SQL>
LEAD
The
LEAD
function is used to return data from the next row. The following query returns the salary from the next row to calulate the difference between the salary of the current row and the following row.
Similar to LAG function, In LEAD(sal, 1, 0), Second parameter '1' represents how many rows forward it should get value. When there is no row left it will show default value '0' which is third parameter to this function.SELECT empno, ename, job, sal, LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next, LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff FROM emp; EMPNO ENAME JOB SAL SAL_NEXT SAL_DIFF ---------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 800 950 150 7900 JAMES CLERK 950 1100 150 7876 ADAMS CLERK 1100 1250 150 7521 WARD SALESMAN 1250 1250 0 7654 MARTIN SALESMAN 1250 1300 50 7934 MILLER CLERK 1300 1500 200 7844 TURNER SALESMAN 1500 1600 100 7499 ALLEN SALESMAN 1600 2450 850 7782 CLARK MANAGER 2450 2850 400 7698 BLAKE MANAGER 2850 2975 125 7566 JONES MANAGER 2975 3000 25 7788 SCOTT ANALYST 3000 3000 0 7902 FORD ANALYST 3000 5000 2000 7839 KING PRESIDENT 5000 0 -5000 SQL>
LISTAGG Analystic Function in 11g Release 2
The
LISTAGG
analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list. If you are using 11g Release 2 you should use this function for string aggregation.
COLUMN employees FORMAT A50 SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 3 rows selected
WM_CONCAT Built-in Function (Not Supported)
If you are not running 11g Release 2, but are running a version of the database where the
WM_CONCAT
function is present, then it is a zero effort solution as it performs the aggregation for you. It is actually an example of a user defined aggregate function described below, but Oracle have done all the work for you.COLUMN employees FORMAT A50 SELECT deptno, wm_concat(ename) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD 3 rows selected.
Note.
WM_CONCAT
is an undocumented function and as such is not supported by Oracle for user applications (MOS Note ID 1336219.1). COLLECT function in Oracle 10g
An example on oracle-developer.net uses the
COLLECT
function in Oracle 10g to get the same result. This method requires a table type and a function to convert the contents of the table type to a string. I've altered his method slightly to bring it in line with this article.CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000); / CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab IN t_varchar2_tab, p_delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS l_string VARCHAR2(32767); BEGIN FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP IF i != p_varchar2_tab.FIRST THEN l_string := l_string || p_delimiter; END IF; l_string := l_string || p_varchar2_tab(i); END LOOP; RETURN l_string; END tab_to_string; /
The query below shows the
COLLECT
function in action.COLUMN employees FORMAT A50 SELECT deptno, tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES 3 rows selected.
0 comments:
Post a Comment