LEAD(), LAD() 함수를 이용해서 쿼리를 짜고 싶은데 잘 안됩니다. 0 4 1,838

by 널라리 [SQL Query] [2015.04.24 13:08:25]


문의.jpg (29,065Bytes)

SID          OBUNO               SRDATE   SRTIME    CSID           CARNO   SRTYPE
32507058 00010110ZZZ25041 20150302 002529 P4354ZZZ0453 79??619   21
32507063 00010110ZZZ25041 20150302 003839 P4785ZZZ1977 79??619   80
201129113 00010110ZZZ25041 20150216 161027 P4354ZZZ0453 79??619  30

 

이런 데이터가 있습니다.

다름이 아니라 lead(), lag() 함수를 써서 SRTYPE='80' 기준으로 앞뒤 ROW를 구하고 싶습니다.

SELECT LAG(SID) OVER (ORDER BY SID)앞수,
   SID 해당수,
   LEAD(SID) OVER(ORDER BY SID)뒷수 FROM
   (
    SELECT SID FROM OBUMANAGER
     WHERE OBUNO = '0001011096925041'
                    AND SRTYPE = '80'
   )
ORDER BY SID
;

select SID,
        SID - lead(SID,1) over(order by SID) next,
        SID - lag(SID,1) over(order by SID) prev from OBUMANAGER
    WHERE SRTYPE = '80'
    AND OBUNO = '00010110ZZZ5041'
;

by 마농 [2015.04.24 13:33:45]

"잘 안됩니다." 식의 밑도 끝도 없는 질문은 좀 지양해야 할 듯 합니다.
뭐가 어떻게 안되나요? 구체적으로...
쿼리엔 문제 없어 보이는데요?
결과물이 원하는대로 안나오나요?
차라리 원하는 결과물을 보여주시는게 좋습니다.


위 문제는 '80' 조건에 있습니다.
'80' 을 뽑은 뒤 LAG, LEAD 해봐야 소용 없습니다.
LAG, LEAD 먼저 구한 뒤 80 을 추려내세요.


by 낭만캐치 [2015.04.24 13:38:26]

WITH BASE AS (
select '32507058' AS SID
       , '00010110ZZZ25041' AS OBUNO
       , '20150302' AS SRDATE
       , '002529' AS SRTIME
       , 'P4354ZZZ0453' AS CSID
       , '79수619' AS CARNO
       ,'21' AS SRTYPE
from dual
union all
select '32507063' AS SID
       , '00010110ZZZ25041' AS OBUNO
       , '20150302' AS SRDATE
       , '003839' AS SRTIME
       , 'P4785ZZZ1977' AS CSID
       , '79수619' AS CARNO
       ,'80' AS SRTYPE
from dual
union all
select '201129113' AS SID
       , '00010110ZZZ25041' AS OBUNO
       , '20150216' AS SRDATE
       , '161027' AS SRTIME
       , 'P4354ZZZ0453' AS CSID
       , '79수619' AS CARNO
       ,'30' AS SRTYPE
from dual
)
select SID
        , OBUNO
        , SRDATE
        , SRTIME
        , CSID
        , CARNO
        , LAG(SRTYPE,1) OVER(ORDER BY SRTYPE) AS 앞수
        , LEAD(SRTYPE,1) OVER (ORDER BY SRTYPE) AS 뒷수
        , SRTYPE
FROM BASE
ORDER BY SRTYPE

 

 

이런 결과를 원하시는건지..


by DarkBee [2015.04.24 13:46:47]
SELECT *
  FROM (
          SELECT LEAD ( srtype ) OVER ( ORDER BY sid ) lead_rn
               , LAG  ( srtype ) OVER ( ORDER BY sid ) lag_rn
               , t.*
            FROM t
) WHERE lead_rn = 80 OR lag_rn = 80 OR srtype = 80

 

 

 

--

 

 

SELECT *
  FROM (
          SELECT DECODE ( srtype
                       , '80'
                       , '80'
                       , DECODE ( LEAD ( srtype ) OVER ( ORDER BY sid )
                                , '80'
                                , '80'
                                , LAG  ( srtype ) OVER ( ORDER BY sid ) 
                                )  
                        ) srtype
                , sid, obuno, srdate, srtime, csid, carno
            FROM t
) WHERE srtype = 80


by 창조의날개 [2015.04.24 14:02:21]

WITH OBUMANAGER(SID, OBUNO, SRDATE, SRTIME, CSID, CARNO, SRTYPE) AS(
      SELECT 32507078, '00010110ZZZ25041', '20150302', '002529', 'P4354ZZZ0453', '79??619', '11' FROM DUAL UNION ALL
      SELECT 32507048, '00010110ZZZ25041', '20150302', '002529', 'P4354ZZZ0453', '79??619', '41' FROM DUAL UNION ALL
      SELECT 32507058, '00010110ZZZ25041', '20150302', '002529', 'P4354ZZZ0453', '79??619', '21' FROM DUAL UNION ALL
      SELECT 32507063, '00010110ZZZ25041', '20150302', '003839', 'P4785ZZZ1977', '79??619', '80' FROM DUAL UNION ALL
      SELECT 201129113, '00010110ZZZ25041', '20150216', '161027', 'P4354ZZZ0453', '79??619', '30' FROM DUAL
)
, T1 AS (
      SELECT ROW_NUMBER() OVER(ORDER BY SID) RN
           , OBUMANAGER.* 
      FROM OBUMANAGER
      WHERE OBUNO = '00010110ZZZ25041'
)
SELECT *
FROM T1
WHERE RN IN (
      SELECT T1.RN + ADD_RN - 2
      FROM T1
        , (SELECT ROWNUM ADD_RN FROM DUAL CONNECT BY LEVEL <= 3)
      WHERE SRTYPE= '80' 
)
ORDER BY SID
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입