SQLでCOUNTをサブクエリに使うと、ユーザーごとの注文数や、条件を満たすユーザーの人数を集計できます。重要なのは注文の行数・ユーザー数・数量・金額を分けることです。相関COUNTは該当行がなければ0を返しますが、GROUP BYした結果をJOINする場合は0件の扱いが変わります。
COUNT関数とは
COUNT関数は、SQLにおいて指定した列や全レコードの数をカウントするための集計関数です。例えば、特定のテーブル内のレコード数を数えたり、条件に一致するレコードの数を数えたりするのに使用されます。
基本的な使い方
COUNT関数は、テーブル全体または特定の列に対して使用できます。基本的な構文は以下の通りです：
SELECT COUNT(*) FROM テーブル名;または特定の列をカウントする場合：
SELECT COUNT(列名) FROM テーブル名;NULL値の扱い
COUNT関数は、列名を指定した場合、NULL値を除外してカウントします。一方、COUNT(*)を使用すると、NULL値を含む全レコードをカウントします。以下に例を示します：
SELECT COUNT(列名) FROM テーブル名; -- NULLを除外してカウント
SELECT COUNT(*) FROM テーブル名; -- NULLを含む全レコードをカウントCOUNT関数は、データベースの基本的な集計や分析において欠かせないツールです。次に、サブクエリについて見ていきましょう。
サブクエリの基本
サブクエリとは、別のSQLクエリの内部に含まれるクエリのことを指します。サブクエリは、親クエリの条件として使用されることが多く、複雑なデータ操作をシンプルに表現するために利用されます。
サブクエリの基本構文
サブクエリは、SELECT文、WHERE句、FROM句など、さまざまな場所で使用できます。基本的な構文は次の通りです：
SELECT 列名
FROM テーブル名
WHERE 列名 IN (SELECT 列名 FROM 他のテーブル WHERE 条件);この構文では、サブクエリがWHERE句の条件として使用されています。
サブクエリの種類
以下では返す結果の形で分類します。外側の列を参照する「相関サブクエリ」かどうかは別の分類です。
スカラーサブクエリ
スカラーサブクエリは1列・最大1行の結果を値として扱います。複数行を返すと多くのDBMSでエラーになります。GROUP BYなしのCOUNTは対象行がなくても0を含む1行を返します。SELECT文の中やWHERE句の条件として使用されます。例：
SELECT 列名,
(SELECT COUNT(*) FROM 他のテーブル WHERE 条件) AS 別名
FROM テーブル名;マルチバリューサブクエリ
マルチバリューサブクエリは、複数の値（行）を返します。INやEXISTSといった条件句で使用されます。例：
SELECT 列名
FROM テーブル名
WHERE 列名 IN (SELECT 列名 FROM 他のテーブル WHERE 条件);サブクエリを活用することで、複雑なクエリをより簡潔に記述できます。次に、COUNT関数を使ったサブクエリの具体的な例を見ていきましょう。
COUNTを使ったサブクエリの例
COUNT関数をサブクエリとして使用することで、特定の条件に基づいたデータの集計が可能になります。ここでは、実際のSQLクエリの例を用いて、COUNTを使ったサブクエリの実装方法を説明します。
例1：各カテゴリに含まれる商品の数をカウントする
例えば、商品テーブルとカテゴリテーブルがある場合、各カテゴリに属する商品の数をカウントするクエリは以下のようになります：
SELECT category_name,
(SELECT COUNT(*)
FROM products
WHERE products.category_id = categories.category_id) AS product_count
FROM categories;このクエリでは、各カテゴリについて、サブクエリがそのカテゴリに属する商品の数をカウントしています。
例2：売上レコードの件数が最多の店舗を1件取得する
次に、店舗ごとの売上数をカウントし、その中で最大の売上数を持つ店舗を取得する例を示します：
SELECT store_id, sales_count
FROM (SELECT store_id,
COUNT(*) AS sales_count
FROM sales
GROUP BY store_id) AS store_sales
ORDER BY sales_count DESC, store_id
LIMIT 1;このクエリでは、まずサブクエリで各店舗の売上数をカウントし、メインクエリでその結果を基に売上数の多い順に並べ替え、先頭の店舗を1件取得しています。COUNT(*)は売上レコードの件数で、売上金額や販売数量の合計ではありません。同数ならstore_idの小さい方を選ぶ例です。LIMITはPostgreSQL・MySQL・SQLite等の構文で、SQL ServerならTOPなどに書き換えます。
例3：特定の条件を満たすユーザーの数をカウントする
最後に、特定の条件（例えば、特定の期間内に注文を行ったユーザー）の数をカウントするクエリの例を示します：
SELECT COUNT(*)
FROM (SELECT user_id
FROM orders
WHERE order_date >= '2023-01-01'
AND order_date < '2024-01-01'
AND user_id IS NOT NULL
GROUP BY user_id) AS yearly_orders;このクエリでは、サブクエリで特定の期間内に注文を行ったユーザーをグループ化し、その数をメインクエリでカウントしています。
これらの例からわかるように、COUNTを使ったサブクエリを活用することで、集計の対象を明示できます。次に、具体的な実用例を見ていきましょう。
実用例：ユーザーごとの注文数のカウント
実際の業務で役立つ例として、ユーザーごとの注文数をカウントする方法を紹介します。この例では、usersテーブルとordersテーブルを使用して、各ユーザーが行った注文の数をカウントします。
テーブル構造
まず、usersテーブルとordersテーブルの構造は以下の通りです：
-- usersテーブル
CREATE TABLE users (
user_id INT PRIMARY KEY,
user_name VARCHAR(255)
);
-- ordersテーブル
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
order_date DATE,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);ユーザーごとの注文数をカウントするクエリ
各ユーザーの注文数をカウントするためのクエリは次の通りです：
SELECT user_name,
(SELECT COUNT(*)
FROM orders
WHERE orders.user_id = users.user_id) AS order_count
FROM users
ORDER BY user_id;このクエリでは、以下の処理を行っています：
usersテーブルからユーザー名を取得する。- サブクエリで
ordersテーブルから該当するユーザーの注文数をカウントする。 - 結果を
order_countとして表示する。
実行結果の例
例えば、usersテーブルに以下のデータがあり、ordersテーブルに注文データが登録されているとします：
-- usersテーブルのデータ
INSERT INTO users (user_id, user_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'Dana');
-- ordersテーブルのデータ
INSERT INTO orders (order_id, user_id, order_date) VALUES
(1, 1, '2023-01-01'),
(2, 1, '2023-02-15'),
(3, 2, '2023-03-10'),
(4, 3, '2023-04-20'),
(5, 3, '2023-05-25'),
(6, 3, '2023-06-30');上記のクエリを実行すると、以下の結果が得られます：
user_name | order_count
------------|-------------
Alice | 2
Bob | 1
Charlie | 3
Dana | 0この結果から、Aliceが2回、Bobが1回、Charlieが3回の注文を行い、Danaは0件であることがわかります。
このように、COUNTを使ったサブクエリを活用することで、ユーザーごとの詳細な注文数を簡単に集計することができます。次に、特定の条件に基づくデータの集計方法を見ていきましょう。
実用例：特定の条件に基づく集計
次に、特定の条件に基づいてデータを集計する方法を紹介します。ここでは、特定の期間内に行われた注文数を集計する例を示します。
期間内の注文数をカウントするクエリ
特定の期間内に行われた注文数をカウントするためのクエリは次の通りです：
SELECT user_name,
(SELECT COUNT(*)
FROM orders
WHERE orders.user_id = users.user_id
AND order_date >= '2023-01-01'
AND order_date < '2024-01-01') AS order_count
FROM users
ORDER BY user_id;このクエリでは、以下の処理を行っています：
usersテーブルからユーザー名を取得する。- サブクエリで
ordersテーブルから該当するユーザーの注文数を、指定した期間内に限定してカウントする。 - 結果を
order_countとして表示する。
実行結果の例
上のテーブルとデータをそのまま使い、次の1行だけを追加します。CREATEや同じ主キーのINSERTを重ねて実行しないでください：
-- 上の6注文を登録した後に、期間外の注文を1件追加する
INSERT INTO orders (order_id, user_id, order_date)
VALUES (7, 1, '2022-12-31');上記のクエリを実行すると、以下の結果が得られます：
user_name | order_count
------------|-------------
Alice | 2
Bob | 1
Charlie | 3
Dana | 0この結果から、Aliceが2023年内に2回、Bobが1回、Charlieが3回の注文を行い、Danaは0件であることがわかります。
商品別の注文件数：注文ヘッダーと明細を分ける
先ほどのordersにはproduct_id列を定義していないため、orders.product_idを参照することはできません。次は別途products(product_id, product_name)とorder_items(order_id, product_id, quantity)がある設計の例です。各主キー・外部キーは実際の設計に合わせて定義してください。
SELECT p.product_id, p.product_name,
(SELECT COUNT(DISTINCT oi.order_id)
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE oi.product_id = p.product_id
AND o.order_date >= '2023-01-01'
AND o.order_date < '2024-01-01') AS order_count
FROM products p
ORDER BY p.product_id;同じ商品が一つの注文で複数の明細行に分かれる可能性があるなら、注文数はCOUNT(DISTINCT oi.order_id)です。明細行数ならCOUNT(*)、個数ならSUM(quantity)であり、別の指標です。未注文の商品も外側のproductsから出力され、件数は0になります。
事前集計してLEFT JOINする書き方
SELECT u.user_id, u.user_name,
COALESCE(o.order_count, 0) AS order_count
FROM users u
LEFT JOIN (
SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2023-01-01'
AND order_date < '2024-01-01'
GROUP BY user_id
) o ON o.user_id = u.user_id
ORDER BY u.user_id;GROUP BYの集計結果には、注文0件のユーザーの行がありません。LEFT JOINでユーザーを残し、COALESCEで結合できなかった値を0にします。外側でWHERE o.order_count > 0とすると、0件のユーザーは除外される点に注意してください。
よくある集計ミスと性能確認
期間は開始以上・翌期間開始未満の半開区間にすると、日時型でも年末日の途中を取りこぼしにくくなります。文字列の日時解釈やタイムゾーンはDBMSに依存するため、本番では列と一致する型のパラメーターを渡してください。サンプルのDATE列と、時刻を含む列の設計を混同しないようにします。
ユーザー人数をGROUP BY後のCOUNT(*)で数える場合、NULLのuser_idも一つのグループになり得ます。上の例はuser_id IS NOT NULLで除外しています。単一列ならCOUNT(DISTINCT user_id)でも非NULLの人数を数えられます。存在するかだけを確認したいならEXISTSを使えます。ただしEXISTS(SELECT COUNT(*) …)は0件でも集計行があるため、存在判定として誤りになります。
相関COUNT、事前集計JOINのどちらが速いかは、データ量・インデックス・DBMSの実行計画によります。「サブクエリなら効率的」と一律に決めません。結合列や期間条件に合うインデックスを検討し、検証環境で実行計画を確認します。EXPLAIN ANALYZEはクエリを実際に実行するため、本番で無条件に使わないでください。
まとめ
COUNT関数を使ったサブクエリは、SQLにおいて非常に強力なツールです。これを利用することで、複雑なデータ集計や分析を効率的に行うことができます。具体的には、ユーザーごとの注文数のカウントや特定の条件に基づく集計など、実際の業務で役立つクエリを簡潔に記述できます。
サブクエリを理解し、適切に活用することで、データベースの持つ情報を最大限に引き出すことが可能となります。これにより、ビジネスの意思決定を支援するための正確で詳細なデータ分析が実現できるでしょう。
今後は、さらに複雑なクエリや他の集計関数と組み合わせて、より高度なデータ処理に挑戦してみてください。SQLのスキルを磨くことで、データベース管理と分析の効率を大幅に向上させることができます。
以上で、SQLにおけるCOUNTを使ったサブクエリの解説と実用例を終わります。この記事が、皆さんのデータベース操作の一助となれば幸いです。
公式資料と検証範囲
PostgreSQL：集計関数／サブクエリ式・EXISTS／LIMITと順序／EXPLAIN。ユーザー別件数・期間別件数・事前集計JOIN・NULLを除くユーザー数・商品別注文件数の5例をローカルのSQLiteで確認しました。これはサンプル結果の確認であり、各DBMSの性能や本番環境の動作を検証したものではありません。
コメント