SQLと串刺し処理と副問い合わせ

SQLの基本命令selectの使い方を説明したけど、 複数の表を組合せた処理の記述の説明が不十分だったので、 串刺しに相当する処理を説明

SQLの直積と串刺し処理

データベースの処理の例として、学生と科目の成績表を使って説明。

まずは、以下の様な表データがあったとする。

User(学生のテーブル)
uid  | name | ...
-----+------+------
1000 | 斉藤 | ...
1001 | 青山 | ...
1002 | 坂本 | ...

Subject(科目名のテーブル)
sid  | name | 単位...
-----+------+------
2000 | データベース ...
2001 | 数学 ...
2002 | 情報構造論

Result(成績表)
uid  | sid  | point
-----+------+-----
1000 | 2000 | 75
1001 | 2000 | 80
1002 | 2001 | 90

このデータの中から、80点以上の情報が欲しければ、SQL や C言語で書くなら、 以下のようになるであろう。

(( SQL ))
select Result.point
    from Result
    where Result.point >= 80 ;

(( C言語 ))
for( i = 0  ; < Result.length ; i++ )
    if ( Result[ i ].point >= 80 )
        printf( "%d¥n" , Result[ i ].point ) ;

しかし、点数ではなく、氏名や科目名が欲しいのなら、C言語なら以下の様な処理を記述することになる。

// C言語で複数の表を串刺し
for( i = 0  ; i < Result.length ; i++ )
    if ( Result[ i ].point >= 80 ) {
        // 対応する名前を探す
        for( j = 0 ; j < User.length ; j++ )
            if ( User[j].uid == Result[i].uid )
                break ;
        // 対応する科目名を探す
        for( k = 0 ; k < Subject.length ; k++ )
            if ( Subject[k].sid == Result[i].sid )
                break ;
        printf( "%s %s¥n" , User[j].name , Subject[k].name ) ;
    }

この場合、if文の処理回数は、Result.length回 + (1/2)*User.length回 + (1/2)*Subject.length回となるであろう。 (1/2)は、単純検索の平均回数の意味。

この処理を SQL で行う場合は、以下のようになるであろう。

(( SQLでの串刺し ))
select User.name , Subject.name
    from User , Subject , Result
    where User.uid = Result.uid
          and Subject.sid = Result.sid
          and Result.point >= 80 ;
-- 2つのand節が重要 --

この問い合わせ文では、"from User,Subject,Result"が直積で、すべての組合せを行うという イメージで捉えると、以下の様なC言語のイメージを持つであろう。

for( i = 0 ; i < Result.length ; i++ )
   for( j = 0 ; j < User.length ; j++ )
      for( k = 0 ; k < Subject.length ; k++ )
         if ( User[j].uid == Result[i].uid
            && Subject[k].sid == Result[i].sid
            && Result[i].point >= 80 ) {
            printf( "%s %s¥n",User[j].name,Subject[k].name ) ;
         }

このプログラムでは、ループ回数は、Result.length * User.length * Subject.length であり、 これだけをみると、処理効率が悪いように見える。 しかし、データベースシステムは、UserやSubjectの列の検索で、uid,sidの値から、 ハッシュ法やB木を使って探すかもしれない。 そうすれば、串刺し検索の処理も必ずしも効率が悪いとは言えないし、 そういった部分はデータベースシステムに任せ、その他のプログラムを効率よく書けば生産性が高いはず。

where節で使える特殊な比較命令

where節で使える比較命令には、大小比較やand,or 以外にも、以下の様な条件も記述できる。

((集合計算))
where 式 in ( 値1,値2, ... ) ;

((範囲計算))
where 式 between 値1 and 値2 ;

((空判定))
where 式 is null ;

((文字列パターンマッチ))
where 文字列 like 'c%t' ;
  _ 1文字の任意文字  c_t = cat ◯ , chart ×
  % 0文字以上の任意文字 c%t = cat ◯ , chart ◯

自己結合

from による直積では、異なる表のすべての組合せを簡単に実現できるが、 1つの表の組合せはどうであろうか?このためのfrom節の書き方に自己結合がある。

例えば、前例のUserの中で、同姓同名の人がいるかどうかを探したい場合は、自己結合で以下のように記述する。

select U1.name
    from  U1 User , U2 User
    where U1.name = U2.name
          and U1.uid <> U2.uid ;

副問い合わせ命令

SQLの問い合わせで便利なのが副問い合わせ命令であろう。 特殊な相関副問い合わせを考えないのであれば、 () の中に記述された select 文を先に実行すると考えればいいであろう。

(( 斉藤の80点以上を出力 ))
select User.name
    from  User
    where User.uid in ( select Result.uid
                         from  Result
                         where Result.point >= 80 )

この副問い合わせでは、() の中の問い合わせを先に実行すると、1001,1002 が求まり、 where User.uid in ( 1001 , 1002 ) という条件となり、最終的にその名前が求まる。

 

2015年12月

    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31    

アーカイブ

Google

このブログ記事について

このページは、T-Saitohが2015年10月26日 10:51に書いたブログ記事です。

ひとつ前のブログ記事は「構造体のワード境界」です。

次のブログ記事は「2分木の応用(構文木と決定木)」です。

最近のコンテンツはインデックスページで見られます。過去に書かれたものはアーカイブのページで見られます。