Oracle SQL之lag()和lead()函数使用

REFERENCE1: LEAD()、LAG()
REFERENCE2: DECODE()

以下为转载原文:

---

首先我们创建一个表user_info表：

CREATE TABLE
       user_info(user_id NUMBER(11) primary key, 
       user_name VARCHAR2(14), 
       user_age NUMBER(4),
       user_birthday DATE);

插入一些数据用于测试，如下：
测试数据截图

前期工作准备完成，接下来就准备测试这些函数了！

函数简介

lag()和lead()这两个函数可以查询我们得到的结果集上下偏移相应行数的相应的结果

lag()函数

查询当前行向上偏移n行对应的结果,该函数有三个参数：第一个为待查询的参数列名,第二个为向上偏移的位数,第三个参数为超出最上面边界的默认值
看如下代码：

–查询向上偏移 1 位的年龄

SELECT user_id,
       user_age,
       lag(user_age, 1, 0) over(ORDER BY user_id) RESULT
FROM user_info;

结果图示：
lag函数运行图示

lead()函数

查询当前行向下偏移n行对应的结果,该函数有三个参数：第一个为待查询的参数列名,第二个为向下偏移的位数,第三个参数为超出最下面边界的默认值。

如下代码

–查询向下偏移 2 位的年龄

SELECT user_id,
       user_age,
       lead(user_age, 2, 0) over(ORDER BY user_id)
  FROM user_info;

结果图示：
LEAD()函数图示

在掌握了上面相关的知识之后，我们，可以来试着解决一个 Oracle问题：

例题描述

英文原题： Show the department number, name, number of employees, and
average salary of all departments, together with the names, salaries,
and jobs of the employees working in each department. 题意理解：
查找出员工信息，以及其所在部门的基本信息和该部门的平均工资。

结果格式示意图：

求解思路：

查找出员工信息，以及其所在部门的基本信息和该部门的平均工资。这一步相对来说会比较基础！

但是如何去除相同的部门名称等呢？

我们可以按照部门名称将结果进行分组
然后使用lag()函数取出每个分组的上面一位，如果到了上边界就设置其值为NULL。

然后使用decode()函数使得相应属性对应为NULL的显示原来的值，不为NULL的显示为NULL。

代码如下：

SELECT decode(lag(depttable.department_name, 1, NULL)
              over(PARTITION BY depttable.department_name ORDER BY emp.department_id ASC),
              NULL,
              depttable.department_id,
              NULL) temp_dep_id,

       decode(lag(depttable.department_name, 1, NULL)
              over(PARTITION BY depttable.department_name ORDER BY emp.department_id ASC),
              NULL,
              depttable.department_name,
              NULL) temp_dep_name,

       decode(lag(depttable.department_name, 1, NULL)
              over(PARTITION BY depttable.department_name ORDER BY emp.department_id ASC),
              NULL,
              depttable.employees_num,
              NULL) temp_emp_num,

       decode(lag(depttable.department_name, 1, NULL)
              over(PARTITION BY depttable.department_name ORDER BY emp.department_id ASC),
              NULL,
              depttable.avg_salary,
              NULL) temp_emp_sa,

       emp.last_name,
       emp.salary,
       emp.job_id
  FROM (SELECT dep1.department_id,
               dep1.department_name,
               COUNT(emp1.employee_id) employees_num,
               round(nvl(AVG(salary), 0), 2) avg_salary
          FROM employees   emp1,
               departments dep1
         WHERE emp1.department_id(+) = dep1.department_id
         GROUP BY dep1.department_id,
                  dep1.department_name
         ORDER BY dep1.department_id) depttable,
       employees emp
 WHERE depttable.department_id = emp.department_id

 ORDER BY emp.department_id ASC,
          temp_dep_name     ASC;