群組合併 - With指令應用

本範例將利用一般表格運算式（Common Table Expression：CTE）即WITH指令，進行相關群組之歸類，如下圖所示，群組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。

註：以下為MSSQL指令，ORACLE只需將#字元剔除即可執行。 

WITH Org

AS

  (

   SELECT GRP

         , VAL

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

   FROM #Test

  ),

Rec (GRP, VAL, NO, NEW_GRP)

 AS

  (

  --(1).Anchor

  SELECT GRP, VAL, NO, GRP NEW_GRP

  FROM Org

  WHERE 1=1

        AND NO = 1

  UNION ALL

  --(2).Recursive

  SELECT C.GRP, C.VAL, C.NO

       , CASE WHEN P.GRP = C.GRP THEN

                   P.NEW_GRP

              ELSE

                   CASE WHEN C.VAL = P.VAL THEN

                             P.NEW_GRP

                        ELSE

                             C.GRP

                   END

         END                 

  FROM Rec P , Org C           

  WHERE 1=1

       AND P.NO = C.NO -1 

  )

SELECT *

FROM Rec

資料

CREATE TABLE #Test

(

GRP VARCHAR(10),

VAL NUMERIC(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);

[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;

[ORACLE] 群組合併 - CONNECT BY應用

本範例將利用CONNECT BY進行相關群組之歸類，如下圖所示，群組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。

SQL:

SELECT GRP

       , VAL

       , NVL(SUBSTR(GRP_LIST, 2, INSTR(GRP_LIST, ',', 2)-2), GRP) NEW_GRP

      -- , GRP_LIST

FROM

    (

      SELECT A.GRP

             , A.VAL

             , MIN(SYS_CONNECT_BY_PATH(GRP, ',')) GRP_LIST

      FROM

          (     

          SELECT GRP

                 , VAL

                 , ROW_NUMBER() OVER(PARTITION BY GRP ORDER BY VAL) GrpSeq

          FROM TEST A

          ) A

      CONNECT BY (

                   (--組內遞推, 只管大小順序, 不計其值

                    GRP = PRIOR GRP 

                        AND GrpSeq = PRIOR GrpSeq+1                   

                    )

                   OR

                   ( --組間遞推, 值需相同

                     GRP >  PRIOR  GRP

                         AND VAL = PRIOR VAL

                   )

                 )

      GROUP BY GRP, VAL

     )

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 ('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;