PostgreSQL: PostgreSQL集計関数とグループ化

最終更新：2026-08-26

1. 学習内容

5つの主要な集計関数：COUNT / SUM / AVG / MAX / MIN
単一列および複数列のGROUP BYグループ化
グループ化結果をフィルタリングするHAVING句
PostgreSQLの機能：GROUPING SETS / ROLLUP / CUBE多次元グループ化
PostgreSQLの機能：条件付き集計のためのFILTER句
集計内でのDISTINCTの使用
集計関数のNULLの扱い方

2. ストーリー

Bobは越境Eコマースプラットフォームのデータアナリストです。年末が近づき、CEOから年次売上分析レポートの作成を依頼されました：

地域ごとの注文数と売上高を集計
複数次元（四半期＋地域）でのクロス分析
注文がある月とない月の収益差を計算
全次元のサマリーデータを一度に生成

Bobは、単純なGROUP BYでは一度に1つの次元しかグループ化できず、複数のSQL文を書いてUNIONする必要があることに気づきました。しかし、PostgreSQLのGROUPING SETSとFILTER句を学んだことで、1つのSQL文ですべての要件を満たせるようになりました。

3. 概念

(1) 集計関数の概要

集計関数は複数の入力行を1つの出力行にまとめ、データ分析の基盤となります。

SQL

SELECT
  COUNT(*)        AS total_rows,
  COUNT(amount)   AS non_null_count,
  SUM(amount)     AS total_amount,
  AVG(amount)     AS avg_amount,
  MAX(amount)     AS max_amount,
  MIN(amount)     AS min_amount
FROM orders;

TEXT 📖 参照専用

 total_rows | non_null_count | total_amount | avg_amount | max_amount | min_amount
------------+----------------+--------------+------------+------------+------------
        100 |             95 |     12500000 |  131578.95 |     500000 |       1200

(2) 5つの集計関数の詳細

関数	目的	NULLの扱い	戻り値の型
`COUNT(*)`	全行をカウント（NULL含む）	NULLを含む	bigint
`COUNT(col)`	NULLでない行をカウント	NULLを無視	bigint
`SUM(col)`	合計	NULLを無視、すべてNULLの場合はNULLを返す	入力と同じ
`AVG(col)`	平均	NULLを無視	numeric
`MAX(col)` / `MIN(col)`	最大/最小値	NULLを無視	入力と同じ

▶ サンプル：COUNT(*) vs COUNT(col)

SQL

SELECT
  COUNT(*)       AS all_rows,
  COUNT(discount) AS rows_with_discount
FROM orders;

TEXT 📖 参照専用

 all_rows | rows_with_discount
----------+-------------------
      100 |                 42

58行はdiscountがNULLのため、COUNT(discount)はそれらを除外します。

▶ サンプル：SUMとAVGのNULL処理

SQL

SELECT
  SUM(discount)  AS total_discount,
  AVG(discount)  AS avg_discount
FROM orders
WHERE region = 'NA';

TEXT 📖 参照専用

 total_discount |     avg_discount
----------------+--------------------
         125000 | 2976.1904761904762

AVGはNULLでない行のみで平均します：125000 / 42 ≈ 2976.19（125000 / 100ではありません）。

▶ サンプル：MAX/MINによる極値取得

SQL

SELECT
  MAX(created_at) AS latest_order,
  MIN(created_at) AS earliest_order
FROM orders;

TEXT 📖 参照専用

     latest_order      |    earliest_order
------------------------+------------------------
 2025-12-28 15:30:00   | 2025-01-03 09:12:00

▶ サンプル：空の結果セットの集計

SQL

SELECT
  COUNT(*)  AS cnt,
  SUM(amount) AS total
FROM orders
WHERE region = 'ANTARCTICA';

TEXT 📖 参照専用

 cnt | total
-----+-------
   0 |

COUNTは空のセットに対して0を返しますが、SUMは空のセットに対してNULLを返します — 古典的な落とし穴です。

(3) GROUP BYグループ化

GROUP BYは指定された列で行をグループ化し、グループごとに1行の集計行を生成します。

SQL

SELECT
  region,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_amount
FROM orders
GROUP BY region;

TEXT 📖 参照専用

 region | order_count | total_amount
--------+-------------+--------------
 EU     |          35 |      4200000
 NA     |          45 |      5800000
 APAC   |          20 |      2500000

▶ サンプル：複数列でのGROUP BY

SQL

SELECT
  region,
  EXTRACT(QUARTER FROM created_at)::int AS quarter,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_amount
FROM orders
GROUP BY region, EXTRACT(QUARTER FROM created_at)
ORDER BY region, quarter;

