Oracle/SQL2012. 4. 9. 22:23

์  ์ด๋ ฅ์„ ์„ ๋ถ„ ์ด๋ ฅ์œผ๋กœ ์ „ํ™˜

์  ์ด๋ ฅ์„ ์„ ๋ถ„ ์ด๋ ฅ์œผ๋กœ ์ „ํ™˜ํ•ด๋ณด์ž.


์˜ˆ์ œ๋ฅผ ์œ„ํ•ด ์•„๋ž˜์™€ ๊ฐ™์ด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜์ž. ๋ณ€๊ฒฝ ์‹œ์ ์— ์ด๋ ฅ์ด ๋ฐœ์ƒํ•˜๋Š” ์  ์ด๋ ฅ ํ…Œ์ด๋ธ”์ด๋‹ค.

1
2
3
4
5
6
7
8
9
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (cd VARCHAR2(1), dt DATE, vl NUMBER);
 
INSERT INTO t1 VALUES ('A', DATE '2050-01-01', 1);
INSERT INTO t1 VALUES ('A', DATE '2050-01-06', 2);
INSERT INTO t1 VALUES ('A', DATE '2050-01-16', 1);
INSERT INTO t1 VALUES ('A', DATE '2050-01-31', 3);
COMMIT;


LEAD ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜๋ฉด ์„ ๋ถ„ ์ด๋ ฅ์„ ์‰ฝ๊ฒŒ ์ƒ์„ฑํ•  ์ˆ˜ ์žˆ๋‹ค.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 2
SELECT   cd, dt AS dt_bg
       , LEAD (dt - 1, 1, DATE '9999-12-31') OVER (ORDER BY dt) AS dt_ed, vl
    FROM t1
ORDER BY 1;
 
CD DT_BG      DT_ED      VL
-- ---------- ---------- --
A  2050-01-01 2050-01-05  1
A  2050-01-31 9999-12-31  3
A  2050-01-16 2050-01-30  1
A  2050-01-06 2050-01-15  2
 
4 ํ–‰์ด ์„ ํƒ๋˜์—ˆ์Šต๋‹ˆ๋‹ค.


๋‹ค์Œ ์˜ˆ์ œ๋ฅผ ์œ„ํ•ด ์•„๋ž˜์™€ ๊ฐ™์ด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜์ž. ๋ณ€๊ฒฝ๊ณผ ๊ด€๋ จ ์—†์ด ์›”๋ณ„๋กœ ์ƒ์„ฑ๋˜๋Š” ์  ์ด๋ ฅ ํ…Œ์ด๋ธ”์ด๋‹ค.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 3
DROP TABLE t1 PURGE;
CREATE TABLE t1 (cd VARCHAR2(1), ym VARCHAR2(6), vl NUMBER);
 
INSERT INTO t1 VALUES ('A', '205001', 1);
INSERT INTO t1 VALUES ('A', '205002', 1);
INSERT INTO t1 VALUES ('A', '205003', 2);
INSERT INTO t1 VALUES ('A', '205004', 2);
INSERT INTO t1 VALUES ('A', '205005', 2);
INSERT INTO t1 VALUES ('A', '205006', 1);
INSERT INTO t1 VALUES ('A', '205007', 1);
INSERT INTO t1 VALUES ('A', '205008', 1);
INSERT INTO t1 VALUES ('A', '205009', 1);
INSERT INTO t1 VALUES ('A', '205010', 3);
INSERT INTO t1 VALUES ('A', '205011', 3);
INSERT INTO t1 VALUES ('A', '205012', 3);
COMMIT;


์•„๋ž˜ ์ฟผ๋ฆฌ๋กœ ์„ ๋ถ„ ์ด๋ ฅ์„ ์ƒ์„ฑํ•  ์ˆ˜ ์žˆ๋‹ค.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 4
SELECT   cd, MIN (ym) AS ym_bg
       , CASE WHEN MAX (r1) = MAX (cn) THEN '999912' ELSE MAX (ym) END AS ym_ed, vl
    FROM (SELECT a.*
               , COUNT (*) OVER (PARTITION BY a.cd) AS cn
               , ROW_NUMBER () OVER (PARTITION BY a.cd ORDER BY a.ym) AS r1
               , ROW_NUMBER () OVER (PARTITION BY a.cd, a.vl ORDER BY a.ym) AS r2
            FROM t1 a)
GROUP BY cd, vl, r1 - r2
ORDER BY 1, 2;
 
CD YM_BG  YM_ED  VL
-- ------ ------ --
A  205001 205002  1
A  205003 205005  2
A  205006 205009  1
A  205010 999912  3
 
4 ํ–‰์ด ์„ ํƒ๋˜์—ˆ์Šต๋‹ˆ๋‹ค.


'Oracle > SQL' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€

์ฃผ๋ฏผ๋“ฑ๋ก๋ฒˆํ˜ธ ๋งˆ์Šคํ‚น  (0) 2012.04.30
๋‹ค์ค‘ ํ–‰์„ ๋ฌธ์ž์—ด๋กœ ์ง‘๊ณ„  (0) 2012.04.16
๊ตฌ๋ถ„์ž ํฌํ•จ ๊ฐ’์„ ํ–‰์œผ๋กœ ๋ถ„๋ฆฌ  (0) 2012.04.13
์—ฐ์†๋œ ๊ณต๋ฐฑ์„ ํ•˜๋‚˜์˜ ๊ณต๋ฐฑ์œผ๋กœ ๋ณ€๊ฒฝ  (0) 2012.04.12
์—ฐ์† ์ƒ์Šน ๊ตฌ๊ฐ„ ์กฐํšŒ  (0) 2012.04.10
Posted by ์ •ํฌ๋ฝ_

ํ‹ฐ์Šคํ† ๋ฆฌํˆด๋ฐ”