PostgreSQLで複数のテーブルを結合して処理する方法
今日は久々に旧ブログから記事を引っ張ってきました。
以下は二年以上前に書いたブログ記事の転載です。
新鮮なネタがないので、ストックの中からPostgreSQLネタでも。
というわけで、PostgreSQLで複数のテーブルを結合して処理する方法のメモです。
「複数のテーブルを結合して集計する」というのがどういう意味かと言うと、例えばまず会員制のショッピングサイトなどでユーザーの情報を管理するDBがあると仮定します。
で、一つ目のテーブル「TABLE1」には下記のように、ユーザーID・ユーザーの氏名・ユーザーのメールアドレス等の基本情報が格納されているとします。
| UserID | UserName | MailAddress |
|---|---|---|
| Amethyst | 鬼瓦 権三郎 | amethyst@amethyst-web.org |
| Alexandrite | 俵田山 兼松 | alexandrite@amethyst-web.org |
| Sapphire | 平等院 鳳凰堂 | sapphire@amethyst-web.org |
次に、二つ目のテーブル「TABLE2」には、各ユーザーの購入商品の情報が格納されているとします。
例えば、注文ID・注文者のユーザーID・商品名・商品価格etc。
| OrderID | UserID | ItemName | ItemPrice |
|---|---|---|---|
| 1 | Amethyst | 扇風機 | 2980 |
| 2 | Amethyst | エアコン | 99800 |
| 3 | Sapphire | コーヒーメーカー | 2800 |
| 4 | Amethyst | 電気コタツ | 5800 |
| 5 | Alexandrite | 上戸彩写真集 | 4800 |
| 6 | Sapphire | マグカップ | 1200 |
| 7 | Alexandrite | エロマンガ | 980 |
さて、上記の注文商品の情報が格納されたTABLE2のデータを元に、注文したユーザー宛に一括で受注確認のメールを送信するプログラムを作りたい時、どうすれば良いでしょうか。
単純に処理しようと思えば、まずTABLE2のデータを一行ずつ取得し、その情報の中からユーザーIDを取得し、次にそのユーザーIDをキーにしてTABLE1からメールアドレスを取得、という流れになります。
perlで書くと、下記のような感じ
#-- TABLE2のデータを取得 $SQLstat1 = $DB->prepare("select OrderID, UserID, ItemName, ItemPrice from TABLE2"); #-- 一行ずつ処理する while(@DATA = $SQLstat1->execute){ #-- 取得したデータを変数にセット ($OrderID,$UserID,$ItemName,$ItemPrice) = @DATA; #-- $UserIDをキーにしてTABLE1のデータを取得 $SQLstat2 = $DB->prepare("select UserName, MailAddress from TABLE1 where UserID = '$UserID'"); #-- 取得したデータを変数にセット ($UserName,$MailAddress) = $SQLstat->execute; #-- 以下にメール送信の処理が入る &SendMail; }
※上記の処理の場合は、実際にはプレースホルダを使った方が良いと思いますが、ここでは処理の流れをわかりやすくするために敢えてこういう記述にしました。
さて、上記のスクリプトの場合は一つ目のselect文の中で何度も別のselect文を発行していることになります。
が、select文にinner joinという句を使うと、一度のSQL文で上記の処理を済ますことができます。
↓こんな感じ。
#-- TABLE2のデータを元にTABLE1からUserIDをキーにしてデータを取得 $SQLstat = $DB->prepare("select TABLE2.OrderID, TABLE2.UserID, TABLE2.ItemName, TABLE2.ItemPrice, TABLE1.UserName, TABLE1.MailAddrss from TABLE2 inner join TABLE1 on TABLE2.UserID = TABLE1.UserID"); #-- 一行ずつ処理する while(@DATA = $SQLstat->execute){ #-- 取得したデータを変数にセット ($OrderID,$UserID,$ItemName,$ItemPrice,$UserName,$MailAddress) = @DATA; #-- 以下にメール送信の処理が入る &SendMail; }
上記スクリプトの
from TABLE2 inner join TABLE1 on TABLE2.UserID = TABLE1.UserID
の部分がポイントです。
これは「TABLE2とTABLE1を結合するよ。条件として"TABLE2のUserIDとTABLE1のUserIDが同じ"データを結合してね」
ということです。
ちなみに、上記select文で出てくる TABLE2.OrderID とか TABLE1.UserID とかはそれぞれ「TABLE2内のOrderID」「TABLE1内のUserID」を表します。
このinner join句は、select文だけではなくupdate文にも使えますので何かと便利です。
delete文には使った記憶がないのでよくわかりません。多分使えるんじゃないですかね？
つぶやく- 関連するブログ記事
-
- カテゴリー
- PostgreSQL | perl/CGI
- タグ
- inner join | perl | PostgreSQL | RDBMS | SQL | コマンドライン | シェル
