Oracle SQL 集計関数のNULL対策とDISTINCTの使い方

「データの合計値が想定より少ない」「平均値が計算と合わない」。
Oracle Database でデータ分析を行う際、こうしたズレの最大の原因は NULL の扱いにあります。

本記事では、Oracle 19c 環境を対象に、集計関数（SUM, AVG, MAX, MIN, COUNT）における NULL の挙動と、重複を除外する DISTINCT オプションの正しい使い方を解説します。検証用テーブルを使って実際に挙動を確認し、集計ミスのないSQLを目指しましょう。

---

結論：集計時のルール一覧

まずは集計関数における NULL と DISTINCT の基本ルールを押さえましょう。

1. NULLの扱い（基本は「無視」）

集計関数は、基本的に NULL を「存在しないデータ」として扱います。計算対象の行からも、分母（行数）からも除外されます。唯一の例外は COUNT(*) です。

関数	NULLの扱い	備考
SUM / AVG	無視する	値がNULLの行は計算式に含まれない
MAX / MIN	無視する	NULL以外の値から最大/最小を探す
COUNT(列名)	無視する	値が入っている行数のみカウント
COUNT(*)	含む	NULLだけの行であっても1行としてカウント

OracleデータベースのNULLについて

Oracleデータベースでは、NULLは「値が不明」や「無の状態」を意味します。これは「ゼロ」とは異なり、データが入れられていない状態を示します。この記事では、NULLの特徴や使用方法、さらにはNULLを正しく扱うための具体的なSQLの例と...

oracle-master00000.com

2024.12.23

2. DISTINCTの扱い（重複排除）

関数の中に DISTINCT を記述すると、重複データを取り除いた後に集計が行われます。

• 記述例：SUM(DISTINCT SAL)
• 処理順：重複排除 → 集計

---

3. 検証環境の構築

挙動を正しく理解するために、検証用の EMP_TEST テーブルを作成します。
ご自身の学習環境（SQL*Plus や SQL Developerなど）で実行可能です。

前提:

• Oracle Database 19c (Enterprise/Standard)
• ユーザーには CREATE TABLE 権限と表領域のクォータが必要です。

-- 1. 検証用テーブルの作成
CREATE TABLE EMP_TEST (
    EMP_ID  NUMBER PRIMARY KEY,    -- 従業員ID
    NAME    VARCHAR2(50),          -- 名前
    JOB     VARCHAR2(50),          -- 職種
    SAL     NUMBER,                -- 給与（NULLあり）
    DEPT_ID NUMBER,                -- 部署ID
    BONUS   NUMBER                 -- ボーナス（NULLあり）
);

-- 2. テータの挿入（NULLを含むデータをあえて投入）
INSERT INTO EMP_TEST VALUES (1, 'Alice', 'Manager', 3000, 10, NULL);
INSERT INTO EMP_TEST VALUES (2, 'Bob', 'Clerk', NULL, 20, NULL);
INSERT INTO EMP_TEST VALUES (3, 'Charlie', 'Analyst', 4000, 10, 500);
INSERT INTO EMP_TEST VALUES (4, 'David', 'Clerk', 2500, 30, 300);
INSERT INTO EMP_TEST VALUES (5, 'Emma', 'Salesman', 2000, 30, 200);
INSERT INTO EMP_TEST VALUES (6, 'Frank', 'Manager', 3000, 20, NULL);
INSERT INTO EMP_TEST VALUES (7, 'Grace', 'Analyst', 4000, 20, 500);
INSERT INTO EMP_TEST VALUES (8, 'Helen', 'Salesman', 2000, 30, 200);
INSERT INTO EMP_TEST VALUES (9, 'Ian', 'Clerk', 2500, 20, NULL);
INSERT INTO EMP_TEST VALUES (10, 'Jane', 'Clerk', NULL, 10, NULL);

COMMIT;

---

4. 実践：集計関数とNULLの挙動

実際にデータを使って、NULL がどのように計算結果に影響するかを確認します。

4-1. SUM（合計）と COUNT（件数）

SAL（給与）列には NULL が2件含まれています（全10行中、値があるのは8行）。

SELECT 
    SUM(SAL)   AS TOTAL_SAL,    -- NULLを除外して合計
    COUNT(SAL) AS COUNT_SAL,    -- NULLでない行数
    COUNT(*)   AS COUNT_ALL     -- 全行数
FROM EMP_TEST;

実行結果と解説

SQL> SELECT
  2      SUM(SAL)   AS TOTAL_SAL,
  3      COUNT(SAL) AS COUNT_SAL,
  4      COUNT(*)   AS COUNT_ALL
  5  FROM EMP_TEST;

 TOTAL_SAL  COUNT_SAL  COUNT_ALL
---------- ---------- ----------
     23000          8         10

• TOTAL_SAL (23000): NULL以外の8人の給与を足した結果です。NULLは0として扱われるのではなく、単に無視されます。
• COUNT_SAL (8): 給与情報がある従業員の数です。
• COUNT_ALL (10): テーブル全体の行数です。

4-2. 【重要】AVG（平均）の落とし穴

平均値の計算では、NULL の扱いが「分母」に影響するため特に注意が必要です。

SELECT 
    AVG(SAL)           AS AVG_IGNORE_NULL,  -- A: NULLを無視した平均
    AVG(NVL(SAL, 0))   AS AVG_INCLUDE_NULL  -- B: NULLを0とみなした平均
