Sisense日本公式ブログ

「Infused Analytics」全く新しい第三世代BIツール

最新記事

カテゴリータグ
マーケティングチーム
By
2021年08月21日

SQLの中央値(Median)

  BI

shutterstock_1382997959

 

SQLの中央値(Median)

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

 

--not going to work! (処理できません)

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

 

 

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

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

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

 

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

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

 

1,Redshiftでの中央値

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

 

select median(price) over () as median

from purchases

limit 1

 

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

 

2,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つの値を確実に得ることができます。

 

3,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」を翻訳・加筆修正したものです。

Whatagraph_banner1

 

monday_banner

 

Powtoon_banner

 

BUY On HUBSPOT