在Oracle数据库中,LAG函数用于获取指定列的前一行数据。它是一种基于某列排序的窗口函数,可以访问查询结果集中前面的数据行。LAG的语法如下:
LAG ( expression [, offset [, default] ] ) OVER ( [ partition_clause ] order_by_clause )
其中,expression顾名思义,是查询结果集中需要获取前一行的列的名称,offset表示需要往前查询的行数,默认值为 1,default是可选的,表示当查询到第一行时返回的默认值,如果不指定则返回NULL。partition_clause表示需要分区的列名,如果不需要分区可以省略;order_by_clause则表示排序的列名。
一、基本使用方法
我们来看一个示例,首先创建一个测试表格:
CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR2(50), last_name VARCHAR2(50), salary NUMBER(20, 2), hire_date DATE ); INSERT INTO employees (employee_id, first_name, last_name, salary, hire_date) VALUES (1, 'Jenny', 'Smith', 5000, '1-JAN-2000'); INSERT INTO employees (employee_id, first_name, last_name, salary, hire_date) VALUES (2, 'John', 'Johnson', 9000, '1-FEB-2010'); INSERT INTO employees (employee_id, first_name, last_name, salary, hire_date) VALUES (3, 'Ben', 'Dover', 6000, '1-JUN-2015'); INSERT INTO employees (employee_id, first_name, last_name, salary, hire_date) VALUES (4, 'Mike', 'Tyson', 10000, '1-JAN-2018'); INSERT INTO employees (employee_id, first_name, last_name, salary, hire_date) VALUES (5, 'Jessica', 'Alba', 7000, '1-MAY-2019');
上述代码创建了一个名为employees的表格,包含五个员工的信息。接下来我们使用LAG函数获取查询结果集中每个员工的薪水在前一行的情况:
SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS last_salary FROM employees;
执行以上代码,结果如下:
EMPLOYEE_ID SALARY LAST_SALARY 1 5000 (null) 2 9000 5000 3 6000 9000 4 10000 6000 5 7000 10000
我们可以看到,使用LAG函数我们成功获取了每个员工薪水在前一行的情况,当到达第一行时返回了null。其中,LAST_SALARY这一列就是通过LAG函数计算得到的前一行的薪水。这里我们通过ORDER BY hire_date进行了排序,表示查询结果集需要根据hire_date字段进行排序。
二、使用多个LAG函数
在一个查询中使用多个LAG函数也是完全可以的,我们可以使用不同的偏移量(offset)和排序方式(order_by_clause)进行查询。下面是一个例子:
SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS last_salary, LAG(salary, 2) OVER (ORDER BY salary) AS last_salary2 FROM employees;
执行以上代码,结果如下:
EMPLOYEE_ID SALARY LAST_SALARY LAST_SALARY2 1 5000 (null) (null) 2 9000 5000 (null) 3 6000 9000 5000 4 10000 6000 9000 5 7000 10000 6000
我们可以看到,在以上代码中我们使用了两个LAG函数,其中第一个使用了默认的偏移量1、按照hire_date进行排序,第二个使用了偏移量2、按照salary进行排序,得到了不同的结果。
三、使用LAG函数实现趋势分析
LAG函数在趋势分析中也是非常有用的,我们可以使用它来计算每个时间段内的增长或下降量。下面是一个例子:
CREATE TABLE sales ( sale_date DATE, sale_amount NUMBER(20, 2) ); INSERT INTO sales (sale_date, sale_amount) VALUES ('1-JAN-2021', 5000); INSERT INTO sales (sale_date, sale_amount) VALUES ('2-JAN-2021', 6000); INSERT INTO sales (sale_date, sale_amount) VALUES ('3-JAN-2021', 7000); INSERT INTO sales (sale_date, sale_amount) VALUES ('4-JAN-2021', 8000); INSERT INTO sales (sale_date, sale_amount) VALUES ('5-JAN-2021', 10000); SELECT sale_date, sale_amount, sale_amount - LAG(sale_amount, 1) OVER (ORDER BY sale_date) AS change FROM sales;
以上代码创建了一个名为sales的表格,包含五天的销售数据。接下来我们使用LAG函数获取每天的销售额增长或下降量:
SALE_DATE SALE_AMOUNT CHANGE 01-JAN-21 5000 (null) 02-JAN-21 6000 1000 03-JAN-21 7000 1000 04-JAN-21 8000 1000 05-JAN-21 10000 2000
我们可以看到,通过使用LAG函数获取每一行的前一行(前一天)的销售额,我们成功计算出了每天的销售额增长或下降量,其中第一天的(change列)值为null。
四、使用LAG函数实现比较
我们也可以使用LAG函数对比不同行的数据,以下是一个例子:
CREATE TABLE inventory ( product_id INT PRIMARY KEY, stock_level INT ); INSERT INTO inventory (product_id, stock_level) VALUES (1, 100); INSERT INTO inventory (product_id, stock_level) VALUES (2, 200); INSERT INTO inventory (product_id, stock_level) VALUES (3, 150); SELECT product_id, stock_level, LAG(stock_level) OVER (ORDER BY product_id) AS last_stock, CASE WHEN stock_level > LAG(stock_level) OVER (ORDER BY product_id) THEN 'UP' WHEN stock_level < LAG(stock_level) OVER (ORDER BY product_id) THEN 'DOWN' ELSE 'STABLE' END AS status FROM inventory;
以上代码创建了一个名为inventory的表格,包含三种商品的库存量。接下来我们使用LAG函数对比每个商品的库存量与上一个时刻(前一个商品ID)的库存量,并加入了一个CASE语句来分析库存量的变化状态:
PRODUCT_ID STOCK_LEVEL LAST_STOCK STATUS 1 100 (null) STABLE 2 200 100 UP 3 150 200 DOWN
我们可以看到,使用LAG函数和CASE语句,我们成功地对比了每个商品上一个时刻(前一个商品ID)的库存量,并判断了其库存量变化的状态,其中第一行的last_stock值为null。
五、使用LAG函数实现排名
在一个查询中使用多个LAG函数也是完全可以的,我们可以使用不同的偏移量(offset)和排序方式(order_by_clause)进行查询。下面是一个例子:
CREATE TABLE scores ( student_id INT PRIMARY KEY, score INT ); INSERT INTO scores (student_id, score) VALUES (1, 90); INSERT INTO scores (student_id, score) VALUES (2, 80); INSERT INTO scores (student_id, score) VALUES (3, 80); INSERT INTO scores (student_id, score) VALUES (4, 70); SELECT student_id, score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank, LAG(score) OVER (ORDER BY score DESC) AS last_score FROM scores;
以上代码创建了一个名为scores的表格,包含四个学生的分数。接下来我们使用LAG函数计算每个学生上一个排名(前一个分数)、DENSE_RANK函数计算出学生成绩的排名:
STUDENT_ID SCORE RANK LAST_SCORE 1 90 1 (null) 2 80 2 90 3 80 2 80 4 70 4 80
我们可以看到,使用LAG函数和DENSE_RANK函数,我们成功地计算出了每个学生的排名以及上一个排名,其中第一行的last_score值为null。
结论
综上所述,LAG函数是Oracle数据库中非常有用的一个内置函数,通过LAG函数我们可以获取指定列的前一行数据,使用它可以实现数据的趋势分析、比较、排名等功能。掌握LAG函数的使用方法对于编写高效、准确的SQL查询非常重要。