OracleLead詳解

一、OracleLead函數(shù)

OracleLead函數(shù)用于在查詢結(jié)果集中獲取ORDER BY排序后后續(xù)行的值，可以用于解決諸如統(tǒng)計同比或環(huán)比、計算移動平均等需求。OracleLead函數(shù)需要 3 個參數(shù)，第一個參數(shù)是本函數(shù)要查詢的列名，第二個參數(shù)是偏移量（即在ORDER BY中的偏移量），第三個參數(shù)是默認(rèn)值（可選）。下面給出一個例子：

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

上述SQL語句的結(jié)果會返回transactions表中的id、day、amount和diff四個列，其中diff列用于記錄amount列相鄰兩行記錄的增長率。

二、OracleLead函數(shù)分組和不分組

OracleLead函數(shù)既可以在分組查詢中使用，也可以在全表查詢中使用。

在全表查詢中，OracleLead函數(shù)會將所有記錄按照ORDER BY語句指定的順序排序并計算后面一行的值，即使兩行記錄只有分組列不相同，也會分別計算。下面是一個例子：

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

上述SQL語句的結(jié)果會返回transactions表中的id、day、amount和diff四個列，其中diff列用于記錄每一天的收益率。

而在分組查詢中，OracleLead函數(shù)會根據(jù)GROUP BY語句中的列進(jìn)行分組計算。下面是一個例子：

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

上述SQL語句的結(jié)果會返回employees表中每個部門中的員工薪水及其薪水變化率。

三、OracleLeadOver選取

除了OracleLead函數(shù)外，Oracle還提供了OracleLeadOver選取語句，可以選擇返回滿足指定條件的前 N 行或者后 N 行數(shù)據(jù)。OracleLeadOver選取有三個參數(shù)：

下面給出一個例子：

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

上述SQL語句的結(jié)果會返回transactions表中前 10 條記錄。