はじめに

行間比較(参照)によるグループ化とは、他行を参照しながらその結果によってダイナミックに複数の行をグループ分けしていくものです。一番簡単な例で言うと、「テーブル行を日付カラムで並べたときに一つ前の行のアイテムカラムと同じアイテム値であればグループとしてまとめる」です。これはグループ化が行の並びに依存するため、単純なGROUP BYではできない処理となります。

オラクルのSQLには、この行間参照のできる機能が以下のようにとても充実しています。バージョンが上がる度にSQLが苦手とする検索を補う機能を追加してきたという感じですね。行間比較によるグループ化はこれらの機能をつかってある程度簡単に実現することができます。ただしそれぞれの機能は特定の検索に特化した非常に特徴的な動きをします。そこで一つの問題をそれぞれの機能をつかって解くことにより機能の特徴（できること、できないこと）や記述法に理解が深まるのではないかとおもいます。

行間参照機能	リリースバージョン	句
階層	-	connect by
分析関数	8i(8.1.6)	over
モデル	10g	model
再帰	11gR2	with
パターンマッチ	12c	match_recognize

ということで、演習問題を３つを作ってみました。

演習データ

create table test_grp_tbl (order_id number, item varchar2(30), qty number);

insert into test_grp_tbl values (1001, 'Kiwi', 8);
insert into test_grp_tbl values (1002, 'Kiwi', 10);
insert into test_grp_tbl values (1003, 'Apple', 5);
insert into test_grp_tbl values (1004, 'Apple', 8);
insert into test_grp_tbl values (1015, 'Apple', 15);
insert into test_grp_tbl values (1016, 'Apple', 5);
insert into test_grp_tbl values (1017, 'Banana', 20);
insert into test_grp_tbl values (1018, 'Apple', 25);
insert into test_grp_tbl values (1019, 'Apple', 55);
insert into test_grp_tbl values (1050, 'Apple', 5);
insert into test_grp_tbl values (1051, 'Apple', 5);
commit;

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.

演習問題と出力結果

上記のテストデータを用いて、以下の各演習問題でそれぞれ「分析関数(&階層)」、「モデル」、「再帰」、「パターンマッチ」の行間参照機能を使用したSQLを作ってみてください。ただし「分析関数」の回答では分析関数および階層クエリのみを使用可とします。

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

以下、見やすくなるよう後から仕切り線等を追加していますが、目標とする出力結果です。GROUP_IDカラムでグループ分けします。SQLの動きとしては、異なるITEMが現れた行で次のグループに切り替えます。

演習１　出力結果（罫線等追加）

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

参考までに、これをLISTAGG等の集合関数を使い最終的にGROUP_IDでGROUP BYすれば以下のような結果が得られます。

GROUP_IDで纏めた出力（参考）

  GROUP_ID    SUM_QTY ITEM       ORDER_LIST
---------- ---------- ---------- -------------------------
         1         18 Kiwi       1001,1002
         2         33 Apple      1003,1004,1015,1016
         3         20 Banana     1017
         4         90 Apple      1018,1019,1050,1051

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

ITEMに関係なく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   <-- 合計QTY超過(51)
         2       1017 Banana             20
         2       1018 Apple              25
         ----------------------------------
         3       1019 Apple              55   <-- 合計QTY超過(単独超過)
         ----------------------------------
         4       1050 Apple               5   <-- 合計QTY超過(60)
         4       1051 Apple               5

参考までにGROUP_IDで纏めるとこうなります。

GROUP_IDで纏めた出力（参考）

  GROUP_ID    SUM_QTY ITEM_LIST       ORDER_LIST
---------- ---------- --------------- ------------------------------
         1         46 Apple,Kiwi      1001,1002,1003,1004,1015
         2         50 Apple,Banana    1016,1017,1018
         3         55 Apple           1019
         4         10 Apple           1050,1051

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

演習１と演習２の合わせ技です。異なるITEMが現れるかまたはグループの合計が50を超える行で次のグループとなります。

演習3　結果出力（罫線等追加）

  GROUP_ID   ORDER_ID ITEM              QTY
---------- ---------- ---------- ----------
         1       1001 Kiwi                8
         1       1002 Kiwi               10
         ----------------------------------
         2       1003 Apple               5   <-- ITEM変化
         2       1004 Apple               8
         2       1015 Apple              15
         2       1016 Apple               5
         ----------------------------------
         3       1017 Banana             20  <-- ITEM変化
         ----------------------------------
         4       1018 Apple              25  <-- ITEM変化
         ----------------------------------
         5       1019 Apple              55  <-- 合計QTY超過(単独超過)
         ----------------------------------
         6       1050 Apple               5  <-- 合計QTY超過(60)
         6       1051 Apple               5

GROUP_IDで纏めると以下です。

GROUP_IDで纏めた出力（参考）

  GROUP_ID    SUM_QTY ITEM       ORDER_LIST
---------- ---------- ---------- ------------------------------
         1         18 Kiwi       1001,1002
         2         33 Apple      1003,1004,1015,1016
         3         20 Banana     1017
         4         25 Apple      1018
         5         55 Apple      1019
         6         10 Apple      1050,1051

それぞれの機能の特徴

分析関数(&階層)

分析関数は、それぞれの行で他行を参照して計算した結果を返します。演習１では前行のITEMを比較するだけですが、演習2,3では不定数の行を参照することになるので少し複雑になります。ここでは、事前に合計を計算した後、自己結合や階層を使用して解決します。

モデル

行とカラムを配列変数やスプレッドシートのセルのように直接アクセスできるためSQLとしては非常に特異な動きをします。カラム値を参照するだけでなく、計算途中で値を書き換えたり、行やカラムを増やしたりすることもできます。ここでは、一つ前の行からITEMや合計を直接取得および比較してグループ分けします。

再帰

他の言語と同様の再帰ループを行います。「階層」と大きく異なるのは、途中の計算結果を次のレベルに渡すことができる点です。これによって飛躍的に自由度が上がりました。ここでは、複雑な再帰は必要なく単純に先頭行から最終行まで直線的にリピートしながら次のレベルにITEMや合計を渡します。

パターンマッチ

縦方向検索に特化した機能で、行の現れるパターンをクエリ内で定義してそれに適合する行を抽出します。この演習問題に最も簡単に解答できますが、12c以降でのみ使用可能です。

参考

演習１の「分析関数」と「パターンマッチ」について、以前の投稿ですでに言及していますので紹介しておきます。

分析関数（ウインドウ関数）をわかりやすく説明してみた

解答編へつづく

解答編です。

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