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 这个需求的关键在于如何将 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 在有分组条件下的 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 重庆达渝仁科技官网：使用 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 * 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 专门函数就意味着需要使用 PL/SQL 代码进行反复的迭代获取。

现在，我们可以使用 lag 函数来轻易实现这个功能lag 函数是一个典型的分析函数它提供了在不使用自连接的情况下，访问多个数据行的能力在返回多个结果行的时候，lag 函数可以访问到向上特定 offset 偏移行的数据value_expr 就是访问到向上数据行进行的操作offset 是返回偏移的函数，默认值为1over 中，可以定义内部分析的顺序列如果我们要获取到对应上个月的销售数据，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 之后对销量变化率的处理就方便了，可以进行增长率比对等操作。

那么，如果是上一年度或者上一季度的数据呢？我们只需要调节 offset，从 1 变化为 12 或者 3 就可以了 重庆达渝仁科技官网：最后，对 ignore/respect nulls 子句的使用是什么呢？该子句的作用是确定当 value_expr表达式计算出的数值为空 null 的时候，该列如何进行计算ignore nulls 的作用就是忽略上面计算为空的行，采用上上行 row 的计算结果respect nulls 的作用是直接反映为nullrespect 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 lead 函数获取下一个月销售量有 lag 的获取上个 offset 处理行的函数，就有 lead 函数处理下一个处理行的函数。

lead函数实际上就是 lag 的逆向过程相关各项参数与 lag 函数的相同区别就在于 lead 函数获取的是排序后结果集合的后offset 数据行记录[sql] view plaincopyprint?1. SQL> select mont,qualities, lead(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 1020 8. 2011。