◀ 21.

MySQLにおける中央値(Median)の導き方(バージョン8未満)

▶
この記事の要点
  • MySQL 8 未満で中央値 (Median) を計算する SQL
  • MySQL 8 未満は組込関数なし → 自己結合 + COUNT で同等の SQLを組む
  • 基本アイデア: 各値について「自分以下の数」と「自分以上の数」が概ね半分になる値が中央値
  • MySQL 8.0ではROW_NUMBERとCOUNTのウィンドウ関数で中央位置を求める。MariaDBのMEDIAN/PERCENTILE_CONTと混同しない

 

自己結合で中央値を求める考え方

奇数個は中央1値、偶数個は中央2値の平均で中央値を計算する図

MySQL 8未満でウィンドウ関数を使わず中央値を求める方法の一つが、次の自己結合です。元のSQLは派生テーブルの別名がなく実行できないため、末尾へAS median_valuesを補いました。この元の例はNULLを含まない数値を前提とします。NULL除外版とMySQL 8.0向けの例は後半に示します。

SELECT
    AVG(target_val)
FROM
    (
        SELECT
            t1.target_val
        FROM
            test_table t1,
            test_table t2
        GROUP BY
            t1.target_val
        HAVING SUM(
            CASE
                WHEN t2.target_val >= t1.target_val THEN 1
                ELSE 0
            END
        ) >= COUNT(*) / 2
    AND SUM(
            CASE
                WHEN t2.target_val <= t1.target_val THEN 1
                ELSE 0
            END
        ) >= COUNT(*) / 2
    ) AS median_values
;

※「達人に学ぶSQL徹底指南書」を参考

 

考え方は、上位半分 + 1レコードと下位半分 + 1レコードをそれぞれ取得して、それぞれに共通する中央の2レコードを足して2で割る。
奇数の場合は共通する中央のレコードが1レコードのみになるのでAVGする必要はない(してもよい)。
 

NULLと重複値を扱う自己結合版

ここでは中央値の対象をNULL以外の数値と定義します。重複値は件数に含め、偶数件では中央2値の平均を使います。DISTINCTで元データの重複を除去すると別の統計になるため注意してください。元の説明でいう中央2レコードは、奇数件なら1レコードです。

SELECT AVG(target_val) AS median
FROM (
    SELECT t1.target_val
    FROM test_table AS t1
    CROSS JOIN test_table AS t2
    WHERE t1.target_val IS NOT NULL AND t2.target_val IS NOT NULL
    GROUP BY t1.target_val
    HAVING SUM(CASE WHEN t2.target_val >= t1.target_val THEN 1 ELSE 0 END) >= COUNT(*) / 2
       AND SUM(CASE WHEN t2.target_val <= t1.target_val THEN 1 ELSE 0 END) >= COUNT(*) / 2
) AS median_values;

t1とt2の両方でNULLを除外します。値ごとに集計し、その値以上・以下の件数が全体の半分以上になる値を選び、AVGを求めます。同じt1値の重複で結合行数も増えますが、比較件数とCOUNTは同じ倍率で増えるため、今回の重複ケースでも中央値を選べています。これは大量データに適した実装という意味ではありません。

MySQL 8.0なら行番号で中央位置を求める

MySQL 8.0にはMariaDBのMEDIANやPERCENTILE_CONTと同じ組み込み構文はありません。次はROW_NUMBERとCOUNTのウィンドウ関数を使う方法です。この記事のタイトルは8未満向けですが、製品・版を取り違えないため8.0の別案を併記します。

WITH ranked AS (
    SELECT target_val,
           ROW_NUMBER() OVER (ORDER BY target_val, id) AS rn,
           COUNT(*) OVER () AS n
    FROM test_table
    WHERE target_val IS NOT NULL
)
SELECT AVG(target_val) AS median
FROM ranked
WHERE rn IN (FLOOR((n + 1) / 2), FLOOR((n + 2) / 2));

