行間比較(参照)によるグループ化SQL演習の解答編です。問題編は以下。

行間比較(参照)によるグループ化SQL演習 (問題編)

ここの挙げたSQLは解答例です。他にもいろいろなやり方があると思います。

##演習データ

テストデータ

SELECT * FROM test_grp_tbl ORDER BY order_id;

  ORDER_ID ITEM              QTY
---------- ---------- ----------
      1001 Kiwi                8
      1002 Kiwi               10
      1003 Apple               5
      1004 Apple               8
      1015 Apple              15
      1016 Apple               5
      1017 Banana             20
      1018 Apple              25
      1019 Apple              55
      1050 Apple               5
      1051 Apple               5

11 rows selected.

##演習１
『ORDER_IDを昇順で並べた連続する行で、隣接する同一のITEMを持つ行でグループ化しなさい』

演習１出力結果

  GROUP_ID   ORDER_ID ITEM              QTY
---------- ---------- ---------- ----------
         1       1001 Kiwi                8
         1       1002 Kiwi               10
         2       1003 Apple               5
         2       1004 Apple               8
         2       1015 Apple              15
         2       1016 Apple               5
         3       1017 Banana             20
         4       1018 Apple              25
         4       1019 Apple              55
         4       1050 Apple               5
         4       1051 Apple               5

####演習１・分析関数

演習１「分析関数」

SELECT   SUM (flag) over (ORDER BY order_id) group_id,
         order_id, item, qty
FROM     (SELECT   order_id, item, qty,
                   DECODE (LAG (item) over (ORDER BY order_id),
                           item, 0, 1) flag
          FROM     test_grp_tbl
         )
ORDER  BY order_id;

LAG等の分析関数で一つ前の行を参照し、前行と異なるITEMであれば以下のようにフラグを１とします。最後にフラグをランニング集計することでグループ分けが完成します。

SELECT order_id, item, 
       DECODE (LAG (item) over (ORDER BY order_id), item, 0, 1) flag
FROM   test_grp_tbl;

  ORDER_ID ITEM             FLAG
---------- ---------- ----------
      1001 Kiwi                1
      1002 Kiwi                0
      1003 Apple               1
      1004 Apple               0
      1015 Apple               0
      1016 Apple               0
      1017 Banana              1
      1018 Apple               1
      1019 Apple               0
      1050 Apple               0
      1051 Apple               0

ちなみに行間参照をしない方法でも可能です。

演習１・旅人算

WITH
    t AS
        (SELECT order_id, item, qty,
                ROW_NUMBER () OVER (ORDER BY order_id)
              - ROW_NUMBER () OVER (PARTITION BY item ORDER BY order_id) gid
         FROM   test_grp_tbl
        )
SELECT GROUP_ID, order_id, t.item, qty
FROM   t,
       (SELECT   item, gid,
                 ROW_NUMBER () OVER (ORDER BY MIN (order_id)) GROUP_ID
        FROM     t
        GROUP BY item, gid) g
WHERE  t.item = g.item AND t.gid = g.gid;

CTE部分でITEMごとのグループ分けは完了しています。残りの部分でGROUP_IDを割り振っています。

####演習１・モデル

演習１「モデル」

SELECT grp GROUP_ID, order_id, item, qty
FROM   test_grp_tbl
MODEL
    DIMENSION BY (ROW_NUMBER () OVER (ORDER BY order_id) rn)
    MEASURES (1 grp, order_id, item, qty)
    RULES
        (grp [rn > 1] ORDER BY rn = 
                grp [CV () - 1]
             +  DECODE (item [CV ()], item [CV () - 1], 0, 1)
        )
ORDER  BY order_id;

DIMENSIONに連番(行番号)を割り当てることで、一つ前の行「CV() - 1」へのアクセスを可能としています。ITEM値「ITEM[CV()]」が前行「ITEM [CV() - 1]」と異なる値であれば、前行のGRP値「grp [CV() - 1]」に１を足すことでグループ番号を進めます。

####演習１・再帰

演習１「再帰」

WITH t
     AS (SELECT ROW_NUMBER() over (ORDER BY order_id) rn,
                order_id, item, qty
         FROM   test_grp_tbl
     ),
     recur(grp, rn, order_id, item, qty)
     AS (SELECT 1, rn, order_id, item, qty
         FROM t
         WHERE  rn = 1
         UNION ALL
         SELECT r.grp + DECODE(r.item, t.item, 0, 1),
                t.rn, t.order_id, t.item, t.qty
         FROM   t, recur r
         WHERE  t.rn = r.rn + 1
     )
SELECT grp group_id, order_id, item, qty FROM recur
ORDER  BY order_id;

事前に連番(行番号)を割り当てることで、行をリニアに処理できるようにしています。次のレベルに手渡すのはITEMとグループ番号です。再帰内で受け取った前行のITEMと比較し異なる場合にGRPに1を加算してグループ分けしています。

