[ORACLE] 群組合併-MODEL指令之應用

本範例將利用MODEL指令進行相關群組之歸類，如下圖所示，群組AAAA與群組BBBB具有『2』之共同值（Val），因此將所有BBBB群組資料併入AAAA群組，即資料列3、4。CCCC與BBBB群組並無共同值，無法串連合併，但DDDD與CCCC兩者具有『5』之共同值，則資料列7、8併入CCCC群組；而EEEE與DDDD具有『6』之共同值，則EEEE將併入DDDD群組，但先前DDDD已併入CCCC群組，則EEEE也將一同併入CCCC群組，即資料列9、10。

SELECT  GRP

      , VAL

      , NO

      , NEW_GRP

FROM

   (

    SELECT GRP

         , VAL

         , ROW_NUMBER() OVER(ORDER BY GRP, VAL) NO

    FROM TEST

   )

MODEL RETURN UPDATED ROWS

DIMENSION BY (NO)

MEASURES (GRP NEW_GRP, VAL, GRP)

RULES AUTOMATIC ORDER

(    

  NEW_GRP[NO] =

      CASE WHEN GRP[CV(NO)] = NVL(GRP[CV(NO)-1], GRP[CV(NO)]) THEN --1.與前1筆同GRP

                NVL(NEW_GRP[CV(NO)-1], GRP[CV(NO)])    --採用前1筆之NEW_GRP值

            ELSE --2.與前1筆不同GRP

               CASE WHEN VAL[CV(NO)] <> VAL[CV(NO)-1] THEN --2.1.與前1筆不同GRP,且值不同

                         GRP[CV(NO)]  --採用此筆GRP值

                    ELSE --2.1.與前1筆不同GRP,且值相同

                         NEW_GRP[CV(NO)-1] --採用前1筆之NEW_GRP值

                END                      

      END

 )

ORDER BY GRP, VAL

資料:

CREATE TABLE Test

(

GRP VARCHAR2(10),

VAL NUMBER(2)

);

INSERT INTO TEST VALUES ('AAAA', 1);

INSERT INTO TEST VALUES ('AAAA', 2);

INSERT INTO TEST VALUES ('BBBB', 2);

INSERT INTO TEST VALUES ('BBBB', 3);

--INSERT INTO TEST VALUES ('BBBB', 4);

INSERT INTO TEST VALUES ('CCCC', 4);

INSERT INTO TEST VALUES ('CCCC', 5);

INSERT INTO TEST VALUES ('DDDD', 6);

INSERT INTO TEST VALUES ('DDDD', 5);

INSERT INTO TEST VALUES ('EEEE', 6);

INSERT INTO TEST VALUES ('EEEE', 8);

COMMIT;