FROM EMP_TEST;

実行結果

SQL> SELECT
  2      AVG(SAL)           AS AVG_IGNORE_NULL,
  3      AVG(NVL(SAL, 0))   AS AVG_INCLUDE_NULL
  4  FROM EMP_TEST;

AVG_IGNORE_NULL AVG_INCLUDE_NULL
--------------- ----------------
           2875             2300

• A: 2875 (23000 ÷ 8人)
  • 給与をもらっている人だけの平均額です。
• B: 2300 (23000 ÷ 10人)
  • NVL(SAL, 0) でNULLを0に変換したため、分母が全従業員数(10)になりました。「給与未定の人も含めた全員の平均」を出したい場合はこちらが正解です。

---

5. 実践：DISTINCTを使った重複排除集計

DISTINCT キーワードを使うと、重複を除外したユニークな値だけで集計できます。「種類数」を知りたい場合などに有効です。

5-1. SUM + DISTINCT

同じ給与額の人を1人としてカウントし、合計を求めます（あまり実用的ではありませんが、挙動理解のために実施します）。

SELECT 
    SUM(SAL)          AS NORMAL_SUM,    -- 通常の合計
    SUM(DISTINCT SAL) AS UNIQUE_SUM     -- 重複を除外した合計
FROM EMP_TEST;

実行結果

SQL> SELECT
  2      SUM(SAL)          AS NORMAL_SUM,
  3      SUM(DISTINCT SAL) AS UNIQUE_SUM
  4  FROM EMP_TEST;

NORMAL_SUM UNIQUE_SUM
---------- ----------
     23000      11500

計算の内訳（UNIQUE_SUM）:
ユニーク値: 2000, 2500, 3000, 4000
合計: 11500

5-2. COUNT + DISTINCT（頻出）

「いくつの部署が存在するか？」など、種類の数を数える際によく使います。

SELECT 
    COUNT(DEPT_ID)          AS ALL_ROWS,    -- 全行のカウント
    COUNT(DISTINCT DEPT_ID) AS UNIQUE_DEPTS -- 部署の種類数
FROM EMP_TEST;

実行結果

SQL> SELECT
  2      COUNT(DEPT_ID)          AS ALL_ROWS,    -- 全行のカウント
  3      COUNT(DISTINCT DEPT_ID) AS UNIQUE_DEPTS -- 部署の種類数
  4  FROM EMP_TEST;

  ALL_ROWS UNIQUE_DEPTS
---------- ------------
        10            3

解説:

DEPT_ID には 10, 20, 30 の3種類の値が繰り返し入っています。COUNT(DISTINCT …) はその種類数である「3」を返します。

---

6. トラブルシューティング・注意点

パフォーマンスへの影響

DISTINCT を使用すると、データベース内部で「ソート（並べ替え）」や「ハッシュ処理」が発生し、重複を探すコストがかかります。数百万件以上のテーブルで安易に COUNT(DISTINCT column) を多用すると、クエリのレスポンスが悪化する原因になります。

ORAエラーの回避

DISTINCT は集計関数のカッコの中に書きます。

• 正: COUNT(DISTINCT col)
• 誤: DISTINCT COUNT(col) … 文法エラーになります。

---

7. FAQ：よくある質問

Q1. NULL を特定の値（例えば「未設定」）としてカウントしたいのですが？

A. NVL または COALESCE 関数を使用します。

COUNT(DISTINCT NVL(JOB, ‘未設定’)) とすることで、NULLも「未設定」という1つの種類としてカウント可能です。

Q2. 複数の列に対してDISTINCT集計はできますか？

A. 基本的な集計関数内では1つの列（または式）のみ指定可能です。

COUNT(DISTINCT col1 || col2) のように文字列結合して回避するか、サブクエリを使用する必要があります。

Q3. AVG関数でNULLを0として計算すべきですか？

A. ビジネス要件によります。

「実績値の平均（NULLは除外）」なのか「全体の期待値（NULLは0）」なのかを確認してください。明確でない場合、レポートには両方併記するのが親切です。

---

まとめ

1. 基本ルール: 集計関数は COUNT(*) 以外、すべて NULL を無視 する。
2. 平均の罠: AVG を使うときは、NULL を無視すべきか NVL で 0 にすべきか必ず検討する。
3. 重複排除: DISTINCT はカッコの中に書く（例: COUNT(DISTINCT 列名)）。
4. 実機確認: 計算が合わないときは、NULL データが紛れていないか確認する。

本記事は Oracle Database 19c を対象に解説します（他バージョンは画面や既定値が異なる場合があります）。

[参考]
Oracle Database SQL言語リファレンス 19c

Oracleのファンクション(関数)を解説

Oracle Databaseのファンクション(関数)は、業務を効率化し、コードをシンプルでメンテナンスしやすくするための強力なツールです。本記事では、初心者がつまずきやすいポイントを丁寧に解説し、実務で即役立つ活用例を多数紹介します。この...

oracle-master00000.com

2024.12.29

はじめてのOracle SQL：SELECT文の基本とSCOTTスキーマでの実践

Oracleデータベースを学ぶ上で最初に触れることになるのが SELECT文です。このSELECT文を正しく理解することは、今後のSQLスキル向上において重要な土台となります。本記事では、以下の構成で解説します。SELECT文の基本構文と実...

oracle-master00000.com

2024.09.25