oracle 점이력 선분이력으로 만들기

T1은 CD의 VAL값의 변동을 기록한 이력테이블입니다.
점으로 저장된 시간을 선분조건이 가능하도록 합니다.
업무규칙은 처음일자 이전은 처음일자부터 다음일자까지 모두 처음값을 사용하고,
중복된 일자가 없으며,마지막일자는 미래(현재포함)까지 사용되고 있다는 조건이 주어진 경우입니다.

인사기록을 기준으로 경력증명서에 직급별 재직기간을 표시할때 응용한다면,
시작일자 SDT부분이 DT만 있으면 되고,종료일자 EDT의 '99991231'는 '현재'로 바꾸면 되겠죠.
설명이 이해가 안가는군요.

WITH T1 ( CD, VAL, DT ) AS 
(
    SELECT '1001',9.2,'20150101' FROM DUAL
    UNION ALL
    SELECT '1001',8.5,'20150601' FROM DUAL
    UNION ALL
    SELECT '1001',9.3,'20160101' FROM DUAL
)
SELECT 
     CD
    ,VAL
    ,NVL2(LAG(DT,1)  OVER (PARTITION BY CD ORDER BY DT),DT,'20000101') AS SDT
    ,TO_CHAR(LEAD(TO_DATE(DT,'YYYYMMDD')-1,1,'99991231') OVER (PARTITION BY CD ORDER BY DT),'YYYYMMDD') AS EDT
FROM T1

초단위까지 포함된 경우 중복된 일자가 있더라도 문제가 없습니다.
초까지 같은 경우는 할말이 없습니다만.

WITH T1 ( CD, VAL, DT ) AS 
(
    SELECT '1001',9.2,'20150101070001' FROM DUAL
    UNION ALL
    SELECT '1001',9.05,'20150402153131' FROM DUAL
    UNION ALL
    SELECT '1001',8.5,'20150601132000' FROM DUAL
    UNION ALL
    SELECT '1001',8.45,'20150601235059' FROM DUAL
    UNION ALL
    SELECT '1001',9.3,'20160101093021' FROM DUAL
)
SELECT 
     CD
    ,VAL
    ,NVL2(LAG(DT,1)  OVER (PARTITION BY CD ORDER BY DT),DT,'20000101000001') AS SDT
    ,TO_CHAR(LEAD(TO_DATE(DT,'YYYYMMDDHH24MISS')-0.00001,1,SYSDATE) OVER (PARTITION BY CD ORDER BY DT),'YYYYMMDDHH24MISS') AS EDT
FROM T1

• PARTITION BY : 그룹기준컬럼으로 커다란 사무실에 각각의 부서를 구분하기 위해 설치한 파티션을 생각하면 쉽습니다.
• ORDER BY : PARTITION BY의 컬럼을 기준으로 ORDER BY에 기술된 컬럼순서로 정렬합니다.
• LAG : 이전행을 참조
• LEAD : 다음행을 참조
• NVL2 : 선택된 값이 NULL이 아니면 두번째인자,NULL이면 세번째인자를 되돌려 줍니다. 

LAG, LEAD를 지원하지 않는 경우는, 아래 예제처럼 응용하시면 됩니다.
비효율이 있겠지만, 지금은 이렇게 밖에 생각이 나지 않는군요.

WITH T1 ( CD, VAL, DT ) AS
(
    SELECT '1001',9.2,'20150101070001' FROM DUAL
    UNION ALL
    SELECT '1001',9.05,'20150402153131' FROM DUAL
    UNION ALL
    SELECT '1001',8.5,'20150601132000' FROM DUAL
    UNION ALL
    SELECT '1001',8.45,'20150601235059' FROM DUAL
    UNION ALL
    SELECT '1001',9.3,'20160101093021' FROM DUAL
    UNION ALL
    SELECT '1002',10.3,'20160101093021' FROM DUAL
    UNION ALL
    SELECT '1002',10.2,'20161001070021' FROM DUAL
    UNION ALL
    SELECT '1003',4.5,'20160101093021' FROM DUAL
)
SELECT
     T2.CD
    ,T2.VAL
    ,CASE WHEN T2.RN = 1 THEN '20000101000001' ELSE T2.DT END AS SDT
    ,CASE WHEN T3.RN IS NULL THEN '99991231235959' ELSE TO_CHAR(TO_DATE(T3.DT,'YYYYMMDDHH24MISS')-0.00001,'YYYYMMDDHH24MISS') END AS EDT
    ,T2.RN
    ,T3.RN
FROM 
(
    SELECT
         CD
        ,VAL
        ,DT
        ,ROW_NUMBER() OVER(PARTITION BY CD ORDER BY DT) AS RN
    FROM T1
) T2 LEFT JOIN
(
    SELECT
         CD
        ,VAL
        ,DT
        ,ROW_NUMBER() OVER(PARTITION BY CD ORDER BY DT) AS RN
    FROM T1
) T3
ON
    T2.CD=T3.CD
AND T2.RN=T3.RN-1
ORDER BY 
    T2.CD,T2.RN

DB2 for iseries 7.1에선 OLAP FUNCTION으로 ROW_NUMBER, RANK, DENSE_RANK함수와 WINDOW-PARTITION을 제공합니다.

 

WITH T1 ( CD, VAL, DT ) AS
(
    SELECT '1001',9.2,'20150101070001' FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT '1001',9.05,'20150402153131' FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT '1001',8.5,'20150601132000' FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT '1001',8.45,'20150601235059' FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT '1001',9.3,'20160101093021' FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT '1002',10.3,'20160101093021' FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT '1002',10.2,'20161001070021' FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT '1003',4.5,'20160101093021' FROM SYSIBM.SYSDUMMY1
)
SELECT
     T2.CD
    ,T2.VAL
    ,CASE WHEN T2.RN = 1 THEN '20000101000001' ELSE T2.DT END AS SDT
    ,CASE WHEN T3.RN IS NULL THEN '99991231235959' ELSE TO_CHAR(CAST(T3.DT AS TIMESTAMP)-1 SECOND,'YYYYMMDDHH24MISS') END AS EDT
FROM
(
    SELECT
         CD
        ,VAL
        ,DT
        ,ROW_NUMBER() OVER(PARTITION BY CD ORDER BY DT) AS RN
    FROM T1
) T2 LEFT OUTER JOIN
(
    SELECT
         CD
        ,VAL
        ,DT
        ,ROW_NUMBER() OVER(PARTITION BY CD ORDER BY DT) AS RN
    FROM T1
) T3
ON
    T2.CD=T3.CD
AND T2.RN=T3.RN-1
ORDER BY
    T2.CD,T2.RN