sql oracle recursive lesson

まえがき

@tlokweng さんの記事を拝見し、
もっと再帰の練習をしたいとおもい、問題作って解いてみました。

参考文献

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

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

難しいプログラムでは自分がいままで書いたコードやこれから書くコードが正しく動くと信じて残りのコードを書く必要がある

Patterns everywhere – Find them fast!Oracle Database 12cのSQLパターン・マッチング

モデリングのSQL

分析関数とmodel句

SQL Snippets ™ for Oracle Developers MODEL Clause The CV() Function

SQL Snippets ™ for Oracle Developers MODEL Clause SEQUENTIAL and AUTOMATIC ORDER

Oracle Database 10g の SQL MODEL 句 式の評価順序

練習仕様

顧客が買い物袋を持ってスーパーに行きました。
商品を選んで、レジへ向かい、精算し終えました。
購入した商品の個数や商品ごとの重量や容積の都合上、
持ってきた買い物袋で持ち帰れる限界を超えてしまいました。。
そこで、買い物袋と同じ大きさのダンボールを用意して持ち帰ることにしました。
顧客は何個ダンボール用意することになりましたか。

データ投入

DMLとかDDLとか練習も込みで、適当にデータつくります。

create_data.sql

DROP TABLE item_mst;
CREATE TABLE item_mst(item CONSTRAINT item_mst_pk PRIMARY KEY ,item_kbn,price,weight,VOLUME) AS 
SELECT DISTINCT
    lpad(dbms_random.STRING('X',TRUNC(dbms_random.VALUE * 3) + 2),6,'p_') AS item
    ,' '--dummuyでデフォ値いれとく
    ,10000--dummuyでデフォ値いれとく
    ,10000--dummuyでデフォ値いれとく
    ,10000--dummuyでデフォ値いれとく
FROM
    dual
CONNECT BY
    LEVEL <= 110--100件作成したいので、110件ぐらいあればできるかなぐらいの感じです
;

MERGE INTO item_mst tar
    USING (
            SELECT
                item
                ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS item_kbn
                ,TRUNC(ABS(dbms_random.VALUE(100,10000)),0) AS price
                ,TRUNC(ABS(dbms_random.VALUE(1,100)),0) AS weight
                ,TRUNC(ABS(dbms_random.VALUE(1,100)),0) AS VOLUME
            FROM item_mst
            ) src
    ON (
        tar.item = src.item
        )
    WHEN MATCHED THEN
    UPDATE
        SET
            tar.item_kbn = src.item_kbn
            ,tar.price = src.price
            ,tar.weight = src.weight
            ,tar.VOLUME = src.VOLUME
;

COMMIT;

DELETE FROM item_mst s1--100件のみ残したいので、こんな感じでかいています
WHERE
    NOT EXISTS(
            WITH tmp_rn AS (
            SELECT
                s2.*
                ,row_number() OVER (ORDER BY s2.item) AS rn
            FROM
                item_mst s2
            )
            SELECT
                1
            FROM
                tmp_rn s3
            WHERE
                s1.item = s3.item
            AND s3.rn <= 100
            )
;

COMMIT;

DROP TABLE cust_mst;
CREATE TABLE cust_mst(cust_id  CONSTRAINT cust_mst_pk PRIMARY KEY ,cust_kbn,basket_no) AS 
SELECT DISTINCT
    lpad(dbms_random.STRING('X',TRUNC(dbms_random.VALUE * 3) + 2),6,'c_') AS cust_id
    ,' '--dummuyでデフォ値いれとく
    ,'      '--dummuyでデフォ値いれとく
FROM
    dual
CONNECT BY
    LEVEL <= 110--100件作成したいので、110件ぐらいあればできるかなぐらいの感じです
;

MERGE INTO cust_mst tar
    USING (
            SELECT
                cust_id
                ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS cust_kbn
                ,lpad(dbms_random.STRING('X',TRUNC(dbms_random.VALUE * 3) + 2),6,'b_') AS basket_no
            FROM cust_mst
            ) src
    ON (
        tar.cust_id = src.cust_id
        )
    WHEN MATCHED THEN
    UPDATE
        SET
            tar.cust_kbn = src.cust_kbn
            ,tar.basket_no = src.basket_no
;

COMMIT;

DELETE FROM cust_mst s1--100件のみ残したいので、こんな感じでかいています
WHERE
    NOT EXISTS(
            WITH tmp_rn AS (
            SELECT
                s2.*
                ,row_number() OVER (ORDER BY s2.cust_id) AS rn
            FROM
                cust_mst s2
            )
            SELECT
                1
            FROM
                tmp_rn s3
            WHERE
                s1.cust_id = s3.cust_id
            AND s3.rn <= 100
            )
;

COMMIT;


DROP TABLE basket_mst;
CREATE TABLE basket_mst(basket_no  CONSTRAINT basket_mst_pk PRIMARY KEY ,upper_limit_weight,upper_limit_volume) AS 
SELECT DISTINCT
    basket_no
    ,1000
    ,1000
FROM
    cust_mst
;

MERGE INTO basket_mst tar
    USING (
            SELECT
                basket_no
                ,TRUNC(ABS(dbms_random.VALUE(100,1000)),0) AS upper_limit_weight
                ,TRUNC(ABS(dbms_random.VALUE(100,1000)),0) AS upper_limit_volume
            FROM basket_mst
            ) src
    ON (
        tar.basket_no = src.basket_no
        )
    WHEN MATCHED THEN
    UPDATE
        SET
            tar.upper_limit_weight = src.upper_limit_volume
            ,tar.upper_limit_volume = src.upper_limit_volume
