まえがき

rubyのeach_slice的な動き(ceil関数を使用すればできる)を模倣出来たらと思っていたが、いつものように脱線して、別のものができてしまった。

環境

$sqlplus user01/ORACLE_PWD@ORCLPDB01

SQL*Plus: Release 19.0.0.0.0 - Production on 土 12月 7 14:36:55 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

最終正常ログイン時間: 土 12月 07 2019 11:20:25 +09:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
に接続されました。
USER01@ORCLPDB01>

スクリプト

引数にグループ化したコレクションと上限件数を与えて、上限件数を超えない範囲でグループ化した結果をコレクションで返却するファンクション。

グループ化したコレクションは以下。

sort_keyには日付項目を元に付番した一意の値を与える。
dtmにはその日付項目を与える。
cntにはその日付項目ごとの取得件数を与える。

日付型はよしなに変える。ここではdate型でやってみた。

create or replace type mymap is object (sort_key number,cnt number,dtm date);
/

create or replace type mymap_liz is table of mymap;
/

返却するコレクションのインターフェースは以下。

日付型はよしなに変える。ここではdate型でやってみた。

create or replace type grzz is object (
    sort_key          number
    , cnt          number
    , cum_cnt      number
    , over_flg     number
    , grp          number
    , mn_sort_key       number
    , mx_sort_key       number
    , mn_cnt       number
    , mx_cnt       number
    , mn_dtm       date
    , mx_dtm       date
    , mn_cum_cnt   number
    , mx_cum_cnt   number
);
/

create or replace type grp_liz is table of grzz;
/

表題のファンクション。

model句のrules句では前回の蓄積結果に自身の件数を足して上限を超える、ないしは自身の件数自体が上限を超える場合は、
自身の件数自体を返却。超えない場合は加算。といったロジックを実装。超過フラグのロジックも同様。

measures句は先頭行の値を設定するイメージ。

model句では分析関数を使用できるので、ランニング集計までrules句に組み込んだ。分析関数を使用する際の左辺の参照セルはanyにしておく。（先頭行も含めてランニング集計したいため。）それ以外は2行目以降に参照セルを限定し、ロジックを組込む。

create or replace function each_slice(tgt mymap_liz,upper_limit_cnt number)
return grp_liz
as
rt grp_liz;
begin
select
    cast(collect(grzz(sort_key, cnt, cum_cnt, over_flg, grp, mn_sort_key, mx_sort_key, mn_cnt, mx_cnt, mn_dtm, mx_dtm, mn_cum_cnt, mx_cum_cnt)) as grp_liz)
    into rt
from(
    select
        sort_key, cnt, cum_cnt, over_flg, grp
        ,min(sort_key)over(partition by grp) as mn_sort_key
        ,max(sort_key)over(partition by grp) as mx_sort_key
        ,min(cnt)over(partition by grp) as mn_cnt
        ,max(cnt)over(partition by grp) as mx_cnt
        ,min(dtm)over(partition by grp) as mn_dtm
        ,max(dtm)over(partition by grp) as mx_dtm
        ,min(cum_cnt)over(partition by grp) as mn_cum_cnt
        ,max(cum_cnt)over(partition by grp) as mx_cum_cnt
    from
        (
        select
            sort_key, dtm,cnt, cum_cnt, over_flg, grp
        from table(tgt)
        model
            dimension by (sort_key)
            measures (dtm as dtm,cnt as cnt,cnt as cum_cnt,1 as over_flg,cast(null as number) as grp)
            rules
            (
                cum_cnt[sort_key>1]=case
                                        when cum_cnt[cv()-1] + cnt[cv()] > upper_limit_cnt or cnt[cv()] > upper_limit_cnt then cnt[cv()]
                                        else cum_cnt[cv()-1] + cnt[cv()]
                                    end
                ,over_flg[sort_key>1]=case
                                        when cum_cnt[cv()-1] + cnt[cv()] > upper_limit_cnt or cnt[cv()] > upper_limit_cnt then 1
                                        else 0
                                    end
                ,grp[any]=sum(over_flg)over(order by sort_key)
            )
        )
    );
    return rt;
