[퀴즈] 직전 값 대체 쿼리 2 8 5,623

by 마농 분석함수 [2010.04.07 15:15:05]


[퀴즈] 직전 값 참조 쿼리

다음과 같은 자료가 있습니다.

WITH t AS
(
SELECT 1 no, 10 v FROM dual
UNION ALL SELECT 2, Null FROM dual
UNION ALL SELECT 3, Null FROM dual
UNION ALL SELECT 4, 20   FROM dual
UNION ALL SELECT 5, 10   FROM dual
UNION ALL SELECT 6, Null FROM dual
UNION ALL SELECT 7, Null FROM dual
UNION ALL SELECT 8, Null FROM dual
UNION ALL SELECT 9, 40   FROM dual
)
SELECT * FROM t;

v 값이 없을때 직전 번호의 v값을 가져와 조회하는 쿼리를 작성하세요.
만약 직전 v값도 없다면 그 이전값을 그값도 없다면 그 이전값을 조회해야 합니다.

[원본] [결과]
NO V NO V V1
1 10 1 10 10
2   2   10
3   3   10
4 20 4 20 20
5 10 5 10 10
6   6   10
7   7   10
8   8   10
9 40 9 40 40

[정답보기] <=== 트리플클릭
SELECT no, v
    , LAST_VALUE(v IGNORE NULLS) OVER(ORDER BY no) v1
  FROM t
;


by 꼬랑지 [2010.04.07 16:32:34]
WITH t AS
(
SELECT 1 no, 10 v FROM dual
UNION ALL SELECT 2, Null FROM dual
UNION ALL SELECT 3, NULL FROM dual
UNION ALL SELECT 4, 20 FROM dual
UNION ALL SELECT 5, 10 FROM dual
UNION ALL SELECT 6, Null FROM dual
UNION ALL SELECT 7, NULL FROM dual
UNION ALL SELECT 8, Null FROM dual
UNION ALL SELECT 9, 40 FROM dual
)
SELECT A.NO ANO,A.V V1
,MAX(B.V) KEEP (DENSE_RANK FIRST ORDER BY B.NO DESC) V2
FROM t A,t B
where A.NO>=B.NO
AND B.V IS NOT NULL
GROUP BY A.NO,A.V
ORDER BY A.NO

by 마농 [2010.04.07 17:06:45]
Self 조인으로 풀어주셨군요.
테이블을 한번만 읽고 처리하도록 해보세요.

by 꼬랑지 [2010.04.07 17:56:09]
WITH A AS
(
SELECT 1 no, 10 v FROM dual
UNION ALL SELECT 2, Null FROM dual
UNION ALL SELECT 3, NULL FROM dual
UNION ALL SELECT 4, 20 FROM dual
UNION ALL SELECT 5, NULL FROM dual
UNION ALL SELECT 6, NULL FROM dual
UNION ALL SELECT 7, NULL FROM dual
UNION ALL SELECT 8, Null FROM dual
UNION ALL SELECT 9, 40 FROM dual
)
SELECT NO,V
,MIN(V) OVER(PARTITION BY FLAG)
FROM
(
SELECT NO,V
,SUM(DECODE(V,NULL,0,1)) OVER(ORDER BY NO) FLAG
FROM A
)

by 발로차는새 [2010.04.07 18:11:54]
-- 헷갈려서 5/10 row 를 5/11 로 변경 하고 테스트 했습니다
WITH t AS
(
SELECT 1 no, 10 v FROM dual
UNION ALL SELECT 2, Null FROM dual
UNION ALL SELECT 3, Null FROM dual
UNION ALL SELECT 4, 20 FROM dual
UNION ALL SELECT 5, 11 FROM dual
UNION ALL SELECT 6, Null FROM dual
UNION ALL SELECT 7, Null FROM dual
UNION ALL SELECT 8, Null FROM dual
UNION ALL SELECT 9, 40 FROM dual
)
SELECT no, v,
last_value(v ignore nulls) over (order by no) as v1
FROM t;

by 마농 [2010.04.08 09:18:25]
와우! 제가 생각했던 답보다 더 간결하네요. ignore nulls 구문이 있는 줄 몰랐네요.
발로차는새님 것을 정답으로 올려야겠습니다.
제가 처음 생각했던 답은 꼬랑지님 답과 유사합니다.
SELECT no, v
, MIN(v) OVER(PARTITION BY grp) v1
FROM
(
SELECT no, v
, COUNT(v) OVER(ORDER BY no) grp
FROM t
)
;

by v상이v [2010.04.08 16:08:34]
WITH t AS
(
SELECT 1 no, 10 v FROM dual
UNION ALL SELECT 2, Null FROM dual
UNION ALL SELECT 3, Null FROM dual
UNION ALL SELECT 4, 20 FROM dual
UNION ALL SELECT 5, 10 FROM dual
UNION ALL SELECT 6, Null FROM dual
UNION ALL SELECT 7, Null FROM dual
UNION ALL SELECT 8, Null FROM dual
UNION ALL SELECT 9, 40 FROM dual
)
SELECT *
FROM t
MODEL
DIMENSION BY ( no )
MEASURES ( v , 0 v2)
RULES ( v2[ANY] = NVL2(v[CV()] , v[CV()] , v2[CV()-1]) )

이미 간결한 답들이 올라왔지만...
요즘 model에 관심이 생겨서...
한번 해봤습니다...

by 선모 [2010.05.19 13:32:48]


WITH t AS
(
SELECT 1 no, 10 v FROM dual
UNION ALL SELECT 2, Null FROM dual
UNION ALL SELECT 3, Null FROM dual
UNION ALL SELECT 4, 20 FROM dual
UNION ALL SELECT 5, 10 FROM dual
UNION ALL SELECT 6, Null FROM dual
UNION ALL SELECT 7, Null FROM dual
UNION ALL SELECT 8, Null FROM dual
UNION ALL SELECT 9, 40 FROM dual
)
SELECT no, v
,nvl(v,nvl(lag(v) over(order by no),first_value(v) over(order by no))) result
FROM t;

by 야쿠르트세컨드 [2012.12.27 00:32:59]

SELECT no, v, MAX(v) over(PARTITION BY grp) v1  FROM(
  SELECT no, v, SUM(v) over(ORDER BY no) grp
  FROM t
)
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입