はじめに
行間比較(参照)によるグループ化とは、他行を参照しながらその結果によってダイナミックに複数の行をグループ分けしていくものです。一番簡単な例で言うと、「テーブル行を日付カラムで並べたときに一つ前の行のアイテムカラムと同じアイテム値であればグループとしてまとめる」です。これはグループ化が行の並びに依存するため、単純な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 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を超える行で次のグループとすると以下の出力となります。
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 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を超える行で次のグループとなります。
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 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以降でのみ使用可能です。
参考
演習１の「分析関数」と「パターンマッチ」について、以前の投稿ですでに言及していますので紹介しておきます。
解答編へつづく
解答編です。