PostgreSQL 入門

PostgreSQL GROUP BY 句

GROUP BY句はデータ分析における基礎的なツールであり、データを論理的な「グループ」に分割することで詳細な分析を可能にします。この句は通常、集約関数（Aggregate functions）と組み合わせて使用され、データセット全体ではなく、そのサブセット（部分集合）に対して計算を実行することを可能にします。同じ特徴を持つデータをまとめ、各グループに対して1行の集約結果を返します。

1. GROUP BY句とは何か

GROUP BY句の主な目的は、同じ値を持つ複数の行を、グループごとの単一のサマリーデータ（要約データ）に統合することです。

これは、前の章で解説した集約関数（COUNT、SUM、AVG、MIN、MAXなど）を特定のグループに適用したい場合に非常に有効です。GROUP BYを使用しない場合、集約関数はクエリの結果セット全体に対して実行され、単一の集約値のみを返します。しかし、GROUP BYを導入することで、独立した各グループごとの集約値を取得できるようになります。

1.1 基本的な構文

GROUP BY句は通常、FROM句やWHERE句の後に記述し、ORDER BY句やHAVING句の前に配置します。

SELECT
    column1,
    aggregate_function(column2) -- 集約関数
FROM
    table_name
WHERE
    condition -- フィルタリング条件（任意）
GROUP BY
    column1;

ここで、column1 はグループ化の基準となるカラムです（1つまたは複数のカラムを指定可能）。重要な注意点： SELECTリスト内で、集約関数に含まれていないすべてのカラムは、必ずGROUP BY句にも含まれている必要があります。

2. 単一カラムによるグループ化

単一のカラムでグループ化すると、そのカラム内のユニークな（重複しない）値に基づいてグループが作成されます。

2.1 例1：顧客ごとの注文数を計算する

order_id（注文ID）、customer_id（顧客ID）、order_total（注文総額）を含む orders（注文）テーブルがあると仮定します。各顧客が何件の注文を行ったかを知りたい場合、customer_id でグループ化し、COUNT() 関数を使用します。

SELECT
    customer_id,
    COUNT(order_id) AS 総注文数
FROM
    orders
GROUP BY
    customer_id;

このクエリは、customer_id とその顧客の総注文数の2カラムを返します。結果セットでは各 customer_id は一度だけ表示され、それぞれが独立したグループを表します。

2.2 例2：カテゴリーごとの製品平均価格を計算する

product_id（製品ID）、product_name（製品名）、category（カテゴリー）、price（価格）を含む products（製品）テーブルがあるとします。カテゴリーごとの製品の平均価格を計算するには、category でグループ化します。

SELECT
    category,
    AVG(price) AS 平均価格
FROM
    products
GROUP BY
    category;

このクエリは製品カテゴリーのリストを生成し、各カテゴリーの隣にそのカテゴリーに属する全製品の平均価格を表示します。

3. 複数カラムによるグループ化

複数のカラムを指定してデータをグループ化することも可能です。複数カラムでグループ化する場合、GROUP BY句は指定されたすべてのカラムのユニークな組み合わせに基づいてグループを作成します。

3.1 例1：各地域の顧客ごとの総売上高

orders テーブルに region（地域）カラムが含まれており、各顧客が特定の地域でいくら注文したかを確認したい場合を考えます。

SELECT
    customer_id,
    region,
    SUM(order_total) AS 総売上高
FROM
    orders
GROUP BY
    customer_id,
    region;

この場合、customer_id と region のユニークな組み合わせごとに新しいグループが作成されます。例えば、「北米地域の顧客A」と「欧州地域の顧客A」は、2つの異なるグループとして扱われます。

3.2 例2：部署および勤務地ごとの従業員数

employee_id（従業員ID）、employee_name（従業員名）、department（部署）、location（勤務地）を含む employees テーブルを考えます。部署と勤務地の組み合わせごとの従業員数を集計します。

SELECT
    department,
    location,
    COUNT(employee_id) AS 従業員数
FROM
    employees
GROUP BY
    department,
    location;

このクエリは、「ニューヨークの営業部」に何人、「ロンドンの営業部」に何人いるか、といった情報を表示します。

4. 式（Expression）を用いたグループ化

GROUP BY句はカラム名だけでなく、「式」によるグループ化も可能です。これにより、より柔軟なグループ化戦略が可能になります。

4.1 例：注文日の「年」ごとにグループ化する

orders テーブルに order_date（注文日）カラム（DATE型またはTIMESTAMP型）がある場合、注文された「年」ごとにデータをまとめたいことがあります。日付関数を使用して年を抽出できます。

SELECT
    EXTRACT(YEAR FROM order_date) AS 注文年,
    COUNT(order_id) AS 総注文数
FROM
    orders
