OracleLead详解

一、OracleLead函数

OracleLead函数用于在查询结果集中获取ORDER BY排序后后续行的值，可以用于解决诸如统计同比或环比、计算移动平均等需求。OracleLead函数需要 3 个参数，第一个参数是本函数要查询的列名，第二个参数是偏移量（即在ORDER BY中的偏移量），第三个参数是默认值（可选）。下面给出一个例子：

SELECT id, day, amount,
  ROUND((amount - LEAD(amount, 1) OVER (ORDER BY id))/amount, 2) diff
FROM transactions;

上述SQL语句的结果会返回transactions表中的id、day、amount和diff四个列，其中diff列用于记录amount列相邻两行记录的增长率。

二、OracleLead函数分组和不分组

OracleLead函数既可以在分组查询中使用，也可以在全表查询中使用。

在全表查询中，OracleLead函数会将所有记录按照ORDER BY语句指定的顺序排序并计算后面一行的值，即使两行记录只有分组列不相同，也会分别计算。下面是一个例子：

SELECT id, day, amount,
  ROUND((amount - LEAD(amount, 1) OVER (ORDER BY day))/amount, 2) diff
FROM transactions;

上述SQL语句的结果会返回transactions表中的id、day、amount和diff四个列，其中diff列用于记录每一天的收益率。

而在分组查询中，OracleLead函数会根据GROUP BY语句中的列进行分组计算。下面是一个例子：

SELECT department, name, salary,
  ROUND((salary - LEAD(salary, 1) OVER (PARTITION BY department ORDER BY salary DESC))/salary, 2) diff
FROM employees;

上述SQL语句的结果会返回employees表中每个部门中的员工薪水及其薪水变化率。

三、OracleLeadOver选取

除了OracleLead函数外，Oracle还提供了OracleLeadOver选取语句，可以选择返回满足指定条件的前 N 行或者后 N 行数据。OracleLeadOver选取有三个参数：

• ORDER BY：必选，指定查询结果的排序方式
• ROWS：必选，指定选取行数
• WHERE：可选，指定查询结果的条件

下面给出一个例子：

SELECT *
  FROM (
    SELECT id, day, amount, ROW_NUMBER() OVER (ORDER BY id) row_num
      FROM transactions
  )
WHERE row_num <= 10;

上述SQL语句的结果会返回transactions表中前 10 条记录。