;

COMMIT;


DROP SEQUENCE sales_seq;
CREATE SEQUENCE sales_seq  START WITH 1 INCREMENT BY 1;

DROP TABLE sales_trn;
CREATE TABLE sales_trn(seq CONSTRAINT sales_trn_pk PRIMARY KEY ,item,qty,price,cust_id,purchase_date) AS
SELECT
    sales_seq.NEXTVAL AS seq
    ,m2.item
    ,TRUNC(ABS(dbms_random.VALUE(1,10)),0) AS qty
    ,m2.price
    ,m1.cust_id
    ,TO_DATE('20180101','YYYYMMDD') + MOD(ABS(dbms_random.random),TO_DATE('20190101', 'YYYYMMDD') - TO_DATE('20180101','YYYYMMDD')) AS purchase_date
FROM--マスタ同士の直積で生じる組み合わせ分、seq発番してじゃんじゃん入れていきます。いったん1万件。
    cust_mst m1
        CROSS JOIN item_mst m2
;

INSERT /*+ APPEND */ INTO sales_trn tar--ダイレクトパスインサート
SELECT
    sales_seq.NEXTVAL AS seq
    ,m2.item
    ,TRUNC(ABS(dbms_random.VALUE(1,10)),0) AS qty
    ,m2.price
    ,m1.cust_id
    ,TO_DATE('20180101','YYYYMMDD') + MOD(ABS(dbms_random.random),TO_DATE('20190101', 'YYYYMMDD') - TO_DATE('20180101','YYYYMMDD')) AS purchase_date
FROM--マスタ同士の直積で生じる組み合わせ分、seq発番してじゃんじゃん入れていきます。のこり99万件。
    cust_mst m1
        CROSS JOIN item_mst m2
        CROSS JOIN (SELECT 1 FROM dual CONNECT BY LEVEL <= 99)
;

COMMIT;

投入データ確認

BASKET_MSTは偶然100件になりました。

conf_data.sql

select 'CUST_MST' as tbl,count(*) from CUST_MST group by 'CUST_MST' union all
select 'ITEM_MST' as tbl,count(*) from ITEM_MST group by 'ITEM_MST' union all
select 'BASKET_MST' as tbl,count(*) from BASKET_MST group by 'BASKET_MST' union all
select 'SALES_TRN' as tbl,count(*) from SALES_TRN group by 'SALES_TRN'
;

再帰クエリ

ひとまず、ある顧客のある日の購入データをもとに取り組んでみます。
リニアに処理したいため、購入日、顧客IDごとのシーケンス順に連番をふっておきます。

create_cumming.sql

DROP TABLE cumming;
CREATE TABLE cumming AS
SELECT
    row_number() OVER (PARTITION BY s1.purchase_date,s1.cust_id ORDER BY s1.seq) AS rn
    , s4.VOLUME * s1.qty AS sum_volume
    , s4.weight * s1.qty AS sum_weight
    , s3.upper_limit_volume
    , s3.upper_limit_weight
FROM
    sales_trn s1
        INNER JOIN cust_mst s2
            ON
                s1.cust_id = s2.cust_id
        INNER JOIN basket_mst s3
            ON
                s2.basket_no = s3.basket_no
        INNER JOIN item_mst s4
            ON
                s1.item = s4.item
WHERE
    s1.cust_id = 'c_369N'
AND s1.purchase_date = '18-10-06'
;

各商品の購入数分、重量＆容積を算出し、それらを漸化的に足し算していきます。
各処理対象行に直前までの累積重量＆累積容積を持たせ、処理対象行の重量＆容積を足したとき
上限値を超えたら、フラグをたて、累積重量＆累積容積を0にrefleshしてまた同じように足し算していきます。

ダンボールの個数がわかるように、フラグをランニング集計しています。

pre_rec.sql

WITH rec (
    rn
    , sum_volume
    , sum_weight
    , upper_limit_volume
    , upper_limit_weight
    , cume_volume
    , cume_weight
    , pre_cume_volume
    , pre_cume_weight
    , over_flg
    , grp
) AS (
    SELECT
        rn
        , sum_volume
        , sum_weight
        , upper_limit_volume
        , upper_limit_weight
        , sum_volume AS cume_volume
        , sum_weight AS cume_weight
        , 0 AS pre_cume_volume
        , 0 AS pre_cume_weight
        , 1 AS over_flg
        , 1 AS grp
    FROM
        cumming
    WHERE
        rn = 1
    UNION ALL
    SELECT
        s2.rn
        , s2.sum_volume
        , s2.sum_weight
        , s2.upper_limit_volume
        , s2.upper_limit_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.cume_volume + s2.sum_volume
            ELSE s2.sum_volume
        END AS cume_volume
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.cume_weight + s2.sum_weight
            ELSE s2.sum_weight
        END AS cume_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.pre_cume_volume + s1.sum_volume
            ELSE 0
        END AS pre_cume_volume
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.pre_cume_weight + s1.sum_weight
            ELSE 0
        END AS pre_cume_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN 0
            ELSE 1
        END AS over_flg
        ,s1.grp
        + CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN 0
            ELSE 1
        END AS grp
    FROM
        rec s1
        , cumming s2
    WHERE
        s1.rn + 1 = s2.rn
)
SELECT
    *
FROM
    rec