end;
/

動作確認

日ごとに集計されたあとのテーブルをdetailサブクエリで模倣しています。
summaryサブクエリではmodel句を使用したグルーピングファンクションに渡す引数を生成しています。
メインクエリでは、detailサブクエリとファンクションの返却結果をunnestしたものをsort_keyでinner joinしています。最後に取得順は保証されないため、sort_keyで並べています。

with detail as(
select
    sort_key
    , rsv_date
    , cnt
from(
    select
        row_number()over(order by to_date(sysdate+level)) as sort_key
        ,to_date(sysdate+level) as rsv_date
        ,trunc(dbms_random.value(1000,10000)) as cnt
    from dual connect by level<=28
    )
),summary as(
select cast(collect(mymap(sort_key,cnt,rsv_date)) as mymap_liz) as tgt from detail
)select
    s2.sort_key
    ,s2.rsv_date
    ,s2.cnt
    ,100000 as upper_limit_cnt
    ,s3.cum_cnt, s3.over_flg, s3.grp, s3.mn_sort_key, s3.mx_sort_key, s3.mn_cnt, s3.mx_cnt, s3.mn_dtm, s3.mx_dtm, s3.mn_cum_cnt, s3.mx_cum_cnt
from summary s1,detail s2,table(each_slice(s1.tgt,100000)) s3
where s2.sort_key=s3.sort_key
order by s3.sort_key
;

あとがき

取込元テーブルから取込先テーブルに取込件数を動的に変化させたいとき、取込上限件数を取込元テーブルごとに管理していれば、ハンディに制御できるかも。送信元テーブルから送信ファイルを生成するときの送信件数を動的に変化させたい場合も同じ。送信上限件数を送信元テーブルごとに管理していれば、handyかも。

脱線したけど、model句の練習になった。普段model句使わないけど、こういうロジックは組みやすい。^^

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

20191213追記

グルーピング後なら、最小と最大を求められるので、引数と戻り値のコレクションから不要項目を除去。

もっとシンプルに。

create or replace type args_map is object (sort_key number,cnt number);
/

create or replace type args_map_liz is table of args_map;
/

create or replace type rt_map is object (
    sort_key       number
    , cnt          number
    , cum_cnt      number
    , over_flg     number
    , grp          number
    , mn_cum_cnt   number
    , mx_cum_cnt   number
);
/

create or replace type rt_map_liz is table of rt_map;
/

create or replace function each_slice(tgt args_map_liz,upper_limit_cnt number)
return rt_map_liz
as
rt rt_map_liz;
begin
select
    cast(collect(rt_map(sort_key, cnt, cum_cnt, over_flg, grp, mn_cum_cnt, mx_cum_cnt)) as rt_map_liz)
    into rt
from(
    select
        sort_key, cnt, cum_cnt, over_flg, grp
        ,min(cum_cnt)over(partition by grp) as mn_cum_cnt
        ,max(cum_cnt)over(partition by grp) as mx_cum_cnt
    from
        (
        select
            sort_key,cnt, cum_cnt, over_flg, grp
        from table(tgt)
        model
            dimension by (sort_key)
            measures (cnt,cnt as cum_cnt,1 as over_flg,cast(null as number) as grp)
            rules
            (
                cum_cnt[sort_key>1]=case
                                        when cum_cnt[cv()-1] + cnt[cv()] > upper_limit_cnt or cnt[cv()] > upper_limit_cnt then cnt[cv()]
                                        else cum_cnt[cv()-1] + cnt[cv()]
                                    end
                ,over_flg[sort_key>1]=case
                                        when cum_cnt[cv()-1] + cnt[cv()] > upper_limit_cnt or cnt[cv()] > upper_limit_cnt then 1
                                        else 0
                                    end
                ,grp[any]=sum(over_flg)over(order by sort_key)
            )
        )
    );
    return rt;