GROUP BY
    EXTRACT(YEAR FROM order_date);

ここでは、EXTRACT(YEAR FROM order_date) が各注文の年を算出する式であり、GROUP BY句はこの計算された年の値に基づいてグループ化を行います。

5. 集約関数との関係

前の章で議論したように、集約関数（COUNT、SUM、AVG、MIN、MAX）は一連の入力値から単一の結果を計算します。GROUP BY句はこれらの関数の作用範囲（スコープ）を変更し、各グループに対して独立して操作を実行させます。

もし SELECT ステートメントで GROUP BY句を付けずに集約関数を使用した場合、その関数は FROM および WHERE 句によって返されたすべての行を「1つの大きなグループ」として扱います。

SELECT
    SUM(order_total) AS 全注文の総収入
FROM
    orders;

このクエリは、すべての注文の合計値を1つだけ返します。

GROUP BYを導入すると、集約関数はそれぞれのグループに個別に適用され、グループごとのサマリーを提供します。

SELECT
    customer_id,
    SUM(order_total) AS 顧客ごとの総収入
FROM
    orders
GROUP BY
    customer_id;

このクエリは、ユニークな customer_id ごとに対応する order_total の合計を返します。

6. 実践的なケーススタディとデモンストレーション

架空の sales（販売）データベースを使用し、以下の transactions（取引）テーブルの構造で考えてみましょう。

transactions テーブル:

  • transaction_id (Integer, Primary Key)
  • product_id (Integer)
  • customer_id (Integer)
  • sale_date (Date)
  • quantity (Integer - 数量)
  • unit_price (Numeric - 単価)
  • store_location (Varchar - 店舗の場所)

サンプルデータの挿入：

INSERT INTO transactions (transaction_id, product_id, customer_id, sale_date, quantity, unit_price, store_location) VALUES
(1, 101, 1, '2023-01-10', 2, 15.00, 'Downtown'),
(2, 102, 2, '2023-01-10', 1, 25.50, 'Uptown'),
(3, 101, 1, '2023-01-11', 3, 15.00, 'Downtown'),
(4, 103, 3, '2023-01-11', 1, 50.00, 'Suburban'),
(5, 102, 2, '2023-01-12', 2, 25.50, 'Uptown'),
(6, 104, 1, '2023-01-12', 1, 10.00, 'Downtown'),
(7, 101, 4, '2023-01-13', 1, 15.00, 'Uptown'),
(8, 103, 3, '2023-01-13', 2, 50.00, 'Suburban'),
(9, 105, 5, '2023-01-14', 1, 5.00, 'Downtown'),
(10, 101, 1, '2023-01-14', 1, 15.00, 'Uptown');

6.1 例1：店舗の場所ごとの総収入を計算する

各店舗のロケーションで発生した総収入を計算します。

SELECT
    store_location,
    SUM(quantity * unit_price) AS 総収入
FROM
    transactions
GROUP BY
    store_location;

解説：

  • SUM(quantity * unit_price) で各取引の売上を計算します。
  • GROUP BY store_location により、ユニークなロケーションごとに取引売上を合算します。
  • 結果には Downtown、Uptown、Suburban が別々の行として表示され、それぞれの総収入が示されます。

6.2 例2：顧客ごとの取引回数を集計する

各顧客が何回の取引を完了したかをカウントします。

SELECT
    customer_id,
    COUNT(transaction_id) AS 取引回数
FROM
    transactions
GROUP BY
    customer_id;

解説：

  • COUNT(transaction_id) で独立した取引の数を数えます。
  • GROUP BY customer_id により、カウントが各顧客ごとに行われることを保証します。

6.3 例3：製品ごとの平均販売数量

すべての取引における、製品ごとの平均販売数量を特定します。

SELECT
    product_id,
    AVG(quantity) AS 平均販売数量
FROM
    transactions
GROUP BY
    product_id;

解説：

  • AVG(quantity) で平均数量を計算します。
  • GROUP BY product_id により、ユニークな product_id ごとに平均値を算出します。

6.4 例4：各店舗の日の売上数量

特定の販売日における各店舗の製品販売総数を計算します。

SELECT
    sale_date,
    store_location,
    SUM(quantity) AS 日別販売総数
FROM
    transactions
GROUP BY
    sale_date,
    store_location
ORDER BY
    sale_date,
    store_location;

解説：

  • このクエリでは GROUP BY句に複数のカラム (sale_date, store_location) を使用しています。
  • 日付と店舗ロケーションの組み合わせごとにユニークなグループが作成されます。
  • SUM(quantity) は、これらの特定の「日付×店舗」グループの数量を合計します。
  • ORDER BY を追加することで、まず日付順、次にロケーション順にソートされ、結果が読みやすくなります。