ORDER BY
    rn
;

【pre_rec.sqlの処理結果】

いけてそうだなー（感覚）

いけてそうなので、量を増やして取り組んでいきます。

rec.sql

WITH src AS(
SELECT
    s1.purchase_date
    , s1.cust_id
    , s1.seq
    , s1.item
    , s2.basket_no
    , row_number() OVER (PARTITION BY s1.purchase_date,s1.cust_id ORDER BY s1.seq) AS rn
    , s4.VOLUME * s1.qty AS sum_volume
    , s4.weight * s1.qty AS sum_weight
    , s3.upper_limit_volume
    , s3.upper_limit_weight
FROM
    sales_trn s1
        INNER JOIN cust_mst s2
            ON s1.cust_id = s2.cust_id
        INNER JOIN basket_mst s3
            ON s2.basket_no = s3.basket_no
        INNER JOIN item_mst s4
            ON s1.item = s4.item
WHERE
    s1.cust_id = 'c_369N'
AND s1.purchase_date = '18-10-06'
)
,rec (
    purchase_date
    , cust_id
    , seq
    , item
    , basket_no
    , rn
    , sum_volume
    , sum_weight
    , upper_limit_volume
    , upper_limit_weight
    , cume_volume
    , cume_weight
    , pre_cume_volume
    , pre_cume_weight
    , over_flg
    , grp
) AS (
    SELECT
        purchase_date
        , cust_id
        , seq
        , item
        , basket_no
        , rn
        , sum_volume
        , sum_weight
        , upper_limit_volume
        , upper_limit_weight
        , sum_volume AS cume_volume
        , sum_weight AS cume_weight
        , 0 AS pre_cume_volume
        , 0 AS pre_cume_weight
        , 1 AS over_flg
        , 1 AS grp
    FROM
        src
    WHERE
        rn = 1
    UNION ALL
    SELECT
        s2.purchase_date
        , s2.cust_id
        , s2.seq
        , s2.item
        , s2.basket_no
        , s2.rn
        , s2.sum_volume
        , s2.sum_weight
        , s2.upper_limit_volume
        , s2.upper_limit_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.cume_volume + s2.sum_volume
            ELSE s2.sum_volume
        END AS cume_volume
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.cume_weight + s2.sum_weight
            ELSE s2.sum_weight
        END AS cume_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.pre_cume_volume + s1.sum_volume
            ELSE 0
        END AS pre_cume_volume
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.pre_cume_weight + s1.sum_weight
            ELSE 0
        END AS pre_cume_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN 0
            ELSE 1
        END AS over_flg
        ,s1.grp
        + CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN 0
            ELSE 1
        END AS grp
    FROM
        rec s1
        , src s2
    WHERE
        s1.rn + 1 = s2.rn
    AND s1.purchase_date = s2.purchase_date
    AND s1.cust_id = s2.cust_id
)
SELECT
    rn, sum_volume, sum_weight, upper_limit_volume, upper_limit_weight, cume_volume, cume_weight, pre_cume_volume, pre_cume_weight, over_flg, grp
FROM
    rec
ORDER BY
    rn
;

【rec.sqlの処理結果】

この人が用意することになったダンボールの数は。。。

calc_carton_cnt.sql

WITH src AS(
SELECT
    s1.purchase_date
    , s1.cust_id
    , s1.seq
    , s1.item
    , s2.basket_no
    , row_number() OVER (PARTITION BY s1.purchase_date,s1.cust_id ORDER BY s1.seq) AS rn
    , s4.VOLUME * s1.qty AS sum_volume
    , s4.weight * s1.qty AS sum_weight
    , s3.upper_limit_volume
    , s3.upper_limit_weight
FROM
    sales_trn s1
        INNER JOIN cust_mst s2
            ON s1.cust_id = s2.cust_id
        INNER JOIN basket_mst s3
            ON s2.basket_no = s3.basket_no
        INNER JOIN item_mst s4
            ON s1.item = s4.item
WHERE
    s1.cust_id = 'c_369N'
AND s1.purchase_date = '18-10-06'
)
,rec (
    purchase_date
    , cust_id
    , seq
    , item
    , basket_no
    , rn
    , sum_volume
    , sum_weight
    , upper_limit_volume
    , upper_limit_weight
    , cume_volume
    , cume_weight
    , pre_cume_volume
    , pre_cume_weight
    , over_flg
    , grp
) AS (
    SELECT
        purchase_date
        , cust_id
        , seq
        , item
        , basket_no
        , rn
        , sum_volume
        , sum_weight
        , upper_limit_volume
        , upper_limit_weight
        , sum_volume AS cume_volume
        , sum_weight AS cume_weight
        , 0 AS pre_cume_volume
        , 0 AS pre_cume_weight
        , 1 AS over_flg
        , 1 AS grp
    FROM
        src
    WHERE
        rn = 1
    UNION ALL
    SELECT
        s2.purchase_date
        , s2.cust_id
        , s2.seq
        , s2.item
        , s2.basket_no
        , s2.rn
        , s2.sum_volume
        , s2.sum_weight
        , s2.upper_limit_volume
        , s2.upper_limit_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.cume_volume + s2.sum_volume
            ELSE s2.sum_volume
        END AS cume_volume
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.cume_weight + s2.sum_weight
            ELSE s2.sum_weight
        END AS cume_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.pre_cume_volume + s1.sum_volume
            ELSE 0
        END AS pre_cume_volume
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.pre_cume_weight + s1.sum_weight
            ELSE 0
        END AS pre_cume_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN 0
            ELSE 1
        END AS over_flg
        ,s1.grp
        + CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN 0
            ELSE 1
        END AS grp
    FROM
        rec s1
        , src s2
    WHERE
        s1.rn + 1 = s2.rn
    AND s1.purchase_date = s2.purchase_date
    AND s1.cust_id = s2.cust_id
)
SELECT
    purchase_date
    ,cust_id
    ,COUNT(DISTINCT grp) AS carton_cnt
