MS Access Tips/Sample and VBA and Blog customize etc...

中央値(Median)と最頻値(Mode)をクエリで求める

クエリで中央値や最頻値を求めたいのですが、ExcelではMEDIANやMODEといったワークシート関数で求められますが、Accessではそのような関数がないので求められません。どうしたらいいでしょう。

という質問があったのでSQLを作成してみました。

難易度:

サンプルテーブル

テーブル名 T_Data
フィールドデータ型 
IDオートナンバー型主キー
F1数値型 

上記のテーブルで、F1フィールドのデータの中央値、最頻値を求める。

中央値(Median)

結果は1件のみです。

最頻値(Mode)

最頻値が複数ある場合は、すべて返します。

中央値クエリの解説

サブクエリを含むSQLでは解読できないという方の為に分解して解説します。

トップ値を50%に設定して、F1の昇順で上位半数を出力

Q_Median1

この最大値を求める。

Q_Median2

データ件数が奇数の場合は、これでちょうど真ん中の中央値が求められるのですが、
偶数の場合は、中央の境界の前になるので、境界の後も求めて、平均を取る必要があります。

そこで、同じくトップ値を50%に設定して、F1の降順で上位半数を出力

Q_Median3

この最小値を求める。

Q_Median4

Q_Median3 と Q_Median4 をユニオンクエリで連結。

Q_Median5

Q_Median5 の平均を求める。

Q_Median

以上、6つのクエリの組み合わせで中央値を取得できます。
これをサブクエリを使って一つにまとめたのが最初のクエリです。

最頻値クエリの解説

集計クエリでF1でグループ化して件数を求めます。

Q_Mode1

このクエリで、件数で降順のトップ値 1 を求めます。

Q_Mode1

以上です。こちらはクエリ2段で求められます。

拍手する

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/336-ab93a253
該当の記事は見つかりませんでした。