연속 값 그룹핑

연속 값을 그룹핑해보자.

예제를 위해 아래와 같이 테이블을 생성하자.

-- 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', 200);
INSERT INTO t1 VALUES ('A', DATE '2050-01-04', 300);
INSERT INTO t1 VALUES ('A', DATE '2050-01-05', 300);
INSERT INTO t1 VALUES ('A', DATE '2050-01-06', 300);
INSERT INTO t1 VALUES ('A', DATE '2050-01-07', 300);
INSERT INTO t1 VALUES ('A', DATE '2050-01-08', 200);
INSERT INTO t1 VALUES ('A', DATE '2050-01-09', 200);
INSERT INTO t1 VALUES ('A', DATE '2050-01-10', 100);
COMMIT;

아래와 같이 분석 함수를 사용하면 연속 값을 쉽게 그룹핑할 수 있다.

-- 2
SELECT a.*
     , SUM (df) OVER (PARTITION BY cd ORDER BY dt) AS gp
  FROM (SELECT a.*
             , CASE WHEN vl = LAG (vl) OVER (PARTITION BY cd ORDER BY dt) THEN 0 ELSE 1 END AS df
          FROM t1 a) a;

CD DT          VL DF GP
-- ---------- --- -- --
A  2050-01-01 100  1  1
A  2050-01-02 200  1  2
A  2050-01-03 200  0  2
A  2050-01-04 300  1  3
A  2050-01-05 300  0  3
A  2050-01-06 300  0  3
A  2050-01-07 300  0  3
A  2050-01-08 200  1  4
A  2050-01-09 200  0  4
A  2050-01-10 100  1  5

10 행이 선택되었습니다.

12.1 이상 버전은 MATCH_RECOGNIZE 절을 사용할 수 있다.

-- 3
SELECT cd, dt, vl, gp
  FROM t1
MATCH_RECOGNIZE (
    PARTITION BY cd
    ORDER BY dt
    MEASURES MATCH_NUMBER () AS gp
    ALL ROWS PER MATCH
    PATTERN (bg eq*)
    DEFINE eq AS eq.vl = PREV (eq.vl))
ORDER BY cd, dt;

CD DT          VL GP
-- ---------- --- --
A  2050-01-01 100  1
A  2050-01-02 200  2
A  2050-01-03 200  2
A  2050-01-04 300  3
A  2050-01-05 300  3
A  2050-01-06 300  3
A  2050-01-07 300  3
A  2050-01-08 200  4
A  2050-01-09 200  4
A  2050-01-10 100  5

10 행이 선택되었습니다.