end;
/

使用感

with detail as(
select
    sort_key
    , rsv_date
    , cnt
from(
    select
        row_number()over(order by to_date(sysdate+level)) as sort_key
        ,to_date(sysdate+level) as rsv_date
        ,trunc(dbms_random.value(1000,10000)) as cnt
    from dual connect by level<=28
    )
),summary as(
select cast(collect(args_map(sort_key,cnt)) as args_map_liz) as tgt from detail
)
--select * from summary;
select
    s2.sort_key
    ,s2.rsv_date
    ,min(s2.rsv_date)over(partition by s3.grp) as mn_dtm
    ,max(s2.rsv_date)over(partition by s3.grp) as mx_dtm
    ,min(s2.sort_key)over(partition by s3.grp) as mn_sort_key
    ,max(s2.sort_key)over(partition by s3.grp) as mx_sort_key
    ,min(s2.cnt)over(partition by s3.grp) as mn_cnt
    ,max(s2.cnt)over(partition by s3.grp) as mx_cnt
    ,s2.cnt
    ,100000 as upper_limit_cnt
    ,s3.cum_cnt, s3.over_flg, s3.grp,s3.mn_cum_cnt, s3.mx_cum_cnt
from summary s1,detail s2,table(each_slice(s1.tgt,100000)) s3
where s2.sort_key=s3.sort_key
order by s3.sort_key
;

アイテムごとにも対応できるようにもう一つ作成した。複数項目ごとの場合は文字列連結して渡せばOK。

create or replace type args_map_byitem is object (sort_key number,cnt number,item varchar2(4000));
/

create or replace type args_map_byitem_liz is table of args_map_byitem;
/

create or replace type rt_map_byitem is object (
    sort_key       number
    , cnt          number
    , item         varchar2(4000)
    , cum_cnt      number
    , over_flg     number
    , grp          number
    , mn_cum_cnt   number
    , mx_cum_cnt   number
);
/

create or replace type rt_map_byitem_liz is table of rt_map_byitem;
/

create or replace function each_slice_byitem(tgt args_map_byitem_liz,upper_limit_cnt number)
return rt_map_byitem_liz
as
rt rt_map_byitem_liz;
begin
select
    cast(collect(rt_map_byitem(sort_key, cnt,item, cum_cnt, over_flg, grp, mn_cum_cnt, mx_cum_cnt)) as rt_map_byitem_liz)
    into rt
from(
    select
        sort_key, cnt,item, cum_cnt, over_flg, grp
        ,min(cum_cnt)over(partition by item,grp) as mn_cum_cnt
        ,max(cum_cnt)over(partition by item,grp) as mx_cum_cnt
    from
        (
        select
            sort_key,cnt,item,cum_cnt, over_flg, grp
        from table(tgt)
        model
            dimension by (sort_key)
            measures (cnt,item,cnt as cum_cnt,1 as over_flg,cast(null as number) as grp)
            rules
            (
                cum_cnt[sort_key>1]=case
                        when item[cv()-1]<>item[cv()] then cnt[cv()]
                        when item[cv()-1]=item[cv()] and cum_cnt[cv()-1] + cnt[cv()] > upper_limit_cnt or cnt[cv()] > upper_limit_cnt then cnt[cv()]
                        when item[cv()-1]=item[cv()] and cum_cnt[cv()-1] + cnt[cv()] <= upper_limit_cnt or cnt[cv()] <= upper_limit_cnt then cum_cnt[cv()-1] + cnt[cv()]
                    end
                ,over_flg[sort_key>1]=case
                        when item[cv()-1]<>item[cv()] then 1
                        when item[cv()-1]=item[cv()] and cum_cnt[cv()-1] + cnt[cv()] > upper_limit_cnt or cnt[cv()] > upper_limit_cnt then 1
                        when item[cv()-1]=item[cv()] and cum_cnt[cv()-1] + cnt[cv()] <= upper_limit_cnt or cnt[cv()] <= upper_limit_cnt then 0
                    end
                ,grp[any]=sum(over_flg)over(partition by item order by sort_key)
            )
        )
    );
    return rt;
