Oracle listagg函数、lag函数、lead函数.doc

重庆达渝仁科技官网：Listagg函数我们有时候会遇到这样的需求：“对员工列表进行操作，将每个部门的员工名称横向排列，以逗号进行分割”。员工表我们使用scott用户schema下的emp表。sql view plaincopyprint?1. SQL select * from emp; 2.3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 4. - - - - - - - - 5. 7369 SMITH CLERK 7902 1980-12-17 800.00 20 6. 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7. 7900 JAMES CLERK 7698 1981-12-3 950.00 30 8. 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 9. 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 10.11. （篇幅原因，有省略） 12.13. 14 rows selected SQL select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - -7369 SMITH CLERK 7902 1980-12-17 800.00 207876 ADAMS CLERK 7788 1987-5-23 1100.00 207900 JAMES CLERK 7698 1981-12-3 950.00 307902 FORD ANALYST 7566 1981-12-3 3000.00 207934 MILLER CLERK 7782 1982-1-23 1300.00 10 （篇幅原因，有省略） 14 rows selected这个需求的关键在于如何将ename员工名称列压扁为一行数据。如果不使用SQL解决，最直观的想法就是使用PL/SQL进行迭代遍历，获取到所有的数据行记录。此时，我们就可以求助Oracle 11g中的函数listagg。首先我们来看一下listagg的函数描述（摘自Oracle SQL Reference）。listagg的作用是将分组范围内的所有行特定列的记录加以合并成行。函数签名中的measure_expr为分组中每个列的表达式，而delimiter为合并分割符。如果delimiter不设置的话，就表示无分割符。中间within group后面的order_by_clause表示的是进行合并中要遵守的排序顺序。而后面的over子句表明listagg是具有分析函数analyze funcation特性的。具体采用listagg有三个场景。当无分组的single-list情况下如果要获取到deptno为30的所有员工横行记录。sql view plaincopyprint?1. SQL select * from emp where deptno=30; 2.3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 4. - - - - - - - - 5. 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 6. 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7. 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 8. 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 9. 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 10. 7900 JAMES CLERK 7698 1981-12-3 950.00 30 11.12. 6 rows selected 13.14. -按照empno进行排序 15. SQL select listagg(ename, ,) within group (order by empno) from emp where deptno=30; 16.17. LISTAGG(ENAME,)WITHINGROUP( 18. - 19. ALLEN , WARD , MARTIN , BLAKE , TURNER , JAMES SQL select * from emp where deptno=30; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - -7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 307521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 307654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 307698 BLAKE MANAGER 7839 1981-5-1 2850.00 307844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 307900 JAMES CLERK 7698 1981-12-3 950.00 30 6 rows selected -按照empno进行排序SQL select listagg(ename, ,) within group (order by empno) from emp where deptno=30;LISTAGG(ENAME,)WITHINGROUP(-ALLEN , WARD , MARTIN , BLAKE , TURNER , JAMES在有分组条件下的listagg使用如果要使用分组统计各个部门的所有员工列表。sql view plaincopyprint?1. SQL select deptno, listagg(ename, ,) within group (order by empno) from emp group by deptno; 2.3. DEPTNO LISTAGG(ENAME,)WITHINGROUP( 4. - - 5. 10 CLARK ,KING ,MILLER 6. 20 SMITH ,JONES ,SCOTT ,ADAMS ,FORD 7. 30 ALLEN ,WARD ,MARTIN ,BLAKE ,TURNER ,JAMES SQL select deptno, listagg(ename, ,) within group (order by empno) from emp group by deptno;DEPTNO LISTAGG(ENAME,)WITHINGROUP(- -10 CLARK ,KING ,MILLER20 SMITH ,JONES ,SCOTT ,ADAMS ,FORD30 ALLEN ,WARD ,MARTIN ,BLAKE ,TURNER ,JAMES使用over分组情况如果要统计所有工作十年以上员工和他们相同部门的员工信息，就需要在listagg的基础上加入over分析函数子句。sql view plaincopyprint?1. SQL select deptno, ename, listagg(ename, , ) within group (order by empno) 2. 2 over (partition by deptno) as emp_list 3. 3 from emp 4. 4 where hiredate select deptno, ename, listagg(ename, , ) within group (order by empno) 2 over (partition by deptno) as emp_list 3 from emp 4 where hiredate select * from sales_qual; 2.3. MONT QUALITIES PRICE 4. - - - 5. 2011-01 1000 23.40 6. 2011-02 1020 23.40 7. 2011-03 1030 33.40 8. 2011-04 1035 10.30 SQL select * from sales_qual;MONT QUALITIES PRICE- - -2011-01 1000 23.402011-02 1020 23.402011-03 1030 33.402011-04 1035 10.30如果要获取到之前月份的信息，没有SQL专门函数就意味着需要使用PL/SQL代码进行反复的迭代获取。现在，我们可以使用lag函数来轻易实现这个功能。lag函数是一个典型的分析函数。它提供了在不使用自连接的情况下，访问多个数据行的能力。在返回多个结果行的时候，lag函数可以访问到向上特定offset偏移行的数据。value_expr就是访问到向上数据行进行的操作。offset是返回偏移的函数，默认值为1。over中，可以定义内部分析的顺序列。如果我们要获取到对应上个月的销售数据，SQL语句如下：sql view plaincopyprint?1. SQL select mont, qualities, lag(qualities,1) over (order by mont) as Next Month Qual 2. 2 from sales_qual 3. 3 order by mont; 4.5. MONT QUALITIES Next Month Qual 6. - - - 7. 2011-01 1000 8. 2011-02 1020 1000 9. 2011-03 1030 1020 10. 2011-04 1035 1030 SQL select mont, qualities, lag(qualities,1) over (order by mont) as Next Month Qual 2 from sales_qual 3 order by mont;MONT QUALITIES Next Month Qual- - -2011-01 10002011-02 1020 10002011-03 1030 10202011-04 1035 1030之后对销量变化率的处理就方便了，可以进行增长率比对等操作。那么，如果是上一年度或者上一季度的数据呢？我们只需要调节offset，从1变化为12或者3就可以了。最后，对ignore/respect nulls子句的使用是什么呢？该子句的作用是确定当value_expr表达式计算出的数值为空null的时候，该列如何进行计算。ignore nulls的作用就是忽略上面计算为空的行，采用上上行row的计算结果。respect nulls的作用是直接反映为null。respect nulls为默认值。sql view plaincopyprint?1. SQL select * from sales_qual; 2.3. MONT QUALITIES PRICE 4. - - - 5. . 6. 2011-04 1035 10.30 7. 2011-05 12.30 8. 2011-06 9.10. 6 rows selected 11.12. SQL select mont, qualities, lag(qualities, 1) ignore nulls over (order by mont) as Next Month Qual 13. 2 from sales_qual 14. 3 order by mont; 15.16. MONT QUALITIES Next Month Qual 17. - - - 18. . 19. 2011-04 1035 1030 20. 2011-05 1035 21. 2011-06 1035 22. 6 rows selected 23.24. SQL select mont,qualities, lag(qualities,1) respect nulls over (order by mont) as Next Month Qual 25. 2 from sales_qual 26. 3 order by mont; 27.28. MONT QUALITIES Next Month Qual 29. - - - 30. . 31. 2011-04 1035 1030 32. 2011-05 1035 33. 2011-06 34.35. 6 rows selected SQL select * from sales_qual;MONT QUALITIES PRICE- - -.2011-04 1035 10.302011-05 12.302011-06 6 rows selectedSQL select mont, qualities, lag(qualities, 1) ignore nulls over (order by mont) as Next Month Qual 2 from sales_qual 3 order by mont;MONT QUALITIES Next Month Qual- - -.2011-04 1035 10302011-05 10352011-06 10356 ro