####演習１・パターンマッチ

演習１「パターンマッチ」

SELECT GROUP_ID, order_id, item, qty
FROM   test_grp_tbl
MATCH_RECOGNIZE (ORDER BY order_id
                 MEASURES match_number () GROUP_ID
                 ALL ROWS PER MATCH
                 PATTERN (s n*)
                 DEFINE n AS s.item = n.item
                )
ORDER  BY order_id;

PATTERN句においてパターンが１行以上であること、DEFINE句においてITEMがパターンを開始した最初のITEMと同じであればパターンとして適合することを定義します。何番目のパターン適合かを返すmatch_numberによってグループ分けが成立します。

以下のような書き方もできます。

                 PATTERN (s n*)
                 DEFINE n AS item = PREV (item)
または
                 PATTERN (n+)
                 DEFINE n AS item = FIRST (item)

##演習２
『ORDER_IDを昇順で並べた連続する行で、グループの開始行から順にQTYを加算していき、合計が50を超えない範囲で最大となるようグループ化しなさい。ただしQTYが50を超える行は単独のグループとする。』

演習2　結果出力

  GROUP_ID   ORDER_ID ITEM              QTY
---------- ---------- ---------- ----------
         1       1001 Kiwi                8
         1       1002 Kiwi               10
         1       1003 Apple               5
         1       1004 Apple               8
         1       1015 Apple              15
         2       1016 Apple               5
         2       1017 Banana             20
         2       1018 Apple              25
         3       1019 Apple              55
         4       1050 Apple               5
         4       1051 Apple               5

####演習２・分析関数

演習２「分析関数」

WITH t
     AS (SELECT order_id, item, qty,
            ROW_NUMBER () OVER (ORDER BY order_id) rn,
            SUM (qty) OVER (ORDER BY order_id) sqty
     FROM   test_grp_tbl
    )
SELECT   SUM (DECODE (f.rn, NULL, 0, 1)) OVER (ORDER BY t.rn) GROUP_ID,
         order_id, item, qty
FROM     (SELECT     rn
          FROM       (SELECT   t1.rn, 
                               NVL (MAX (t2.rn), t1.rn) + 1 next_rn
                      FROM     t t1,
                               t t2
                      WHERE    t2.sqty(+) BETWEEN (t1.sqty - t1.qty) + 1 
                                              AND (t1.sqty - t1.qty) + 50
                      GROUP BY t1.rn)
          START WITH rn = 1
          CONNECT BY PRIOR next_rn = rn) f,
         t
WHERE    f.rn(+) = t.rn
ORDER BY order_id;

事前に連番（行番号）とランニング集計によるQTYの合計を計算したカラムを用意し、自己結合によりそれぞれの行に対してQTY合計が50位内である続き行を算出します。その最大値を取得すれば、それぞれの行からスタートして加算した合計が50を超える行の行番号（次のスタート行）が以下のように得られます。

WITH t
     AS (SELECT order_id, item, qty,
                ROW_NUMBER () over (ORDER BY order_id) rn,
                SUM (qty) over (ORDER BY order_id) sqty
         FROM   test_grp_tbl)
SELECT t1.rn, NVL (MAX (t2.rn), t1.rn) + 1 next_rn,
       MAX (NVL (t2.sqty, t1.sqty) - (t1.sqty - t1.qty)) sqty
FROM   t t1,
       t t2
WHERE  t2.sqty(+) BETWEEN ( t1.sqty - t1.qty ) + 1 
                      AND ( t1.sqty - t1.qty ) + 50
GROUP  BY t1.rn;

        RN    NEXT_RN       SQTY
---------- ---------- ----------
         1          6         46
         2          7         43
         3          7         33
         4          8         48
         5          8         40
         6          9         50
         7          9         45
         8          9         25
         9         10         55
        10         12         10
        11         12          5

階層クエリでRN=1からスタートした連結リストを取得すれば、グループのスタート地点となる行だけが得られるので、演習１と同様にこの行だけフラグを立ててランニング集計すればグループ分けができます。

        RN    NEXT_RN       SQTY
---------- ---------- ----------
         1          6         46
         6          9         50
         9         10         55
        10         12         10

####演習２・モデル

演習２

SELECT   grp GROUP_ID, order_id, item, qty
FROM     test_grp_tbl
MODEL
    DIMENSION BY (ROW_NUMBER () OVER (ORDER BY order_id) rn)
    MEASURES (1 grp, order_id, item, qty, 0 sqty)
    RULES
        (sqty [ANY] ORDER BY rn =
              qty [CV()]
            + CASE
                   WHEN qty [CV ()] + NVL (sqty [CV () - 1], 0) > 50
                   THEN 0
                   ELSE NVL (sqty [CV () - 1], 0)
              END,
         grp [rn > 1] ORDER BY rn = 
              grp [CV () - 1]
            + CASE 
                   WHEN qty[CV ()] + sqty [CV () - 1] > 50
                   THEN 1
                   ELSE 0
              END)