end;
/

使用感

drop table test_stock purge;
create table test_stock (
    item          varchar2(8 byte)
    , whs_dtm       date
    , epr_dtm       date
    , stk_qty       number
    , rsv_stk_qty   number
);

REM INSERTING into EXPORT_TABLE
set define off;
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-08','RR-MM-DD'),270,6);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-09','RR-MM-DD'),219,9);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),168,4);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-10','RR-MM-DD'),to_date('20-01-09','RR-MM-DD'),285,2);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-10','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),294,8);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-11','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),175,7);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-08','RR-MM-DD'),195,5);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-09','RR-MM-DD'),297,3);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),185,8);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-10','RR-MM-DD'),to_date('20-01-09','RR-MM-DD'),177,10);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-10','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),267,10);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-11','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),161,8);
commit;

with stock_detail as(
    select
        row_number()over(order by item,whs_dtm,epr_dtm) as sort_key
        ,item
        ,row_number()over(partition by item order by whs_dtm,epr_dtm) as grpseq
        ,whs_dtm
        ,epr_dtm
        ,stk_qty-rsv_stk_qty as enable_cnt
    from
        test_stock
),stock_args as(
    select cast(collect(args_map_byitem(sort_key,enable_cnt,item)) as args_map_byitem_liz) as tgt
    from stock_detail
)
select s2.sort_key, s2.cnt, s2.item, s2.cum_cnt,700 as upper_limit_cnt, s2.over_flg, s2.grp, s2.mn_cum_cnt, s2.mx_cum_cnt
from stock_args s1,table(each_slice_byitem(s1.tgt,700)) s2;

20191222追記

グループ化列の有無の違いを吸収。他DBでも書き換え可能なように再帰with版も。

create or replace type args_grp_each_slice is object (
    grp_key            number
    , grpseq_key       number
    , enable_cnt       number
);
/

create or replace type args_grp_liz_each_slice is table of args_grp_each_slice;
/

create or replace type rt_grp_each_slice is object (
    grp_key            number
    , grpseq_key       number
    , enable_cnt       number
    , cum_cnt          number
    , over_flg         number
    , subgrp_key       number
    , mn_cum_cnt       number
    , mx_cum_cnt       number
);
/

create or replace type rt_grp_liz_each_slice is table of rt_grp_each_slice;
/

create or replace function each_slice(tgt args_grp_liz_each_slice,upper_limit_cnt number)
return rt_grp_liz_each_slice
as
rt rt_grp_liz_each_slice;
begin
select
    cast(collect(rt_grp_each_slice(grp_key,grpseq_key,enable_cnt,cum_cnt, over_flg, subgrp_key, mn_cum_cnt, mx_cum_cnt)) as rt_grp_liz_each_slice)
    into rt
