SQLで中央値(MEDIAN)を計算する

16076 ワード
Window関数 MySQL SQL 分析関数 MySQL テキストリンク

はじめに

SQLで中央値を算出する方法は、こちらの記事の問４の解のようににググれば見つかりますが、以下の記事の投稿者のようにビッグデータに対して実行すれば、自己結合を使っているためパフォーマンスが非常に悪いと思われます。

そこで、パフォーマンスを改善する方法として、件数の集計を行ってから自己結合する方法を思いつきましたので紹介します。

解説

シンプルなサンプルデータとして、件数が偶数の偶数件データと奇数の奇数件データを用意します。

偶数件データ

値
1
1
2
3

奇数件データ

値
1
1
2
3
3

(1)まず最初に値毎にデータ件数を集計する

SELECT
  値
, COUNT(*) AS 件数
FROM 偶数件データ
GROUP BY 値

上記のテーブルをビューやテンポラリテーブルまたはサブクエリとして、以降のＳＱＬでは集計データと表現する。

偶数件データの実行結果

値 件数
1 2
2 1
3 1

奇数件データの実行結果

値 件数
1 2
2 1
3 2

(2)件数の累積を計算する

SELECT
  Z.値
, Z.件数
, SUM(A.件数) AS 累積件数
FROM 集計データ AS Z,
     集計データ AS A
WHERE Z.値 >= A.値
GROUP BY
  Z.値
, Z.件数

偶数件データの実行結果

値 件数 累積件数
1 2 2
2 1 3
3 1 4

奇数件データの実行結果

値 件数 累積件数
1 2 2
2 1 3
3 2 5

(3)累積前件数(該当レコードの件数を加算する前の件数)と中間件数(全体の半分に該当する件数)を合わせて表示する

SELECT
  Z.値
, Z.件数
, SUM(A.件数) - Z.件数 AS 累積前件数
, SUM(A.件数) AS 累積件数
, (SELECT SUM(件数)/2 FROM 集計データ) AS 中間件数
FROM 集計データ AS Z,
     集計データ AS A
WHERE Z.値 >= A.値
GROUP BY
  Z.値
, Z.件数

偶数件データの実行結果

値 件数 累積前件数 累積件数 中間件数
1 2 0 2 2
2 1 2 3 2
3 1 3 4 2

奇数件データの実行結果

値 件数 累積前件数 累積件数 中間件数
1 2 0 2 2.5
2 1 2 3 2.5
3 2 3 5 2.5

(4)累積前件数 ≦ 中間件数 ≦ 累積件数 を満たすレコードを抽出する

SELECT *
FROM 
	(SELECT
	  Z.値
	, Z.件数
	, SUM(A.件数) - Z.件数 AS 累積前件数
	, SUM(A.件数) AS 累積件数
	, (SELECT SUM(件数)/2 FROM 集計データ) AS 中間件数
	FROM 集計データ AS Z,
	     集計データ AS A
	WHERE Z.値 >= A.値
	GROUP BY
	  Z.値
	, Z.件数) AS SUB
WHERE 中間件数 BETWEEN 累積前件数 AND 累積件数

偶数件データの実行結果

値 件数 累積前件数 累積件数 中間件数
1 2 0 2 2
2 1 2 3 2

※１レコードまたは２レコードが対象となります。

奇数件データの実行結果

値 件数 累積前件数 累積件数 中間件数
2 1 2 3 2.5

※必ず１レコードのみが対象となります。

(5) (4)で抽出したレコードの値の平均を算出する

SELECT AVG(値) AS 中央値
FROM 
    (SELECT
      Z.値
    , Z.件数
    , SUM(A.件数) AS 累積件数
    , (SELECT SUM(件数) FROM 集計データ) AS 総件数
    FROM 集計データ AS Z,
         集計データ AS A
    WHERE Z.値 >= A.値
    GROUP BY
      Z.値
    , Z.件数) AS SUB
WHERE 総件数/2 BETWEEN 累積件数 - 件数  AND 累積件数

※一部表現をスッキリするために、計算順序を変えてあります。

偶数件データの実行結果

中央値
1.5

奇数件データの実行結果

中央値
2

こちらの記事の問４の実行結果

(3)の実行結果が以下の通りとなり、黄色の２レコードが(4)の抽出対象となり、結果は220000と235000の平均で227500となります

参考までに分析関数を使用したSQLの例は以下の通りとなります

WITH SUB AS (
  SELECT
    値
  , 件数
  , SUM(件数) OVER( ORDER BY 値) AS 累積件数
  , SUM(件数) OVER() AS 総件数
  FROM 集計データ
)

SELECT AVG(値) AS 中央値
FROM SUB
WHERE 総件数/2 BETWEEN 累積件数 - 件数 AND 累積件数

良く考えると、分析関数を使用した場合は自己結合が不要になるので、集計データをわざわざ使用する必要もないので以下の内容で可能です

WITH SUB AS (
  SELECT
    値
  , ROW_NUMBER() OVER (ORDER BY 値) AS 連番
  , COUNT(*) OVER () AS 総件数
  FROM 元テーブル
)

SELECT AVG(値) AS 中央値
FROM SUB
WHERE 総件数/2 BETWEEN 連番 - 1 AND 連番