–两行之间的数据进行比较
SELECT ID,FLAG,CHANGEDT FROM TEST;
原始结果:
SELECT
ID
,FLAG
,MAX(CHANGEDT) OVER(PARTITION BY ID ORDER BY CHANGEDT) START_DT
,MAX(CHANGEDT) OVER(PARTITION BY ID ORDER BY CHANGEDT ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) END_DT
FROM TEST
之后结果:
SELECT
ID
,MAX(DATE1) OVER (PARTITION BY ID ORDER BY DATE1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM
(
SELECT ‘ID1’ ID,1 DATE1 FROM DUAL UNION ALL
SELECT ‘ID1’ ID,2 DATE1 FROM DUAL UNION ALL
SELECT ‘ID1’ ID,3 DATE1 FROM DUAL UNION ALL
SELECT ‘ID1’ ID,4 DATE1 FROM DUAL UNION ALL
SELECT ‘ID2’ ID,1 DATE1 FROM DUAL UNION ALL
SELECT ‘ID2’ ID,2 DATE1 FROM DUAL UNION ALL
SELECT ‘ID3’ ID,1 DATE1 FROM DUAL UNION ALL
SELECT ‘ID3’ ID,2 DATE1 FROM DUAL UNION ALL
SELECT ‘ID3’ ID,3 DATE1 FROM DUAL
)
结果如下: