【SQL】COUNT関数完全ガイド｜COUNT(*)・COUNT(列)・DISTINCT・条件付きカウント・ウィンドウ関数・NULL挙動まで解説

-- ① COUNT(*) : NULL を含むすべての行数をカウント
SELECT COUNT(*) FROM orders;          -- 結果: 7

-- ② COUNT(列名) : 指定列が NULL でない行数をカウント
SELECT COUNT(amount) FROM orders;     -- 結果: 6（amountがNULLの行6を除く）

-- ③ COUNT(DISTINCT 列名) : 重複を除いた種類数をカウント
SELECT COUNT(DISTINCT customer_id) FROM orders;  -- 結果: 5（101〜105）

-- サンプルテーブル: orders（注文テーブル）
-- order_id | customer_id | status    | amount | region
-- ---------+-------------+-----------+--------+---------
--        1 |         101 | shipped   |   5000 | east
--        2 |         102 | pending   |   3000 | west
--        3 |         101 | shipped   |   8000 | east
--        4 |         103 | cancelled |   2000 | east
--        5 |         102 | shipped   |   6000 | west
--        6 |         104 | pending   |   NULL | north
--        7 |         105 | NULL      |   4000 | east

-- status が NULL の行（行7）が 1 件ある
SELECT
    COUNT(*)          AS count_all,    -- 7 ← NULL行も含む
    COUNT(status)     AS count_status, -- 6 ← statusがNULLの行7を除く
    COUNT(amount)     AS count_amount  -- 6 ← amountがNULLの行6を除く
FROM orders;

-- 結果:
-- count_all | count_status | count_amount
-- ----------+--------------+-------------
--         7 |            6 |            6

-- NULL の行だけを数える（IS NULL）
SELECT COUNT(*) AS null_count
FROM orders
WHERE status IS NULL;   -- 結果: 1

-- NULL でない行だけを数える（IS NOT NULL）
SELECT COUNT(*) AS non_null_count
FROM orders
WHERE status IS NOT NULL;  -- 結果: 6

-- または COUNT(列名) で直接取得（IS NOT NULL と等価）
SELECT COUNT(status) AS non_null_count
FROM orders;               -- 結果: 6

-- status が 'shipped' の注文数
SELECT COUNT(*) AS shipped_count
FROM orders
WHERE status = 'shipped';  -- 結果: 3

-- 特定地域かつ金額が3000以上の件数
SELECT COUNT(*) AS filtered_count
FROM orders
WHERE region = 'east'
  AND amount >= 3000;      -- 結果: 2（行1と行3）

-- 複数条件を OR で
SELECT COUNT(*) AS shipped_or_pending
FROM orders
WHERE status IN ('shipped', 'pending');  -- 結果: 5

-- 非効率: テーブルを3回スキャンしている
SELECT COUNT(*) FROM orders WHERE status = 'shipped';
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders WHERE status = 'cancelled';

-- 効率的: テーブルを1回スキャンするだけ
SELECT
    COUNT(*)                                          AS total,
    COUNT(CASE WHEN status = 'shipped'   THEN 1 END) AS shipped_count,
    COUNT(CASE WHEN status = 'pending'   THEN 1 END) AS pending_count,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_count,
    COUNT(CASE WHEN amount >= 5000       THEN 1 END) AS high_value_count
FROM orders;

-- 結果:
-- total | shipped_count | pending_count | cancelled_count | high_value_count
-- ------+---------------+---------------+-----------------+-----------------
--     7 |             3 |             2 |               1 |               2

-- ステータスごとの件数
SELECT status, COUNT(*) AS cnt
FROM orders
GROUP BY status
ORDER BY cnt DESC;

-- 結果:
-- status    | cnt
-- ----------+----
-- shipped   |   3
-- pending   |   2
-- cancelled |   1
-- NULL      |   1  ← NULLのステータスも1グループとして集計される

-- 2件以上注文した顧客のみ表示
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY order_count DESC;

-- 結果:
-- customer_id | order_count
-- ------------+------------
--         101 |           2
--         102 |           2

-- WHERE は集計前の絞り込み、HAVING は集計後の絞り込み
-- 組み合わせ例: shipped 注文が2件以上の顧客
SELECT customer_id, COUNT(*) AS shipped_count
FROM orders
WHERE status = 'shipped'   -- 先にshippedのみに絞る
GROUP BY customer_id
HAVING COUNT(*) >= 2;