TEXT 📖 参照専用

 region | quarter | order_count | total_amount
--------+---------+-------------+--------------
 APAC   |       1 |           5 |       620000
 APAC   |       2 |           6 |       780000
 APAC   |       3 |           4 |       500000
 APAC   |       4 |           5 |       600000
 EU     |       1 |           8 |       950000
 EU     |       2 |           9 |      1100000
 ...

(4) HAVINGによるグループのフィルタリング

WHEREはグループ化の前に行をフィルタリングし、HAVINGはグループ化の後にグループをフィルタリングします。

句	適用タイミング	集計の使用可否
WHERE	GROUP BYの前	不可
HAVING	GROUP BYの後	可

▶ サンプル：HAVINGで高収益地域をフィルタリング

SQL

SELECT
  region,
  SUM(amount) AS total_amount
FROM orders
GROUP BY region
HAVING SUM(amount) > 3000000
ORDER BY total_amount DESC;

TEXT 📖 参照専用

 region | total_amount
--------+--------------
 NA     |      5800000
 EU     |      4200000

APACの合計2,500,000はフィルタリングされます。

▶ サンプル：WHERE + HAVINGの組み合わせ

SQL

SELECT
  region,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_amount
FROM orders
WHERE amount >= 5000
GROUP BY region
HAVING COUNT(*) >= 10
ORDER BY total_amount DESC;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

まず5,000未満の注文がフィルタリングされ、次にグループ内の注文数が10未満の地域が除外されます。

4. キーポイント

(1) 集計内でのDISTINCTの使用

▶ サンプル：ユニークな顧客数のカウント

SQL

SELECT
  COUNT(DISTINCT customer_id) AS unique_customers,
  COUNT(*)                    AS total_orders
FROM orders;

TEXT 📖 参照専用

 unique_customers | total_orders
------------------+--------------
              780 |          1000

▶ サンプル：重複を除外したSUM DISTINCT

SQL

SELECT
  SUM(DISTINCT bonus) AS unique_bonus_total
FROM employee_targets;

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

(2) FILTER句（PostgreSQLの機能）

FILTER句を使うと、複数のCASE WHEN式を書くことなく、同じ行セットを異なる条件で集計できます。

SQL

SELECT
  region,
  COUNT(*) FILTER (WHERE amount >= 10000)  AS high_value_orders,
  COUNT(*) FILTER (WHERE amount < 10000)   AS low_value_orders,
  SUM(amount) FILTER (WHERE quarter = 1)   AS q1_revenue,
  SUM(amount) FILTER (WHERE quarter = 2)   AS q2_revenue
FROM orders
GROUP BY region;

手法	構文	可読性	パフォーマンス
CASE WHEN	`SUM(CASE WHEN ... THEN x ELSE 0 END)`	普通	1回のスキャン
FILTER	`SUM(x) FILTER (WHERE ...)`	優れている	1回のスキャン

▶ サンプル：FILTERで注文あり/なしの月を計算

SQL

SELECT
  region,
  COUNT(DISTINCT EXTRACT(MONTH FROM created_at))
    FILTER (WHERE amount > 0)  AS months_with_orders,
  12 - COUNT(DISTINCT EXTRACT(MONTH FROM created_at))
    FILTER (WHERE amount > 0) AS months_without_orders
FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2025
GROUP BY region;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

▶ サンプル：FILTERで前年比比較

SQL

SELECT
  region,
  SUM(amount) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2024) AS revenue_2024,
  SUM(amount) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2025) AS revenue_2025
FROM orders
GROUP BY region;

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

(3) GROUPING SETS / ROLLUP / CUBE（PostgreSQLの機能）

1つのクエリで複数次元の集計結果を生成し、複数のSQL文を書いてUNIONする必要がありません。

▶ サンプル：GROUPING SETSでカスタム次元の組み合わせ

SQL

SELECT
  region,
  EXTRACT(QUARTER FROM created_at)::int AS quarter,
  SUM(amount) AS total_amount
FROM orders
GROUP BY GROUPING SETS (
  (region, EXTRACT(QUARTER FROM created_at)),
  (region),
  (EXTRACT(QUARTER FROM created_at)),
  ()
)
ORDER BY region NULLS LAST, quarter NULLS LAST;

TEXT 📖 参照専用

 region | quarter | total_amount
--------+---------+--------------
 APAC   |       1 |       620000
 APAC   |       2 |       780000
 APAC   |       3 |       500000
 APAC   |       4 |       600000
 APAC   |         |      2500000
 EU     |       1 |       950000
 ...
        |       1 |      2200000
 ...
        |         |     12500000

