안녕하세요, 혹시 이런 결과값은 쿼리로 어떻게 구현해야하는지 궁금하네요..
기존 아래와 같은 결과값이 있는데,
DATE COL1 COL2
201801031530 eclipse.exe 280
201801031532 eclipse.exe 285
201801031534 eclipse.exe 288
201801031536 eclipse.exe 290
201801031538 eclipse.exe 295
아래와같이 COL3를 추가하고싶습니다.
DATE COL1 COL2 COL3
201801031530 eclipse.exe 280 0
201801031532 eclipse.exe 285 5
201801031534 eclipse.exe 288 3
201801031536 eclipse.exe 290 2
201801031538 eclipse.exe 295 5
COL3은 이전 ROW값을 뺀 값이 들어가야하는데,
어떻게 접근해야할지 감이 안오네요..
WITH T AS ( SELECT '201801031530' DT , 'eclipse.exe' COL1 , 280 COL2 FROM DUAL UNION ALL SELECT '201801031532' , 'eclipse.exe' , 285 FROM DUAL UNION ALL SELECT '201801031534' , 'eclipse.exe' , 288 FROM DUAL UNION ALL SELECT '201801031536' , 'eclipse.exe' , 290 FROM DUAL UNION ALL SELECT '201801031538' , 'eclipse.exe' , 295 FROM DUAL ) SELECT DT , COL1 , NVL(COL2 - LAG(COL2) OVER(ORDER BY DT),0) COL3 , ROW_NUMBER() OVER(ORDER BY DT) RN FROM T ORDER BY 4