関係データベースがひろくつかわれるようになっているが，SQL によって記述したクエリがどのように実行されているかは不透明である． クエリは通常，DBMS の内部でインタプリタ的に実行される． 機械語にコンパイルせずに実行されるおもな理由は，クエリの対象であるテーブルの性質をみて実行時にクエリを最適化することが重要だからである． しかし，ここではクエリの実行のしくみをみるために，あえてそれを Perl のプログラム (“コンパイル・コード”) でシミュレートしてみる．
目次
- はじめに
- 使用するデータ
- 単純なクエリ
- DISTINCT 演算子つきのクエリ
- WHERE 節つきのクエリ
- AND 演算子, OR 演算子を使用した WHERE 節つきのクエリ
- LIKE 演算子を使用した WHERE 節つきのクエリ
- ORDER BY 節を使用したクエリ
- SUM 関数による値の集約
- COUNT 関数, AVG 関数による値の集約
- COUNT 関数と DISTINCT 演算子をくみあわせたクエリ
- GROUP BY 節を使用したクエリ
- GROUP BY 節と HAVING 節を使用したクエリ
- JOIN 演算子を使用したクエリ
- SELECT が入れ子になったクエリ
- UNION 演算子を使用したクエリ
- UNION ALL 演算子を使用したクエリ
- INTERSECT 演算子をふくむクエリ
- MINUS 演算子をふくむクエリ
はじめに
「SQL 結合」 というサイトには，SQL の文とその実行例がいろいろ書かれている． そこにある例題をすこしだけかえて，Perl に翻訳してみることにする． このページのなかにひととおりのプログラムを収容しているが，全部をつないだ ソースコード もある．
使用するデータ
「SQL 結合」 でつかわれているデータを一部だけ変更したものをしめす． データベースは通常ディスクに格納されるが，ここでは主記憶上にあることを前提とする． データベースを揮発性のメモリにおくことはかんがえられないが，主記憶が不揮発性であれば可能な選択肢である．
my @Store_Information =
({store_name => 'Los Angeles', Sales => 1500, Date => 'Jan-05-1999'},
{store_name => 'San Diego', Sales => 250, Date => 'Jan-07-1999'},
{store_name => 'San Francisco', Sales => 300, Date => 'Jan-08-1999'},
{store_name => 'Los Angeles', Sales => 300, Date => 'Jan-08-1999'},
{store_name => 'Boston', Sales => 700, Date => 'Jan-08-1999'});
my @Geography =
({region_name => 'East', store_name => 'Boston'},
{region_name => 'East', store_name => 'New York'},
{region_name => 'West', store_name => 'Los Angeles'},
{region_name => 'West', store_name => 'San Diego'});
my @Internet_Sales =
({Date => 'Jan-07-1999', Sales => 250},
{Date => 'Jan-10-1999', Sales => 535},
{Date => 'Jan-11-1999', Sales => 320},
{Date => 'Jan-12-1999', Sales => 750});
ここで Store_Information 表は店舗ごとの売り上げの記録，Geography 表は店舗とそれが存在する地域との関係，Internet_Sales 表は 1 日ごとのインターネット上での売り上げの記録である． 表は配列によって表現し，その要素すなわちレコード (tuple) は Perl のハッシュによって表現している．
単純なクエリ
まずもっとも単純な SELECT 文を例とする． もとの SQL の文はつぎのとおりである． この文は Store_Information 表 (ハッシュの配列) から店舗名 (store_name) だけをとりだす．
SELECT store_name FROM Store_Information
これを Perl に翻訳するとつぎのようになる．
sub select_store_name() {
print_result(@Store_Information, ['store_name']);
}
サブルーティン select_store_name は Store_Information 表の各行から 'store_name' というキーをもつ要素だけをとりだして印刷する． print_result はこれ以降も使用する汎用のサブルーティンであり，あたえられたハッシュの配列から指定された (1 個または複数個の) キーをもつ要素だけをとりだして印刷する．
sub print_result(\@$) {
my ($result, $keys) = @_;
foreach my $key (@$keys) {
print "\t$key";
}
print "\n\t----------------\n";
foreach my $record (@$result) {
foreach my $key (@$keys) {
print "\t$record->{$key}";
}
print "\n";
}
}
サブルーティン select_store_name() を実行すれば，つぎのような結果がえられる．
store_name ---------------- Los Angeles San Diego San Francisco Los Angeles Boston
Store_Information 表には “Los Angeles” というなまえの (ことなる) 店舗が 2 つあるため，それが 2 回表示されている．
これ以降の例 (たとえば WHERE 節つきののクエリ) と比較するには，select_store_name をつぎのようにかきかえるとよい．
sub select_store_name() {
my @result = ();
foreach my $record (@Store_Information) { # FROM Store_Information
push(@result, $record);
}
print_result(@result, ['store_name']); # SELECT store_name
}
しかし，追加されたループは実質的な仕事をなにもしていない (配列をコピーしているだけな) ので，これを除去すると最初にしめしたプログラムのようになる．
DISTINCT 演算子つきのクエリ
つぎの例では上記の例と同様に Store_Information 表から店舗名をとりだすが，重複する店舗名を除去する．
SELECT DISTINCT store_name FROM Store_Information
これを Perl に翻訳するとつぎのようになる．
sub select_distinct_store_name() {
my %Distinct_Store_Information = ();
foreach my $record (@Store_Information) { # FROM Store_Information
$Distinct_Store_Information{$record->{store_name}} = $record; # DISTINCT
}
my @result = ();
foreach my $store_name (keys %Distinct_Store_Information) { # DISTINCT
my $record = $Distinct_Store_Information{$store_name}; # DISTINCT
push(@result, $record);
}
print_result(@result, ['store_name']); # SELECT store_name
}
重複した店舗名を排除するためにハッシュ表 %Distinct_Store_Information を使用している． Perl にはハッシュがくみこまれているので，容易に実現することができる． 配列 @Store_Information においては重複していた “Los Angeles” が @result においてはひとつにまとめられる．
select_distinct_store_name() をよびだすと，結果はつぎのように表示される．
store_name ---------------- San Francisco San Diego Los Angeles Boston
すなわち，“Los Angeles” の重複がなくなっている．
WHERE 節つきのクエリ
つぎの例も Store_Information 表から店舗名を抽出するが，販売量が 1000 (ドル) をこえる店舗だけをえらびだす．
SELECT store_name FROM Store_Information WHERE Sales > 1000
これを Perl に翻訳するとつぎのようになる．
sub select_store_name_where() {
my @result = ();
foreach my $record (@Store_Information) { # FROM Store_Information
if ($record->{Sales} > 1000) { # WHERE Sales > 1000
push(@result, $record);
}
}
print_result(@result, ['store_name']); # SELECT store_name
}
このサブルーティンを，ループを追加したサブルーティン select_store_name とくらべると，WHERE 節に対応する条件文がひとつ追加されているだけであることがわかる．
select_store_name_where() をよびだすと，結果はつぎのように表示される．
store_name ---------------- Los Angeles
AND 演算子, OR 演算子を使用した WHERE 節つきのクエリ
前記の例と同様に WHERE 節をつかい，そのなかで演算子 AND や OR を使用した例をしめす．
SELECT store_name FROM Store_Information WHERE Sales > 1000 OR (Sales < 500 AND Sales > 275)
これを Perl に翻訳するとつぎのようになる．
sub select_store_name_and_or() {
my @result = ();
foreach my $record (@Store_Information) { # FROM Store_Information
if ($record->{Sales} > 1000 ||
$record->{Sales} < 500 && $record->{Sales} > 275) {
# WHERE Sales > 1000 OR (Sales < 500 AND Sales > 275)
push(@result, $record);
}
}
print_result(@result, ['store_name']); # SELECT store_name
}
select_store_name_and_or() をよびだすと，結果はつぎのように表示される．
store_name ---------------- Los Angeles San Francisco Los Angeles
IN 演算子を使用した WHERE 節つきのクエリ
前記の例と同様に WHERE 節をつかい，そのなかで演算子 IN を使用した例をしめす．
SELECT *
FROM Store_Information
WHERE store_name IN ('Los Angeles', 'San Diego')
Perl ではつぎのように表現することができる．
sub select_store_name_in() {
my @result = ();
foreach my $record (@Store_Information) { # FROM Store_Information
if (grep ($_ eq $record->{store_name},
'Los Angeles', 'San Diego')) {
# WHERE store_name IN ('Los Angeles', 'San Diego')
push(@result, $record);
}
}
print_result(@result, ['store_name', 'Sales', 'Date']); # SELECT *
}
リストの検索に grep を使用している点と，店舗名とあわせて売り上げや日付をあわせて表示している点をのぞくと，まえの例とほとんどかわらない．
select_store_name_in() をよびだすと，結果はつぎのように表示される．
store_name Sales Date ---------------- Los Angeles 1500 Jan-05-1999 San Diego 250 Jan-07-1999 Los Angeles 300 Jan-08-1999
LIKE 演算子を使用した WHERE 節つきのクエリ
前記の例と同様に WHERE 節をつかい，そのなかで演算子 LIKE を使用した例をしめす．
SELECT * FROM Store_Information WHERE store_name LIKE '%AN%'
LIKE は正規表現をつかって文字列のパターンマッチをする演算子である． これも Perl の得意とする機能なので，容易に表現することができる．
sub select_store_name_like() {
my @result = ();
foreach my $record (@Store_Information) { # FROM Store_Information
if ($record->{store_name} =~ /.*AN.*/i) { # WHERE store_name LIKE '%AN%'
push(@result, $record);
}
}
print_result(@result, ['store_name', 'Sales', 'Date']); # SELECT *
}
select_store_name_like() をよびだすと，結果はつぎのように表示される．
store_name Sales Date ---------------- Los Angeles 1500 Jan-05-1999 San Diego 250 Jan-07-1999 San Francisco 300 Jan-08-1999 Los Angeles 300 Jan-08-1999
ORDER BY 節を使用したクエリ
売り上げがおおい順に結果を整列するには，つぎのように ORDER BY 節を使用する．
SELECT store_name, Sales, Date FROM Store_Information ORDER BY Sales
Perl ではつぎのように表現することができる．
sub select_store_name_order_by() {
my @result = ();
@result = sort {$b->{Sales} <=> $a->{Sales}} @Store_Information; # ORDER BY Sales
print_result(@result, ['store_name', 'Sales', 'Date']);
# SELECT store_name, Sales, Date
}
すなわち，sort をつかって整列してから表示すればよい．
select_store_name_order_by() をよびだすと，結果はつぎのように表示される．
store_name Sales Date ---------------- Los Angeles 1500 Jan-05-1999 Boston 700 Jan-08-1999 San Francisco 300 Jan-08-1999 Los Angeles 300 Jan-08-1999 San Diego 250 Jan-07-1999
SUM 関数による値の集約
ここまでの例には複数のレコードをつかった計算がふくまれなかったが，つぎの例では関数 SUM をつかって売り上げの合計をもとめる．
SELECT SUM(Sales) FROM Store_Information
Perl ではつぎのように表現することができる．
sub select_sales_sum() {
my $SUM_Sales = 0;
foreach my $record (@Store_Information) { # FROM Store_Information
$SUM_Sales += $record->{Sales};
}
print "\tSUM(Sales)\n";
print "\t$SUM_Sales\n";
}
select_sales_sum() をよびだすと，結果はつぎのように表示される．
SUM(Sales) 3050
COUNT 関数, AVG 関数による値の集約
SUM と同様の例だが，レコード数を COUNT 関数によってかぞえ，平均値 (AVG 関数) を計算する例をしめす．
SELECT COUNT(store_name), AVG(Sales) FROM Store_Information WHERE store_name is not NULL
Perl ではつぎのように表現することができる．
sub select_store_name_count() {
my $COUNT = 0;
my $SUM_Sales = 0;
foreach my $record (@Store_Information) { # FROM Store_Information
if ($record->{store_name}) { # WHERE store_name is not NULL
$COUNT++; # COUNT(store_name)
$SUM_Sales += $record->{Sales};
}
}
print "\tCOUNT(store_name)\tAVG(Sales)\n";
print "\t$COUNT\t", $SUM_Sales / $COUNT, "\n";
}
中間値としては SUM 関数とおなじ値をもとめ，最後にレコード数によってわることによって平均をもとめている．
select_store_name_count() をよびだすと，結果はつぎのように表示される．
COUNT(store_name) AVG(Sales) 5 610
COUNT 関数と DISTINCT 演算子をくみあわせたクエリ
つぎは COUNT と DISTINCT とをくみあわせた例である．
SELECT COUNT(DISTINCT store_name) FROM Store_Information
Perl ではつぎのように表現することができる．
sub select_store_name_count_distinct() {
my %Distinct_Store_Information = (); # DISTINCT
foreach my $record (@Store_Information) { # FROM Store_Information
$Distinct_Store_Information{$record->{store_name}} = $record; # DISTINCT
}
my $COUNT_store_name = 0;
foreach my $store_name (keys %Distinct_Store_Information) { # DISTINCT
$COUNT_store_name++; # COUNT(DISTINCT store_name)
}
print "COUNT(DISTINCT store_name)\n";
print $COUNT_store_name, "\n";
}
select_store_name_count_distinct() をよびだすと，結果はつぎのように表示される．
COUNT(DISTINCT store_name) 4
“Los Angeles” という店舗が 2 つあるので，ここではそれをあわせてひとつとかぞえている．
GROUP BY 節を使用したクエリ
同名の店舗をグループ化して，グループごとの売り上げを表示させる．
SELECT store_name, SUM(Sales) FROM Store_Information GROUP BY store_name
Perl ではつぎのように表現することができる．
sub select_store_name_group_by() {
my %Grouped_Store_Information = ();
foreach my $record (@Store_Information) { # FROM Store_Information
if (!$Grouped_Store_Information{$record->{store_name}}) { # GROUP BY store_name
$Grouped_Store_Information{$record->{store_name}} =
{store_name => $record->{store_name},
SUM_Sales => $record->{Sales}};
} else {
$Grouped_Store_Information{$record->{store_name}}->{SUM_Sales} +=
$record->{Sales};
}
}
my @result = ();
foreach my $store_name (keys %Grouped_Store_Information) {
push(@result, $Grouped_Store_Information{$store_name});
}
print "store_name SUM(Sales)\n";
print_result(@result, ['store_name', 'SUM_Sales']); # SELECT store_name, SUM(Sales)
}
最初のループ内に条件文があるが，その then 側であたらしいレコードをつくっている． これが SUM の計算のための中間値および最終値を保持するためのレコードである． これまでの例においてはこのような値を保持する必要がなかったため，既存のレコードへのポインタを使用すればよく，あたらしいレコード (ハッシュ) を生成する必要はなかった． その条件文の else 側では SUM の中間値の更新だけをおこなっている．
select_store_name_group_by() をよびだすと，結果はつぎのように表示される．
store_name SUM_Sales ---------------- San Francisco 300 San Diego 250 Los Angeles 1800 Boston 700
“Los Angeles” という名の 2 つの店舗の売り上げが加算されているが，それ以外は Store_Information 表にふくまれる値とかわらない．
GROUP BY 節と HAVING 節を使用したクエリ
“GROUP BY” によってグルーピングした結果に条件をつけるため，“HAVING” を使用する．
SELECT store_name, SUM(sales) FROM Store_Information GROUP BY store_name HAVING SUM(sales) > 1500
ここでは，グループの売り上げの和が 1500 をこえるものを表示する．
Perl ではつぎのように表現することができる．
sub select_store_name_having() {
my %Grouped_Store_Information = ();
foreach my $record (@Store_Information) { # FROM Store_Information
if (!$Grouped_Store_Information{$record->{store_name}}) { # GROUP BY store_name
$Grouped_Store_Information{$record->{store_name}} =
{store_name => $record->{store_name},
SUM_Sales => $record->{Sales}};
} else {
$Grouped_Store_Information{$record->{store_name}}->{SUM_Sales} +=
$record->{Sales};
}
}
my @result = ();
foreach my $store_name (keys %Grouped_Store_Information) {
my $record = $Grouped_Store_Information{$store_name};
if ($record->{SUM_Sales} > 1500) { # HAVING SUM(sales) > 1500
push(@result, $record);
}
}
print "store_name SUM(Sales)\n";
print_result(@result, ['store_name', 'SUM_Sales']); # SELECT store_name, SUM(sales)
}
WHERE 節の条件ははじめのループにはいっているが，HAVING 節の条件はグルーピングのあとで適用されるため，あとのループにはいっている．
select_store_name_having() をよびだすと，結果はつぎのように表示される．
store_name SUM_Sales ---------------- Los Angeles 1800
JOIN 演算子を使用したクエリ
JOIN は関係データベースの演算のなかでももっとも重要なものである．
SELECT Geography.region_name, SUM(Store_Information.Sales) FROM Geography, Store_Information WHERE Geography.store_name = Store_Information.store_name GROUP BY Geography.region_name
この例では 2 つの表がともに店舗名をふくんでいるので，それをつかって 2 つの表を結合する． すなわち，地域 (region_name) ごとの売り上げを計算する．
JOIN の実行法にはさまざまあり，その最適化 (効率化) は 1980 年代ころのデータベース研究のもっとも重要なテーマのひとつだった． しかし，ここではひとつの方法だけをとりあげる． hash join といわれる，ハッシュを使用した効率化をおこなった方法である．
sub select_store_geo_join() {
my %Distinct_Geography = ();
foreach my $record (@Geography) { # FROM Geography
push(@{$Distinct_Geography{$record->{store_name}}}, $record); # hash chain
}
my %Grouped = ();
foreach my $record_S (@Store_Information) { # FROM Store_Information
foreach my $record_G (@{$Distinct_Geography{$record_S->{store_name}}}) {
if (!$Grouped{$record_G->{region_name}}) { # GROUP BY region_name
$Grouped{$record_G->{region_name}} =
{region_name => $record_G->{region_name},
Sales => $record_S->{Sales}};
} else {
$Grouped{$record_G->{region_name}}->{Sales} +=
$record_S->{Sales};
}
}
}
my @result = ();
foreach my $region_name (keys %Grouped) {
push(@result, $Grouped{$region_name});
}
print_result(@result, ['region_name', 'Sales']); # SELECT store_name
}
最初のループにおいてハッシュ表をつくっている． “DISTINCT” や “GROUP BY” の例などでもハッシュ表をつかっているが，それらにおいてはハッシュ表のひとつのエントリに対してひとつだけの結果 (SUM の値など) が対応していた． それに対してこの例においてはひとつのエントリが複数のレコードを (配列として) 保持するため，やや複雑になっている． それでも，Perl をつかえば hash join のアルゴリズムをこのようにみじかい行数で表現することができる．
2 つめのループは 2 重ループになっているが，内側のくりかえし回数は通常はすくない． このループ内に条件文があるが，その then 側であたらしいレコードをつくっている． これが 2 つのテーブルのレコードを結合したレコードである． その条件文の else 側ではそのレコードの更新だけをおこなっている．
select_store_geo_join() をよびだすと，結果はつぎのように表示される．
region_name Sales ---------------- West 2050 East 700
SELECT が入れ子になったクエリ
サブクエリをふくむ例，すなわち SELECT が入れ子になった例をしめす．
SELECT SUM(Sales) FROM Store_Information WHERE Store_name IN (SELECT store_name FROM Geography WHERE region_name = 'West')
この文においては西の店舗の売り上げだけを合計する． Perl ではつぎのように表現することができる．
sub select_store_subquery() {
my @work = ();
foreach my $record (@Geography) { # FROM Geography
if ($record->{region_name} eq 'West') { # WHERE region_name = 'West')
push(@work, $record->{store_name}); # SELECT store_name
}
}
my $SUM_Sales = 0;
foreach my $record (@Store_Information) { # FROM Store_Information
if (grep ($_ eq $record->{store_name}, @work)) { # WHERE store_name IN ...
$SUM_Sales += $record->{Sales};
}
}
print "SUM_Sales\n";
print $SUM_Sales, "\n";
}
はじめのループが入れ子の内側の SELECT に対応し，つぎのループが外側の SELECT に対応している．
select_store_subquery() をよびだすと，結果はつぎのように表示される．
SUM_Sales 2050
UNION 演算子を使用したクエリ
複数のクエリの結果の和をとる例である．
SELECT Date FROM Store_Information UNION SELECT Date FROM Internet_Sales
Perl ではつぎのように表現することができる．
sub select_date_union() {
my %work = ();
foreach my $record (@Store_Information) { # FROM Store_Information
$work{$record->{Date}} = 1; # SELECT DISTINCT Date
}
foreach my $record (@Internet_Sales) { # FROM Internet_Sales
$work{$record->{Date}} = 1; # UNION SELECT DISTINCT Date
}
my @result = ();
foreach my $Date (keys %work) {
push(@result, {Date => $Date});
}
print_result(@result, ['Date']);
}
UNION 演算子においては重複した値を排除する必要があるため，ハッシュ表を使用している． ハッシュ表がふくむ値は null ('' または 0) か 1 かのいずれかである． 1 がふくまれている要素だけが UNION の結果にふくまれる．
select_date_union() をよびだすと，結果はつぎのように表示される．
Date ---------------- Jan-07-1999 Jan-05-1999 Jan-11-1999 Jan-12-1999 Jan-10-1999 Jan-08-1999
日付の重複はないが，整列していないので順序はきまっていない．
UNION ALL 演算子を使用したクエリ
直前の例にちかいが，“UNION” のかわりに “UNION ALL” を使用する． すなわち，重複を排除しない．
SELECT Date FROM Store_Information UNION ALL SELECT Date FROM Internet_Sales
Perl ではつぎのように表現することができる．
sub select_date_union_all() {
my @work = ();
foreach my $record (@Store_Information) { # FROM Store_Information
push(@work, {Date => $record->{Date}}); # SELECT Date
}
foreach my $record (@Internet_Sales) { # FROM Internet_Sales
push(@work, {Date => $record->{Date}}); # UNION ALL SELECT Date
}
my @result = ();
foreach my $record (@work) {
push(@result, $record);
}
print_result(@result, ['Date']);
}
UNION ALL 演算子においては重複した値を排除する必要がないため，UNION 演算子より容易に実現することができる．
select_date_union_all() をよびだすと，結果はつぎのように表示される．
Date ---------------- Jan-05-1999 Jan-07-1999 Jan-08-1999 Jan-08-1999 Jan-08-1999 Jan-07-1999 Jan-10-1999 Jan-11-1999 Jan-12-1999
INTERSECT 演算子をふくむクエリ
UNION のかわりに INTERSECT (積) をふくむ例である．
SELECT Date FROM Store_Information INTERSECT SELECT Date FROM Internet_Sales
Perl ではつぎのように表現することができる．
sub select_date_intersect() {
my %work = ();
foreach my $record (@Store_Information) { # FROM Store_Information
$work{$record->{Date}} = 1;
}
foreach my $record (@Internet_Sales) { # FROM Internet_Sales
if ($work{$record->{Date}}) {
$work{$record->{Date}} = 2; # INTERSECT
}
}
my @result = ();
foreach my $Date (keys %work) {
if ($work{$Date} == 2) {
push(@result, {Date => $Date});
}
}
print_result(@result, ['Date']); # SELECT Date
}
INTERSECT 演算子は UNION 演算子よりやや実現しにくい． ここでは 2 をふくむハッシュ表の要素だけが INTERSECT の結果にふくまれる． しかし，この方法は演算式が複雑になったときはこのままでは適用できないとかんがえられる．
select_date_intersect() をよびだすと，結果はつぎのように表示される．
Date ---------------- Jan-07-1999
MINUS 演算子をふくむクエリ
2 つの結果の差を計算する例である．
SELECT Date FROM Store_Information MINUS SELECT Date FROM Internet_Sales
Perl ではつぎのように表現することができる．
sub select_date_minus() {
my %work = ();
foreach my $record (@Store_Information) { # FROM Store_Information
$work{$record->{Date}} = {Date => $record->{Date}};
}
foreach my $record (@Internet_Sales) { # FROM Internet_Sales
if ($work{$record->{Date}}) {
$work{$record->{Date}} = ''; # MINUS
# or, delete $work{$record->{Date}};
}
}
my @result = ();
foreach my $Date (keys %work) {
if ($work{$Date}) {
push(@result, {Date => $Date});
}
}
print_result(@result, ['Date']); # SELECT Date
}
select_date_minus() をよびだすと，結果はつぎのように表示される．
Date ---------------- Jan-05-1999 Jan-08-1999