NULLはその次元がサマリーレベルであることを意味します。GROUPING()関数を使って実際のNULLとサマリーレベルのNULLを区別します。

▶ サンプル：ROLLUP階層サマリー

SQL

SELECT
  region,
  EXTRACT(QUARTER FROM created_at)::int AS quarter,
  SUM(amount) AS total_amount,
  GROUPING(region) AS g_region,
  GROUPING(quarter) AS g_quarter
FROM orders
GROUP BY ROLLUP (region, EXTRACT(QUARTER FROM created_at))
ORDER BY region NULLS LAST, quarter NULLS LAST;

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

構文	同等のGROUPING SETS	出力次元
`ROLLUP(a, b)`	`(a,b), (a), ()`	階層：明細 → 小計 → 総計
`CUBE(a, b)`	`(a,b), (a), (b), ()`	全クロス：全組み合わせ
`GROUPING SETS((a),(b))`	`(a), (b)`	任意のカスタム組み合わせ

▶ サンプル：CUBE全次元クロス

SQL

SELECT
  region,
  EXTRACT(QUARTER FROM created_at)::int AS quarter,
  SUM(amount) AS total_amount
FROM orders
GROUP BY CUBE (region, EXTRACT(QUARTER FROM created_at))
ORDER BY region NULLS LAST, quarter NULLS LAST;

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

(4) 集計関数のNULL動作まとめ

シナリオ	COUNT(*)	COUNT(col)	SUM	AVG	MAX/MIN
NULLでない値がある	全行をカウント	NULLでない行のみカウント	NULLを無視して合計	NULLを無視して平均	NULLを無視して極値
すべてNULL	行をカウント	0	NULL	NULL	NULL
空の結果セット	0	0	NULL	NULL	NULL

5. 実践

▶ サンプル：商品カテゴリ別の売上トップ3

SQL

SELECT
  category,
  SUM(amount) AS total_amount
FROM orders
GROUP BY category
ORDER BY total_amount DESC
LIMIT 3;

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

▶ サンプル：地域別の顧客維持率

SQL

SELECT
  region,
  COUNT(DISTINCT customer_id) FILTER (
    WHERE EXTRACT(YEAR FROM created_at) = 2024
  ) AS customers_2024,
  COUNT(DISTINCT customer_id) FILTER (
    WHERE EXTRACT(YEAR FROM created_at) = 2025
  ) AS customers_2025,
  ROUND(
    COUNT(DISTINCT customer_id) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2025)::numeric
    / NULLIF(
      COUNT(DISTINCT customer_id) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2024),
      0
    ) * 100, 1
  ) AS retention_rate
FROM orders
GROUP BY region;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

▶ サンプル：月次前年比トレンド

SQL

SELECT
  EXTRACT(MONTH FROM created_at)::int AS month_num,
  SUM(amount) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2024) AS revenue_2024,
  SUM(amount) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2025) AS revenue_2025
FROM orders
GROUP BY EXTRACT(MONTH FROM created_at)
ORDER BY month_num;

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

▶ サンプル：多次元売上レポート（ROLLUP + FILTER）

SQL

SELECT
  region,
  category,
  SUM(amount) AS total_amount,
  COUNT(*) FILTER (WHERE amount >= 50000) AS big_deals,
  GROUPING(region)  AS g_region,
  GROUPING(category) AS g_category
