

ある列の値が1になったら、新しい番号でグループを採番したいのですが、  
SQLを用いて実装できるかご存じでしょうか。

例えば、Aのようなデータがあったときに、Bのようなデータを作成したいです。

lag、first_valueのようなウィンドウ関数を用いてもうまくいかないので、  
ならばpl/sqlかなと思っていますが、実現可能な実装方法が見つかりません。

【データA】

[code]

    列1 列2
    A   1
    A   2
    A   3
    A   1
    A   2
    A   3
    
[/code]

【データB】

[code]

    列1 列2 列3
    A   1   1
    A   2   1
    A   3   1
    A   1   2
    A   2   2
    A   3   2
    
[/code]


CC BY-SA 4.0
2021-03-06T14:29:33.320
74461
2021-03-07T03:04:07.513
2021-03-07T00:35:25.437
19110
44223
question
sql
SQLである列の値が1になったら、新しい番号でグループを採番したい


主キーや更新日時などで順序を保証しているならば、副問い合わせで実現できます。

下記のSQLはOracleで更新日時順(updatカラム)に並べ、piyoカラムが1になる度に採番をカウントアップするサンプルコードです。

**SQL**

[code]

    select src.*,
          (select nvl(sum(1), 0)
           from hoge tmp
           -- where tmp.rowid <= src.rowid -- ROWIDで無理矢理採番
           where tmp.updat <= src.updat  -- 更新日時順で採番
           and   tmp.piyo = 1) group_value
    from   hoge src
    order by updat;
    
[/code]

**構成**

[code]

    create table hoge (
      fuga char(1),
      piyo number(1),
      updat date
    );
    
    insert into hoge values ('A', 1, sysdate);
    insert into hoge values ('A', 2, sysdate + 1);
    insert into hoge values ('A', 3, sysdate + 2);
    insert into hoge values ('A', 1, sysdate + 3);
    insert into hoge values ('A', 2, sysdate + 4);
    insert into hoge values ('A', 3, sysdate + 5);
    
[/code]

[SQL Fiddle](http://sqlfiddle.com/#!4/22c1f6/11)

なお行の順番を指定していない場合はSQLで取得する行の順序が保証されていません。  
DBエンジンが発行する一意キーとしてOracleやSQLiteではrowidが、postgresqlではctidが使えますが、データ移行などで値が変わってしまいますのでこれらの一意キーに依存した設計はせず、主キーを作ることをおすすめします。


CC BY-SA 4.0
2021-03-06T23:23:22.037
74465
2021-03-06T23:23:22.037
9820
74461
answer


### 考え方

「ある列」の値が1ならば1、それ以外なら0を列Xとして、先頭行から当該行までの列Xの合計を求めて「新しい番号」とする方法です。

### SQLの例

[code]

    with TMPTBL(NO, C1, C2, X) as (
        select
                NO, C1, C2, case C2 when 1 THEN 1 else 0 end as X 
        from TARTGET_TABLE
    )
    select
        NO, C1, C2, sum(X) over(order by NO rows between unbounded preceding and current row) as 新しい番号
    from TMPTBL
    order by NO;
    
[/code]

対象のテーブルはTARTGET_TABLEで、列名はC1とC2です。「ある列」はC2です。  
ソートのキーとなる列NOを加えています。  
unbounded precedingは先頭行  
current rowは現在行です。

### 実行例

[code]

    with TARTGET_TABLE(NO, C1, C2) as (
        select 1, 'A', 1 union all
        select 2, 'A', 2 union all
        select 3, 'A', 3 union all
        select 4, 'A', 1 union all
        select 5, 'A', 2 union all
        select 6, 'A', 3 union all
        select 7, 'A', 4 union all
        select 8, 'A', 1
    )
    , TMPTBL(NO, C1, C2, X) as (
        select
                NO, C1, C2, case C2 when 1 THEN 1 else 0 end as X 
        from TARTGET_TABLE
    )
    select
        NO, C1, C2, sum(X) over(order by NO rows between unbounded preceding and current row) as 新しい番号
    from TMPTBL
    order by NO;
    
    +----+----+----+-----------------+
    | NO | C1 | C2 | 新しい番号      |
    +----+----+----+-----------------+
    |  1 | A  |  1 |               1 |
    |  2 | A  |  2 |               1 |
    |  3 | A  |  3 |               1 |
    |  4 | A  |  1 |               2 |
    |  5 | A  |  2 |               2 |
    |  6 | A  |  3 |               2 |
    |  7 | A  |  4 |               2 |
    |  8 | A  |  1 |               3 |
    +----+----+----+-----------------+
    
[/code]

試したDBMSはMariaDBの10.5.9です。


CC BY-SA 4.0
2021-03-07T03:04:07.513
74468
2021-03-07T03:04:07.513
35558
74461
answer