Oracle SQLにおいて最終値+１の値から連番を振りたい

Question

SQLを軽く本で勉強しただけの初心者です。
簡単なSELECTとUPDATEならばできますが、WHILEやLOOPはなんとなとく意味がわかる程度で、コードを書いたことはありません。

【前提】
■keycode_masterという一つのテーブルを用意し、データが20万件程度入っている。
|メーカー名|品番|管理用コード|採番用|
|:--|:--:|--:|
|AAA|10001|AAA001|1|
|AAA|10002|AAA002|2|
|AAA|10003|AAA003|3|
|AAA|10004|AAA004|4|
|BBB|AB001|BBB001|1|
|BBB|AB002|BBB002|2|
|BBB|AB003|BBB003|3|
|CCC|10001|CCC001|1|

■採番については下記のルールで実施
・採番用のフィールドにはメーカー毎の連番を入力。
・管理用キーコードは採番用のフィールド+メーカー名で生成。

下記のSQLでできた。

MERGE
INTO    "keycode_master" u
USING   (
        SELECT  rowid AS rid,
                ROW_NUMBER() OVER (PARTITION BY メーカー名 ORDER BY 品番) AS rn
        FROM    "keycode_master"
        )
ON      (u.rowid = rid)
WHEN MATCHED THEN
　UPDATE
　SET     採番用 = rn;
　　　　　 管理用キーコード = メーカー名 || lpad(rn,10,'0');

【わからないところ】
■商品追加時は他のマスター(main_master)と品番でマージさせ、
メーカー名と品番をkeycode_masterにINSERTさせている。
★その際に自動で採番用の値+１からNULLの分だけ、自動採番をさせたい。

下図だと、メーカー名AAAには5と6を
メーカー名CCCには2を入れたい

|メーカー名|品番|管理用コード|採番用|
|:--|:--:|--:|
|AAA|10001|AAA001|1|
|AAA|10002|AAA002|2|
|AAA|10003|AAA003|3|
|AAA|10004|AAA004|4|
|AAA|10005||
|AAA|10006||
|BBB|AB001|BBB001|1|
|BBB|AB002|BBB002|2|
|BBB|AB003|BBB003|3|
|CCC|10001|CCC001|1|
|CCC|10002||

【聞きたいこと】
どのようなロジックで組めばいいか、まったくわかりませんので、
考えられるパターンを教えてください。

組めるかはわかりませんが、考えられるロジックとしては、
メーカー名毎に最終の値と、NULLの値を取得して、
NULLの数だけ最終の値+１をしていくというループをしていく？
その場合は、CASEでメーカー名を指定する？

です。
よろしくお願いいたします。

Answer 1

こういう感じですか？

update
    keycode_master m
set
    order_no = (select count(*) + 1 from (
        select
            *
        from
            keycode_master
    ) t
    where
        maker_name = m.maker_name
        and
        prd_number < m.prd_number
    )
where
    order_no is null
;

ちょっと手元にMySQLしかないのでOracleだったら以下でも通るかもしれません。

update
    keycode_master m
set
    order_no = (select count(*) + 1 from keycode_master
    where
        maker_name = m.maker_name
        and
        prd_number < m.prd_number
    )
where
    order_no is null
;

Comments

[hatsuk0i1216]

ご回答ありがとうございます。
想像通りの採番ができました！

一点不明なのは、prd_number < m.prd_numberの不等号なのですが、
prd_numberは品番の列のことをさしていますよね？
品番は数値だけだったり、小数点もあったり、英語+数字だったり、バラバラなんですが、
それでも不等号で実行できて、連番がふれたのがよく理解できません、、、

[sazi]

部品に欠番が無いなら大丈夫ですけど、あるなら駄目ですね。

[denzow]

sazi様がおっしゃるように採番用列が欠番してない前提ですね。
prd_numberは文字列の場合、辞書順なので同じメーカ内でprd_numberが辞書順でみて
大きくなるのであれば大丈夫です。

自身の行よりも若い行の数を取得して、それの+1を採番するというSQLでした。

Answer 2

SQLクエリで実施する方法を回答します。

質問文には書いていないですが、メーカー名と品番を主キーとしますね。
最終的な目標は以下のようになると考えます。

メーカー名	品番	管理用コード	採番
AAA	10005	5
AAA	10006	6
CCC	10002	2

まず、元の表から、更新前の採番値の最大値を列に連結するクエリを作ります。
max() over (partition by 条件)で作れます。

select
    maker_name
    ,hinban
    ,kanri_code
    ,saiban
    ,max(saiban) over (partition by  maker_name) max_saiban
from keycode_master tbl1
order by maker_name, hinban
;

MAK HINBA KANRI_     SAIBAN MAX_SAIBAN
--- ----- ------ ---------- ----------
AAA 10001 AAA001          1          4
AAA 10002 AAA002          2          4
AAA 10003 AAA003          3          4
AAA 10004 AAA004          4          4
AAA 10005                            4
AAA 10006                            4
BBB AB001 BBB001          1          3
BBB AB002 BBB002          2          3
BBB AB003 BBB003          3          3
CCC 10001 CCC001          1          1
CCC 10002                            1

11行が選択されました。 

MAX_SAIBAN に各メーカーごとの現時点での最大値が出てきました。

次に、この表を基に採番がNULLのレコードに対し、row_numberを最大値に加えたNEW_SAIBAN列を作成します。

with sub1 as (
select
    maker_name
    ,hinban
    ,kanri_code
    ,saiban
    ,max(saiban) over (partition by  maker_name) max_saiban
from keycode_master tbl1
order by maker_name, hinban
)
select
    maker_name
    ,hinban
    ,kanri_code
    ,saiban
    ,row_number() over (partition by maker_name order by maker_name, hinban) row_num_by_maker
    ,max_saiban + row_number() over (partition by maker_name order by maker_name, hinban) new_saiban
from sub1
    where sub1.saiban is null
    ;


MAK HINBA KANRI_     SAIBAN ROW_NUM_BY_MAKER NEW_SAIBAN
--- ----- ------ ---------- ---------------- ----------
AAA 10005                                  1          5
AAA 10006                                  2          6
CCC 10002                                  1          2

withで作った一時表のsub1は上のクエリと同一です。

最後に、この表とメーカー名、品番を連結してNEW_SAIBANを元表のSAIBANに更新するUPDATE文を作成します。

update keycode_master upd
set saiban = (
    with sub1 as (
        select
            maker_name
            ,hinban
            ,kanri_code
            ,saiban
            ,max(saiban) over (partition by  maker_name) max_saiban
        from keycode_master tbl1
        order by maker_name, hinban
        ),sub2 as (
            select
                maker_name
                ,hinban
                ,kanri_code
                ,saiban
                ,max_saiban + row_number() over (partition by maker_name order by maker_name, hinban) new_saiban
            from sub1
            where saiban is null
        )
        select
            new_saiban
        from sub2
        where upd.maker_name = sub2.maker_name
            and upd.hinban = sub2.hinban
            and sub2.saiban is null
    ) 
where saiban is null
;

最後に全体をSELECTしてみましょう。

select * from keycode_master;

MAK HINBA KANRI_     SAIBAN
--- ----- ------ ----------
AAA 10001 AAA001          1
AAA 10002 AAA002          2
AAA 10003 AAA003          3
AAA 10004 AAA004          4
AAA 10005                 5
AAA 10006                 6
BBB AB001 BBB001          1
BBB AB002 BBB002          2
BBB AB003 BBB003          3
CCC 10001 CCC001          1
CCC 10002                 2

11行が選択されました。 

以上のような手順で、作りたいデータを順番に生成していくとそんなに難しくないよ！ということです。

---

まあPL/SQLでループ回して書いたほうが遥かに簡単なんですけどね。

Comments

[hatsuk0i1216]

ご回答ありがとうございました！
無事に思い描いた通りの値を得ることができました。
またわかりやすく経過まで書いていただき、理解を深めることができました。

WITH句についてはまったく触れたことがなかったのですが、
まずは最大値を取得してその後最大値+NULLの行数を取得してSETするというロジックということは理解できました。

[sazi]

部品に欠番が無いなら大丈夫ですけど、あるなら駄目ですね。
あ、こっちじゃなかった、すみません。（汗）