Sisense日本公式ブログ

SQLの中央値（Median）

購入テーブルで注文の最小値、最大値、平均値を見つけるのは簡単です。しかし、価格の中央値はどうでしょうか？中央値はデータベースが計算するにはもっと難しいので、通常は以下のような組み込み関数はありません。

--not going to work! （処理できません）

select median(price) from purchases （価格の中央値を購入額テーブルから選択）

なぜ中央値は難しいのか？

標準的な集約関数（min、max、countなど）では、データを1回通過させるだけで結果を得ることができ、その際に現在のmin/max/countを保存します。列にインデックスが付けられている場合は、行を見なくても最小値や最大値を見つけることができます。

中央値（Median）はアルゴリズム的にはるかに複雑であるため、そのようには機能することはできません。中央値とは、真ん中の値のことです。中央値を見つけるためには、データを並べ替える必要があります。

異なるデータベースで中央値を求める

中央値は最小値、最大値、カウントよりも難しく、標準的な集計関数ではないので、計算するためにはもう少し工夫が必要です。いくつかの異なるデータベースでの方法を紹介します。

１，Redshiftでの中央値

Redshiftチームは、中央値ウィンドウ関数をリリースしました。これにより中央値を求める最も簡単な構文の一つとなりました。

select median(price) over () as median

from purchases

limit 1

注意「limit 1」：中央値はウィンドウ関数であり、集約関数ではないので、表の各行に対して1つの値を返します。

２，Postgresでの中央値

Postgresで独自の関数を定義するのが好きな方は、Postgres Wikiに中央値の定義があります。これをSQLで実行しウィンドウ関数row_number()ですべての行に番号を付けることで、Postgresが中央値を見つけてくれるようにしましょう。

まず、CTEですべての行をソートして番号を付け、後に役立つカウントを行います。

with ordered_purchases as (

  select

      price,

      row_number() over (order by price) as row_id,

      (select count(1) from purchases) as ct

  from purchases

)

そして、真ん中の1行または2行を見つけて、その値を平均します。

select avg(price) as median

from ordered_purchases

where row_id between ct/2.0 and ct/2.0 + 1

where節では、betweenが境界を含むため、値が偶数の場合は真ん中の2つの値を、奇数の場合は真ん中の1つの値を確実に得ることができます。

３，MySQLでの中央値

MySQL にはウィンドウ関数はありませんが、変数がありますので、それを使って同じ結果を得ることができます。

まず、2つの変数を設定します。1つは行数用、もう1つは先ほどのrow_idの役割を果たします。

set @ct := (select count(1) from purchases);

set @row_id := 0;

そして先ほどと同じように、真ん中の1つか2つの値を平均化します：

select avg(price) as median

from (select * from purchases order by price)

where (select @row_id := @row_id + 1)

between @ct/2.0 and @ct/2.0 + 1

row_id := @row_id + 1 構文は、各行の @row_id カウンタを単純にインクリメントします。Postgresとは異なり、変数によってその場でrow_idを計算することができるので、row_idを持つ行の一時的な結果セットを構築する必要はありません。

最頻値の設定

平均値と中央値の記事は、最頻値なしでは完成しません。列の最頻値（最も頻度の高い値と定義）は、簡単なグループ分けとカウントで求めることができます。

select price as mode

from purchases

group by 1

order by count(1) desc

limit 1

このクエリでは、最頻値としてテーブル内のcount()が最大の価格が得られます。

これで、Redshift、Postgres、MySQLデータベースのSQLで中央値を計算する方法がわかりましたね。

Sisenseは無料版も提供していますので是非最先端の第三世代BIツールに触れてみてください。

※本記事は、「Medians in SQL」を翻訳・加筆修正したものです。