FROM orders
GROUP BY ROLLUP (region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

▶ サンプル：グループ化後の高頻度顧客フィルタリング

SQL

SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 5 AND SUM(amount) >= 100000
ORDER BY total_spent DESC;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

6. 総合的な例

Bobの年次売上分析 — 1つのSQL文で全次元レポートを生成：

SQL

SELECT
  region,
  EXTRACT(QUARTER FROM created_at)::int AS quarter,
  SUM(amount)                              AS total_revenue,
  COUNT(*)                                 AS order_count,
  COUNT(DISTINCT customer_id)              AS unique_customers,
  SUM(amount) FILTER (WHERE amount >= 50000) AS big_deal_revenue,
  COUNT(*)  FILTER (WHERE amount >= 50000)   AS big_deal_count,
  AVG(amount)                              AS avg_order_value,
  GROUPING(region)  AS g_region,
  GROUPING(quarter) AS g_quarter
FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2025
GROUP BY ROLLUP (region, EXTRACT(QUARTER FROM created_at))
ORDER BY region NULLS LAST, quarter NULLS LAST;

TEXT 📖 参照専用

 region | quarter | total_revenue | order_count | unique_customers | big_deal_revenue | big_deal_count | avg_order_value | g_region | g_quarter
--------+---------+---------------+-------------+------------------+------------------+----------------+-----------------+----------+-----------
 APAC   |       1 |        620000 |           5 |                4 |          120000  |              1 |     124000.00   |        0 |         0
 APAC   |       2 |        780000 |           6 |                5 |          250000  |              2 |     130000.00   |        0 |         0
 APAC   |       3 |        500000 |           4 |                3 |           50000  |              1 |     125000.00   |        0 |         0
 APAC   |       4 |        600000 |           5 |                4 |          100000  |              1 |     120000.00   |        0 |         0
 APAC   |         |       2500000 |          20 |               12 |          520000  |              5 |     125000.00   |        0 |         1
 EU     |       1 |        950000 |           8 |                7 |          350000  |              3 |     118750.00   |        0 |         0
 ...
        |         |      12500000 |         100 |               780 |         5000000  |             45 |     125000.00   |        1 |         1

7. 実行フロー

集計クエリの完全な実行順序：

flowchart TD
    A[FROM] --> B[WHERE]
    B --> C[GROUP BY]
    C --> D[HAVING]
    D --> E["集計関数<br/>COUNT/SUM/AVG/MAX/MIN"]
    E --> F[SELECT]
    F --> G[ORDER BY]
    G --> H[LIMIT]

    style A fill:#e1f5fe
    style C fill:#fff9c4
    style D fill:#fff9c4
    style E fill:#c8e6c9

ステップ	句	説明
1	FROM	データソースの決定
2	WHERE	行のフィルタリング（グループ化前）
3	GROUP BY	行のグループ化
4	集計関数	各グループの集計計算
5	HAVING	グループのフィルタリング（グループ化後）
6	SELECT	出力列の選択
7	ORDER BY	ソート
8	LIMIT	行数の制限

❓ よくある質問

Q COUNT(*)とCOUNT(1)に違いはありますか？

A PostgreSQLでは完全に同等です。COUNT(*)が推奨される形式で、意味がより明確です。

Q 空のグループに対してSUMが0ではなくNULLを返すのはなぜですか？

A SQL標準では、すべてNULLまたは空の入力に対するSUMはNULLを返すと定められています。0が必要な場合は、COALESCE(SUM(col), 0)を使用してください。

Q HAVINGは集計関数なしで使用できますか？

A はい — HAVING region = 'NA'は構文的に有効ですが、そのような条件はパフォーマンス向上のためにWHEREに記述すべきです。

Q FILTER句はCASE WHENと同じくらい速いですか？

A 基本的に同じです — どちらもデータを1回だけスキャンします。FILTERの方が読みやすく、PostgreSQLが推奨する形式です。

Q GROUPING SETSは複数のUNIONされたSQL文とどう違いますか？

A GROUPING SETSはテーブルを1回だけスキャンしますが、複数のUNIONされたSQL文は複数回スキャンします。大量データではパフォーマンスの差が顕著です。

Q GROUP BYの後、グループ化されていない列をSELECTに含められますか？

A PostgreSQLの厳格モードでは不可です。SELECT内の集計されていない列はGROUP BYにも含める必要があり、そうでなければエラーになります。

📖 まとめ

5つの集計関数COUNT/SUM/AVG/MAX/MINはNULLを異なる方法で処理します
GROUP BYは列でグループ化し、HAVINGはグループ化結果をフィルタリングします
WHEREはグループ化前に行をフィルタリングし、HAVINGはグループ化後にグループをフィルタリングします
PostgreSQLのFILTER句はよりクリーンな構文でCASE WHENを置き換えます
GROUPING SETS / ROLLUP / CUBEは1つのクエリで多次元サマリーを生成します
GROUPING()関数は実際のNULLとサマリーレベルのNULLを区別します
COUNT(*)は空のセットに対して0を返し、SUM/AVGはNULLを返します

📝 練習問題

⭐ ordersテーブルで各地域の注文数と合計金額を集計し、金額の降順でソートしてください。
⭐ 注文数が10件以上で平均金額が50,000 USDを超えるcustomer_idの値を見つけてください。
⭐⭐ FILTER句を使用して、各地域のQ1〜Q4の四半期売上を1つのSQL文で出力してください。
⭐⭐ CUBEを使用して(region, category)の完全なクロス次元サマリーを計算し、GROUPING()でサマリー行をマークしてください。
⭐⭐⭐ 地域サマリー、カテゴリサマリー、地域+カテゴリ明細、全体のテーブル合計を1回のordersテーブルスキャンで出力する単一のSQL文を書いてください。

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて