데이터베이스 질문과 답변
고수님들께 한 가지 여쭙고 싶습니다.
현재 오라클과 PostgreSQL 사용 중인데요.
아래와 같은 테이블이 있습니다.
ID, Val
1, 10
2, 120
3, 130
4, 10
5, 20
6, 50
7, 100
8, 120
9, 30
10, 40
Val이 100점 미만인 항목에 대해서만 row_number() 를 매기고 싶습니다.
단, 연속되는 row에 한해서만 시퀀셜하게 1씩 증가 시키고,
연속이 끊기면 다시 1부터 초기화 하고 싶습니다.
ID, Val, SEQ
1, 10, 1
2, 120, null
3, 130, null
4, 10, 1
5, 20, 2
6, 50, 3
7, 100, null
8, 120, null
9, 30, 1
10, 40, 2
예전부터 이런 부분은 어플리케이션에서 처리해 왔는데요,
혹시 SQL로 윈도우 펑션 등을 이용하여 해결할 수 없을까요?
아시는분 답변 부탁 드립니다.~
감사합니다. (_ _)
by 마농
[2017.05.15 09:00:23]
WITH t AS
(
SELECT 1 id, 10 val FROM dual
UNION ALL SELECT 2, 120 FROM dual
UNION ALL SELECT 3, 130 FROM dual
UNION ALL SELECT 4, 10 FROM dual
UNION ALL SELECT 5, 20 FROM dual
UNION ALL SELECT 6, 50 FROM dual
UNION ALL SELECT 7, 100 FROM dual
UNION ALL SELECT 8, 120 FROM dual
UNION ALL SELECT 9, 30 FROM dual
UNION ALL SELECT 10, 40 FROM dual
)
SELECT id, val
, CASE WHEN val < 100 THEN ROW_NUMBER() OVER(PARTITION BY grp, flag ORDER BY id) END seq
FROM (SELECT id, val
, FLOOR(val/100) flag
, SUM(FLOOR(val/100)) OVER(ORDER BY id) grp
FROM t
)
ORDER BY id
;
SELECT id, val
, LENGTH(REGEXP_SUBSTR(REPLACE(wm_concat(FLOOR(val/100)) OVER(ORDER BY id), ','), '0+$')) seq
FROM t
;
SELECT * FROM t MODEL DIMENSION BY (id) MEASURES (val, 0 seq) RULES AUTOMATIC ORDER ( seq[ANY] = CASE WHEN val[cv()] < 100 THEN NVL(seq[cv()-1], 0) + 1 END ) ;
WITH t AS
(
SELECT 1 id, 10 val FROM dual
UNION ALL SELECT 2, 120 FROM dual
UNION ALL SELECT 3, 130 FROM dual
UNION ALL SELECT 4, 10 FROM dual
UNION ALL SELECT 5, 20 FROM dual
UNION ALL SELECT 6, 50 FROM dual
UNION ALL SELECT 7, 100 FROM dual
UNION ALL SELECT 8, 120 FROM dual
UNION ALL SELECT 9, 30 FROM dual
UNION ALL SELECT 10, 40 FROM dual
)
, tmp(id, val, seq) AS
(
SELECT id, val
, CASE WHEN val < 100 THEN 1 END seq
FROM t
WHERE id = 1
UNION ALL
SELECT c.id, c.val
, CASE WHEN c.val < 100 THEN NVL(p.seq, 0) + 1 END seq
FROM tmp p
, t c
WHERE c.id = p.id + 1
)
SELECT *
FROM tmp
;
by 미소애비
[2017.05.15 10:21:13]
감사합니다~~~~~
(_ _)
) 버튼을 클릭하여 작성 하시면 됩니다.