Oracle/SQL2012. 4. 10. 11:40

์—ฐ์† ์ƒ์Šน ๊ตฌ๊ฐ„ ์กฐํšŒ

์—ฐ์† ์ƒ์Šน ๊ตฌ๊ฐ„์„ ์กฐํšŒํ•ด๋ณด์ž.


์˜ˆ์ œ๋ฅผ ์œ„ํ•ด ์•„๋ž˜์™€ ๊ฐ™์ด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜์ž.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (cd VARCHAR2(1), dt DATE, vl NUMBER);

INSERT INTO t1 VALUES ('A', DATE '2050-01-01', 100);
INSERT INTO t1 VALUES ('A', DATE '2050-01-02', 200);
INSERT INTO t1 VALUES ('A', DATE '2050-01-03', 300);
INSERT INTO t1 VALUES ('A', DATE '2050-01-04', 400);
INSERT INTO t1 VALUES ('A', DATE '2050-01-05', 500);
INSERT INTO t1 VALUES ('A', DATE '2050-01-06', 400);
INSERT INTO t1 VALUES ('A', DATE '2050-01-07', 500);
INSERT INTO t1 VALUES ('A', DATE '2050-01-08', 600);
INSERT INTO t1 VALUES ('A', DATE '2050-01-09', 700);
INSERT INTO t1 VALUES ('A', DATE '2050-01-10', 500);
COMMIT;


์•„๋ž˜์™€ ๊ฐ™์ด LAG ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์ „์ผ๋Œ€๋น„ ๊ฐ’์ธ df๋ฅผ ์ƒ์„ฑํ•˜๊ณ , ์ „์ฒด ์ˆœ๋ฒˆ๊ณผ df ๋ณ„ ์ˆœ๋ฒˆ์˜ ์ฐจ๋ฅผ ๊ธฐ์ค€์œผ๋กœ cn์„ ๊ณ„์‚ฐํ•˜๋ฉด ์—ฐ์† ์ƒ์Šน ๊ตฌ๊ฐ„์„ ์กฐํšŒํ•  ์ˆ˜ ์žˆ๋‹ค.

-- 2
WITH w1 AS (
SELECT a.*
     , NVL (SIGN (vl - LAG (vl) OVER (PARTITION BY cd ORDER BY dt)), 0) AS df
  FROM t1 a)
SELECT *
  FROM (SELECT a.*
             , COUNT (*) OVER (PARTITION BY cd, rn) AS cn
          FROM (SELECT a.*
                     , ROW_NUMBER () OVER (PARTITION BY cd ORDER BY dt)
                     - ROW_NUMBER () OVER (PARTITION BY cd, df ORDER BY dt) AS rn
                  FROM w1 a) a
         WHERE df = 1)
 WHERE cn >= 3;

CD DT          VL DF RN CN
-- ---------- --- -- -- --
A  2050-01-02 200  1  1  4
A  2050-01-03 300  1  1  4
A  2050-01-04 400  1  1  4
A  2050-01-05 500  1  1  4
A  2050-01-07 500  1  2  3
A  2050-01-08 600  1  2  3
A  2050-01-09 700  1  2  3

7 ํ–‰์ด ์„ ํƒ๋˜์—ˆ์Šต๋‹ˆ๋‹ค.


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

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

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