from(
    select
        grp_key,grpseq_key,enable_cnt,cum_cnt, over_flg, subgrp_key
        ,min(cum_cnt)over(partition by grp_key,subgrp_key) as mn_cum_cnt
        ,max(cum_cnt)over(partition by grp_key,subgrp_key) as mx_cum_cnt
    from
        (
        select
            grp_key,grpseq_key,enable_cnt,cum_cnt, over_flg, subgrp_key
        from table(tgt)
        model
            partition by (grp_key)
            dimension by (case when grp_key is null then row_number()over(order by grpseq_key) else grpseq_key end as grpseq_key)
            measures (enable_cnt,enable_cnt as cum_cnt,1 as over_flg,cast(null as number) as subgrp_key)
            rules
            (
                cum_cnt[grpseq_key>1]=case
                        when cum_cnt[cv()-1] + enable_cnt[cv()] > upper_limit_cnt or enable_cnt[cv()] > upper_limit_cnt then enable_cnt[cv()]
                        when cum_cnt[cv()-1] + enable_cnt[cv()] <= upper_limit_cnt or enable_cnt[cv()] <= upper_limit_cnt then cum_cnt[cv()-1] + enable_cnt[cv()]
                    end
                ,over_flg[grpseq_key>1]=case
                        when cum_cnt[cv()-1] + enable_cnt[cv()] > upper_limit_cnt or enable_cnt[cv()] > upper_limit_cnt then 1
                        when cum_cnt[cv()-1] + enable_cnt[cv()] <= upper_limit_cnt or enable_cnt[cv()] <= upper_limit_cnt then 0
                    end
                ,subgrp_key[any]=sum(over_flg)over(partition by grp_key order by grpseq_key)
            )
        )
    );
    return rt;
end;
/


create or replace function each_slice_std(tgt args_grp_liz_each_slice,upper_limit_cnt number)
return rt_grp_liz_each_slice
as
rt rt_grp_liz_each_slice;
begin
    with sub as(
        select
            grp_key
            ,case
                when grp_key is null then row_number()over(order by grpseq_key)
                else grpseq_key
            end as grpseq_key
            ,enable_cnt
        from table(tgt)
    ),rec(grp_key,grpseq_key,enable_cnt,cum_cnt, over_flg) as(
        select grp_key,grpseq_key,enable_cnt,enable_cnt,1 from sub where grpseq_key=1
        union all
        select s2.grp_key,s2.grpseq_key,s2.enable_cnt
            ,case
                when s1.cum_cnt + s2.enable_cnt > upper_limit_cnt or s2.enable_cnt > upper_limit_cnt then s2.enable_cnt
                when s1.cum_cnt + s2.enable_cnt <= upper_limit_cnt or s2.enable_cnt <= upper_limit_cnt then s1.cum_cnt + s2.enable_cnt
            end
            ,case
                when s1.cum_cnt + s2.enable_cnt > upper_limit_cnt or s2.enable_cnt > upper_limit_cnt then 1
                when s1.cum_cnt + s2.enable_cnt <= upper_limit_cnt or s2.enable_cnt <= upper_limit_cnt then 0
            end
        from rec s1,sub s2
        where
            nvl(s1.grp_key,1)=nvl(s2.grp_key,1)
        and s1.grpseq_key+1=s2.grpseq_key
    )
    select
        cast(collect(rt_grp_each_slice(grp_key,grpseq_key,enable_cnt,cum_cnt, over_flg, subgrp_key, mn_cum_cnt, mx_cum_cnt)) as rt_grp_liz_each_slice)
        into rt
    from(
        select
            grp_key
            ,grpseq_key
            ,enable_cnt
            ,cum_cnt
            ,over_flg
            ,subgrp_key
            ,min(cum_cnt)over(partition by grp_key,subgrp_key) as mn_cum_cnt
            ,max(cum_cnt)over(partition by grp_key,subgrp_key) as mx_cum_cnt
        from(
            select
                grp_key
                ,grpseq_key
                ,enable_cnt
                ,cum_cnt
                ,over_flg
                ,sum(over_flg)over(partition by grp_key order by grpseq_key) as subgrp_key
            from(
                select
                    grp_key,grpseq_key,enable_cnt,cum_cnt, over_flg
                from rec
                )
            )
        );
    return rt;
end;
/

drop table test_stock purge;
create table test_stock (
    item          varchar2(8 byte)
    , whs_dtm       date
    , epr_dtm       date
    , stk_qty       number
    , rsv_stk_qty   number
);

