行間比較(参照)によるグループ化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では書けないと思われるような検索も難なくこなしてくれるので覚えておいて損はないんじゃないでしょうか。
以下の投稿でも、階層、再帰、モデルをそれぞれ使って一つの問題を解いています。よかったら参考にしてください。