このブログ記事では、SQL文についての紹介を続けます。
前回の記事では、単一のテーブルに対するCRUD操作を実行するための基本的なSQL文について説明しました。 しかし、SQLでは複数のテーブルを含むより複雑な操作を実行することができます。 そのため、この記事では、それらの複雑な操作を実行するためのSQL文について引き続き説明します。
集計関数
集計関数は、タプルのセットに対して計算を実行し、単一の値を返す関数です。これにはMIN()、MAX()、COUNT()、SUM()、AVG()が含まれます。
これらはSELECT文と共に使用でき、例えばSELECT COUNT(*) FROM Product;でタプルの数をカウントしたり、SELECT AVG(price) FROM Product;で製品の平均価格を計算したりできます。
PostgreSQLでは、デフォルトの属性名はcountやavgのようにあまり良い名前ではないため、SELECT COUNT(*) AS product_count ...やSELECT AVG(price) AS avg_price ...のようにエイリアスをよく使用します。
また、GROUP BYを使用して集計結果を得る列を指定することもできます。例えば、製品カテゴリ（電子機器、書籍など）がある場合、SELECT category, AVG(price) FROM Product GROUP BY category;を使用して各カテゴリの平均価格を取得できます。
同様に、SELECT year, COUNT(*) FROM Product GROUP BY year;を使用して、毎年追加された製品の数を取得できます。
集計関数を使用した条件を指定するにはWHEREではなくHAVINGを使用します。例えば、SELECT year, COUNT(*) From Product GROUP BY year HAVING category ='Books' AND COUNT(*) > 5 ORDER BY COUNT(*) DESC;は、
'Books'カテゴリの製品が毎年追加された数をカウントし、製品数が5を超える年のみを表示し、カウント数の降順で並べ替えます。
外部キー
外部キー制約により、あるリレーションの属性を別のリレーションの（主キーまたは一意の属性から）選択するよう制限することで、異なるリレーション間の関係を表現できます。
例えば、CustomerとOrderの関係間の一対多の関係（顧客は複数の注文を持つことができ、各注文は1人の顧客のみを持つ）を表現するために、Orderテーブルに外部キー制約を持つcustomer_id属性を作成し、
その値をCustomerテーブルのid属性に存在する値に制限できます。PostgreSQLでは、これはCREATE TABLE Order(..., customer_id INT REFERENCES Customer (id));で実現できます。
この制約により、Customerテーブルのid属性値に存在しないcustomer_idを持つ新しいタプルを挿入することは無効になります。
また、ProductとOrderの関係間の多対多の関係（注文は多くの製品を持つことができ、製品は多くの注文に含まれることができる）を表現するために、
OrderItemsという新しいテーブルを作成し、order_idとproduct_idの2つの属性を持たせ、それぞれに外部キー制約を設定することができます：CREATE TABLE OrderItems(id SERIAL NOT NULL PRIMARY KEY, order_id INT REFERENCES Order (id), product_id REFERENCES Product (id));。
デフォルトでは、その顧客IDに関連する注文がある限り、顧客を削除することはできません。
ただし、外部キー定義にON DELETE CASCADEを追加することで、顧客が削除されたときにその顧客IDに関連するすべての注文も削除されるように設定できます。
OrderとOrderItemsは「has-a」関係を持つため、OrderItemsテーブルのorder_idに対してもON DELETE CASCADEを設定するのが適切です。
しかし、ProductとOrderItemsは「is-in」関係を持つため、製品を削除するときにその製品に関連するすべての注文を削除するのは適切ではありません。
このような場合、ON DELETE SET NULLやON DELETE SET DEFAULTの方が適切で、これらは値をnullまたはデフォルト値に設定することで行を保持します。
結合
他のテーブルのタプルを外部キーで参照し、それらのタプルの属性を取得したい場合、外部キーに基づいてテーブルを結合することができます。
例えば、適切な領収書を作成するために注文から顧客の名前を知りたい場合、OrderテーブルとCustomerテーブルを結合して、
関連する属性（注文IDと顧客名）を持つ新しいテーブルを作成できます。例えば、SELECT Order.id, Customer.name FROM Order JOIN Customer ON Order.customer_id = Customer.id;のようにします。
その後、WHEREを使用して領収書を作成する注文IDを指定できます。
必ずしも外部キーを使用してテーブルを結合する必要はありませんが、そのような結合を見ることはまれです。
デフォルトの結合は内部結合であり、一致しない属性値を持つすべての行が削除されます。
これは外部キーでない属性に基づいてテーブルを結合する場合に発生する可能性があります。
あるいは、左結合を使用することもできます。左結合では、左テーブル（上記の例ではOrderテーブル、一部の顧客名がnullになる可能性があります）から一致しない属性値を持つタプルが残ります。
右結合では、右テーブル（上記の例ではCustomerテーブル、一部の注文IDがnullになる可能性があります）から一致しない属性値を持つタプルが残ります。
完全結合では、一致しない属性値を持つすべてのタプルが残ります。
サブクエリ
集計値を完全なテーブルの新しい列として表示したい場合、サブクエリを使用できます。サブクエリは、クエリ内に別のクエリを含めることを意味します。
例えば、各製品の平均価格からの差額を表示するには、SELECT name, price - (SELECT AVG(price) FROM Product) FROM Product;とします。
また、サブクエリを使用して平均より低い価格の製品をフィルタリングすることもできます。例えば、SELECT name, price FROM Product WHERE price >= (SELECT AVG(price) FROM Product);のようにします。
サブクエリの使用例は集計値に限定されません。例えば、SELECT name, price FROM Product WHERE id IN (SELECT product_id FROM OrderItems WHERE order_id = 1);のように、
サブクエリを使用して注文内の製品リストを取得することができます。また、...UNION SELECT "Total", SUM(price) FROM Product WHERE id IN (SELECT product_id FROM OrderItems WHERE order_id = 1);をUNIONを用いて付け加えることで、最後の行に合計を表示することもできます。
ただし、この例では、結合を使用して同じ結果を得ることができ、一般的には結合が好まれるアプローチです。
しかし、複雑なクエリではサブクエリが避けられない場合もあります。例えば、SELECT p.name, p.price, Order.date FROM (SELECT OrderItems.order_id, name, price FROM Product JOIN OrderItems ON Product.id = OrderItems.product_id) AS p JOIN Order ON p.order_id = Order.id;のようなケースです。
ウィンドウ関数
以前、集計値を列として表示するためにサブクエリを使用しましたが、ウィンドウ関数でも同じことを実現できます。
例えば、価格差を表示する以前のクエリは SELECT name, price - AVG(price) OVER () FROM Product; として書き直すことができます。
ウィンドウ関数は集計関数の適用方法においてより柔軟性を提供します。例えば、PARTITION BYを使用してGROUP BY と同様に各カテゴリごとの集計値を計算し、
新しい列として表示することができます。SELECT name, AVG(price) OVER (PARTITION BY category) FROM Product; は各カテゴリの平均価格を計算し、各製品に対して表示します。
また、ORDER BY を使用して累積集計値を計算することもできます。例えば SELECT name, AVG(price) OVER (PARTITION BY category ORDER BY price) FROM Product; は、
各カテゴリ内の製品の価格を昇順に並べた累積平均価格を計算します（DESC を使用すると降順に変更できます）。
異なる列間で同じウィンドウを共有するには、WINDOW 句を使用できます。例：SELECT name, AVG(price) OVER w, AVG(quantity) OVER w FROM Product WINDOW w AS (PARTITION BY category);
結論
この記事では、PostgreSQLにおける集計関数、外部キー制約、テーブル結合、サブクエリ、ウィンドウ関数を（他の句やステートメントとともに）紹介しました。 これらにより、複数の関連テーブルに対して複雑な操作、主にクエリを実行することができます。DBMSで利用可能なステートメントの詳細な説明については、 対応する公式ドキュメントを確認することをお勧めします。
リソース
- PostgreSQL. n.d. PostgreSQL 17.4 Documentation. PostgreSQL.