set define off;
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-08','RR-MM-DD'),270,6);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-09','RR-MM-DD'),219,9);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),168,4);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-10','RR-MM-DD'),to_date('20-01-09','RR-MM-DD'),285,2);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-10','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),294,8);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-11','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),175,7);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-08','RR-MM-DD'),195,5);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-09','RR-MM-DD'),297,3);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),185,8);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-10','RR-MM-DD'),to_date('20-01-09','RR-MM-DD'),177,10);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-10','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),267,10);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-11','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),161,8);
commit;

動作確認

with stock_detail as(
    select
        dense_rank()over(order by item) as grp_key
        ,dense_rank()over(partition by item order by whs_dtm,epr_dtm) as grpseq_key
        ,item
        ,whs_dtm
        ,epr_dtm
        ,stk_qty-rsv_stk_qty as enable_cnt
    from
        test_stock
),stock_args as(
    select cast(collect(args_grp_each_slice(null,grpseq_key,enable_cnt)) as args_grp_liz_each_slice) as tgt
    from stock_detail
)
select 
    s2.grp_key, s2.grpseq_key, s2.enable_cnt, s2.cum_cnt,700 as upper_limit_cnt, s2.over_flg, s2.subgrp_key, s2.mn_cum_cnt, s2.mx_cum_cnt
from stock_args s1
,table(each_slice(s1.tgt,700)) s2
order by s2.grp_key, s2.grpseq_key
;

with stock_detail as(
    select
        dense_rank()over(order by item) as grp_key
        ,dense_rank()over(partition by item order by whs_dtm,epr_dtm) as grpseq_key
        ,item
        ,whs_dtm
        ,epr_dtm
        ,stk_qty-rsv_stk_qty as enable_cnt
    from
        test_stock
),stock_args as(
    select cast(collect(args_grp_each_slice(grp_key,grpseq_key,enable_cnt)) as args_grp_liz_each_slice) as tgt
    from stock_detail
)
select 
    s2.grp_key, s2.grpseq_key, s2.enable_cnt, s2.cum_cnt,700 as upper_limit_cnt, s2.over_flg, s2.subgrp_key, s2.mn_cum_cnt, s2.mx_cum_cnt
from stock_args s1
,table(each_slice(s1.tgt,700)) s2
order by s2.grp_key, s2.grpseq_key
;

with stock_detail as(
    select
        dense_rank()over(order by item) as grp_key
        ,dense_rank()over(partition by item order by whs_dtm,epr_dtm) as grpseq_key
        ,item
        ,whs_dtm
        ,epr_dtm
        ,stk_qty-rsv_stk_qty as enable_cnt
    from
        test_stock
),stock_args as(
    select cast(collect(args_grp_each_slice(null,grpseq_key,enable_cnt)) as args_grp_liz_each_slice) as tgt
    from stock_detail
)
select 
    s2.grp_key, s2.grpseq_key, s2.enable_cnt, s2.cum_cnt,700 as upper_limit_cnt, s2.over_flg, s2.subgrp_key, s2.mn_cum_cnt, s2.mx_cum_cnt
from stock_args s1
,table(each_slice_std(s1.tgt,700)) s2
order by s2.grp_key, s2.grpseq_key
;

with stock_detail as(
    select
        dense_rank()over(order by item) as grp_key
        ,dense_rank()over(partition by item order by whs_dtm,epr_dtm) as grpseq_key
        ,item
        ,whs_dtm
        ,epr_dtm
        ,stk_qty-rsv_stk_qty as enable_cnt
    from
        test_stock
),stock_args as(
    select cast(collect(args_grp_each_slice(grp_key,grpseq_key,enable_cnt)) as args_grp_liz_each_slice) as tgt
    from stock_detail
)
select 
    s2.grp_key, s2.grpseq_key, s2.enable_cnt, s2.cum_cnt,700 as upper_limit_cnt, s2.over_flg, s2.subgrp_key, s2.mn_cum_cnt, s2.mx_cum_cnt
from stock_args s1
,table(each_slice_std(s1.tgt,700)) s2
order by s2.grp_key, s2.grpseq_key
;