确定当前记录和下一条记录之间相差的天数
创建测试用表:
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() over() 分析函数:
SELECT id, hiredate, LEAD(hiredate) OVER(ORDER BY hiredate) next_hd FROM v;
当数据提取到同一行后,在计算就比较简单了:
SELECT id, hiredate, next_hd - hiredate AS diff FROM (SELECT id, hiredate, LEAD(hiredate) OVER(ORDER BY hiredate) next_hd FROM v);
和lead对应的就是lag函数,如果能记住两个函数的区别当然比较好,如果记不住,可直接实验。
SELECT id, hiredate, LEAD(hiredate) OVER(ORDER BY hiredate) lead_hd, hiredate, LAG(hiredate) OVER(ORDER BY hiredate) lag_hd FROM v;