SQLアタマアカデミー

第1回 連番の特性を利用してデータ操作をもっと自由に SQLで連番を扱う (2)行集合に連番を割り当てる

この記事を読むのに必要な時間:およそ 3 分

行集合に連番を割り当てる

主キーが1つの場合

まずは行に連番を割り振ることから始めましょう。表1のような,学生の体重を保持する簡単なテーブルを例に使います。

表1 Weights

student_id(学生ID)Weight(体重kg)
A10050
A10155
A12455
B34360
B34672
C56372
C34572

学生ID順に連番を振ることを考えます。ROW_NUMBER関数が使える環境なら,リスト1のように簡単に記述でき,図1の実行結果が得られます。

リスト1 主キーが1列の場合(ROW_NUMBER)

SELECT student_id,
       ROW_NUMBER() OVER (ORDER BY student_id) AS seq
    FROM Weights;

図1 リスト1,2の実行結果

student_id   seq
-----------   ----
A100           1
A101           2
A124           3
B343           4
B346           5
C345           6
C563           7

PostgreSQLやMySQLといったROW_NUMBER関数が使えない実装なら,相関サブクエリで置き換えましょうリスト2)。このサブクエリは,ノイマン型の再帰集合を作り,その要素数をCOUNT関数で数えているのです注2)。主キーであるsutdent_idを比較キーに使用しているため,再帰集合は要素が1つずつ増えていくことが保証されます。それを連番の生成に利用しているのがこのトリックの要諦です。

リスト2 主キーが1列の場合(相関サブクエリ)

SELECT student_id,
       (SELECT COUNT(*)
          FROM Weights W2
         WHERE W2.student_id <= W1.student_id) AS seq →ROW_NUMBER を相関サブクエリで代用
  FROM Weights W1;
注2)
gihyo.jp連載「SQLアタマ養成講座」の第5回SQL流行間比較(1)はじめにを参照。再帰集合のイメージは,入れ子集合の図を見るとわかりやすいでしょう。

主キーが2つ以上の場合

さて,それではちょっとテーブルを改変して,主キーを2つの複合キーにしてみましょう表2)。今度は「クラス,学生ID」で一意になります。こういうケースでも連番を割り振れるよう一般化しましょう。

表2 Weights2

class(クラス)student_id(学生ID)weight(体重kg)
110050
110155
110256
210060
210172
210273
210373

まず,ROW_NUMBERを使う場合には,特に悩む必要はありません。ORDER BYのキーに追加しましょうリスト3)。実行結果は図2です。

リスト3 主キーが複数列の場合(ROW_NUMBER)

SELECT class, student_id,
       ROW_NUMBER() OVER (ORDER BY class, student_id) AS seq
  FROM Weights2;

図2 リスト3,4の実行結果

class  student_id seq
----- ------------ ---
    1  100         1
    1  101         2
    1  102         3
    2  100         4
    2  101         5
    2  102         6
    2  103         7

一方,相関サブクエリの場合はどうでしょう。いくつか方法があるのですが,私が最もシンプルと思うのは,複合キーを文字列とみなして連結し,強引に1列のキーに作り変えてしまうことです。そうすれば,あとは先ほどと原理は同じですリスト4)。

リスト4 主キーが複数列の場合(相関サブクエリ:文字列結合して1 列とみなす)

SELECT class, student_id,
       (SELECT COUNT(*)
          FROM Weights2 W2
         WHERE (W2.class || W2.student_id) <= (W1.class || W1.student_id) ) AS seq
  FROM Weights2 W1;

このとき注意が必要なのは,キーが数値や日付など文字型以外のデータ型の場合は,結合の前に文字型へのキャストが必要なことです。このときにデータの桁数や書式を統一しておくことが無用の混乱を避けるうえでも重要です(特に数値の場合は,きっちり前ゼロを埋める形式にしないとうまく連番が並びません)。あとは,キーが3列以上になってもまったく同じように拡張できます。

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書:『達人に学ぶ SQL徹底指南書』(翔泳社、2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社、2007)

SQLアタマアカデミー:サポートページ

コメント

コメントの記入