SQLである列の値が1になったら、新しい番号でグループを採番したい


ある列の値が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]




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

下記の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が使えますが、データ移行などで値が変わってしまいますのでこれらの一意キーに依存した設計はせず、主キーを作ることをおすすめします。

