为了计算当前记录与下一条记录之间的天数差异，我们首先需要创建一个测试视图：
CREATE OR REPLACE VIEW v AS
SELECT '1' AS id, DATE '2015-1-1' AS hiredate FROM DUAL
UNION ALL
SELECT '2' AS id, DATE '2015-5-1' AS hiredate FROM DUAL
UNION ALL
SELECT '3' AS id, DATE '2015-3-1' AS hiredate FROM DUAL;
查看视图中的数据：
SELECT * FROM v;
接下来，我们需要将下一条记录的雇佣日期移到当前行，这可以通过使用LEAD()分析函数来实现：
SELECT id, hiredate, LEAD(hiredate) OVER(ORDER BY hiredate) AS next_hiredate FROM v;
当数据提取到同一行后，计算天数差异就变得简单了：
SELECT id, hiredate, next_hiredate, next_hiredate - hiredate AS days_diff FROM (SELECT id, hiredate, LEAD(hiredate) OVER(ORDER BY hiredate) AS next_hiredate FROM v);
除了LEAD函数，还有LAG函数，用于获取前一条记录的数据。了解这两个函数的区别有助于更好地应用它们：
SELECT id, hiredate, LEAD(hiredate) OVER(ORDER BY hiredate) AS lead_hiredate, LAG(hiredate) OVER(ORDER BY hiredate) AS lag_hiredate FROM v;
更多详细信息可以参考：原文链接
京公网安备 11010802041100号