Oracle的LAG函數(shù)和LEAD函數(shù)都是分析函數(shù),用于在查詢結(jié)果中獲取同一結(jié)果集中前一行或后一行的數(shù)據(jù)
語法:
LAG(column_name, offset, default_value) OVER (ORDER BY order_by_column)
示例:
SELECT employee_id, salary, LAG(salary, 1, 0) OVER (ORDER BY employee_id) AS previous_salary
FROM employees;
語法:
LEAD(column_name, offset, default_value) OVER (ORDER BY order_by_column)
示例:
SELECT employee_id, salary, LEAD(salary, 1, 0) OVER (ORDER BY employee_id) AS next_salary
FROM employees;
總結(jié): LAG函數(shù)和LEAD函數(shù)的主要區(qū)別在于它們獲取數(shù)據(jù)的方向。LAG函數(shù)用于獲取前一行的數(shù)據(jù),而LEAD函數(shù)用于獲取后一行的數(shù)據(jù)。這兩個函數(shù)在處理需要比較相鄰行數(shù)據(jù)的場景時(shí)非常有用,例如計(jì)算累計(jì)總和、排名或比較同一列中不同行的值。