SQLでCOUNTする時のパターン集
データアナリストにとって、SQLのCOUNT関数は最も頻繁に使用する集計関数の一つです。しかし、一見シンプルに見えるCOUNTも、実際の業務では様々な使い方のパターンがあります。この記事では、初級者から中級者向けに、実用的なCOUNTのパターンを実際のサンプルデータを使って解説します。
実際には、基本パターン3つを習得することで、かなりの幅のビジネス分析ニーズをカバーできます。応用パターンは、より複雑な分析要件に対応するための発展形として紹介します。
※本記事では、実装パターンの例示でLLMを用いて記述されている箇所があります。
※他にも抜けているパターンを思いついたら適宜更新していきます
対象読者
- これからデータアナリストとして活動したいと思っている初級者から中級者の方を対象にしています。基本的なSQLの知識（SELECT、WHERE、GROUP BYなど）があることを前提としています。
- また記載しているSQLはGoogleBigQueryのものとなっているので、使用しているツールに合わせて記述文法は読み替える必要があります。ただし基本文法は共通のため、内容自体はツールに依らず適用可能かと思います
使用するサンプルデータ
この記事の一部では、TableauのSuper Storeデータセットを使用して実例を示します。Super Storeは架空の小売店のデータで、以下のような構造を持っています：
- Order ID：注文ID
- Customer ID：顧客ID
- Product ID：商品ID
- Category：商品カテゴリ
- Sub-Category：商品サブカテゴリ
- Sales：売上金額
- Quantity：数量
- Discount：割引率
- Profit：利益
- Order Date：注文日
- Ship Date：出荷日
基本パターン編
【基本1】通常のCOUNT DISTINCT
目的：重複を除いた件数を数える
最も基本的なパターンです。重複する値を除外して、ユニークな値の数を数えます。データアナリストが最も頻繁に使用するパターンで、顧客数、商品数、注文数などあらゆる場面で活用できます。
現場こぼれ話
- DISTINCTをつけ忘れて、カウント数が異常になるのはSQLあるあるです。カウント数が多過ぎるとなった時は、DISTINCTをつけ忘れていないか?まずはチェックする事も多いです
-- 総顧客数を数える
SELECT
COUNT(DISTINCT customer_id) as unique_customers -- 重複する顧客IDを除外してカウント
FROM superstore;
-- 商品カテゴリごとのユニーク顧客数
SELECT
category, -- グループ化の基準となるカテゴリ
COUNT(DISTINCT customer_id) as unique_customers -- カテゴリ内でのユニーク顧客数
FROM superstore
GROUP BY category -- カテゴリごとに集計
ORDER BY unique_customers DESC; -- 顧客数の多い順で並び替え
このパターンで解決できる分析例：
- 月別新規顧客数
- 商品カテゴリ別の購入者数
- 地域別のアクティブユーザー数
- キャンペーン別のリーチユーザー数
他業種での応用例：
金融業界：
-- 支店ごとのアクティブユーザー数
SELECT
branch_name, -- 支店名
COUNT(DISTINCT account_id) as active_accounts -- 重複を除いたアカウント数
FROM transactions
WHERE transaction_date >= CURRENT_DATE - INTERVAL 30 DAY -- 過去30日以内の取引
GROUP BY branch_name;
ヘルスケア業界：
-- 診療科ごとの患者数
SELECT
department, -- 診療科
COUNT(DISTINCT patient_id) as unique_patients -- ユニーク患者数
FROM medical_records
WHERE visit_date >= '2024-01-01' -- 対象期間の指定
GROUP BY department;
【基本2】条件付きCOUNT DISTINCT（CASE WHEN）
目的：特定の条件を満たすレコードのみを対象にカウントする
CASE WHEN文と組み合わせることで、条件に合致する場合のみフラグを立ててカウントできます。顧客セグメンテーションや条件別分析に必須のパターンです。
現場こぼれ話
- "〇〇の条件をカウントしたい" というフォーマットの集計依頼では、このパターンで処理する事が多いです。【条件式を定義し条件を満たした行のみをカウントする】という動作をマスタできると条件式の表現次第で、かなり幅広い範囲の集計タスクに対応できます
-- 利益が出ている注文をした顧客数と、損失が出た注文をした顧客数
SELECT
COUNT(DISTINCT -- 重複を除いてカウント
CASE WHEN profit > 0 -- 利益が正の場合のみ
THEN customer_id -- 顧客IDを返す
END -- 条件に合わない場合はNULL（カウント対象外）
) as profitable_customers,
COUNT(DISTINCT
CASE WHEN profit < 0 -- 損失が出ている場合のみ
THEN customer_id
END
) as loss_customers
FROM superstore;
-- カテゴリごとに、高額購入者（売上1000以上）の数を集計
SELECT
category, -- カテゴリで集計
COUNT(DISTINCT
CASE WHEN sales >= 1000 -- 売上が1000以上の場合のみ
THEN customer_id -- 高額購入者としてカウント
END
) as high_value_customers,
COUNT(DISTINCT customer_id) as total_customers -- 全顧客数（比較用）
FROM superstore
GROUP BY category -- カテゴリごとにグループ化
ORDER BY high_value_customers DESC; -- 高額購入者数の多い順
このパターンで解決できる分析例：
- 購入金額別の顧客分類
- 行動パターン別のユーザー分析
- 成果指標達成者の割合分析
- リスクレベル別の分類
他業種での応用例：
SaaS業界：
-- ユーザーのエンゲージメント分析
SELECT
COUNT(DISTINCT
CASE WHEN login_frequency >= 20 -- 月20回以上ログイン
THEN user_id END
) as highly_engaged_users,
COUNT(DISTINCT
CASE WHEN login_frequency BETWEEN 5 AND 19 -- 月5-19回ログイン
THEN user_id END
) as moderately_engaged_users
FROM user_activity_monthly;
EC業界：
-- 購入チャネル別のロイヤルカスタマー分析
SELECT
sales_channel,
COUNT(DISTINCT
CASE WHEN purchase_frequency >= 5 -- 頻繁購入者
AND avg_order_value >= 100 -- かつ高額購入者
THEN customer_id END
) as loyal_customers,
COUNT(DISTINCT customer_id) as total_customers
FROM customer_behavior
GROUP BY sales_channel;
【基本3】ウィンドウ関数との組み合わせ
目的：累積カウントや移動平均的なカウントを行う
ウィンドウ関数と組み合わせることで、時系列での累積カウントや順序を考慮した分析が可能になります。顧客の成長や変化を追跡する分析に強力なパターンです。
現場こぼれ話
- 基本2のパターンで対応不可なものとして、累積や特定の対象(この例だとユーザー)に集計範囲を絞った集計ができないというパターンがあります。こうした場合も、本パターンのようにWINDOW関数を組み合わせることで対応可能です
- イメージとしては、"PARTITION BY で絞り込んだ行の範囲に限定した行だけをカウントする" という感じです
-- 顧客ごとの注文回数と、その時点での累積ユニーク商品数
SELECT
customer_id, -- 顧客ID
order_date, -- 注文日
product_id, -- 商品ID
COUNT(*) OVER ( -- ウィンドウ関数でカウント
PARTITION BY customer_id -- 顧客ごとに区切り
ORDER BY order_date -- 注文日順で並び替え
) as order_sequence, -- その顧客の何回目の注文か
COUNT(DISTINCT product_id) OVER ( -- 累積ユニーク商品数
PARTITION BY customer_id -- 顧客ごとに区切り
ORDER BY order_date -- 日付順で処理
ROWS BETWEEN UNBOUNDED PRECEDING -- 最初の行から
AND CURRENT ROW -- 現在の行まで
) as cumulative_unique_products -- 累積で購入したユニーク商品数
FROM superstore
ORDER BY customer_id, order_date; -- 顧客ID、注文日順で並び替え
このパターンで解決できる分析例：
- 顧客の購買行動の変化追跡
- ユーザーの機能利用進捗
- 時系列での成長分析
- 順序を考慮したランキング分析
他業種での応用例：
教育業界：
-- 学生の学習進捗追跡
SELECT
student_id,
completion_date,
course_id,
COUNT(DISTINCT course_id) OVER ( -- 累積受講コース数
PARTITION BY student_id
ORDER BY completion_date
ROWS UNBOUNDED PRECEDING
) as courses_completed
FROM student_courses
ORDER BY student_id, completion_date;
製造業：
-- 生産ラインでの累積品質管理
SELECT
production_line,
inspection_date,
COUNT(DISTINCT
CASE WHEN quality_grade = 'A'
THEN batch_id END
) OVER ( -- 累積優良バッチ数
PARTITION BY production_line
ORDER BY inspection_date
ROWS UNBOUNDED PRECEDING
) as cumulative_grade_a_batches
FROM quality_control
ORDER BY production_line, inspection_date;
応用パターン編
ここから紹介する3つのパターンは、上記の基本パターンの組み合わせや発展形です。より複雑な分析要件に対応できますが、やっている事自体は基本パターンの組み合わせで構成されています
3つのパターンの応用で、かなり複雑な集計も幅広く対応できることの例示となっています。
【応用1】複数条件での組み合わせカウント
（基本2の発展形：複数のCASE WHEN条件を組み合わせ）
目的：複雑な条件での顧客セグメンテーション
-- 顧客を購買行動で分類してカウント（基本2の複数条件版）
SELECT
COUNT(DISTINCT
CASE WHEN total_orders >= 5 -- 注文回数が5回以上
AND avg_order_value >= 500 -- かつ平均注文額が500以上
THEN customer_id
END
) as vip_customers, -- VIP顧客数
COUNT(DISTINCT
CASE WHEN total_orders >= 2 -- 注文回数が2回以上5回未満
AND total_orders < 5
AND avg_order_value >= 200 -- かつ平均注文額が200以上
THEN customer_id
END
) as regular_customers, -- 一般顧客数
COUNT(DISTINCT
CASE WHEN total_orders = 1 -- 1回のみの注文
OR avg_order_value < 200 -- または平均注文額が200未満
THEN customer_id
END
) as light_customers -- ライト顧客数
FROM (
SELECT
customer_id, -- 顧客ID
COUNT(DISTINCT order_id) as total_orders, -- 顧客ごとの総注文回数（基本1）
AVG(sales) as avg_order_value -- 顧客ごとの平均注文金額
FROM superstore
GROUP BY customer_id -- 顧客ごとに集計
) customer_summary; -- サブクエリの結果をもとに分類（基本2）
【応用2】期間比較でのカウント
（基本1 + 基本2の組み合わせ：時系列比較分析）
目的：期間を比較した成長分析
-- 年度比較：新規顧客数と既存顧客数（基本1 + 基本2の組み合わせ）
SELECT
YEAR(order_date) as year, -- 年度で集計
COUNT(DISTINCT customer_id) as total_customers, -- その年の総顧客数（基本1）
COUNT(DISTINCT
CASE WHEN customer_id NOT IN ( -- 過去に取引履歴がない顧客を検索（基本2）
SELECT DISTINCT customer_id
FROM superstore s2
WHERE YEAR(s2.order_date) < YEAR(superstore.order_date) -- 現在の年より前の年
) THEN customer_id
END
) as new_customers, -- 新規顧客数
COUNT(DISTINCT customer_id) - COUNT(DISTINCT
CASE WHEN customer_id NOT IN (
SELECT DISTINCT customer_id
FROM superstore s2
WHERE YEAR(s2.order_date) < YEAR(superstore.order_date)
) THEN customer_id
END
) as returning_customers -- 既存顧客数（計算による算出）
FROM superstore
GROUP BY YEAR(order_date) -- 年度ごとにグループ化
ORDER BY year; -- 年度順で並び替え
【応用3】割合を含むカウント分析
（基本1 + 基本2 + 計算の組み合わせ：構成比分析）
目的：構成比や割合と組み合わせた分析
-- カテゴリごとの顧客分布と割合（基本1 + 基本2 + 割合計算）
SELECT
category, -- 商品カテゴリ
COUNT(DISTINCT customer_id) as unique_customers, -- カテゴリ別ユニーク顧客数（基本1）
ROUND( -- 小数点第2位まで四捨五入
COUNT(DISTINCT customer_id) * 100.0 / -- 顧客数を100倍して
(SELECT COUNT(DISTINCT customer_id) -- 全体の顧客数で割る（基本1をサブクエリで）
FROM superstore), 2
) as customer_percentage, -- 全体に対する顧客割合
COUNT(DISTINCT
CASE WHEN profit > 0 -- 利益が出ている取引（基本2）
THEN customer_id END
) as profitable_customers, -- 利益を出している顧客数
ROUND(
COUNT(DISTINCT
CASE WHEN profit > 0
THEN customer_id END
) * 100.0 / -- 利益顧客数を100倍
COUNT(DISTINCT customer_id), 2 -- カテゴリ内総顧客数で割る
) as profitable_customer_rate -- カテゴリ内利益顧客率
FROM superstore
GROUP BY category -- カテゴリごとに集計
ORDER BY unique_customers DESC; -- 顧客数の多い順で並び替え
終わりに
SQLのCOUNT関数は、基本パターン3つ（通常DISTINCT/条件付きDISTINCT/ウィンドウ関数DISTINCT）を習得することで、ほとんどのビジネス分析ニーズをカバーできる強力なツールです。
応用パターンは、これらの基本パターンの組み合わせや発展形に過ぎません。まずは基本パターンを確実に身につけ、実際の業務データで練習してみることが重要かと思います。
継続的な学習と実践を通じて、データアナリストとしての分析力を向上させていきましょう。