ORDER BY order_id;

モデルは"デフォルト"でRULES内のエントリを上から順に処理します。最初のエントリで上から順にQTYを加算していき、合計が５０を超える場合にはリセットします。

SELECT   order_id, item, qty, sqty
FROM     test_grp_tbl
MODEL
    DIMENSION BY (ROW_NUMBER () OVER (ORDER BY order_id) rn)
    MEASURES (order_id, item, qty, 0 sqty)
    RULES
        (sqty [ANY] ORDER BY rn =
              qty [CV()]
            + CASE
                   WHEN qty [CV ()] + NVL (sqty [CV () - 1], 0) > 50
                   THEN 0
                   ELSE NVL (sqty [CV () - 1], 0)
              END
        );

  ORDER_ID ITEM              QTY       SQTY
---------- ---------- ---------- ----------
      1001 Kiwi                8          8
      1002 Kiwi               10         18
      1003 Apple               5         23
      1004 Apple               8         31
      1015 Apple              15         46
      1016 Apple               5          5    <--- リセット
      1017 Banana             20         25
      1018 Apple              25         50
      1019 Apple              55         55    <--- リセット
      1050 Apple               5          5    <--- リセット
      1051 Apple               5         10

次のエントリでは、上記のSQTYを参照します。一つ前の行のSQTYに現在のQTYを足すと50を超える行でグループを進めてグループ分けします。

####演習２・再帰

WITH 
    t AS
        (SELECT   ROW_NUMBER () OVER (ORDER BY order_id) rn,
                  order_id, item, qty
         FROM     test_grp_tbl
         ),
    recur (grp, rn, order_id, item, qty, sqty) AS
        (SELECT 1, rn, order_id, item, qty, qty
         FROM   t
         WHERE  rn = 1
         UNION ALL
         SELECT r.grp
              + CASE 
                    WHEN r.sqty + t.qty > 50
                    THEN 1
                    ELSE 0
                END,
                t.rn, t.order_id, t.item, t.qty,
                t.qty
              + CASE
                    WHEN r.sqty + t.qty > 50
                    THEN 0
                    ELSE r.sqty
                END
         FROM   t, recur r
         WHERE  t.rn = r.rn + 1
        )
SELECT grp GROUP_ID, order_id, item, qty
FROM   recur
ORDER BY order_id;

演習１と同様に連番を事前定義してリニアに処理します。次のレベルに手渡すのはQTYの合計とグループ番号です。QTYの合計が50を超える行で合計をリセットし、同時にグループ番号を進めます。

####演習２・パターンマッチ

SELECT GROUP_ID, order_id, item, qty
FROM   test_grp_tbl
MATCH_RECOGNIZE (ORDER BY order_id
                 MEASURES match_number () GROUP_ID
                 ALL ROWS PER MATCH
                 PATTERN (s n*)
                 DEFINE n AS SUM (qty) <= 50
                )
ORDER BY order_id;

パターンマッチはほとんどそのまま。DEFINEの条件を変えただけです。とても簡単ですね。
ちなみに上記のSUM(qty)は、s.qty + SUM(n.qty)と同等です。

単独超過の存在をわかりやすくするために以下の書き方でもいいかもしれません。

                 PATTERN (s | n+)
                 DEFINE s AS qty > 50, n AS sum(qty) <= 50

##演習３
『ORDER_IDを昇順で並べた連続する行で、隣接する同一のITEMを持つ行において、グループの開始行から順にQTYを加算していき合計が50を超えない範囲で最大となるようグループ化しなさい。ただしQTYが50を超える行は単独のグループとする。』

演習３出力結果

  GROUP_ID   ORDER_ID ITEM              QTY
---------- ---------- ---------- ----------
         1       1001 Kiwi                8
         1       1002 Kiwi               10
         2       1003 Apple               5
         2       1004 Apple               8
         2       1015 Apple              15
         2       1016 Apple               5
         3       1017 Banana             20
         4       1018 Apple              25
         5       1019 Apple              55
         6       1050 Apple               5
         6       1051 Apple               5

（以下追記）

####演習３・分析関数

演習３「分析関数」

WITH
    t AS
        (SELECT grp, order_id, item, qty, flag,
                ROW_NUMBER () OVER (PARTITION BY grp ORDER BY order_id) rn,
                SUM (qty) OVER (PARTITION BY grp ORDER BY order_id) sqty
         FROM   (SELECT SUM (flag) OVER (ORDER BY order_id) grp,
                        order_id, item, qty, flag
                 FROM   (SELECT order_id, item, qty, 
                                DECODE (LAG (item) OVER (ORDER BY order_id), item,
                                        0, 1) flag 
                         FROM test_grp_tbl))
        )
SELECT   SUM (SIGN (t.flag + NVL (f.rn, 0))) OVER (ORDER BY t.grp, t.rn) GROUP_ID,
         t.order_id, t.item, t.qty
FROM     (SELECT     grp, rn, next_rn
          FROM       (SELECT   t1.grp, t1.rn, NVL (MAX (t2.rn), t1.rn) + 1 next_rn
                      FROM     t t1, t t2
                      WHERE    t2.sqty(+) BETWEEN (t1.sqty - t1.qty) + 1
                                              AND (t1.sqty - t1.qty) + 50
                           AND t1.grp = t2.grp(+)
                      GROUP BY t1.rn, t1.grp)
          CONNECT BY PRIOR next_rn = rn
                 AND PRIOR grp = grp
          START WITH rn = 1) f,
         t
WHERE    t.grp = f.grp(+)
     AND t.rn = f.rn(+)
ORDER BY order_id;

前半CTE部分で連続するITEMによるグループ分けを行い、後半部分でそのグループ内でQTYによるグループ分けを行っています。ほぼ単純に演習１と演習２の解答の合成です。

####演習３・モデル

演習３「モデル」

SELECT grp GROUP_ID, order_id, item, qty
FROM   test_grp_tbl
MODEL
    DIMENSION BY (ROW_NUMBER () OVER (ORDER BY order_id) rn)
    MEASURES (1 grp, 0 sqty, 1 item_grp, order_id, item, qty)
    RULES (
        sqty [ANY] ORDER BY rn =
                qty[CV ()]
              + CASE
                    WHEN item[CV ()] != item[CV () - 1]
                      OR qty[CV ()] + NVL (sqty[CV () - 1], 0) > 50
                    THEN 0
                    ELSE NVL (sqty[CV () - 1], 0)
                END,
        grp [rn > 1] ORDER BY rn =
                grp[CV () - 1] 
              + CASE
                     WHEN item[CV ()] != item[CV () - 1]
                       OR qty[CV ()] + sqty[CV () - 1] > 50 
                     THEN 1 
                     ELSE 0
                END
    )
ORDER BY order_id;

RULES句内の最初のエントリで、連続するITEMの確認とQTYの集計を同時に行い、次のエントリでグループ分けをしています。

####演習３・再帰

演習３「再帰」

WITH
    t AS (SELECT ROW_NUMBER () OVER (ORDER BY order_id) rn,
                 order_id, item, qty
          FROM   test_grp_tbl),
    recur (grp, rn, order_id, item, qty, sqty) AS
        (SELECT 1, rn, order_id, item, qty, qty
         FROM   t
         WHERE  rn = 1
         UNION ALL
         SELECT r.grp + CASE 
                             WHEN r.item != t.item OR r.sqty + t.qty > 50
                             THEN 1 
                             ELSE 0
                        END,
                t.rn, t.order_id, t.item, t.qty,
                t.qty + CASE
                             WHEN r.item != t.item OR r.sqty + t.qty > 50
                             THEN 0
                             ELSE r.sqty
                        END
         FROM   t, recur r
         WHERE  t.rn = r.rn + 1)
SELECT grp GROUP_ID, order_id, item, qty
FROM   recur
ORDER BY order_id;

同じく、集計のリセットとグループを進める条件が、同一のITEMでないかQTYが50を超えた時です(r.item != t.item OR r.sqty + t.qty > 50)。

####演習３・パターンマッチ

演習３「パターンマッチ」

SELECT GROUP_ID, order_id, item, qty
FROM   test_grp_tbl
MATCH_RECOGNIZE (ORDER BY order_id
                 MEASURES match_number () GROUP_ID, SUM (qty) sqty
                 ALL ROWS PER MATCH
                 PATTERN (s n*)
                 DEFINE n AS item = FIRST (item) AND SUM (qty) <= 50)
ORDER BY order_id;

DEFINEへの条件を一つ追加するだけです。非常にシンプルですね。

###おわりに

演習３は蛇足だったような気もしないでもないですが、色んな方法で解くというのは手持ちのカードを増やす良い練習だとおもいます。特に「分析関数」、「モデル」、「再帰」、「パターンマッチ」は行間比較のできる機能であり、これを使用するとSQLでは書けないと思われるような検索も難なくこなしてくれるので覚えておいて損はないんじゃないでしょうか。

以下の投稿でも、階層、再帰、モデルをそれぞれ使って一つの問題を解いています。よかったら参考にしてください。

12cR2の新機能LISTAGGオーバーフローコントロールを他のバージョンでも使える単一SQLクエリで書いてみる