SQL자격 검정 노랭이 123p 118번 문제 풀이

 

SELECT ID, START_VAL, NVL(END_VAL, 99) AS END_VAL,
LAG(END_VAL) OVER(PARTITION BY ID ORDER BY START_VAL, NVL(END_VAL, 99)) AS FLAG1,
LEAD(START_VAL) OVER(PARTITION BY ID ORDER BY START_VAL, NVL(END_VAL, 99)) AS FLAG2
FROM TBL;

NVL(END_VAL, 99)
END_VAL 컬럼이 NULL인 행에 대해서 99를 출력한다.

LAG(END_VAL)
PARTITION BY ID에 의해 ID별로 각 레코드행의 END_VAL컬럼의 이전행을 출력한다.
ORDER BY절을 통해 START_VAL을 기준으로 오름차순 정렬하고 동일한 값이 있을경우 NVL(END_VAL,99)값 즉, END_VAL 값을 기준으로 정렬한다.

 

 

LAG함수를 사용한 FLAG1컬럼과 FLAG2 컬럼에 아래와 같이 CASE WHEN을 적용해본다.

SELECT 
ID
, START_VAL
, NVL(END_VAL, 99) AS END_VAL
, CASE WHEN START_VAL = LAG(END_VAL) OVER(PARTITION BY ID 
                                              ORDER BY START_VAL
                                                     , NVL(END_VAL, 99)
                                          )
    THEN 1 ELSE 0 END AS FLAG1
, CASE WHEN END_VAL = LEAD(START_VAL) OVER(PARTITION BY ID
                                               ORDER BY START_VAL
                                                      , NVL(END_VAL, 99)
                                          ) 
    THEN 1 ELSE 0 END AS FLAG2
FROM TBL;

위 쿼리문의 결과는 다음과 같다.

 

LAG함수를 적용한 값이 START_VAL과 동일하다면 1, 동일하지 않다면 0을 FLAG1 컬럼으로 출력한 결과 와

LEAD함수를 적용한 값이 END_VAL과 동일하다면 1, 동일하지 않다면 0을 FLAG2 컬럼으로 출력한 결과이다

 

 

이전 쿼리 인라인 뷰 서브쿼리를 활용하여 값을 추출해본다.

SELECT
  ID
, START_VAL
, END_VAL
FROM(
    SELECT 
    ID
    , START_VAL
    , NVL(END_VAL, 99) AS END_VAL
    , CASE WHEN START_VAL = LAG(END_VAL) OVER(PARTITION BY ID 
                                                  ORDER BY START_VAL
                                                         , NVL(END_VAL, 99)
                                              )
        THEN 1 ELSE 0 END AS FLAG1
    , CASE WHEN END_VAL = LEAD(START_VAL) OVER(PARTITION BY ID
                                                   ORDER BY START_VAL
                                                          , NVL(END_VAL, 99)
                                              ) 
        THEN 1 ELSE 0 END AS FLAG2
    FROM TBL
    )
WHERE FLAG1 = 0
   OR FLAG2 = 0;

조건절에 의해 FLAG1과 FLAG2가 각각 0인 1,4,5,6번째의 행이 아래와 같이 출력된다.