FROM
    rec
GROUP BY
    purchase_date
    ,cust_id
;

【calc_carton_cnt.sqlの取得結果】

16個でした。

ちなみにこの日もっとも用意したダンボールの数が多かったのは、c_c_I3さんでした。
再帰でないwithのwhere句の部分をs1.purchase_date = '18-10-06'のみにすることとで求められます。

あとがき

再帰、あたまめっちゃつかう。。
model句、match_recoganize句でも書き換えて練習していきたいと思います。

20181007追記(model clause)

model句で書き換えてみたけど、うまいこといかない。。原因調査中。
参考文献にいろいろ調べたので、追記します。

↓create_cumming.sqlでぶっこんだinsert文です。

create_cumming_data.sql

DROP TABLE cumming;

CREATE TABLE cumming (
    rn                   NUMBER
    , sum_volume           NUMBER
    , sum_weight           NUMBER
    , upper_limit_volume   NUMBER
    , upper_limit_weight   NUMBER
)
;

SET DEFINE OFF;
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (1,14,14,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (2,45,49,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (3,322,210,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (4,270,33,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (5,504,198,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (6,280,368,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (7,110,65,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (8,21,21,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (9,184,760,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (10,234,684,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (11,115,410,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (12,180,105,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (13,18,648,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (14,315,305,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (15,58,118,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (16,414,270,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (17,588,132,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (18,225,245,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (19,288,24,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (20,146,4,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (21,140,118,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (22,45,66,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (23,176,108,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (24,261,531,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (25,70,280,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (26,686,644,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (27,9,1,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (28,280,264,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (29,156,462,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (30,72,176,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (31,603,333,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (32,324,189,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (33,90,11,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (34,252,244,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (35,288,704,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (36,117,261,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (37,28,12,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (38,192,82,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (39,56,224,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (40,64,396,973,973);
INSERT INTO cumming (rn,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (41,56,280,973,973);

COMMIT;

model.sql

WITH sub AS (
SELECT
    rn, flg,volume_over_flg,weight_over_flg, upper_limit_volume, sum_volume,cume_volume, upper_limit_weight, sum_weight, cume_weight
FROM
    cumming
MODEL
    DIMENSION BY(rn)
    MEASURES(1 AS flg,0 AS volume_over_flg,0 AS weight_over_flg, upper_limit_volume,sum_volume,0 AS cume_volume,upper_limit_weight,sum_weight,0 AS cume_weight)
    RULES(
    cume_volume[ANY] ORDER BY rn =
    sum_volume[cv()] +
    CASE
        WHEN nvl(cume_volume[cv() - 1], 0) + sum_volume[cv()] <= upper_limit_volume[cv()]
            AND nvl(cume_weight[cv() - 1], 0) + sum_weight[cv()] <= upper_limit_weight[cv()] THEN nvl(cume_volume[cv() - 1], 0)
        ELSE 0
    END
    ,cume_weight[ANY] ORDER BY rn =
    sum_weight[cv()] +
    CASE
        WHEN nvl(cume_volume[cv() - 1], 0) + sum_volume[cv()] <= upper_limit_volume[cv()]
            AND nvl(cume_weight[cv() - 1], 0) + sum_weight[cv()] <= upper_limit_weight[cv()] THEN nvl(cume_weight[cv() - 1], 0)
        ELSE 0
    END
    ,flg[rn > 1] ORDER BY rn = CASE
                                WHEN nvl(cume_volume[cv() - 1], 0) + sum_volume[cv()] <= upper_limit_volume[cv()]
                                    AND nvl(cume_weight[cv() - 1], 0) + sum_weight[cv()] <= upper_limit_weight[cv()] THEN 0
                                ELSE 1
                             END
    ,volume_over_flg[ANY] ORDER BY rn = CASE
                                WHEN nvl(cume_volume[cv() - 1], 0) + sum_volume[cv()] <= upper_limit_volume[cv()] THEN 0
                                ELSE 1
                             END
    ,weight_over_flg[ANY] ORDER BY rn = CASE
                                WHEN nvl(cume_weight[cv() - 1], 0) + sum_weight[cv()] <= upper_limit_weight[cv()] THEN 0
                                ELSE 1
                             END
            )
)
SELECT s1.*,SUM(s1.flg) OVER (ORDER BY rn) AS grp FROM sub s1
;

【model.sqlの取得結果】

選択行があやしすぎる。。
なんでincreされているんだろか。。refreshされていない。

条件判定を逆にしたsqlでも同じようにincreされ、refleshされていない結果

model_reverse.sql

WITH sub AS (
SELECT
    rn, flg,volume_over_flg,weight_over_flg, upper_limit_volume, sum_volume,cume_volume, upper_limit_weight, sum_weight, cume_weight
FROM
    cumming
MODEL
    DIMENSION BY(rn)
    MEASURES(1 AS flg,0 AS volume_over_flg,0 AS weight_over_flg, upper_limit_volume,sum_volume,0 AS cume_volume,upper_limit_weight,sum_weight,0 AS cume_weight)
    RULES(
    cume_volume[ANY] ORDER BY rn =
    sum_volume[cv()] +
    CASE
        WHEN nvl(cume_volume[cv() - 1], 0) + sum_volume[cv()] > upper_limit_volume[cv()]
            OR nvl(cume_weight[cv() - 1], 0) + sum_weight[cv()] > upper_limit_weight[cv()] THEN 0
        ELSE nvl(cume_volume[cv() - 1], 0)
    END
    ,cume_weight[ANY] ORDER BY rn =
    sum_weight[cv()] +
    CASE
        WHEN nvl(cume_volume[cv() - 1], 0) + sum_volume[cv()] > upper_limit_volume[cv()]
            OR nvl(cume_weight[cv() - 1], 0) + sum_weight[cv()] > upper_limit_weight[cv()] THEN 0
        ELSE nvl(cume_weight[cv() - 1], 0)
    END
    ,flg[rn > 1] ORDER BY rn = CASE
                                WHEN nvl(cume_volume[cv() - 1], 0) + sum_volume[cv()] > upper_limit_volume[cv()]
                                    OR nvl(cume_weight[cv() - 1], 0) + sum_weight[cv()] > upper_limit_weight[cv()] THEN 1
                                ELSE 0
                             END
    ,volume_over_flg[ANY] ORDER BY rn = CASE
                                WHEN nvl(cume_volume[cv() - 1], 0) + sum_volume[cv()] <= upper_limit_volume[cv()] THEN 0
                                ELSE 1
                             END
    ,weight_over_flg[ANY] ORDER BY rn = CASE
                                WHEN nvl(cume_weight[cv() - 1], 0) + sum_weight[cv()] <= upper_limit_weight[cv()] THEN 0
                                ELSE 1
                             END
            )
)
SELECT s1.*,SUM(s1.flg) OVER (ORDER BY rn) AS grp FROM sub s1
;

ちなみに、最初の再帰クエリで条件判定を逆にしてみても取得できる結果はおなじ。
条件判定の問題ではなさそう。。モデル句での書き方の問題？？（これがおそらく濃厚。。初めて書いたし。）

pre_rec_reverse.sql

WITH rec (
    rn
    , sum_volume
    , sum_weight
    , upper_limit_volume
    , upper_limit_weight
    , cume_volume
    , cume_weight
    , pre_cume_volume
    , pre_cume_weight
    , over_flg
    , grp
) AS (
    SELECT
        rn
        , sum_volume
        , sum_weight
        , upper_limit_volume
        , upper_limit_weight
        , sum_volume AS cume_volume
        , sum_weight AS cume_weight
        , 0 AS pre_cume_volume
        , 0 AS pre_cume_weight
        , 1 AS over_flg
        , 1 AS grp
    FROM
        cumming
    WHERE
        rn = 1
    UNION ALL
    SELECT
        s2.rn
        , s2.sum_volume
        , s2.sum_weight
        , s2.upper_limit_volume
        , s2.upper_limit_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume > s2.upper_limit_volume
                OR s1.cume_weight + s2.sum_weight > s2.upper_limit_weight THEN s2.sum_volume
            ELSE s1.cume_volume + s2.sum_volume
        END AS cume_volume
        , CASE
            WHEN s1.cume_volume + s2.sum_volume > s2.upper_limit_volume
                OR s1.cume_weight + s2.sum_weight > s2.upper_limit_weight THEN s2.sum_weight
            ELSE s1.cume_weight + s2.sum_weight
        END AS cume_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume > s2.upper_limit_volume
                OR s1.cume_weight + s2.sum_weight > s2.upper_limit_weight THEN 0
            ELSE s1.pre_cume_volume + s1.sum_volume
        END AS pre_cume_volume
        , CASE
            WHEN s1.cume_volume + s2.sum_volume > s2.upper_limit_volume
                OR s1.cume_weight + s2.sum_weight > s2.upper_limit_weight THEN 0
            ELSE s1.pre_cume_weight + s1.sum_weight
        END AS pre_cume_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume > s2.upper_limit_volume
                OR s1.cume_weight + s2.sum_weight > s2.upper_limit_weight THEN 1
            ELSE 0
        END AS over_flg
        ,s1.grp
        + CASE
            WHEN s1.cume_volume + s2.sum_volume > s2.upper_limit_volume
                OR s1.cume_weight + s2.sum_weight > s2.upper_limit_weight THEN 1
            ELSE 0
        END AS grp
    FROM
        rec s1
        , cumming s2
    WHERE
        s1.rn + 1 = s2.rn
)
SELECT
    rn, upper_limit_volume, upper_limit_weight, sum_volume, cume_volume, pre_cume_volume, sum_weight, cume_weight, pre_cume_weight, over_flg, grp
FROM
    rec
ORDER BY
    rn
;

はまっているけど、model句楽しい。。

20181007追記(match_recognize clause)

model句はまったまま、match_recognize句でも書き換えてみました。

今の現場が11gであることが恨めしい。。orz　短すぎて感動した。

単独超過の場合がなかったので、patch.sql投げて出るようにしてみました。

match_recognize.sql

select
    *
from
    cumming
    match_recognize(
                    order by rn
                    measures
                        match_number() as grp--パターン一致ごとに、見つかった順序で1から始まる連番が返されます。
                        ,classifier() as ptn--特定の行に適用されているパターン全体の変数名が返されます。
                    all rows per match--複数行にわたる一致で、一致の行ごとに1つの出力行が生成されます。
                    pattern (alone_exceeded | cume_exceeded+ )--正規表現らしい。。alone_exceededまたはrecurrenceが1回以上の繰り返し
                    define alone_exceeded as sum_volume > upper_limit_volume or sum_weight > upper_limit_weight--単独超過の場合
                    , cume_exceeded as sum(sum_volume) <= upper_limit_volume and sum(sum_weight) <= upper_limit_weight--累積加算超過の場合(pattern句の「+」と相まって漸化的に加算されていくぽい)
                    )
;

【match_recognize.sqlの取得結果】

patch.sql

update cumming set SUM_VOLUME = 1000 where rn = 3;
update cumming set SUM_WEIGHT = 1000 where rn = 23;
commit;

【patch.sql適用後のmatch_recognize.sqlの取得結果】

20181007追記(analyze function)

分析関数でも書き換えてみました。データに商品やバスケットも含めてみました。
また、basketに含まれるitemを一覧で見れるsqlもanalyze function,recursvie,match_recognizeのそれぞれで
作ってみました。modelは明日以降かなー。orz

create_cumming_inc_item.sql

DROP TABLE cumming;

CREATE TABLE cumming (
    rn                   NUMBER
    , basket_no            CHAR(6 BYTE)
    , item                 VARCHAR2(24 BYTE)
    , sum_volume           NUMBER
    , sum_weight           NUMBER
    , upper_limit_volume   NUMBER
    , upper_limit_weight   NUMBER
)
;

SET DEFINE OFF;
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (1,'b_b7VR','p_p_HD',14,14,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (2,'b_b7VR','p_p_1D',45,49,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (3,'b_b7VR','p_QZ2L',322,210,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (4,'b_b7VR','p_pS2H',270,33,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (5,'b_b7VR','p_QZNO',504,198,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (6,'b_b7VR','p_T7SP',280,368,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (7,'b_b7VR','p_HV01',110,65,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (8,'b_b7VR','p_p_HD',21,21,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (9,'b_b7VR','p_0LYE',184,760,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (10,'b_b7VR','p_pA19',234,684,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (11,'b_b7VR','p_W2DH',115,410,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (12,'b_b7VR','p_p9JJ',180,105,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (13,'b_b7VR','p_pZUV',18,648,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (14,'b_b7VR','p_3OSX',315,305,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (15,'b_b7VR','p_WDM1',58,118,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (16,'b_b7VR','p_QZ2L',414,270,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (17,'b_b7VR','p_pDRK',588,132,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (18,'b_b7VR','p_QNV8',225,245,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (19,'b_b7VR','p_pHS1',288,24,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (20,'b_b7VR','p_pHJE',146,4,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (21,'b_b7VR','p_L0MO',140,118,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (22,'b_b7VR','p_6GR3',45,66,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (23,'b_b7VR','p_pS3N',176,108,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (24,'b_b7VR','p_WDM1',261,531,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (25,'b_b7VR','p_4LLB',70,280,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (26,'b_b7VR','p_pWRO',686,644,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (27,'b_b7VR','p_pC4J',9,1,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (28,'b_b7VR','p_OWHZ',280,264,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (29,'b_b7VR','p_p_1Y',156,462,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (30,'b_b7VR','p_GEJ7',72,176,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (31,'b_b7VR','p_pBZO',603,333,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (32,'b_b7VR','p_OASV',324,189,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (33,'b_b7VR','p_pS2H',90,11,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (34,'b_b7VR','p_3OSX',252,244,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (35,'b_b7VR','p_GEJ7',288,704,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (36,'b_b7VR','p_3LOF',117,261,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (37,'b_b7VR','p_pP5A',28,12,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (38,'b_b7VR','p_UVPO',192,82,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (39,'b_b7VR','p_4LLB',56,224,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (40,'b_b7VR','p_p_CY',64,396,973,973);
INSERT INTO cumming (rn,basket_no,item,sum_volume,sum_weight,upper_limit_volume,upper_limit_weight) VALUES (41,'b_b7VR','p_p_CD',56,280,973,973);

commit;

analyze_function_inc_item.sql

WITH src AS (
SELECT
    c1.rn
    , c1.basket_no
    , c1.item
    , c1.sum_volume
    , c1.sum_weight
    , c1.upper_limit_volume
    , c1.upper_limit_weight
    ,SUM(c1.sum_volume) OVER (ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cume_volume
    ,SUM(c1.sum_weight) OVER (ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cume_weight
    ,nvl(SUM(c1.sum_volume) OVER (ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS pre_cume_volume
    ,nvl(SUM(c1.sum_weight) OVER (ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS pre_cume_weight
FROM
    cumming c1
)
,pre_cmb AS (
SELECT
    s1.rn AS start_rn
    ,s2.rn AS end_rn
    ,s1.sum_volume
    ,s2.cume_volume
    ,s1.pre_cume_volume
    ,s1.pre_cume_volume + s1.upper_limit_volume AS range_volume
    ,s1.sum_weight
    ,s2.cume_weight
    ,s1.pre_cume_weight
    ,s1.pre_cume_weight + s1.upper_limit_weight AS range_weight
FROM
    src s1
        LEFT OUTER JOIN src s2
            ON
                s2.cume_volume BETWEEN s1.pre_cume_volume + 1 AND s1.pre_cume_volume + s1.upper_limit_volume
            AND s2.cume_weight BETWEEN s1.pre_cume_weight + 1 AND s1.pre_cume_weight + s1.upper_limit_weight
ORDER BY
    s1.rn
    ,s2.rn
)
,cmb AS (
SELECT
    start_rn ,end_rn, next_rn
FROM
    (SELECT start_rn ,MAX(end_rn) AS end_rn, MAX(end_rn) + 1 AS next_rn FROM pre_cmb GROUP BY start_rn ORDER BY start_rn) sub
START WITH
    start_rn = 1
CONNECT BY
    PRIOR next_rn = start_rn
)
SELECT
    ROWNUM as grp, basket_no, item_cnt, item_liz, sum_volume_liz, summry_volume, upper_limit_volume, sum_weight_liz, summry_weight, upper_limit_weight
FROM
    (
    SELECT
        c1.next_rn
        , MAX(s1.basket_no) AS basket_no
        , regexp_count(LISTAGG(s1.item,',') WITHIN GROUP (ORDER BY s1.rn), ',') + 1 AS item_cnt
        , LISTAGG(s1.item,',') WITHIN GROUP (ORDER BY s1.rn) AS item_liz
        , LISTAGG(lpad(to_char(s1.sum_volume),3,' '),' + ') WITHIN GROUP (ORDER BY s1.rn) AS sum_volume_liz
        ,SUM(s1.sum_volume) AS summry_volume
        , MAX(s1.upper_limit_volume) AS upper_limit_volume
        , LISTAGG(lpad(to_char(s1.sum_weight),3,' '),' + ') WITHIN GROUP (ORDER BY s1.rn) AS sum_weight_liz
        ,SUM(s1.sum_weight) AS summry_weight
        , MAX(s1.upper_limit_weight) AS upper_limit_weight
    --  , listagg(s1.rn,',') within group (order by s1.rn) as rn_liz
    FROM
        cmb c1
            LEFT OUTER JOIN src s1
                ON
                    s1.rn BETWEEN c1.start_rn AND c1.end_rn
    GROUP BY
        c1.next_rn
    )
;

【analyze_function_inc_item.sqlの取得結果】

rec_inc_item.sql

WITH src AS(
SELECT
    s1.purchase_date
    , s1.cust_id
    , s1.seq
    , s1.item
    , s2.basket_no
    , row_number() OVER (PARTITION BY s1.purchase_date,s1.cust_id ORDER BY s1.seq) AS rn
    , s4.VOLUME * s1.qty AS sum_volume
    , s4.weight * s1.qty AS sum_weight
    , s3.upper_limit_volume
    , s3.upper_limit_weight
FROM
    sales_trn s1
        INNER JOIN cust_mst s2
            ON s1.cust_id = s2.cust_id
        INNER JOIN basket_mst s3
            ON s2.basket_no = s3.basket_no
        INNER JOIN item_mst s4
            ON s1.item = s4.item
WHERE
    s1.cust_id = 'c_369N'
AND s1.purchase_date = '18-10-06'
)
,rec (
    purchase_date
    , cust_id
    , seq
    , item
    , basket_no
    , rn
    , sum_volume
    , sum_weight
    , upper_limit_volume
    , upper_limit_weight
    , cume_volume
    , cume_weight
    , pre_cume_volume
    , pre_cume_weight
    , over_flg
    , grp
) AS (
    SELECT
        purchase_date
        , cust_id
        , seq
        , item
        , basket_no
        , rn
        , sum_volume
        , sum_weight
        , upper_limit_volume
        , upper_limit_weight
        , sum_volume AS cume_volume
        , sum_weight AS cume_weight
        , 0 AS pre_cume_volume
        , 0 AS pre_cume_weight
        , 1 AS over_flg
        , 1 AS grp
    FROM
        src
    WHERE
        rn = 1
    UNION ALL
    SELECT
        s2.purchase_date
        , s2.cust_id
        , s2.seq
        , s2.item
        , s2.basket_no
        , s2.rn
        , s2.sum_volume
        , s2.sum_weight
        , s2.upper_limit_volume
        , s2.upper_limit_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.cume_volume + s2.sum_volume
            ELSE s2.sum_volume
        END AS cume_volume
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.cume_weight + s2.sum_weight
            ELSE s2.sum_weight
        END AS cume_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.pre_cume_volume + s1.sum_volume
            ELSE 0
        END AS pre_cume_volume
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.pre_cume_weight + s1.sum_weight
            ELSE 0
        END AS pre_cume_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN 0
            ELSE 1
        END AS over_flg
        ,s1.grp
        + CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN 0
            ELSE 1
        END AS grp
    FROM
        rec s1
        , src s2
    WHERE
        s1.rn + 1 = s2.rn
    AND s1.purchase_date = s2.purchase_date
    AND s1.cust_id = s2.cust_id
)
SELECT
    s1.grp
    ,MIN(s1.basket_no) AS basket_no
    ,regexp_count(LISTAGG(s1.item,',') WITHIN GROUP (ORDER BY s1.rn), ',') + 1 AS item_cnt
    ,LISTAGG(s1.item,',') WITHIN GROUP (ORDER BY s1.rn) AS item_liz
    ,LISTAGG(lpad(s1.sum_volume,3,' '),' + ') WITHIN GROUP (ORDER BY s1.rn) AS sum_volume_liz
    ,SUM(s1.sum_volume) AS summry_volume
    ,MIN(s1.upper_limit_volume) AS upper_limit_volume
    ,LISTAGG(lpad(s1.sum_weight,3,' '),' + ') WITHIN GROUP (ORDER BY s1.rn) AS sum_weight_liz
    ,SUM(s1.sum_weight) AS summry_weight
    ,MIN(s1.upper_limit_weight) AS upper_limit_weight
FROM
    rec s1
GROUP BY
    s1.grp
;

【rec_inc_item.sqlの取得結果】

match_recognize_inc_item.sql

SELECT
    s1.grp
    ,MIN(s1.basket_no) AS basket_no
    ,regexp_count(LISTAGG(s1.item,',') WITHIN GROUP (ORDER BY s1.rn), ',') + 1 AS item_cnt
    ,LISTAGG(s1.item,',') WITHIN GROUP (ORDER BY s1.rn) AS item_liz
    ,LISTAGG(lpad(s1.sum_volume,3,' '),' + ') WITHIN GROUP (ORDER BY s1.rn) AS sum_volume_liz
    ,SUM(s1.sum_volume) AS summry_volume
    ,MIN(s1.upper_limit_volume) AS upper_limit_volume
    ,LISTAGG(lpad(s1.sum_weight,3,' '),' + ') WITHIN GROUP (ORDER BY s1.rn) AS sum_weight_liz
    ,SUM(s1.sum_weight) AS summry_weight
    ,MIN(s1.upper_limit_weight) AS upper_limit_weight
FROM
    cumming
    MATCH_RECOGNIZE(
                    ORDER BY rn
                    MEASURES
                        match_number() AS grp--パターン一致ごとに、見つかった順序で1から始まる連番が返されます。
                        ,classifier() AS ptn--特定の行に適用されているパターン全体の変数名が返されます。
                    ALL ROWS PER MATCH--複数行にわたる一致で、一致の行ごとに1つの出力行が生成されます。
                    PATTERN (alone_exceeded | cume_exceeded+ )--正規表現らしい。。alone_exceededまたはrecurrenceが1回以上の繰り返し
                    DEFINE alone_exceeded AS sum_volume > upper_limit_volume OR sum_weight > upper_limit_weight--単独超過の場合
                    , cume_exceeded AS SUM(sum_volume) <= upper_limit_volume AND SUM(sum_weight) <= upper_limit_weight--累積加算超過の場合(pattern句の「+」と相まって漸化的に加算されていくぽい)
                    ) s1
GROUP BY
    s1.grp
;

【match_recognize_inc_item.sqlの取得結果】

20181010追記

model句の不思議な挙動を解明すべく漁った参考文献を追記しています。
結論はrules句に AUTOMATIC ORDER と書くことで解決しました。rules句の式の評価順序がデフォルトだと
SEQUENTIAL ORDERだそうです。今回のような意図しない結果に遭遇したらAUTOMATIC ORDERと明記してみるといいことがあるかもしれません。式の依存関係が正しい順序で処理されるための呪文です。

いやースッキリした！

model_rev.sql

WITH sub AS (
SELECT
    rn, flg,volume_over_flg,weight_over_flg, upper_limit_volume, sum_volume,cume_volume, upper_limit_weight, sum_weight, cume_weight
FROM
    cumming
MODEL
    DIMENSION BY(rn)
    MEASURES(1 AS flg,0 AS volume_over_flg,0 AS weight_over_flg, upper_limit_volume,sum_volume,0 AS cume_volume,upper_limit_weight,sum_weight,0 AS cume_weight)
    RULES AUTOMATIC ORDER(--ここに「AUTOMATIC ORDER」追記
    cume_weight[ANY] ORDER BY rn =
    sum_weight[cv()] +
    CASE
        WHEN nvl(cume_volume[cv() - 1], 0) + sum_volume[cv()] <= upper_limit_volume[cv()]
            AND nvl(cume_weight[cv() - 1], 0) + sum_weight[cv()] <= upper_limit_weight[cv()] THEN nvl(cume_weight[cv() - 1], 0)
        ELSE 0
    END
    ,cume_volume[ANY] ORDER BY rn =
    sum_volume[cv()] +
    CASE
        WHEN nvl(cume_volume[cv() - 1], 0) + sum_volume[cv()] <= upper_limit_volume[cv()]
            AND nvl(cume_weight[cv() - 1], 0) + sum_weight[cv()] <= upper_limit_weight[cv()] THEN nvl(cume_volume[cv() - 1], 0)
        ELSE 0
    END
    ,flg[rn > 1] ORDER BY rn = CASE
                                WHEN nvl(cume_volume[cv() - 1], 0) + sum_volume[cv()] <= upper_limit_volume[cv()]
                                    AND nvl(cume_weight[cv() - 1], 0) + sum_weight[cv()] <= upper_limit_weight[cv()] THEN 0
                                ELSE 1
                             END
    ,volume_over_flg[ANY] ORDER BY rn = CASE
                                WHEN nvl(cume_volume[cv() - 1], 0) + sum_volume[cv()] <= upper_limit_volume[cv()] THEN 0
                                ELSE 1
                             END
    ,weight_over_flg[ANY] ORDER BY rn = CASE
                                WHEN nvl(cume_weight[cv() - 1], 0) + sum_weight[cv()] <= upper_limit_weight[cv()] THEN 0
                                ELSE 1
                             END
            )
)
SELECT s1.*,SUM(s1.flg) OVER (ORDER BY rn) AS grp FROM sub s1
;

以上、ありがとうざいました。