-- ユニークな顧客数（重複する customer_id を除く）
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;  -- 結果: 5（101, 102, 103, 104, 105）

-- ユニークなステータスの種類数（NULLは除外）
SELECT COUNT(DISTINCT status) AS status_types
FROM orders;  -- 結果: 3（shipped, pending, cancelled。NULLは除外）

-- 地域ごとのユニーク顧客数
SELECT region, COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY region
ORDER BY region;

-- 結果:
-- region | unique_customers
-- -------+-----------------
-- east   |               3
-- north  |               1
-- west   |               2

-- 各注文行に「同じ顧客の総注文数」を付与する
SELECT
    order_id,
    customer_id,
    status,
    amount,
    COUNT(*) OVER (PARTITION BY customer_id) AS customer_order_count
FROM orders
ORDER BY customer_id, order_id;

-- 結果:
-- order_id | customer_id | status    | amount | customer_order_count
-- ---------+-------------+-----------+--------+---------------------
--        1 |         101 | shipped   |   5000 |                    2
--        3 |         101 | shipped   |   8000 |                    2
--        2 |         102 | pending   |   3000 |                    2
--        5 |         102 | shipped   |   6000 |                    2
--        4 |         103 | cancelled |   2000 |                    1
--        6 |         104 | pending   |   NULL |                    1
--        7 |         105 | NULL      |   4000 |                    1

-- テーブル全体の件数を各行に付与（ページネーションのトータル件数などに便利）
SELECT
    order_id,
    customer_id,
    COUNT(*) OVER () AS total_count  -- PARTITION BY なし = 全体
FROM orders;
-- 結果: total_count が全行で 7 になる

-- 各注文が「同一顧客の注文全体」に占める金額の割合
SELECT
    order_id,
    customer_id,
    amount,
    COUNT(*) OVER (PARTITION BY customer_id)                          AS orders_in_group,
    ROUND(amount * 100.0
          / SUM(amount) OVER (PARTITION BY customer_id), 1)           AS amount_pct
FROM orders
WHERE amount IS NOT NULL;

-- SQL Server: 20億を超えるレコード数は COUNT(*) だと int オーバーフロー
-- → COUNT_BIG(*) で bigint を返す
SELECT COUNT_BIG(*) AS total_rows
FROM very_large_table;

-- PostgreSQL: 正確な件数より「だいたいの件数」でよい場合の高速代替
SELECT reltuples::bigint AS approx_count
FROM pg_class
WHERE relname = 'orders';  -- テーブル名を指定
-- VACUUM/ANALYZE 後の統計情報をもとにした概算値
-- フルスキャン不要で非常に高速

-- 各列の NULL 件数と NULL 率を一度に確認する
SELECT
    COUNT(*)                                    AS total_rows,
    COUNT(*) - COUNT(customer_id)               AS customer_id_nulls,
    COUNT(*) - COUNT(status)                    AS status_nulls,
    COUNT(*) - COUNT(amount)                    AS amount_nulls,
    ROUND((COUNT(*) - COUNT(amount)) * 100.0
          / COUNT(*), 1)                        AS amount_null_pct
FROM orders;

-- 全顧客数と注文済み顧客数を比較して「注文したことがある顧客の割合」を求める
SELECT
    (SELECT COUNT(DISTINCT customer_id) FROM orders)  AS ordered_customers,
    (SELECT COUNT(*)                   FROM customers) AS total_customers,
    ROUND(
        (SELECT COUNT(DISTINCT customer_id) FROM orders) * 100.0
        / (SELECT COUNT(*) FROM customers),
        1
    )                                                  AS conversion_pct;

-- MySQL / PostgreSQL（DATE関数の書き方が異なる）
-- MySQL:
SELECT DATE(created_at) AS order_date, COUNT(*) AS daily_count
FROM orders
WHERE created_at >= CURDATE() - INTERVAL 30 DAY
GROUP BY DATE(created_at)
ORDER BY order_date;

-- PostgreSQL:
SELECT created_at::date AS order_date, COUNT(*) AS daily_count
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY created_at::date
ORDER BY order_date;