NULLを除いた件数をnとし、1始まりの中央位置はFLOOR((n+1)/2)とFLOOR((n+2)/2)です。n=3なら両方2、n=4なら2と3になります。idは一意な主キーを想定し、同値の順序も確定させています。実テーブルの一意キーへ置き換えてください。部門別等の中央値には対象を分けた集計が別途必要で、この例はテーブル全体です。

検証用のテーブルとデータ

次は使い捨てのローカル検証DB用です。既存のtest_tableへそのまま実行せず、実環境ではテーブル名・型・集計範囲を確認してください。本番DDLや既存データの変更は今回行っていません。

CREATE TABLE test_table (
    id INT PRIMARY KEY,
    target_val DECIMAL(12,2) NULL
);
INSERT INTO test_table VALUES (1,1),(2,2),(3,4),(4,8);

上の2種類のSELECTは、このデータではともに中央値3を返します。表示される小数桁はDBや式の型によって異なります。DECIMALの桁数は実データの範囲と精度に合わせて設計します。

入力値期待する中央値
1, 3, 93
1, 2, 4, 83
1, 1, 2, 2, 2, 92
NULL, 1, 32（NULLを除外）
-9, -3, 0, 2-1.5
1.25, 2.752
空集合・NULLのみNULL。0へ勝手に置き換えない

MariaDBのMEDIANは別の構文

MariaDB 11.4で次のウィンドウ関数を確認しました。NULL除外後の各行に同じ中央値を返すため、ここではDISTINCTで結果を1値にしています。元データの重複値を削除する処理とは異なります。空集合ならこのSELECTは0行で、上のAVG版が1行のNULLを返す点とは違います。

SELECT DISTINCT MEDIAN(target_val) OVER () AS median
FROM test_table
WHERE target_val IS NOT NULL;

MariaDBのPERCENTILE_CONTにもOVER句が必要です。製品名・版・関数構文を確認せず、MySQL 8へ流用しないでください。

実行確認と性能上の注意

隔離したMySQL 8.0.46 / MariaDB 11.4.13で、空・NULL・奇数/偶数・重複・負数・小数など12データ組を用意し、自己結合版と行番号版の結果をPythonの中央値と照合しました。派生テーブル別名なしのエラー1248も再現しています。MySQL 5.7等の旧版実行や本番の実データ性能は未検証です。

自己結合は組み合わせが大きくなり、大量データでは負荷が増えます。先に集計の対象条件を決め、検証環境で実行計画・件数・時間を確認してください。高速化目的で本番へ無条件にインデックスやキャッシュを追加する説明ではありません。

一次資料: MySQL 8.0のウィンドウ関数、MySQLの派生テーブルと別名、MariaDB MEDIAN（2026年10月確認）。

Post Share
子ページ

子ページはありません

同階層のページ
  1. ダウンロード&インストール方法(Windows)
  2. インストール方法(Linux)
  3. コマンド一覧
  4. SQL
  5. データ型
  6. 関数
  7. 管理ツール
  8. 設定
  9. パフォーマンスチューニング関連
  10. エクスポートおよびインポート
  11. エラー&トラブル
  12. 文字コードの確認
  13. 実行中の SQL の状態確認およびプロセスキルの方法
  14. パスワードの無効化設定
  15. root ユーザーの初期パスワード確認方法
  16. rootユーザーのパスワード変更方法
  17. LIMIT, OFFSET の始まりと挙動
  18. mysqlのバージョン確認方法
  19. MySQLで実行計画を表示する方法
  20. レプリケーションのステータス確認方法
  21. 中央値の導き方(バージョン8未満)
  22. 階層SQL(バージョン8未満)
  23. パーセンタイルの導き方
  24. 特定スキーマの全テーブルの全カラム情報を取得する方法
2026-10-07 13:49:45 atom 1248

人気ページ

最近更新/作成されたページ