OracleLead详解

发布时间：2023-05-19

一、Oracle Lead函数

Oracle Lead函数用于在查询结果集中获取ORDER BY排序后后续行的值，可以用于解决诸如统计同比或环比、计算移动平均等需求。Oracle Lead函数需要 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列相邻两行记录的增长率。

二、Oracle Lead函数分组和不分组

Oracle Lead函数既可以在分组查询中使用，也可以在全表查询中使用。 在全表查询中，Oracle Lead函数会将所有记录按照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列用于记录每一天的收益率。 而在分组查询中，Oracle Lead函数会根据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表中每个部门中的员工薪水及其薪水变化率。

三、Oracle Lead Over选取

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

  • 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 条记录。

推荐文章